在 SELECT 中创建数组

我正在使用 PostgreSQL 9.1，并且有以下数据结构：

A     B
-------
1     a
1     a
1     b
1     c
1     c
1     c
1     d
2     e
2     e

我需要一个产生此结果的查询：

1    4     {{c,3},{a,2},{b,1},{d,1}}
2    1     {{e,2}}

A=1，总共 4 行，A=1，部分计数（3 行有 c 值，2 行有值，......）

• “A”列的不同值
• 与“A”值相关的所有行的计数
• 数组包含与“A”值相关的所有元素及其自身的相对计数

数组所需的排序基于每个组的计数（如示例 3,2,1,1）。

这应该可以解决问题：

SELECT a
     , sum(ab_ct)::int AS ct_total
     , count(*)::int   AS ct_distinct_b
     , array_agg(b || ', ' || ab_ct::text) AS b_arr
FROM  (
    SELECT a, b, count(*) AS ab_ct
    FROM   tbl
    GROUP  BY a, b
    ORDER  BY a, ab_ct DESC, b  -- append "b" to break ties in the count
    ) t
GROUP  BY a
ORDER  BY ct_total DESC;

Returns:

• ct_total: 总数b per a.
• ct_distinct_b：不同的计数b per a.
• b_arr: 数组b加上频率b，按频率排序b.

按总数排序b per a.

或者，您可以使用ORDER BY聚合调用中的子句 https://www.postgresql.org/docs/current/functions-aggregate.html在 PostgreSQL 9.0 或更高版本中。喜欢：

SELECT a
     , sum(ab_ct)::int AS ct_total
     , count(*)::int   AS ct_distinct_b
     , array_agg(b || ', ' || ab_ct::text ORDER BY a, ab_ct DESC, b) AS b_arr
FROM  (
    SELECT a, b, count(*) AS ab_ct
    FROM   tbl
    GROUP  BY a, b
    ) t
GROUP  BY a
ORDER  BY ct_total DESC;

可能会更清楚。但通常速度较慢。对子查询中的行进行排序适用于像这样的简单查询。更多解释：

• 如何将 ORDER BY 和 LIMIT 与聚合函数结合应用？ https://dba.stackexchange.com/a/213724/3684