窗口函数-分析函数

• 1 lead(x,y,z) 函数:领先,找行号更大的数据 返回当前行后面的值
• 2 lag(x,y,z) 函数:落后,找行号更小的数据 返回当前行之前的值
• 3 FIRST_VALUE(expr) 返回指定列的第一个值
• 4 LAST_VALUE(expr) 返回指定列的最后一个值
• 5 (巨重要)nth_value(x,n) 函数返回X列,按指定顺讯的第n个值
• 小结

1 lead(x,y,z) 函数:领先,找行号更大的数据 返回当前行后面的值

LEAD(x,y,z) 函数:   领先,找行号更大的数据   返回当前行后面的值
										x: 列名  y:偏移量(如果y =2,则说明要以当前行为基准,向后移动两行为返回值)
										z: 默认值时null ,如果传入z ,则默认值是z
LEAD(x) over()    1 最后一列没有下一列结果显示null
									2 lead(x) x的值 与排序的列可以不同


栗子:
统计id 为1的网站，每天访问的人数以及下一天访问的人数- 返回字段：`day`日期，`users`访问人数，`lead` 下
一天访问人数
 							
SELECT
		day,
		users,
		LEAD(users) over(order by day) as `下一天访问日期人数`
FROM
statistics
WHERE website_id = 1;
实际用途!!!!!!!!!!!!!!!! 
从业务逻辑来看 这可以很容易的告诉我们有关网站的很多信息
① 正数: 上升期
②负数: 找到下滑原因

使用LEAD()函数计算增量
SELECT 
		day,
		clicks,
		LEAD(clicks) over(order by day) as `下一天的点击量`,
		clicks - LEAD(clicks) over(order by day)  as `点击差值`	
from 
statistics
WHERE website_id =2;


需求：统计id为1的网站，每日收入，后一天收入，以及每日收入的环比。
SELECT
		revenue,
		LEAD(revenue) over(ORDER BY day) as `下一天收入`,
		LEAD(revenue) over(ORDER BY day) -revenue as `环比`		
FROM
statistics
WHERE
website_id = 1;

需求：统计id为2的网站，在2016年5月1日到5月14日之间，每天的用户访问数量以及7天后的用户访问数量
需要注意，最后7行最后一列会返回NULL，因为最后7行没有7日后的数据。

SELECT 
		day,
		users,
		lead(users,7) over(ORDER BY day) as 	`七天后的用户访问量`
FROM
statistics
WHERE website_id = 2 and day BETWEEN '2016-05-01' AND '2016-05-14';

2 lag(x,y,z) 函数:落后,找行号更小的数据 返回当前行之前的值

lag(x) 函数         落后,找行号更小的数据  返回当前行之前的值
										x: 列名  y:偏移量(如果y =2,则说明要以当前行为基准,向前移动两行为返回值)
										z: 默认值时null ,如果传入z ,则默认值是z
lag(x) over()     1 最后一列没有下一列结果显示null
									2 lag(x) x的值 与排序的列可以不同


需求：统计id为3的网站每天的点击数量，前一天的点击数量
SELECT 
	day,
	clicks,
	LAG(clicks,1,-1) over(ORDER BY day) as `前一天的连击量`
FROM
statistics
WHERE website_id =3;


需求：统计id = 3的网站每日广告收入以及三天前的广告收入

SELECT
	day,
	revenue,
	LAG(revenue,3,-1) over(ORDER BY day) as 	`三天前的广告收入`
FROM
statistics
WHERE website_id =3;


转化率定义：转化率= 点击次数 clicks / 展示次数 impressions * 100
需求：统计id = 1的网站，5月15日至5月31日，每天点击次数 clicks，展示次数 impressions，转化率
（conversion）和前一天的转化率（previous_conversion）
SELECT
	day,
	clicks,
	impressions,
	clicks / impressions * 100 as `转化率` ,
	LAG(clicks) over(ORDER BY day) /LAG(impressions) over(ORDER BY day) * 100 as `前一天转化率`
FROM
statistics
where website_id = 1 and day BETWEEN '2016-05-15' AND '2016-05-31' ;

3 FIRST_VALUE(expr) 返回指定列的第一个值

需求：统计id为2的网站每天用户访问情况，以及最少用户访问人数。
SELECT
	day,
	users,
	FIRST_VALUE(users) over(ORDER BY	users ) as `最少用户访问人数`
from 
statistics
WHERE website_id = 2;

需求：统计id = 3 的网站收入情况，返回日期，收入，和第一天的收入
SELECT
	day,
	revenue,
	FIRST_VALUE(revenue) over(order by day ) as `第一天的收入`
FROM
statistics
WHERE website_id =3;

统计id为1的网站的广告展示情况，返回每日日期，广告展示次数，以及访问用户最多的一天广告展示的次数
SELECT
	day,
	impressions,
	FIRST_VALUE(impressions) over(order by impressions desc) as `最高展示次数`
FROM
statistics
WHERE website_id = 1;

4 LAST_VALUE(expr) 返回指定列的最后一个值

注意:使用last_value()时候的时候.要注意范围的限制.
要加上 rows BETWEEN unbounded preceding AND unbounded following

返回最近开始营业的网站，我们运行一下
SELECT
	name,
	opened,
	LAST_VALUE(opened) over(ORDER BY opened  rows 
												BETWEEN unbounded preceding AND unbounded following)
	
from
website;


需求：统计id为1的网站，每日的访问用户数，最后一天的访问用户数，每日用户数与最后一天用户数的差值
SELECT
	day,
	users,
	LAST_VALUE(users) over(ORDER BY day rows BETWEEN unbounded  preceding AND unbounded following) as `最后一天访问用户数`,
	users - LAST_VALUE(users) over(ORDER BY day rows BETWEEN unbounded  preceding AND unbounded following) as 		`diff`
FROM
statistics
WHERE
website_id = 1;

5 (巨重要)nth_value(x,n) 函数返回X列,按指定顺讯的第n个值

!!!巨他妈重要partition by order by topn问题
nth_value(x,n) 函数返回X列,按指定顺讯的第n个值
排序的时候加上desc调整排序的顺序,配合nth_value(x,n) 在某些场景下更方便
NTH_VALUE 通常要求把window frame修改成 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

统计id为2的网站的收入情况，在5月15和5月31日之间，每天的收入，以及这半个月内的第三高的日收入金额
SELECT
	day,
	revenue,
	NTH_VALUE(revenue,3) over(ORDER BY revenue desc rows BETWEEN unbounded preceding AND  unbounded following) as `半个月内第三高的收入金额`
FROM
statistics
WHERE website_id = 2 and day BETWEEN '2016-05-15' AND '2016-05-31';

统计5月14日的不同网站收入情况，返回如下字段：
网站website_id, 当日收入revenue
所有网站当日最高收入highest_revenue
所有网站当日最少收入lowest_revenue
SELECT
	website_id,
	revenue,
	NTH_VALUE(revenue,1) over(ORDER BY revenue desc) as `highest_revenue`,
	NTH_VALUE(revenue,1) over(ORDER BY revenue asc)  as `lowest_revenue`
FROM
statistics
WHERE day = '2016-05-14';


需求：统计id为1的网站的点击量，返回如下字段
- 日期 day, 点击量 clicks ，5月点击量的中位数
- 提示：5月一共31天，将点击量按顺序排列，第16位点击量即为中位数    
SELECT
	day,
	clicks,
	NTH_VALUE(clicks,16) over(order by clicks rows BETWEEN unbounded preceding AND unbounded following) as `点击中位数`
FROM
statistics
WHERE website_id = 1;

p140 
需求：统计id为3的网站每天点击的情况，返回如下字段
- 日期`day`，点击量`clicks`，最高点击量和当天点击量的比例`ratio`（用整数表示）

SELECT
	day,
	clicks,
	ROUND(	clicks / LAST_VALUE(clicks) over(ORDER BY clicks desc rows BETWEEN unbounded preceding and unbounded following)) as `ratio`
from
statistics
WHERE website_id = 3
ORDER BY day;

小结

• LEAD(x) 和 LAG(x) 分别返回传入的列x对于当前行的下一行/前一行的值
• LEAD(x,y) 和 LAG(x,y) 分别返回传入的列x对于当前行的后y行/前y行的值
• FIRST_VALUE(x) 和 LAST_VALUE(x) 分别返回列x 的第一个值/最后一个值
• NTH_VALUE(x,n) 返回 x 列的 第n个值
• LAST_VALUE 和 NTH_VALUE 通常要求把window frame修改成 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING