>我想在PostgresSQL的字符串数组中获取所有具有特定值(或值集(的记录组。例如,采用下表
+---------+------------+--------+
| user_id | date | group |
+---------+------------+--------+
| 1 | 2019-02-06 | groupA |
| 1 | 2019-02-06 | groupB |
| 1 | 2019-02-06 | groupC |
| 2 | 2019-02-06 | groupB |
| 2 | 2019-02-06 | groupC |
| 3 | 2019-02-06 | groupA |
| 3 | 2019-02-06 | groupC |
+---------+------------+--------+
我可以使用以下查询对它们进行分组:
SELECT
user_id,
date,
string_agg(group, ',') as groups
FROM t
GROUP BY
user_id,
date
它给出了以下内容;
+---------+------------+-------------------------+
| user_id | date | groups |
+---------+------------+-------------------------+
| 1 | 2019-02-06 | {groupA,groupB,groupC} |
| 2 | 2019-02-06 | {groupB,groupC} |
| 3 | 2019-02-06 | {groupA,groupC} |
+---------+------------+-------------------------+
但我只想要包含groupA
的组.也就是说,这是所需的输出:
+---------+------------+-------------------------+
| user_id | date | groups |
+---------+------------+-------------------------+
| 1 | 2019-02-06 | {groupA,groupB,groupC} |
| 3 | 2019-02-06 | {groupA,groupC} |
+---------+------------+-------------------------+
如何筛选出不包含groupA
的行?
这是我尝试的一个查询,但没有成功:
SELECT
user_id,
date,
string_agg(group, ',') as groups
FROM t
GROUP BY
user_id,
date
HAVING 'groupA' IN string_agg(group, ',')
IN
不能与元素列表一起使用,string_agg()
返回单个字符值。
但是您可以将这些值聚合到一个数组中,该数组可以在 having 子句中使用:
SELECT user_id,
"date",
string_agg("group", ',') as groups
FROM t
GROUP BY user_id, "date"
HAVING 'groupA' = any (array_agg("group"));
另一种选择是使用包含运算符@>
SELECT user_id,
"date",
string_agg("group", ',') as groups
FROM t
GROUP BY user_id, date
HAVING array_agg("group") @> array['groupA']::varchar[]