SQL技巧笔记(一):连续3人的连号问题—— LeetCode601.体育馆的人流量

本文主要是介绍SQL技巧笔记(一):连续3人的连号问题—— LeetCode601.体育馆的人流量,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

SQL 技巧笔记

前言:我发现大数据招聘岗位上的应聘流程都是需要先进行笔试,其中占比很大的部分是SQL题目,经过一段时间的学习之后,今天开了一个力扣年会员,我觉得我很有必要去多练习笔试题目,这些题目是有技巧性的,很贴近生活!

Tips:我很享受独自做出题目的感觉,也很喜欢和大家分享自己的思路!我会继续努力,遇到有趣的题目,独特的思路会和大家多多交流!

文章目录

  • SQL 技巧笔记
    • 一、连续 3 人的连号问题
      • 1. 题目来源
      • 2. 题目描述
      • 3. 题目理解
      • 4. 思路顺序
        • (1) 筛选每行的人数大于或等于 `100`
        • (2) 找出 id,前一个 id,后一个 id
        • (3) 找出三个id之间的关系
        • (4) 找出五个id之间的关系
      • 5. 提交答案

一、连续 3 人的连号问题

1. 题目来源

  • LeetCode 601.体育馆的人流量
  • 困难型题目
  • 网易公司的笔试题

2. 题目描述

表:Stadium

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| visit_date    | date    |
| people        | int     |
+---------------+---------+
visit_date 是该表中具有唯一值的列。
每日人流量信息被记录在这三列信息中:序号 (id)、日期 (visit_date)、 人流量 (people)
每天只有一行记录,日期随着 id 的增加而增加

编写解决方案找出每行的人数大于或等于 100id 连续的三行或更多行记录。

返回按 visit_date 升序排列 的结果表。

查询结果格式如下所示。

示例 1:

输入:
Stadium 表:
+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 1    | 2017-01-01 | 10        |
| 2    | 2017-01-02 | 109       |
| 3    | 2017-01-03 | 150       |
| 4    | 2017-01-04 | 99        |
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
+------+------------+-----------+
输出:
+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
+------+------------+-----------+
解释:
id 为 5、6、7、8 的四行 id 连续,并且每行都有 >= 100 的人数记录。
请注意,即使第 7 行和第 8 行的 visit_date 不是连续的,输出也应当包含第 8 行,因为我们只需要考虑 id 连续的记录。
不输出 id 为 2 和 3 的行,因为至少需要三条 id 连续的记录。

数据源:

Create table If Not Exists Stadium (id int, visit_date DATE NULL, people int);
Truncate table Stadium;
insert into Stadium (id, visit_date, people) values ('1', '2017-01-01', '10');
insert into Stadium (id, visit_date, people) values ('2', '2017-01-02', '109');
insert into Stadium (id, visit_date, people) values ('3', '2017-01-03', '150');
insert into Stadium (id, visit_date, people) values ('4', '2017-01-04', '99');
insert into Stadium (id, visit_date, people) values ('5', '2017-01-05', '145');
insert into Stadium (id, visit_date, people) values ('6', '2017-01-06', '1455');
insert into Stadium (id, visit_date, people) values ('7', '2017-01-07', '199');
insert into Stadium (id, visit_date, people) values ('8', '2017-01-09', '188');

3. 题目理解

  • 需求一:编写解决方案找出每行的人数大于或等于 100

  • 需求二:且 id 连续的三行或更多行记录。

  • 需求三:返回按 visit_date 升序排列 的结果表。


4. 思路顺序

(1) 筛选每行的人数大于或等于 100

代码:

SELECT*
FROM Stadium WHERE people >=100

效果:发现 id 为 5, 6, 7, 8 满足至少连 3 号

在这里插入图片描述


(2) 找出 id,前一个 id,后一个 id

代码:

SELECTid,LAG(id,1) OVER(ORDER BY id) as pre_id, # 前一个idLEAD(id,1) OVER(ORDER BY id) as next_id, # 后一个idvisit_date,peopleFROM Stadium WHERE people >=100

效果:发现最前面的id 的前一个id为null,最后一个id的后一个id为null

在这里插入图片描述


(3) 找出三个id之间的关系

代码:

with t1 as(SELECTid,LAG(id,1) OVER(ORDER BY id) as pre_id, # 前一个idLEAD(id,1) OVER(ORDER BY id) as next_id, # 后一个idvisit_date,peopleFROM Stadium WHERE people >=100
)
select id,visit_date,people from t1
where
(id = pre_id + 1 and id = next_id - 1) OR # 当前 id 是连续序列的中间部分
(next_id = id + 1 and pre_id is null ) OR # 当前 id 是连续序列的最开始部分
(pre_id = id - 1 and next_id is null)    # 当前 id 是连续序列的最结束部分
order by id;

效果:发现原本需要的 5 居然不见了,明显找三者关系条件远远不够

在这里插入图片描述


(4) 找出五个id之间的关系

代码:

with t1 as(SELECTid,LAG(id,1) OVER(ORDER BY id) as pre_id, # 前一个idLAG(id,2) OVER (ORDER BY id) as pre_2_id, # 前两个idLEAD(id,1) OVER(ORDER BY id) as next_id, # 后一个idLEAD(id,2) OVER(ORDER BY id) as next_2_id, # 后两个idvisit_date,peopleFROM Stadium WHERE people >=100
)
select id,visit_date,people from t1
where
(id = pre_id + 1 and id = next_id - 1) OR # 当前 id 是连续序列的中间部分
(pre_id is null and next_id = id + 1 and next_2_id = id + 2 ) OR # 当前 id 是连续序列的最开始部分
(next_id = id + 1 and next_2_id = id + 2 ) OR # 当前 id 是连续序列的最开始部分
(id = pre_id + 1 and next_id is NULL and pre_2_id = id - 2) OR  # 当前 id 是连续序列的最结束部分
(pre_id = id - 1 and pre_2_id = id - 2)    # 当前 id 是连续序列的最结束部分
order by id;

效果:答案正确,3 个id的联系需要考虑极端情况,所以一共需要 5个 条件!


5. 提交答案

效果展示:经过20分钟思考,解题结果提交通过!

对比官方:官方的答案很简略,不过我觉得自己想出来的思路很有趣哦!

# 官方答案select distinct t1.*
from stadium t1, stadium t2, stadium t3
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
and
((t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1)  -- t1, t2, t3or(t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1) -- t2, t1, t3or(t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2) -- t3, t2, t1
)
order by t1.id;

这篇关于SQL技巧笔记(一):连续3人的连号问题—— LeetCode601.体育馆的人流量的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/779878

相关文章

Ilya-AI分享的他在OpenAI学习到的15个提示工程技巧

Ilya(不是本人,claude AI)在社交媒体上分享了他在OpenAI学习到的15个Prompt撰写技巧。 以下是详细的内容: 提示精确化:在编写提示时,力求表达清晰准确。清楚地阐述任务需求和概念定义至关重要。例:不用"分析文本",而用"判断这段话的情感倾向:积极、消极还是中性"。 快速迭代:善于快速连续调整提示。熟练的提示工程师能够灵活地进行多轮优化。例:从"总结文章"到"用

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点: 1.确保子表中的某个字段(外键)只能引用父表中的有效记录2.主表中的列被删除时,子表中的关联列也会被删除3.主表中的列更新时,子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展,我们的业务数据越来越多,单纯使用 MySQL 已经不能满足我们的数据查询需求,例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后,随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步,我们目前的解决方案是建立数据中间表。把需要检索的业务数据,统一放到一张M

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数,谈性能都是耍流氓。MySQL没有限制单表最大记录数,它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB,才推荐分库分表。性能由综合因素决定,抛开业务复杂度,影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考,并非铁律。 博主曾经操作过超过4亿行数据

好题——hdu2522(小数问题:求1/n的第一个循环节)

好喜欢这题,第一次做小数问题,一开始真心没思路,然后参考了网上的一些资料。 知识点***********************************无限不循环小数即无理数,不能写作两整数之比*****************************(一开始没想到,小学没学好) 此题1/n肯定是一个有限循环小数,了解这些后就能做此题了。 按照除法的机制,用一个函数表示出来就可以了,代码如下

hdu1043(八数码问题,广搜 + hash(实现状态压缩) )

利用康拓展开将一个排列映射成一个自然数,然后就变成了普通的广搜题。 #include<iostream>#include<algorithm>#include<string>#include<stack>#include<queue>#include<map>#include<stdio.h>#include<stdlib.h>#include<ctype.h>#inclu

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题,本文将一起从代码层去分析为什么没有建立索引? 开源ERP项目地址:https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置

MySQL数据库宕机,启动不起来,教你一招搞定!

作者介绍:老苏,10余年DBA工作运维经验,擅长Oracle、MySQL、PG、Mongodb数据库运维(如安装迁移,性能优化、故障应急处理等)公众号:老苏畅谈运维欢迎关注本人公众号,更多精彩与您分享。 MySQL数据库宕机,数据页损坏问题,启动不起来,该如何排查和解决,本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志,排查哪个表(表空间

poj2406(连续重复子串)

题意:判断串s是不是str^n,求str的最大长度。 解题思路:kmp可解,后缀数组的倍增算法超时。next[i]表示在第i位匹配失败后,自动跳转到next[i],所以1到next[n]这个串 等于 n-next[n]+1到n这个串。 代码如下; #include<iostream>#include<algorithm>#include<stdio.h>#include<math.

购买磨轮平衡机时应该注意什么问题和技巧

在购买磨轮平衡机时,您应该注意以下几个关键点: 平衡精度 平衡精度是衡量平衡机性能的核心指标,直接影响到不平衡量的检测与校准的准确性,从而决定磨轮的振动和噪声水平。高精度的平衡机能显著减少振动和噪声,提高磨削加工的精度。 转速范围 宽广的转速范围意味着平衡机能够处理更多种类的磨轮,适应不同的工作条件和规格要求。 振动监测能力 振动监测能力是评估平衡机性能的重要因素。通过传感器实时监