MySQL 汇总函数、其他函数

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

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;

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

ntile(N)OVER(PARTITION BY 分区字段 ORDER BY )排序字段 DESC/ASC)
#对排序的字段，分成 N 组
#有分区的字段，对排序的字段分成 N 组

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;