MySQL 分布函数、前后函数
- 2020 年 1 月 2 日
- 笔记
本文标识 : MQ0017
本文编辑 : 长安月下赏美人儿
编程工具 : MySQL、DBeaver
阅读时长 : 6分钟
1、分布函数
（1）应用场景：快速查看某个记录所归属的组内的比例
（2）分布函数分类及基础语法
percent_rank()over(partition by 分区字段 order by 排序字段 desc/asc); cume_dist()over (partition by 分区字段 order by 排序字段 desc/asc);
（3）两种分布函数的介绍
percent_rank() 函数
- 显示的结果，每行按照如下公式进行计算: (rank-1)/(rows-1)
- rank 为 RANK() 函数产生的序号，rows 为当前窗口的记录总行数
- 对于重复值，取重复值的第一行记录的位置
cume_dist() 函数
- 显示的结果，每行按照如下公式进行计算：rank/rows
- rank 为 RANK() 函数产生的序号，rows 为当前窗口的记录总行数
- 对于重复值，取重复值的最后一行记录的位置
（4）实例比较两种分布函数
SELECT t3.* ,PERCENT_RANK()OVER(ORDER BY tal_amt)AS p1 ,CUME_DIST()OVER(ORDER BY tal_amt)AS p2 FROM( SELECT t2.goodsID ,t2.dimShopID ,SUM(t2.AMT) AS tal_amt ,RANK()OVER(PARTITION BY t2.dimShopID ORDER BY SUM(t2.AMT) DESC)AS rn2 FROM fct_sales AS t1 JOIN fct_sales_item AS t2 ON t1.salesID=t2.salesID WHERE t1.dimDateID BETWEEN 20170801 AND 20170807 AND t2.dimShopID=34 GROUP BY t2.goodsID ,t2.dimShopID ORDER BY SUM(t2.AMT) DESC) AS t3 WHERE t3.rn2<101;
数据结果：
差异点对比：
- 由 percent_rank() 函数，产生的 p1 数列，计算方式为：（25-1）/（100-1）
- 由 cume_dist() 函数，产生的 p2 数列，计算方式为：26/100
2、前后函数
（1）应用场景
- 常用于计算时间序列数据，计算时间间隔，如会员每次购买商品的时间间隔等
- 当前行与前 n 行或者后 n 行的值
- 若有分区，分区值则所有分区的前后 n 行的值
（2）前后函数分类及基础语法
lag(字段，N)over(partition by 分区字段 order by 排序字段 desc/asc); lead(字段,N)over (partition by 分区字段 order by 排序字段 desc/asc);
（3）两种前后函数的介绍
- lag(字段,N) 函数，指当前行的前 N 行的对应字段值
- lead(字段,N) 函数，指当前行的后 N 行的对应字段值
（4）实例比较前后函数
SELECT t1.dimMemberID ,t1.salesno ,t1.dimDateID ,lag(t1.dimDateID,1)OVER(PARTITION BY t1.dimMemberID ORDER BY t1.dimDateID) AS t1 ,lag(t1.dimDateID,2)OVER(PARTITION BY t1.dimMemberID ORDER BY t1.dimDateID) AS t2 ,lead(t1.dimDateID,1)OVER(PARTITION BY t1.dimMemberID ORDER BY t1.dimDateID) AS t3 FROM fct_sales AS t1 WHERE t1.dimMemberID>0 LIMIT 100;
数据结果：
结果解析：
- 由 lag(t1.dimDateID,1) 产生的数列 t1，观察红线连接的两个数字，显示 8 号客户，最新购买商品的时间为 20170607，以此时间点向前查询，上 1 次购买商品的时间为 20170603
- 由 lag(t1.dimDateID,2)产生的数列 t2，观察红线连接的两个数字，显示 9 号客户，最新购买商品的时间为 20170615，以此时间点向前查询，上 2 次购买商品的时间为 20170602
- 由 lead(t1.dimDateID,1) 产生的数列 t3，观察红线连接的两个数字，显示 9 号客户，以 20170613 向后查询，下 1 次购买商品的时间为 20170615
