PostgreSQL 14 group by distinct子句分組去重

我們通過group by子句進行分組時，經常會出現資料重複的情況。例如GROUP BY CUBE (a,b), CUBE (b,c)便可能出現資料重複的情況。

PG14中支援group by distinct的文法，可以用來進行資料去重。

例子：

group by：

bill@bill=>select a, b, c
from (values (1, 2, 3), (4, null, 6), (7, 8, 9)) as t (a, b, c)
group by  rollup(a, b), rollup(a, c)
order by a, b, c;
 a | b | c
---+---+---
 1 | 2 | 3
 1 | 2 |
 1 | 2 |
 1 |   | 3
 1 |   | 3
 1 |   |
 1 |   |
 1 |   |
 4 |   | 6
 4 |   | 6
 4 |   | 6
 4 |   |
 4 |   |
 4 |   |
 4 |   |
 4 |   |
 7 | 8 | 9
 7 | 8 |
 7 | 8 |
 7 |   | 9
 7 |   | 9
 7 |   |
 7 |   |
 7 |   |
   |   |
(25 rows)
           

group by distinct：

bill@bill=>select a, b, c
bill-# from (values (1, 2, 3), (4, null, 6), (7, 8, 9)) as t (a, b, c)
bill-# group by distinct rollup(a, b), rollup(a, c)
bill-# order by a, b, c;
 a | b | c
---+---+---
 1 | 2 | 3
 1 | 2 |
 1 |   | 3
 1 |   |
 4 |   | 6
 4 |   | 6
 4 |   |
 4 |   |
 7 | 8 | 9
 7 | 8 |
 7 |   | 9
 7 |   |
   |   |
(13 rows)
           

需要注意并不等價于先distinct再group by：

bill@bill=>select distinct a, b, c
bill-# from (values (1, 2, 3), (4, null, 6), (7, 8, 9)) as t (a, b, c)
bill-# group by rollup(a, b), rollup(a, c)
bill-# order by a, b, c;
 a | b | c
---+---+---
 1 | 2 | 3
 1 | 2 |
 1 |   | 3
 1 |   |
 4 |   | 6
 4 |   |
 7 | 8 | 9
 7 | 8 |
 7 |   | 9
 7 |   |
   |   |
(11 rows)