wordpress 七牛oss,seo系统,宁波网站推广有哪些,网站建设属于广告费么除了基本的分组功能之外#xff0c;GROUP BY 子句还提供了几个高级选项#xff0c;可以用来实现更复杂的报表功能。
本文比较五种主流数据库实现的高级分组功能#xff0c;包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。
功能MySQLOracleSQL ServerPostgreSQL…除了基本的分组功能之外GROUP BY 子句还提供了几个高级选项可以用来实现更复杂的报表功能。
本文比较五种主流数据库实现的高级分组功能包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。
功能MySQLOracleSQL ServerPostgreSQLSQLiteROLLUP✔️✔️✔️✔️❌CUBE❌✔️✔️✔️❌GROUPING SETS❌✔️✔️✔️❌GROUPING 函数✔️✔️✔️✔️❌
小计、合计与总计
GROUP BY 子句的 ROLLUP 选项可以生成按照不同层级进行汇总的结果从而实现报表中的小计、合计和总计。例如
-- Oracle、Microsoft SQL Server 以及 PostgreSQL
SELECT dept_id AS 部门编号, sex AS 性别, COUNT(*) AS 员工数量
FROM employee
GROUP BY ROLLUP (dept_id, sex);其中ROLLUP 表示首先按照不同部门和性别的组合统计员工数量然后按照不同的部门统计员工数量最后统计全体员工的数量注意括号不能省略。Oracle、Microsoft SQL Server 以及 PostgreSQL 实现了以上语法查询返回的结果如下
部门编号|性别|员工数量
------|---|-------1|男 | 31| | 32|男 | 32| | 33|女 | 23| | 24|男 | 84|女 | 14| | 95|男 | 85| | 8| | 25查询结果中性别为空的记录表示按照不同部门统计的员工数量部门编号和性别都为空的记录表示全体员工的数量。
MySQL 提供了 ROLLUP 选项但是语法略有不同。例如
-- MySQL 和 Microsoft SQL Server
SELECT dept_id AS 部门编号, sex AS 性别, COUNT(*) AS 员工数量
FROM employee
GROUP BY dept_id, sex WITH ROLLUP;其中WITH ROLLUP 位于分组字段之后而且无须使用括号。查询返回的结果和上面的示例相同。另外Microsoft SQL Server 也支持这种语法。 提示GROUP BY 子句的 ROLLUP 选项表示先按照所有分组字段进行分组汇总然后从右至左依次去掉一个分组字段再进行分组汇总被去掉的字段显示为空。最后将所有的数据进行一次汇总所有分组字段都显示为空。 SQLite 目前不支持 ROLLUP 选项。
交叉统计报表
GROUP BY 子句的 CUBE 选项可以对分组字段进行各种组合产生类似于 Excel 数据透视表的多维度交叉报表。例如
-- Oracle、Microsoft SQL Server 以及 PostgreSQL
SELECT dept_id AS 部门编号, sex AS 性别, COUNT(*) AS 员工数量
FROM employee
GROUP BY CUBE (dept_id, sex);其中CUBE 表示首先按照不同部门和性别的组合统计员工数量然后按照不同的部门统计员工数量之后按照不同的性别统计员工数量最后统计全体员工的数量。Oracle、Microsoft SQL Server 以及 PostgreSQL 实现了以上语法查询返回的结果如下
部门编号|性别|员工数量
------|---|-------1|男 | 32|男 | 34|男 | 85|男 | 8|男 | 223|女 | 24|女 | 1|女 | 3| | 251| | 32| | 33| | 24| | 95| | 8查询结果中性别为空的记录表示按照不同部门统计的员工数量部门编号为空的记录表示按照不同性别统计的员工数量部门编号和性别都为空的记录表示全体员工的数量。 提示GROUP BY 子句的 CUBE 选项产生的分组情况随着分组字段的增加呈指数级 2n 增长ROLLUP选项产生的分组情况随着分组字段的增加呈线性n1 增长。 MySQL 和 SQLite 目前不支持 CUBE 选项。
自定义维度统计
ROLLUP 和 CUBE 选项都是按照固定的方式进行分组GROUP BY 子句还支持一种更为灵活的分组选项GROUPING SETS。该选项可以用来指定自定义的分组集也就是自定义分组字段的组合方式。例如
GROUP BY dept_id, sex相当于指定了 1 个分组集
GROUP BY GROUPING SETS ((dept_id, sex))其中(dept_id, sex) 表示按照不同部门和性别的组合进行分组括号内的所有字段作为一个分组集最外面的括号则包含了所有的分组集。
同样以下 ROLLUP 选项
GROUP BY ROLLUP(dept_id, sex)相当于指定了 3 个分组集
GROUP BY GROUPING SETS ((dept_id, sex), (dept_id), ())其中(dept_id, sex) 表示按照不同部门和性别的组合进行分组(dept_id) 表示按照不同的部门进行分组() 表示对全体员工进行汇总。
同样以下 CUBE 选项
GROUP BY CUBE(dept_id, sex)相当于指定了 4 个分组集
GROUP BY GROUPING SETS ((dept_id, sex), (dept_id), (sex), ())其中(dept_id, sex) 表示按照不同部门和性别的组合进行分组(dept_id) 表示按照不同的部门进行分组(sex) 表示按照不同的性别进行分组() 表示对全体员工进行汇总。
因此ROLLUP 和 CUBE 选项都属于 GROUPING SETS 选项的特例。GROUPING SETS 选项的优势在于可以指定任意的分组方式。例如
-- Oracle、Microsoft SQL Server 以及 PostgreSQL
SELECT dept_id AS 部门编号, sex AS 性别, COUNT(*) AS 员工数量
FROM employee
GROUP BY GROUPING SETS ((dept_id), (sex), ());以上查询分别按照不同的部门、不同的性别统计员工的数量同时统计了全体员工的数量。查询返回的结果如下
部门编号|性别|员工数量
-------|---|-------| | 254| | 92| | 33| | 21| | 35| | 8|男 | 22|女 | 3当分组统计的维度越来越多时这种方式可以方便我们实现各种不同的业务统计需求。
MySQL 和 SQLite 目前不支持 GROUPING SETS 选项。
GROUPING 函数
我们在使用 GROUP BY 子句的扩展选项时查询会产生一些空值数据。这些空值意味着对应的记录是针对这个字段所有数据的汇总我们可以利用 GROUPING 函数识别这些空值数据。例如
-- Oracle、Microsoft SQL Server 以及 PostgreSQL
SELECT sex AS 性别, COUNT(*) AS 员工数量,GROUPING(sex) AS 所有性别
FROM employee
GROUP BY ROLLUP (dept_id, sex);查询返回的结果如下
性别|员工数量|所有性别
---|-------|-------
男 | 22| 0
女 | 3| 0| 25| 1其中GROUPING(sex) 函数返回 0表示当前记录不是所有性别的汇总数据返回 1表示当前记录是所有性别的汇总数据。因此结果中的最后一条记录表示全体员工的数量。
我们可以进一步利用 CASE 表达式对查询结果进行转换显示
-- Oracle、Microsoft SQL Server 以及 PostgreSQL
SELECT CASE GROUPING(sex) WHEN 1 THEN 所有性别 ELSE sex END AS 性别,COUNT(*) AS 员工数量
FROM employee
GROUP BY ROLLUP (sex);查询返回的结果如下
性别 |员工数量
-------|------
女 | 3
男 | 22
所有性别| 25我们将最后一行中性别为空的数据显示为“所有性别”。
MySQL 同样支持 GROUPING 函数例如
-- MySQL 和 Microsoft SQL Server
SELECT CASE GROUPING(sex) WHEN 1 THEN 所有性别 ELSE sex END AS 性别,COUNT(*) AS 员工数量
FROM employee
GROUP BY sex WITH ROLLUP;查询返回的结果和上面的示例相同。
SQLite 中的 GROUP BY 子句不支持扩展选项因此也就没有提供 GROUPING 函数。
案例销售数据分析
接下来我们将会使用一个虚拟的销售数据集sales_data。该数据集包含了 2019 年 1 月 1 日到 2019 年 6 月 30 日三种产品在三个渠道每天的销售情况。示例表的创建脚本可以从图书《SQL编程思想》的配套网站下载以下是该表中的部分数据
saledate |product|channel|amount
----------|-------|-------|-------
2019-01-01|桔子 |淘宝 |1864.00
2019-01-01|桔子 |京东 |1329.00
2019-01-01|桔子 |店面 |1736.00
2019-01-01|香蕉 |淘宝 |1573.00
2019-01-01|香蕉 |京东 |1364.00
2019-01-01|香蕉 |店面 |1178.00
2019-01-01|苹果 |淘宝 | 511.00
2019-01-01|苹果 |京东 | 568.00
2019-01-01|苹果 |店面 | 847.00
...我们首先通过分组汇总了解一下产品的整体销售情况
-- Oracle、Microsoft SQL Server 以及 PostgreSQL
SELECT CASE GROUPING(product) WHEN 1 THEN 所有产品 ELSE product END AS 产品,CASE GROUPING(channel) WHEN 1 THEN 所有渠道 ELSE channel END AS 渠道,SUM(amount) 销售金额
FROM sales_data
GROUP BY ROLLUP (product, channel)
ORDER BY product, SUM(amount) DESC;其中GROUP BY ROLLUP 子句表示统计不同产品不同渠道的销售金额小计、不同产品所有渠道的销售金额合计以及所有产品的销售总计。查询返回的结果如下
产品 |渠道 |销售金额
------|-------|---------
桔子 |所有渠道| 909261.00
桔子 |京东 | 311799.00
桔子 |淘宝 | 302782.00
桔子 |店面 | 294680.00
苹果 |所有渠道| 937052.00
苹果 |京东 | 318614.00
苹果 |淘宝 | 311795.00
苹果 |店面 | 306643.00
香蕉 |所有渠道| 925369.00
香蕉 |店面 | 311445.00
香蕉 |淘宝 | 307891.00
香蕉 |京东 | 306033.00
所有产品|所有渠道|2771682.00在我们的模拟数据中桔子的销售金额为 909261 元在京东商城的销量最高在店面的销量最低苹果的销售金额为 937052 元在京东商城的销量最高在店面的销量最低香蕉的销售金额为 925369 元在店面的销量最高在京东商城的销量最低所有产品的销售金额总计为 2771682 元。
对于 MySQL我们可以使用 WITH ROLLUP 选项实现相同的功能。
在 Excel 中有一个分析功能叫作数据透视表Pivot Table数据透视表可以提供不同级别的数据统计、对比分析和趋势分析等。考虑一下如何通过 SQL 查询实现以下数据透视表
产品 |渠道 |1 月 |2 月 |3 月 |4 月 |5 月 |6 月 |【合计】
-------|-----|------|------|-------|------|------|------|-------
桔子 |京东 | 41289| 43913| 49803| 49256| 64889| 62649| 311799
桔子 |店面 | 41306| 37906| 48866| 48673| 58998| 58931| 294680
桔子 |淘宝 | 43488| 37598| 48621| 49919| 58530| 64626| 302782
桔子 |- - |126083|119417| 147290|147848|182417| 186206| 909261
苹果 |京东 | 38269| 40593| 56552| 56662| 64493| 62045| 318614
苹果 |店面 | 43845| 40539| 44909| 55646| 56771| 64933| 306643
苹果 |淘宝 | 42969| 43289| 48769| 58052| 58872| 59844| 311795
苹果 |- - |125083|124421| 150230|170360|180136| 186822| 937052
香蕉 |京东 | 36879| 36981| 51748| 54801| 64936| 60688| 306033
香蕉 |店面 | 41210| 39420| 50884| 52085| 60249| 67597| 311445
香蕉 |淘宝 | 42468| 41955| 52780| 54971| 56504| 59213| 307891
香蕉 |- - |120557|118356| 155412|161857|181689| 187498| 925369
【总计】|- - |371723|362194| 452932|480065|544242| 560526|2771682我们同样可以利用分组汇总加上 CASE 表达式实现以上报表
-- Oracle 和 PostgreSQL
SELECT CASE GROUPING(product) WHEN 1 THEN 【总计】 ELSE product END AS 产品,CASE GROUPING(channel) WHEN 1 THEN -- ELSE channel END AS 渠道,SUM(CASE EXTRACT(MONTH FROM saledate) WHEN 1 THEN amount END) 1 月,SUM(CASE EXTRACT(MONTH FROM saledate) WHEN 2 THEN amount END) 2 月,SUM(CASE EXTRACT(MONTH FROM saledate) WHEN 3 THEN amount END) 3 月,SUM(CASE EXTRACT(MONTH FROM saledate) WHEN 4 THEN amount END) 4 月,SUM(CASE EXTRACT(MONTH FROM saledate) WHEN 5 THEN amount END) 5 月,SUM(CASE EXTRACT(MONTH FROM saledate) WHEN 6 THEN amount END) 6 月,SUM(amount) 【合计】
FROM sales_data
GROUP BY ROLLUP (product, channel)
ORDER BY product, channel;其中GROUP BY ROLLUP 子句表示统计不同产品不同渠道的销售金额小计、不同产品所有渠道的销售金额合计以及所有产品的销售总计EXTRACT 函数加上 CASE 表达式用于获取每个月的销售金额。
对于 MySQL我们需要使用 WITH ROLLUP 选项替换 ROLLUP。对于Microsoft SQL Server我们需要使用 DATEPART 函数替换 EXTRACT 函数。
