窗口函数-分析函数
- 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