mysql中的窗口函数

1. 窗口函数基本语法

<窗口函数> OVER ( [ PARTITION BY <列清单> ]ORDER BY <排序用列清单> )

• partition by 划分的范围被称为窗口
• order by 决定窗口范围内的数据以什么样的方式排序
• 至于窗口函数与group by的区别：
  • 两个order by的区别，窗口函数中的order by只是决定着窗口里的数据的排序方式，普通的order by决定查询出的数据以什么样的方式整体排序；
  • 窗口函数可以在保留原表中的全部数据之后，可以对某些字段做分组排序或者计算，而group by只能保留与分组字段聚合的结果；
  • 在加入窗口函数的基础上SQL的执行顺序也会发生变化 FROM — WHERE — GROUP BY — HAVING — SELECT — WINDOW — ORDER BY — LIMIT

2. 窗口函数类别

• 静态窗口函数
  • rank() 间断的组内排序 1，1，3，4，4，6
  • dense_rank() 不间断的组内排序 1，1，2，2，3
  • row_number() 当前行在组内的序号 1，2，3，4，5
• 聚合函数
  • sum(字段名) 和
  • avg(字段名) 平均
  • percent_rank(字段名) 累计百分比
  • cume_dist(字段名) 累计分布值
• 取值函数
  • first_value() 返回分组内第一个值
  • last_value() 返回分组内最后一个值
  • nth_value() 返回分组内第N行
  • lag() 从当前行开始往前去第N行，默认为null
  • ntile() 返回当前行在分组内截止当前行的第N行
• 窗口数据集
  • over()
  • partiton by 分区参数
  • order by 排序参数
  • rows/range between … preceding and … 框架参数

3. 滑动窗口函数

3.1 Preceding 移动平均

• ROW 2 PRECEDING 当前行和前面两行取平均

• select product_id,sale_price,
  avg(sale_price) over(order by product_id row 2 preceding) as moving_avg
  from product
  
  
  product_id		sale_price 			moving_avg
  001 			1000 				1000
  002				500					750    (1000+500)/2
  003         	4000				1833   (1000+500+4000)/3
  004    			3000				2500   (500+4000+3000)/3
  
  

3.2 Following

• 与Preceding 相反 当前行和后面几行的平均

3.3 两者结合

• select product_id,sale_price,
  avg(sale_price) over(order by product_id  
  					 row between 1 preceding and  1 following) as moving_avg
  from product
  
  
  product_id		 sale_price 	     moving_avg
  001 			 1000 					750    (1000+500)/2
  002				 500					1833   (1000+500+4000)/3
  003        		 4000					2500   (500+4000+3000)/3
  004    			 3000					3500   (3000+4000)/2
  
  

4. 实战演练

4.1 组内排序问题

• 获取当前（to_date=‘9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水salary**(可能有多个)**

select emp_no,salary
from(
    -- 当薪水第二的人有多个时只会输出一个
    -- select emp_no,salary,row_number() over(order by salary desc) t 
  	-- 当薪水第二的人有多个时全部输出
    select emp_no,salary,rank() over(order by salary desc) t
    from salaries
    where to_date='9999-01-01'
)
where t=2

• 请你写出一个sql语句查询每个日期登录新用户个数，并且查询结果按照日期升序排序

select tmp.date,sum(case tmp.rk when 1 then 1 else 0 end) as new
from (select user_id,date,rank() over(partition by user_id order by date) as rk from login) tmp
group by tmp.date

4.2 连续登陆问题

• 假设有一张含两列**(用户id、登陆日期)的表**，查询每个用户连续登陆的天数、最早登录时间、最晚登录时间和登录次数。
  • 首先要对数据进行去重，防止同一个用户一天之内出现连续登录的情况；
  • 假如一个用户是连续登录的话，用login_time-窗口函数的排序后得到的日期应该是一样的，连续登录的用户前后之间的时间差就是一个差值为1的等差数列；

-- 计算出辅助列
select a.user_id, a.date, a.rk, date_sub(a.date,interval a.rk day) as assist
from
(
   select user_id,date(login_time) as date,
   row_number() over(partition by user_id order by login_time) as rk
   from user_login
)as a

user_id  	date  			rk 		assist
1	        2016-10-31 		1		2016-10-30
1			2016-11-01		2 		2016-10-30
1			2016-11-09  	3       2016-11-06

	
-- 按照id和辅助列作为分组信息 分到一组的就是连续登录的用户 
-- 最小的日期就是最早的登陆日期，最大的日期就是最近的登陆日期，对每个组内的用户进行计数就是用户连续登录的天数
select b.user_id, min(b.date) as '最早登陆日期', max(b.date) as '最近登陆日期',
count(b.date) as '登陆次数'
from
(
   select distinct a.user_id, a.date, a.rk, date_sub(a.date,interval a.rk day) as assist
    from
    (
      select user_id,date(login_time) as date,
    	row_number() over(partition by user_id order by login_time) as rk
      from user_login
    )as a
)b
group by b.user_id,b.assist
'''
user_id  最早登陆日期 		最晚登陆日期      登陆次数  
1	     2016-10-30 	    2016-11-01      2
1		 2016-11-09	    	2016-11-09      1

'''

• 求解连续登陆5天的用户

  -- 上述方法再一层嵌套
  select c.user_id
  from 
  (
    select b.user_id, count(b.date) as '登陆次数'
    from
    (
      select distinct a.user_id, a.date, a.rk, date_sub(a.date,interval a.rk day) as assist
      from
      (
        select user_id,date(login_time) as date,
      	row_number() over(partition by user_id order by login_time) as rk
        from user_login
      )as a
    )b
  	group by b.user_id,b.assist
  )c
  where c.'登陆次数' == 5
  
  
  -- lead 查找每个用户5天后的登陆日期是多少
  select distinct user_id, date(login_time) as date,
  	lead(date(login_time),4) over(partiton by user_id order by login_time) as fifth_login_date
  from user_login
  
  '''
  user_id   		date   				fifth_login_date
  1				2016-10-31			2016-11-23
  1				2016-11-01  		2016-11-24
  1				2016-11-09  		null
  1				2016-11-10  		null
  1				2016-11-23  		null
  1				2016-11-24  		null
  '''
  
  select distinct b.user_id
  from
  (
  		select user_id, a.date, a.fifth_login_date ,DATEDIFF(a.fifth_login_date , a.date) +1 as diff_date
    	from
    	(
      	select distinct user_id, date(login_time) as date,
  			lead(date(login_time),4) over(partiton by user_id order by login_time) as fifth_login_date
  			from user_login
      )as a
  )as b
  where b.diff_date >= 5