如何利用grouping实现集合操作

发布网友 发布时间：2022-04-22 01:14

我来回答

共2个回答

热心网友 时间：2022-04-11 18:14

声明:本人不是专门学数据库的,也不是专门的翻译,只是因为碰到一个问题(SQL CookBook中)找了一下,发现一个英文网站的解释很清晰,特此翻译过来,mark.不喜勿砖,谢谢!
原文链接:
ROLLUP, CUBE, GROUPING Functions and GROUPING SETS
*/
环境:

DROP TABLE dimension_tab;
CREATE TABLE dimension_tab (
fact_1_id NUMBER NOT NULL,
fact_2_id NUMBER NOT NULL,
fact_3_id NUMBER NOT NULL,
fact_4_id NUMBER NOT NULL,
sales_value NUMBER(10,2) NOT NULL
);

INSERT INTO dimension_tab
SELECT TRUNC(DBMS_RANDOM.value(low => 1, high => 3)) AS fact_1_id,
TRUNC(DBMS_RANDOM.value(low => 1, high => 6)) AS fact_2_id,
TRUNC(DBMS_RANDOM.value(low => 1, high => 11)) AS fact_3_id,
TRUNC(DBMS_RANDOM.value(low => 1, high => 11)) AS fact_4_id,
ROUND(DBMS_RANDOM.value(low => 1, high => 100), 2) AS sales_value
FROM al
CONNECT BY level <= 1000;
COMMIT;

1.Group by 基本用法
为了方便理解,先用一个聚合函数,求和.(未使用group by)

SELECT SUM(sales_value) AS sales_value
FROM dimension_tab;

SALES_VALUE
-----------
50528.39

1 row selected.

SQL>
SELECT SUM(sales_value) AS sales_value
FROM dimension_tab;

SALES_VALUE
-----------
50528.39

1 row selected.

SQL>

将想要分组的列放在group by 之后.结果的行数是我们目标列中包含不同值的个数.

SELECT fact_1_id,
COUNT(*) AS num_rows,
SUM(sales_value) AS sales_value
FROM dimension_tab
GROUP BY fact_1_id
ORDER BY fact_1_id;

FACT_1_ID NUM_ROWS SALES_VALUE
---------- ---------- -----------
1 478 24291.35
2 522 26237.04

2 rows selected.

SQL>

如果包含两列,将会产生聚合结果(解释为笛卡尔积?),返回10列(2*5)

SELECT fact_1_id,
fact_2_id,
COUNT(*) AS num_rows,
SUM(sales_value) AS sales_value
FROM dimension_tab
GROUP BY fact_1_id, fact_2_id
ORDER BY fact_1_id, fact_2_id;

FACT_1_ID FACT_2_ID NUM_ROWS SALES_VALUE
---------- ---------- ---------- -----------
1 1 83 4363.55
1 2 96 4794.76
1 3 93 4718.25
1 4 105 5387.45
1 5 101 5027.34
2 1 109 5652.84
2 2 96 4583.02
2 3 110 5555.77
2 4 113 5936.67
2 5 94 4508.74

10 rows selected.

SQL>

2.ROLLUP
出了正常的分组结果外,使用rollup还会,返回部分列分组,规则:从右向左,知道一个完整的分组.如果,rollup中的列数为n,那么将会有n+1个分组层次.

SELECT fact_1_id,
fact_2_id,
SUM(sales_value) AS sales_value
FROM dimension_tab
GROUP BY ROLLUP (fact_1_id, fact_2_id)
ORDER BY fact_1_id, fact_2_id;

FACT_1_ID FACT_2_ID SALES_VALUE
---------- ---------- -----------
1 1 4363.55
1 2 4794.76
1 3 4718.25
1 4 5387.45
1 5 5027.34
1 24291.35
2 1 5652.84
2 2 4583.02
2 3 5555.77
2 4 5936.67
2 5 4508.74
2 26237.04
50528.39

13 rows selected.

SQL>

以下语句结果:Click Here.当行中包含null的时候,这种并不是一种很好的方法,稍后会讨论.

SELECT fact_1_id,
fact_2_id,
fact_3_id,
SUM(sales_value) AS sales_value
FROM dimension_tab
GROUP BY ROLLUP (fact_1_id, fact_2_id, fact_3_id)
ORDER BY fact_1_id, fact_2_id, fact_3_id;

也可以在group by的时候使用rolluo做部分分组.结果:Click Here

SELECT fact_1_id,
fact_2_id,
fact_3_id,
SUM(sales_value) AS sales_value
FROM dimension_tab
GROUP BY fact_1_id, ROLLUP (fact_2_id, fact_3_id)
ORDER BY fact_1_id, fact_2_id, fact_3_id;

3.CUBE
Cube是rollup的拓展,Cube将会返回所有分组的一个统计,如果,Cube中有n组,那么将返回2的n次方组合结果.

SELECT fact_1_id,
fact_2_id,
SUM(sales_value) AS sales_value
FROM dimension_tab
GROUP BY CUBE (fact_1_id, fact_2_id)
ORDER BY fact_1_id, fact_2_id;

FACT_1_ID FACT_2_ID SALES_VALUE
---------- ---------- -----------
1 1 4363.55
1 2 4794.76
1 3 4718.25
1 4 5387.45
1 5 5027.34
1 24291.35
2 1 5652.84
2 2 4583.02
2 3 5555.77
2 4 5936.67
2 5 4508.74
2 26237.04
1 10016.39
2 9377.78
3 10274.02
4 11324.12
5 9536.08
50528.39

18 rows selected.

SQL>

如果cube中有三组(或n组)时,结果(或需计算):Click Here

SELECT fact_1_id,
fact_2_id,
fact_3_id,
SUM(sales_value) AS sales_value
FROM dimension_tab
GROUP BY CUBE (fact_1_id, fact_2_id, fact_3_id)
ORDER BY fact_1_id, fact_2_id, fact_3_id;

也有可能只是一部分进行cube分组,结果:Click Here

SELECT fact_1_id,
fact_2_id,
fact_3_id,
SUM(sales_value) AS sales_value
FROM dimension_tab
GROUP BY fact_1_id, CUBE (fact_2_id, fact_3_id)
ORDER BY fact_1_id, fact_2_id, fact_3_id;

4.Grouping 函数
上面曾经说过,如果某一列存在null值,而我们的rollup或者cube时,也将会出现列为null的时候,该怎样区分null到底是由数据null还是由于rollup活cube自己生成的null呢.这里使用grouping来解决这个问题.如果是数据本身的值(null或其他值),grouping(列名)将会在这一行返回0,如果这一行是由于rollup或者cube产生的话,将会返回1.

SELECT fact_1_id,
fact_2_id,
SUM(sales_value) AS sales_value,
GROUPING(fact_1_id) AS f1g,
GROUPING(fact_2_id) AS f2g
FROM dimension_tab
GROUP BY CUBE (fact_1_id, fact_2_id)
ORDER BY fact_1_id, fact_2_id;

FACT_1_ID FACT_2_ID SALES_VALUE F1G F2G
---------- ---------- ----------- ---------- ----------
1 1 4363.55 0 0
1 2 4794.76 0 0
1 3 4718.25 0 0
1 4 5387.45 0 0
1 5 5027.34 0 0
1 24291.35 0 1
2 1 5652.84 0 0
2 2 4583.02 0 0
2 3 5555.77 0 0
2 4 5936.67 0 0
2 5 4508.74 0 0
2 26237.04 0 1
1 10016.39 1 0
2 9377.78 1 0
3 10274.02 1 0
4 11324.12 1 0
5 9536.08 1 0
50528.39 1 1

18 rows selected.

SQL>

可以看出:(都是由rollup或cube所产生的)
F1G=0,F2G=0 : 正常的group by结果
F1G=0,F2G=1 : 一行 FACT_1_ID 列的统计
F1G=1,F2G=0 : 一行FACT_2_ID 列的统计
F1G=1,F2G=1 : 由FACT_1_ID 和FACT_2_ID 所产生的一个统计
grouping函数可以用来筛选排序结果.

SELECT fact_1_id,
fact_2_id,
SUM(sales_value) AS sales_value,
GROUPING(fact_1_id) AS f1g,
GROUPING(fact_2_id) AS f2g
FROM dimension_tab
GROUP BY CUBE (fact_1_id, fact_2_id)
HAVING GROUPING(fact_1_id) = 1 OR GROUPING(fact_2_id) = 1
ORDER BY GROUPING(fact_1_id), GROUPING(fact_2_id);

热心网友 时间：2022-04-11 19:32

MySQL 8.0 新增了 GROUPING() 函数，用来理清 GROUP BY with rollup 子句检索后所产生的每个分组汇总结果。 grouping 可用在分组列，having 子句以及 order by 子句。

GROUPING() 函数用来返回每个分组是否为 ROLLUP 结果，是为 1 否为 0。从结果中，很容易就能区分哪些 NULL 是正常记录，哪些是 ROLLUP 的结果。

GROUPING() 函数不仅仅是针对单个字段来统计汇总值，还可以针对多个字段。

此时会发现，GROUPING() 函数对多个字段结果并非只有 1 和 0，还有一个值为 3。在 GROUPING() 函数包含多个参数时，按照以下方式来返回结果：

GROUPING(r1,r2) 等价于 GROUPING(r2)  + GROUPING(r1) << 1

GROUPING(r1,r2,r3,...) 等价于 GROUPING(r3) + GROUPING(r2) << 1 + GROUPING(r1) << 2

以此类推

GROUPING 语句还可以用在 HAVING 子句里，比如用 GROUPING 子句来过滤掉非 ROLLUP 的结果。