【SQL常用语法】grouping sets, partition by

1. grouping sets

参考：https://www.yiibai.com/sql/sql-grouping-sets.html
使用场景：在同一查询中定义多个分组集

例：如下为inventory表，希望从中查询分别以(warehouse,product),(warehouse),(product)分组的情况

SELECT 
    *
FROM
    inventory;
+---------------+---------+----------+----------+
| warehouse     | product | model    | quantity |
+---------------+---------+----------+----------+
| San Jose      | iPhone  | 6s       |      100 |
| San Fransisco | iPhone  | 6s       |       50 |
| San Jose      | iPhone  | 7        |       50 |
| San Fransisco | iPhone  | 7        |       10 |
| San Jose      | iPhone  | X        |      150 |
| San Fransisco | iPhone  | X        |      200 |
| San Jose      | Samsung | Galaxy S |      200 |
| San Fransisco | Samsung | Galaxy S |      200 |
| San Fransisco | Samsung | Note 8   |      100 |
| San Jose      | Samsung | Note 8   |      150 |
+---------------+---------+----------+----------+
10 rows in set

（1）使用union all

特点：
1）UNION ALL要求所有结果集具有相同的列数，因此需要将NULL添加到每个查询的选择列表中
2）数据库系统必须多次扫描库存表，性能较差
3）易读性较差

SELECT
    warehouse,
    product, 
    SUM(quantity) qty
FROM
    inventory
GROUP BY
    warehouse,
    product
UNION ALL
SELECT
    warehouse, 
    null,
    SUM(quantity) qty
FROM
    inventory
GROUP BY
    warehouse
UNION ALL
SELECT
    null,
    product, 
    SUM(quantity) qty
FROM
    inventory
GROUP BY
    product
UNION ALL
SELECT
    null,
    null,
    SUM(quantity) qty
FROM
    inventory;

（2）使用grouping sets

特点：可读性和执行速度更优

SELECT
    warehouse,
    product, 
    SUM (quantity) qty
FROM
    inventory
GROUP BY
    GROUPING SETS(
        (warehouse,product),
        (warehouse),
        (product),
        ()
    );

最终查询出来的结果都是：

+---------------+---------+------+
| warehouse     | product | qty  |
+---------------+---------+------+
| San Fransisco | iPhone  | 260  |
| San Fransisco | Samsung | 300  |
| San Jose      | iPhone  | 300  |
| San Jose      | Samsung | 350  |
| San Fransisco | NULL    | 560  |
| San Jose      | NULL    | 650  |
| NULL          | iPhone  | 560  |
| NULL          | Samsung | 650  |
| NULL          | NULL    | 1210 |
+---------------+---------+------+
9 rows in set

2. partition by

主要语法：

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

简单来说，partition by 用于分组，order by用于排序

实例：分别找出学生的总成绩年级排名、总成绩班级排名、单科年级排名

1）原表：

+---------------+---------+------+--------+
| 班级 | 姓名 | 语文 |  数学 |  英语  | 总成绩
+---------------+---------+------+--------+
| 1班  |  A   | 90  |  100  |  96   |  286 
| 1班  |  B   | 80  |  80   |  98   |  258
| 2班  |  C   | 80  |  75   |  92   |  247
| 2班  |  D   | 88  |  96   |  89   |  273
| 3班  |  E   | 98  |  98   |  80   |  276
| 3班  |  F   | 90  |  100  |  99   |  289
+---------------+---------+------+--------+

2）查询语句：

select 班级,
	   姓名,
	   语文,
	   数学,
	   英语,
	   row_number() over (order by 总成绩 desc) as 总成绩排名,
	   row_number() over (partition by 班级 order by 总成绩 desc) as 班级排名,
	   row_number over (order by 语文 desc) as 语文排名,
	   row_number over (order by 数学 desc) as 数学排名,
	   row_number over (order by 英语 desc) as 英语排名
from 成绩表