在SELECT中创建数组 [英] Create array in SELECT

问题描述

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

I'm using PostgreSQL 9.1 and I have this data structure:

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行，A = 1，部分计数（3个带c值的行，2个带值的行，...）

A=1, 4 rows total with A=1, the partial counts (3 rows with c value, 2 rows with a value, .....)

• A列的不同值


• 与 A值相关的所有行的计数


• 一个数组包含与 A值相关的所有元素以及自身的相对计数

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

The sort needed for the array is based of the count of each group (like the example 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;

返回值：

• ct_total ：每个 a 的 b 个总数


• ct_distinct_b ：每个 b 计数> a 。


• b_arr ： b 加上 b 的频率，按 b 的频率排序。



• ct_total: total count of b per a.
• ct_distinct_b: count of distinct b per a.
• b_arr: array of b plus frequency of b, sorted by frequency of b.

按每个 a 的 b 个总数进行排序。

Ordered by total count of b per a.

或者，您可以使用 PostgreSQL 9.0或更高版本中的聚集调用中的 ORDER BY 子句。像：

Alternatively, you can use an ORDER BY clause within the aggregate call in PostgreSQL 9.0 or later. Like:

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:

• 如何申请

• How to apply ORDER BY and LIMIT in combination with an aggregate function?

这篇关于在SELECT中创建数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！