SQL_窗口函数

一、常用函数

1、row_number

        使用说明：row_number() over(分组字段 排序字段)

                        分组字段，排序字段 非必填

        使用场景：

                生成编号

                分组后每个组取N条（相同数据编号不同）

                统计用户最大的连续登录天数

select user_id,max(days)
from
(	-- 统计相同日期出现次数就是连续登录天数
	select user_id,m,count(1) days
	from
	(	-- 日期-排名 = 同一天
		select user_id,login_day,n,date_sub(login_day, interval n day) as m
		from
		(	-- 根据用户分组，登录日期升序，生成排名序号
			select user_id,login_day,
			row_number() over(partition by user_id order by login_day) n 
			from
            (	-- 用户，日期 去重
				select user_id,login_day from user_log group by user_id,login_day
			) as tmp
		) tmp1
	) tmp2 group by user_id,m
) tmp3 group by user_id

2、rank

        使用说明：rank() over(分组字段 排序字段)

                         分组字段，排序字段 非必填

        使用场景：

                生成编号

                分组后每个组取N条（相同数据编号相同）

                统计成绩排名

PS：rank()并列排名之后跳过编号，dense_rank()并列排名之后不会跳过编号

-- 根据条件取top
select class,name,source,ranking
from
(
    -- 班级，同学，分数，排名
	select class,name,score,
	rank() over(partition by class order by score) ranking
	from school
) tmp where ranking<3

3、sum

        使用说明：sum(聚合字段) over(分组字段 排序字段)

                        分组字段，排序字段 非必填

        使用场景：

                求和

                分组统计累加值，总量值

                统计每个商品每天销售额，以及每天累计销售额

-- 订单，日期，当日金额，累加金额，总金额，所有订单的交易额
select 
order_id,event_day,money,
sum(money) over(partition by user_id order by login_day) as add_money,
sum(money) over(partition by user_id) as total_money
sum(money) over() as total_order_money
from 
(
    -- 订单，日期，当日金额
	select order_id,event_day,sum(money) as money 
    from table_name group by order_id,event_day
) tmp

4、聚合开窗函数

        使用说明：函数(聚合字段) over(分组字段 排序字段)

                        分组字段，排序字段 非必填

                        同sum函数逻辑一致

        使用场景：

                想要一次性查询 详情+聚合 数据时使用

                例如：统计每天每个订单交易额以及当日占总交易额比例

                        统计每天每个订单交易额以每日均值

-- 计数
count(money) over(partition by ... order by ...)
-- 最大值
max(money) over(partition by ... order by ...)
-- 最小值
min(money) over(partition by ... order by ...)
-- 求和
sum(money) over(partition by ... order by ...)
-- 均值
avg(money) over(partition by ... order by ...)       

5、特殊开窗函数

        使用说明：函数(聚合字段) over(分组字段 排序字段)

                        分组字段，排序字段 非必填

        使用场景：

                想要一次性查询 当前行+前一行 数据时使用

                例如：统计日环比

                        每一天数据和第一天/最后一天数据对比

-- 组内第一条
first_value(money) over(partition by … order by …)
-- 组内最后一条
last_value(money) over(partition by … order by …)

-- 拉链式 前一条的值
lag(money) over(partition by … order by …)
-- 拉链步长，前头补0，默认一步 不补0
lag(money,2,0)
-- 拉链式 后一条的值
lead(money) over(partition by … order by …)
-- 拉链步长，后头补0 默认一步 不补0
lead(money,2,0)

二、其他函数

1、cume_dist

        使用说明：函数(聚合字段) over(分组字段 排序字段)

                        分组字段，排序字段 存在才有意义

        适用场景：

                返回某个值相对于分区中的位置

                分组后位于总数据条数的第几条

                例如：抽取每个班成绩排名前50%的同学

        相似函数：percent_rank

                区别：cume_dist 0< 生成的值 <=1，percent_rank 0=< 生成的值 <=1

-- 排名前50%的同学
select class,name,source,ranking
from
(
    -- 班级，同学，分数，排名
	select class,name,score,
	cume_dist() over(partition by student order by score) ranking
	from school
) tmp where ranking>0.5

2、ntile

        使用说明：函数(分桶数) over(分组字段 排序字段)

        适用场景：

                对数据平均分成几份，每份数据同一个编号

                例如：将一个班的学生随机分三组

select name,ntile(3) over() n from class

3、nth_value

        使用说明：函数(字段，n) over(分组字段 排序字段)

        适用场景：

                返回组内第n个字段的值

三、常见问题

        1、划分session

select
uid,session_id,count(1),concat(min(time),"~",max(time))
from
(
    # sum划分session
	select uid,time,concat(uid, '-', sum(flag) OVER (partition by uid order by time)) as session_id
	from (
        # 计算本次请求时间距离上次时间差，超时1，否则0
		select uid,time,if(time - lag(time) over (partition by uid order by time) > 30, 1, 0) as flag
		from mydb.session
	) t
)t1 group by uid,session_id