postgresql string_agg（），filter用法

我们在分组后，可以查出分组中复合条件的count，以及分组的count。

postgres=# create table test(id int, c1 int);
CREATE TABLE
postgres=# insert into test select generate_series(1,10000), random()*10;
INSERT 0 10000

postgres=# \d+ test
Table "public.test"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
id | integer | | | | plain | |
c1 | integer | | | | plain | |

postgres=# select * from test limit 10;
id | c1
----+----
1 | 5
2 | 4
3 | 4
4 | 8
5 | 7
6 | 4
7 | 4
8 | 3
9 | 3
10 | 7
(10 rows)

postgres=# select count(), count() filter (where id<1000) from test group by c1;
count | count
-------+-------
1018 | 94
982 | 105
1044 | 107
1004 | 88
502 | 56
500 | 35
983 | 101
970 | 81
960 | 103
1010 | 115
1027 | 114
(11 rows)

postgres=# select c1,count(), count() filter (where id<1000) from test group by c1;
c1 | count | count
----+-------+-------
8 | 1027 | 114 按照c1分组，c1=8的有1027个，ID<1000的有114个
10 | 500 | 35
9 | 1018 | 94
7 | 960 | 103
1 | 1010 | 115
5 | 1044 | 107
2 | 970 | 81
4 | 1004 | 88
0 | 502 | 56
6 | 983 | 101
3 | 982 | 105
(11 rows)

postgres=# select c1,count(*) from test group by c1;
c1 | count
----+-------
8 | 1027 按照c1分组，c1=8的有1027个
10 | 500
9 | 1018
7 | 960
1 | 1010
5 | 1044
2 | 970
4 | 1004
0 | 502
6 | 983
3 | 982
(11 rows)

postgres=# select count(*) from test;
count

10000
(1 row)

select string_agg(id::text, '-' order by id) filter (where id<100) from test group by c1;
string_agg(表达式，分隔符);将一个表达式变成字符串
array_agg(表达式)，将表达式变成一个数组，一般配合array_to_string()使用

postgres=# select id,c1 from test where c1=8 limit 20;
id | c1
-----+----
4 | 8
13 | 8
20 | 8
31 | 8
45 | 8
55 | 8
65 | 8
73 | 8
79 | 8
81 | 8
82 | 8
86 | 8
93 | 8
94 | 8
95 | 8
97 | 8
108 | 8
109 | 8
116 | 8
132 | 8
(20 rows)

postgres=# select string_agg(id::text,'-') filter (where id<1000) from test where c1=8;
string_agg

4-13-20-31-45-55-65-73-79-81-82-86-93-94-95-97-108-109-116-132-141-152-155-166-181-198-201-214-217-226-230-249-25
7-263-267-268-307-315-317-322-323-332-349-356-360-367-374-378-382-384-399-416-440-455-470-471-475-490-534-546-547-
556-560-561-568-577-583-586-594-597-603-619-628-633-648-658-664-668-680-691-694-704-717-720-724-725-737-759-772-77
5-777-788-790-793-795-801-826-831-841-843-848-853-855-874-876-884-929-938-939-949-971-973-979-980
(1 row)

可以看到string_agg将id列转为text类型用分隔符'-'进行连接，将id<1000的进行连接起来
select string_agg(id::text, '-' order by id) filter (where id<100) from test group by c1;
postgres=# select string_agg(id::text, '-' order by id) filter (where id<100) from test group by c1;
string_agg

33-43-60-66-77-99
12-24-27-32-39-63-90-98
34-48-49-50-72-92
8-9-14-22-38-57-67-69-71-80-84-85-87
2-3-6-7-11-17-25-68-70-74
1-18-21-23-42-53-54-61
19-30-37-47-62-64-76-78-88-91-96
5-10-15-29-36-44-46-52-89
4-13-20-31-45-55-65-73-79-81-82-86-93-94-95-97
16-26-28-35-40-41-56-58-59-75-83
51
(11 rows)

select percentile_cont(0.5) within group (order by id) from test group by c1;

参考的原文链接：https://blog.csdn.net/qq_28125719/article/details/84884635