SQL笔记

over使用方法

1. rank(), row_number(), dense_rank() over (partition by a order by b) 表示按照a group by 后，对b排序加顺序号。
2. avg(cost) over(order by orderdate rows between 3 preceeding and 3 following) 表示算上当日以及之前2天，以及之后3天的平均开销。
3. 计算中位数方法：
   select cast(row_number() over(partition by a order by b asc) as signed) as rnk1
   ,cast(row_number() over(partition by a order by b desc) as signed) as rnk2
   from table
   where abs(rnk1-rnk2)=0 or rnk1=rnk2
4. 连续登录天数超过多少天写法：

select distinct userid
from
(
select userid
,logindate
,lead(logindate,5) over(partition by userid order by logindate) as date2
from login
where month(login)=7
) a 
where datediff(date2,logindate) = 5

5. 用户最大连续登录天数
   datesub函数：datesub(date, interval 日期差 day)

select userid
,consec_days as max_consec_days
from
(
select userid
,consec_days
,row_number() over (partition by userid order by consec_days desc) as rank0
from
(
select userid
,count(distinct startdate) as consec_days
from
(
select userid
,logindate
,date_sub(logindate, interval dayrank day) as startdate
from
(
select userid
,logindate
,row_number over (partition by userid order by logindate) as dayrank
from login 
where month(login)=1
) a
) b
group by 1
) c 
) d 
where rank0= 1