PostgreSQL 14 group by distinct子句分组去重_postgresql 分组后根据某一字段去重

我们通过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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

需要注意并不等价于先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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18