MySQL 汇总函数、其他函数

2020 年 1 月 1 日
筆記

本文标识 : MQ0019

本文编辑 : 长安月下赏美人儿

编程工具 : MySQL、DBeaver

阅读时长 : 7分钟

1、汇总函数

（1）窗口函数之汇总函数，比较常用的是 sum()over()，且要时刻谨记窗口分析范围值是第一行到当前行。

SUM(字段)over(PARTITION BY 分区字段 ORDER BY 排序字段 DESC/ASC)  #第一行到当前行字段累计汇总值  #如果相同的值则对应的累计汇总值相同    AVG(字段)over(PARTITION BY 分区字段 ORDER BY 排序字段 DESC/ASC)  #第一行到当前行字段累计平均值  #如果相同的值则对应的累计平均值相同    COUNT(字段)over(PARTITION BY 分区字段 ORDER BY 排序字段 DESC/ASC)  #第一行到当前行的累计数  #如果相同的值则对应累计计数值相同    MAX(字段)over(PARTITION BY 分区字段 ORDER BY 排序字段 DESC/ASC)  #第一行到当前行的最大值    MIN(字段)over(PARTITION BY 分区字段 ORDER BY 排序字段 DESC/ASC)  #注意默认的范围是第一行到当前行  RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

（2）实例展现汇总函数

SELECT t2.*        ,SUM(t2.amt)OVER(PARTITION BY dimShopID ORDER BY t2.amt DESC)AS s1        ,AVG(t2.amt)OVER(PARTITION BY dimShopID ORDER BY t2.amt DESC)AS a2        ,COUNT(t2.amt)OVER(PARTITION BY dimShopID ORDER BY t2.amt DESC)AS c3        ,MAX(t2.amt)OVER(PARTITION BY dimShopID ORDER BY t2.amt DESC)AS m4        ,MIN(t2.amt)OVER(PARTITION BY dimShopID ORDER BY t2.amt DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)AS m5  FROM(SELECT t1.dimShopID        ,t1.dimDateID        ,SUM(AMT) AS amt  FROM dw.fct_sales AS t1  WHERE dimDateID BETWEEN 20170801 AND 20170810  GROUP BY t1.dimShopID          ,t1.dimDateID)AS t2  ORDER BY dimShopID,amt DESC;

数据结果：

注意：数列 m5 加入了限定范围，如果没有加入又会如何？感兴趣的小伙伴儿可以去尝试一下！

2、其他函数

（1）应用场景：用于数据分区，且从1到参数值的整数范围，尽可能相等的划分分区。

（2）函数基础语法

nth_value(指定字段，N) OVER（PARTITION BY   分区字段 ORDER BY 排序字段 DESC/ASC)  #指定字段，指排序字段不同分区中，指定字段在窗口范围第 N 个的值    ntile(N)OVER(PARTITION BY 分区字段 ORDER BY )排序字段 DESC/ASC)  #对排序的字段，分成 N 组  #有分区的字段，对排序的字段分成 N 组

（3）实例展现

SELECT t2.*        ,nth_value(amt,3)OVER(PARTITION BY dimShopID ORDER BY t2.amt DESC)AS n1        ,ntile(2)OVER(PARTITION BY dimShopID ORDER BY t2.amt DESC)AS n2        ,ntile(4)OVER(PARTITION BY dimShopID ORDER BY t2.amt DESC)AS n3        ,ntile(5)OVER(PARTITION BY dimShopID ORDER BY t2.amt DESC)AS n4  FROM(SELECT t1.dimShopID        ,t1.dimDateID        ,SUM(AMT) AS amt  FROM dw.fct_sales AS t1  WHERE dimDateID BETWEEN 20170801 AND 20170810  GROUP BY t1.dimShopID          ,t1.dimDateID)AS t2  ORDER BY dimShopID,amt DESC;

数据结果：

结果解析：

由 nth_value() 产生的数列 n1，在以 dimShopID 分组后，精准锁定 amt 数列第 3 个位置的数据；
由 ntile(2) 与 ntile(5) 产生的数列 n2 和 n4 ，在以 dimShopID 分组后，均匀的将 amt 数列分为了 2 组和 5 组；

3、ntile(N) 如何精准分组

（1）困惑

由 ntile(3) 产生的数列 n3，在以 dimShopID 分组后，将 amt 数列分为了 4 组，那么又是如何精准划分的呢？这就涉及到统计学中的四分位数！

（2）四分位数

含义：即 Quartile 也称四分位点，是指在统计学中把所有数值由小到大排列并分成四等份，处于三个分割点位置的数值。

（3）实例解析

以 dimShopID=33 为例，amt 数列项为 10 项，则

四分位数 Q2 为该组数列的中位数
第一四分位数（n+1）/4= 10/4 =2.25 Q1 在第 2 与第 3 个数字之间
第三四分位数 3(10+1）/4= 33/4 =8.25 Q3在第 8 与第 9 个数字之间

计算结果如下

Q1=0.75*63718.71+0.25*65459.27=64153.85
Q2=(56704.89+52233.34)/2=54469.115
Q3=0.25*45016.49+0.75*46852.21=46393.28

根据计算结果，已经清晰确定，第三四分位数的位置在第 8 与第 9 数字之间，且 Q3 = 46393.28，通过对比发现 45016.49< Q3 <46852.21 ,故 45016.49 在第四组。

写在后面，最后关于四分位数的部分，还是蛮有趣的，大家可以仔细观察一下第一四分位数与中位数，与分组边界值的关系，期待有新的发现！