在SELECT语句中创建数组

Question

我正在使用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，A=1的总行数为4，局部计数（有c值的3行，有a值的2行，...）

• 列"A"的不同值
• 与"A"值相关的所有行的计数
• 一个包含与"A"值相关的所有元素及其相对计数的数组

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

- Deviling Master

Answer 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;

返回：

• ct_total: 每个a中b的总数。
• ct_distinct_b: 每个a中不同b的数量。
• b_arr: b的数组加上b频率，按照b频率排序。

按每个a中b的总数排序。

另外，在PostgreSQL 9.0或更高版本中，您可以在聚合调用中使用ORDER BY子句。格式如下：

SELECT a
     , sum(ab_ct)::int AS ct_total
     , count(*)::int   AS ct_distinct_b
     , array_agg(b || ', ' || ab_ct::text <b>ORDER BY a, ab_ct DESC, b</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？

- Erwin Brandstetter

Answer 2

也许我有所遗漏，但是这应该可以解决问题：

SELECT a, 
       count(*) as cnt,
       array_agg(b) as all_values
FROM your_table
GROUP BY a

- user330315

Answer 3

这是你需要的内容：

SELECT A, COUNT(*), array_agg(b)
FROM YourTable
GROUP BY A

- Adriano Carneiro