Sql 如何转换和汇总行数

发布于03月19日

这是我的数据

with _temp_data as 
(

select 
    unnest(ARRAY['A','B','A','A']) as hobbies_1
    ,unnest(ARRAY['E','F','A','F']) as hobbies_2
)
select * from _temp_data

输入数据:

hobbies_1|hobbies_2|
---------+---------+
A        |E        |
B        |F        |
A        |A        |
A        |F        |

我想将该表转换为单行组合，如下所示

hobbies_1      |  hobbies_2
{'A':3,'B':1}  |{'E':1,'F':2,'F':1}

推荐答案

在jsonb_object_agg的帮助下，您可以使用下面的查询来实现这一点，

    WITH _temp_data AS (
    SELECT
        unnest(ARRAY['A','B','A','A']) AS hobbies_1,
        unnest(ARRAY['E','F','A','F']) AS hobbies_2
)
SELECT
    jsonb_object_agg(h1, count_h1) AS hobbies_1,
    jsonb_object_agg(h2, count_h2) AS hobbies_2
FROM (
    SELECT
        h1,
        COUNT(*) AS count_h1
    FROM (
        SELECT
            hobbies_1 AS h1
        FROM
            _temp_data
    ) AS subquery
    GROUP BY
        h1
) AS aggregated_hobbies_1
CROSS JOIN (
    SELECT
        h2,
        COUNT(*) AS count_h2
    FROM (
        SELECT
            hobbies_2 AS h2
        FROM
            _temp_data
    ) AS subquery
    GROUP BY
        h2
) AS aggregated_hobbies_2;

内部子查询只按它们所关注的相应列(hobbies_1和hobbies_2)进行 Select 和分组，然后外部查询执行聚合和交叉连接