postgresql 在 SELECT 中创建数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9796092/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create array in SELECT
提问by Deviling Master
I'm using PostgreSQL 9.1 and I have this data structure:
我正在使用 PostgreSQL 9.1 并且我有这个数据结构：
A B
-------
1 a
1 a
1 b
1 c
1 c
1 c
1 d
2 e
2 e
I need a query that produces this result:
我需要一个产生这个结果的查询：
1 4 {{c,3},{a,2},{b,1},{d,1}}
2 1 {{e,2}}
A=1, 4 rows total with A=1, the partial counts (3 rows with c value, 2 rows with a value, .....)
A=1，A=1共4行，部分计数（3行c值，2行a值，.....）
- The distinct values of column "A"
- The count of all rows related to the "A" value
- An array contains all the elements related to the "A" value and the relative count of itself
- “A”列的不同值
- 与“A”值相关的所有行的计数
- 一个数组包含所有与“A”值相关的元素以及它自身的相对计数
The sort needed for the array is based of the count of each group (like the example 3,2,1,1).
数组所需的排序基于每个组的计数（如示例 3,2,1,1）。
回答by Erwin Brandstetter
This should do the trick:
这应该可以解决问题：
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: total count ofbpera.ct_distinct_b: count of distinctbpera.b_arr: array ofbplus frequency ofb, sorted by frequency ofb.
ct_total：总计数b每a。ct_distinct_b:b每个a.b_arr:b加频率的数组b，按频率排序b。
Ordered by total count of bper a.
按bper的总数排序a。
Alternatively, you can use an ORDER BYclause within the aggregate callin PostgreSQL 9.0 or later. Like:
或者，您可以在 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 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;May be clearer. But it's typically slower. And sorting rows in a subquery works for simple queries like this one. More explanation:
可能会更清楚。但它通常更慢。子查询中的行排序适用于像这样的简单查询。更多解释：
回答by a_horse_with_no_name
Maybe I'm missing something, but this should do it:
也许我错过了一些东西，但这应该可以：
SELECT a,
count(*) as cnt,
array_agg(b) as all_values
FROM your_table
GROUP BY a
回答by Adriano Carneiro
This is what you need:
这是你需要的：
SELECT A, COUNT(*), array_agg(b)
FROM YourTable
GROUP BY A
