窗口函数也称为OLAP函数，对数据库数据进行分析处，窗口函数就是为了实现OLAP分析而添加的标准SQL功能。不同数据库支持情况不同，mysql只有高版本才可以支持，数据分析功能本身在传统关系数据库支持较弱，在使用时确认下是否支持。与对一组输入行返回单个聚合值的聚合函数不同，分析函数对一组输入行计算函数，并为每一行返回一个值。分析函数是一种强大的机制，可简洁地表示复杂的分析操作.窗口函数同时具有分组和排序的功能，不减少原表的行数。应用场景包括分区排序、动态Group By、TopN计算、累计计算、层次查询等。

关键词 over partition by group_concat rollup cube

一、窗口函数

<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)

PARTITION BY 子句将输入行划分成多个分区。该子句与 GROUP BY 类似，但并不会实际合并具有相同键的行;

ORDER BY 子句指定每个分区中的排序方式;

窗口函数位置可以放两种函数，专用窗口排名函数和聚合函数。专用窗口函数有rank, dense_rank, row_number排名函数。聚合函数包括sum, avg, count, max, min等。group by分组汇总后改变了表的行数，一行只有一个类别，而partiition by和rank函数不会减少原表中的行数。

举例说明，根据所选课程进行分数排名：


SELECT *, rank() over (PARTITION BY cno ORDER BY grade DESC) AS ranking FROM coursegrade;


mysql窗口函数sum mysql窗口函数和group_mysql窗口函数sum


下面区分rank函数、dense_rank函数、row_number函数区别：


SELECT  *,
 rank() over (partition by cno order by grade) as rank,
 dense_rank() over (partition by cno order by grade) as dense_rank,
 row_number() over (partition by cno order by grade) as row_number
FROM
 coursegrade;


mysql窗口函数sum mysql窗口函数和group_rollup函数_02


rank: 对人按照分数排名，排名代表有多少个人（设为n）的分数比你高，有并列名次的行占用后面的排名。

dense_rank: 对分数按照分数排名，排名代表有多少个分数（设为n）比你的分数高，有并列名次的行，不占用后面排名。

row_number: 对人按照次序排名，有多少人（设为n）在你前面，不考虑并列名次的情况。

窗口函数位置使用聚合函数，对于分组内的数据进行聚合：


SELECT
 cno,
 sum(grade) over (partition by cno) as cnosum,
 count(grade) over (partition by cno) as cnocount,
 avg(grade)over (partition by cno) as cnoavg,
 max(grade) over (partition by cno) as cnomax
FROM
coursegrade;


mysql窗口函数sum mysql窗口函数和group_rollup函数_03


聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。

窗口函数与聚合函数（Aggregate Function）有些类似，他们都是对一系列的行进行聚合计算，然而不同于聚合函数的是，窗口函数的输出并不是被聚集到单独的一行。像 SUM, AVG, COUNT 这些聚合函数最终输出都是一行记录。窗口函数可以通过计算每行周围窗口上的集合值来分析数据，例如：计算一定记录范围内、一定值域内、或者一段时间内的累计和以及移动平均值等等。之所以使用窗口这个术语，是因为对结果的处理使用了一个滑动的查询结果集范围。窗口函数查询指定使用 OVER 关键字，默认情况下，用于计算的行集合（Window窗口）是整个数据集，并且可以用 ORDER BY 子句排序，PARTITION BY 子句可以用于将窗口缩小到特定的集合内。对于Mysql低版本来实现窗口函数功能比较复杂，可以参考下面语句：


mysql窗口函数sum mysql窗口函数和group_mysql 窗口函数_04


二、GROUP_CONCAT函数

对于Group By查询结果，经常要把同一个分组中的值连接起来，返回一个字符串结果，下面介绍下GROUP_CONCAT 函数。

group_concat( [distinct]要连接的字段[orderby排序字段asc/desc ] [separator '分隔符'] )

通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。


SELECT cno, GROUP_CONCAT(grade order by grade) FROM coursegrade GROUP BY cno;


mysql窗口函数sum mysql窗口函数和group_mysql窗口函数_05


SELECT cno, GROUP_CONCAT(CONCAT_WS('-',sno,grade) ORDER BY grade) FROM coursegrade GROUP BY cno;


mysql窗口函数sum mysql窗口函数和group_pb分组数据累计_06


三、ROLLUP和CUBE函数

WITH ROLLUP在group分组字段的基础上再进行统计数据。用户需要对N个维度进行聚合查询操作，普通的group by语句需要N个查询和N次groupby操作。而rollup一次可以进行N次group by的结果，这样可以提高查询效率。


SELECT sno,cno,SUM(grade) FROM coursegrade GROUP BYcno,sno WITH ROLLUP;


mysql窗口函数sum mysql窗口函数和group_pb分组数据累计_07


使用多个group by实现方式：


select sno,cno,sum(grade) from coursegrade
group by sno,cno
union
select sno,cno,sum(grade) from coursegrade
group by sno
union
select null,null, sum(qty) from coursegrade；


除去rollup函数，还有cube函数，rollup是cube的一种特殊情况，和rollup一样，cube也是一种对数据的聚合操作。但是rollup只在层次上对数据进行聚合，而cube对所有的维度进行聚合。具有N个维度的列，cube进行2的N次方次分组操作，而rollup只进行N次分组操作。假设有n个维度，rollup会有n个聚合：

rollup(a,b)统计列包含：(a,b)、(a)、()

cube(a,b,c)统计列包含：(a,b,c)、(a,b)、(a)、()

四、时间聚合分析

在日常数据分析过程中，经常需要对于各种粒度时间进行处理分析，这时候需要实现MTD（Month To Date）当月累计，YTD（Year To Date）当年累计，同比（去年同期相比），环比（相邻两个时间段相比）等各种数据计算，由于sql本身和数据库支持情况，实现相对比较复杂，因此也有专门的数据分析软件进行处理，不建议使用sql语句实现，相关方式可以参考下面语句。


select
 (select sum(isok)
  from logs
  where open_date>= date(now()) - interval 1 day
  )as last_24,
 (select sum(isok) 
  from logs
  where year(curdate())=year(open_date) and month(curdate())=month(open_date)
   )as mtd,
  (select sum(isok) 
   from logs
   where year(curdate())=year(open_date)
   )as ytd
from logs;


环比增长率＝（本期数－上期数）/上期数*100％反映本期比上期增长了多少；

同比增长率＝（本期数－同期数）/同期数*100％指和去年同期相比较的增长率；