MySQL开窗函数over与group by进行olap数据分析

作用：

开窗函数类似于聚合函数(group by)，主要实现数据的分组统计，在PostgreSQL中被称为窗口函数、在Oracle中被称为分析函数、在DB2中被称为OLAP函数。over在某些情况下可以完全替代group by，但大多情况下，over比group by更灵活、更强大。

优点：

1）同时具有分组和排序的功能

2）不减少原表的行数，经常用来在每组内排名

3）分组排序灵活

对比group by：

1）group by 只能得到分组后的统计数据，over partition by 不仅可以得到分组后的统计数据，还可以同时显示明细数据。

2）group by 是在where子句之后；over partition by 是from子句之前。

3）over比group by更灵活、更强大。

用法：

OVER ( [ PARTITION BY column ] [ ORDER BY culumn ] )

1）PARTITION BY 子句进行分组；

2）ORDER BY 子句进行排序。

3）窗口函数OVER()指定一组行，开窗函数计算从窗口函数输出的结果集中各行的值。

开窗函数不需要使用GROUP BY就可以对数据进行分组，还可以同时返回基础行的列和聚合列。

聚合函数一般指SUM(),MAX(),MIN,COUNT(),AVG()等常见函数。

排序函数一般指RANK(),ROW_NUMBER(),DENSE_RANK(),NTILE()，LEAD()，LAG（），STRING_AGG（）等

CREATE TABLE `Employee` (

  `ID` int(11) NOT NULL,

  `NAME` varchar(20) DEFAULT NULL,

  `GroupName` varchar(20) DEFAULT NULL,

  `Salary` int(11) DEFAULT NULL,

  PRIMARY KEY (`ID`) /*T![clustered_index] CLUSTERED */

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

INSERT INTO `test`.`Employee`(`ID`, `NAME`, `GroupName`, `Salary`) VALUES (1, '小明', '开发部', 8000);

INSERT INTO `test`.`Employee`(`ID`, `NAME`, `GroupName`, `Salary`) VALUES (4, '小张', '开发部', 7600);

INSERT INTO `test`.`Employee`(`ID`, `NAME`, `GroupName`, `Salary`) VALUES (5, '小白', '开发部', 7000);

INSERT INTO `test`.`Employee`(`ID`, `NAME`, `GroupName`, `Salary`) VALUES (8, '小王', '财务部', 5000);

INSERT INTO `test`.`Employee`(`ID`, `NAME`, `GroupName`, `Salary`) VALUES (9, NULL, '财务部', NULL);

INSERT INTO `test`.`Employee`(`ID`, `NAME`, `GroupName`, `Salary`) VALUES (15, '小刘', '财务部', 6000);

INSERT INTO `test`.`Employee`(`ID`, `NAME`, `GroupName`, `Salary`) VALUES (16, '小高', '行政部', 4500);

INSERT INTO `test`.`Employee`(`ID`, `NAME`, `GroupName`, `Salary`) VALUES (18, '小王', '行政部', 4000);

INSERT INTO `test`.`Employee`(`ID`, `NAME`, `GroupName`, `Salary`) VALUES (23, '小李', '行政部', 4500);

INSERT INTO `test`.`Employee`(`ID`, `NAME`, `GroupName`, `Salary`) VALUES (29, '小吴', '行政部', 4700);

INSERT INTO `test`.`Employee`(`ID`, `NAME`, `GroupName`, `Salary`) VALUES (30, '小30', '行政部', 3333);

rank函数：这个例子中是4700，4500，4500，4000，3333，也就是如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，5，但是现在2，3名是并列的名次，结果是：1，2，2，4，5。

dense_rank函数：这个例子中是4700，4500，4500，4000，3333，也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，5，但是现在2，3名是并列的名次，结果是：1，2，2，3，4。

row_number函数：这个例子中是4700，4500，4500，4000，3333，也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4，5。

SELECT

        ID,

        NAME,

        GroupName,

        Salary,

        ranking,

        denseRank,

        rowNumber

FROM

        (

        SELECT

                ID,

                NAME,

                GroupName,

                Salary,

                rank ( ) over ( PARTITION BY GroupName ORDER BY Salary DESC ) AS ranking,

                dense_rank ( ) over ( PARTITION BY GroupName ORDER BY Salary DESC ) AS denseRank,

                row_number ( ) over ( PARTITION BY GroupName ORDER BY Salary DESC ) AS rowNumber

        FROM

                Employee

        ) newTb

WHERE

        ranking <= 5

        AND GroupName = '行政部'

 

DENSE_RANK()排名是连续的1-1-2-2-3排列--中国式排名，RANK()是跳跃的1-1-3 排列--美国式排名，ROW_NUMBER()是连续排序不跳跃，NTILE（N）把结果平分为N个分区, 如果不能均分，则优先多分前面的组

SELECT

        *,

        NTILE ( 5 ) OVER ( ORDER BY Salary DESC ) AS 分区后排序

FROM

        Employee;

 

https://blog.csdn.net/weixin_39740272/article/details/111090489

https://blog.csdn.net/navy0168/article/details/83737478

https://zhuanlan.zhihu.com/p/67656512

https://blog.csdn.net/weixin_39737492/article/details/110869246

https://stackoverflow.com/questions/39579224/using-group-by-and-over