sql --- 窗口函数

交流qq： 2499496272

row_number() over()——分组TOPN

例子：

select
 id,age,name,sex
 from (
 select id,age,name,sex,
 row_number() over(partition by sex order by age desc) as rankfrom t_rownumber) tmpwhere rank<=2;

sum() over()——级联求和

sum()over()的累加范围指定语法：

• sum() over(partition by x order by y rows between 8 preceding and
  current row)
• sum() over(partition by x order by y rows between 8 preceding and 5
  following)
• sum() over(partition by x order by y rows between unbounded preceding
  and 5 following)
• sum() over(partition by x order by y rows between unbounded preceding
  and unbounded following)

select
 name,month,amt,
 sum(amt) over(partition by name order by month rows between unbounded preceding and current row) as accumulate
 from t_tmp;

3. 窗口分析函数综合应用案例

select 
distinct shopfrom
 (select shop,count(1) as cnt
 from
 (
 select shop,s_date,amt,date_sub(s_date,rn) as diff
 from(
 select shop,s_date,amt,row_number() over(partition by shop order by s_date) as rnfrom t18
 ) o1
 ) o2 
 group by shop,diff having cnt>=4
 ) o3;