用于查找唯一列和连接两列结果的 SQL 查询

Question

我有一个包含以下信息的表A。

+----+-------+-------+-----+--------+-----------+
| ID | jobID | user  | ip  |  time  | userAgent |
+----+-------+-------+-----+--------+-----------+
|  1 | uuid1 | user1 | IP1 | Epoch1 | JSONB1    |
|  2 | uuid1 | user1 | IP2 | Epoch2 | JSONB2    |
|  3 | uuid2 | user3 | IP3 | Epoch3 | JSONB3    |
|  4 | uuid1 | user2 | IP4 | Epoch2 | JSONB2    |
|  5 | uuid1 | user4 | IP5 | Epoch4 | JSONB4    |
|  6 | uuid1 | user1 | IP2 | Epoch2 | JSONB2    |
+----+-------+-------+-----+--------+-----------+

我只想要唯一出现的 (jobID & user) 并按以下方式连接用户的结果。

+-------+-------------------+-------------+
| jobID |       user        | count(user) |
+-------+-------------------+-------------+
| uuid1 | user1,user2,user4 |           3 |
| uuid2 | user3             |           1 |
+-------+-------------------+-------------+

现在我写了一个类似

的查询

SELECT
 DISTINCT 
  "jobID",
  "user"
 FROM
  "A"
 ORDER BY
  "jobID",
  "user";

它给了我

+-------+-------+
| jobID | user  |
+-------+-------+
| uuid1 | user1 |
| uuid1 | user1 |
| uuid2 | user3 |
| uuid1 | user2 |
| uuid1 | user4 |
+-------+-------+

Answer 1

您应该按 "jobID" 对数据进行分组，并使用 distinct 的聚合函数:

select 
    "jobID", 
    string_agg(distinct "user", ',') as users, 
    count(distinct "user")
from "A"
group by "jobID"
order by "jobID";