SQL 刷题

题库来源

题源

leetcode180. 连续出现的数字

题目请大家自己链接上的东西吧，下面是我写的解法。

with detail as (
          select 1 as id , 1 as num 
union all select 2 as id , 1 as num
union all select 3 as id , 1 as num 
union all select 4 as id , 2  as num 
union all select 5 as id , 1  as num 
union all select 6 as id , 2  as num 
union all select 7 as id , 2  as num 
), detail_rn as (
select id , num 
      ,row_number() over(partition by num order by id) as rn
from detail 
), detail_rn_cnt as (
select id , num 
       ,rn
       ,count(1) over(partition by rn - id 
                      order by rn  rows between unbounded preceding and unbounded following ) as g
  from detail_rn 
)
select *
 from detail_rn_cnt
where g >= 3

下面我来讲讲我的解题思路。
解这种连续性的问题，要做三步：

1. 使用 row_number 构建一个大的等差数列——A。关键的是 A 的顺序要按照连续字段来进行排序。
2. 然后在在连续出现事件子集上构建一个小的等差数列——subA。这个的关键是根据连续的事件进行分组，然后根据连续字段进行排序。
3. 然后对 A 和 subA 做一个相减。假设， A = A1,…,An 等差为 d1 ，subA = subA1,…,subAn 等差为 subd1，那么 A - subA = d1-subd1,…,d1-subd1, 从结果上看 A - subA ， 成为一个等差为 0 的数列。我就可以根据 A - subA 不同的结果来对再分组，并求出分组中的记录数据大于等于 3 的，大功告成。

将上面的套路放到这个题目上来，id 就是我们的大的等差数列，也是我们的联系字段，num 是事件字段，我以 num 作为分组，做出了子等差数列。然后将两者做查。然后得到了字段 g , g 就是事件连续发生的分组，然后对分组做了计数，最后取出连续发生次数大于三的分组。

leetcode182. 查找重复的电子邮箱

我的答案

with detail as (
select 1 as id ,  'a@b.com' as email 
union all select 2  as id , 'c@d.com' as email
union all select 3  as id , 'a@b.com' as email
), detail_rn as (
select id , email 
      ,row_number() over( partition by email order by id ) as rn 
from detail 
)
select *
 from detail_rn 
where rn > 1

解题思路：

我解题思路很简单，给不同的邮箱做一个分组序号，当序号最大值大于 1 的，这个邮箱就是重复的，这个里面只重复了一次，如果重复多次，我们可以做一次去重操作。

leetcode197. 上升的温度

这个题目还可以加点难度。如果我们想取出气温连续增长开始和结束日期

+---------+------------------+------------------+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+---------+------------------+------------------+
|       1 |       2015-01-01 |               10 |
|       2 |       2015-01-02 |               20 |
|       3 |       2015-01-03 |               30 |
|       4 |       2015-01-04 |               40 |
|       5 |       2015-01-05 |               20 |
|       6 |       2015-01-06 |               30 |
|       7 |       2015-01-07 |               40 |
|       8 |       2015-01-08 |               50 |
|       9 |       2015-01-09 |               10 |
|       10 |       2015-01-10 |               10 |
+---------+------------------+------------------+

答案如下：

with detail as (
	select  1  as id ,'2015-01-01' as d         ,10 as t  
	union all select  2 as id,'2015-01-02' as d ,20 as t
	union all select  3 as id,'2015-01-03' as d ,30 as t
	union all select  4 as id,'2015-01-04' as d ,40 as t
	union all select  5 as id,'2015-01-05' as d ,20 as t
	union all select  6 as id,'2015-01-06' as d ,30 as t
	union all select  7 as id,'2015-01-07' as d ,40 as t
	union all select  8 as id,'2015-01-08' as d ,50 as t
	union all select  9 as id,'2015-01-09' as d ,10 as t
	union all select  10 as id,'2015-01-10' as d,10 as t
), detail_lag as (
select id , d , t 
       ,lag(t , 1 , 1) over(order by id ) as last_t 
  from detail
), detail_lag_case as (
select id , d , t, last_t 
      ,case when t > last_t then 1 else 0 end as continues_ind
from detail_lag 
), detail_lag_case_div as (
select id , d , t, last_t ,continues_ind
     , id - (row_number() over( partition by continues_ind order by id )) as g 
from detail_lag_case
) 
select id , d , t, last_t ,continues_ind 
, max(d) over(partition by g order by id) as end_dat 
, min(d) over(partition by g order by id) as end_dat    
  from detail_lag_case_div
where continues_ind = 1 

这个题和 leetcode601. 体育馆的人流量、leetcode603. 连续空余座位（SQL) 是一类题目。