本文已参与「新人创作礼」活动，一起开启掘金创作之路。
46.关于rank()窗口做减法
select
log_id,
rank() over(order by log_id) -log as grp #错误
from logs
select
min(log_id) start_id,
max(log_id) end_id
from
(
select
log_id,
rank() over(order by log_id) as grp
from logs
)kk
group by kk.log_id - grp #必须在外面,而且必须大减小,否则会报错什么bigint出问题的情况
order by min(log_id)
47.连接连接再连接
select s1.student_id, s1.student_name, s2.subject_name, count(e.subject_name) attended_exams
from Students s1
join Subjects s2
left join
Examinations e #做了笛卡尔积之后还能再连接一次
on s1.student_id=e.student_id and s2.subject_name=e.subject_name
group by s1.student_id, s2.subject_name
order by s1.student_id, s2.subject_name
48.这也是连接的一个妙用:不断往上找领导
select distinct e1.employee_id
from Employees e1,Employees e2, Employees e3
where e1.manager_id=e2.employee_id and e2.manager_id=e3.employee_id
and e3.manager_id=1 and e1.employee_id !=1
49.统计妙用:
(
select spend_date,user_id,sum(amount) as amount,
if(count(*)=1,platform,both) as platform #如果有两个平台都有那就是both,如果没有平台也是both
from Spending
group by spend_date,user_id
)
50.mysql中时间最好还是做diff,如果直接相减可能会导致意想不到的后果
51.leetcode1225题
在一个函数中还可以做窗口,相当于压缩着写了
# Write your MySQL query statement below
select state as period_state,min(dt) as start_date,max(dt) as end_date
from
(
select
*,
#获得到所有的时间差,其语义为:从某一时间点开始
#因为有成功失败之分且数据都是连续的,同时即使有后续紧接上去的也会成功地切分出来
subdate(dt,row_number() over(partition by state order by dt)) as dif
# dt-row_number() over(partition by state order by dt) as dif #不行
#mysql中时间最好还是做diff,如果直接相减可能会导致意想不到的后果
from
(
select fail_date as dt ,'failed' as state from failed
union all
select *,'succeeded' from succeeded
)a2
where dt>='2019-01-01' and dt<='2019-12-31'
)t2
group by state,dif
order by dt
52.有了窗口函数中分组,就不需要再别的变量上再做group by了
select
gender,day,sum(score_points) over(partition by gender order by day asc) as total
from scores
order by gender,day
