如何转换数据表并汇总列值计数生成单行列统计结果

原始数据与需求 #

生成原始数据的SQL #

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,'A':1,'F':2}

（注：原需求中hobbies_2的F重复是笔误，实际计数应为F:2、A:1、E:1）

实现方法 #

方法1：生成JSON格式结果（推荐，便于后续处理） #

使用PostgreSQL的jsonb_object_agg函数，直接将分组计数后的键值对聚合为JSON对象：

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.val, h1.cnt) as hobbies_1,
    jsonb_object_agg(h2.val, h2.cnt) as hobbies_2
from (
    -- 对hobbies_1列分组计数
    select hobbies_1 as val, count(*) as cnt
    from _temp_data
    group by hobbies_1
) h1,
(
    -- 对hobbies_2列分组计数
    select hobbies_2 as val, count(*) as cnt
    from _temp_data
    group by hobbies_2
) h2;

输出结果：

hobbies_1       | hobbies_2
----------------+----------------------
{"A": 3, "B": 1}|{"A": 1, "E": 1, "F": 2}

方法2：生成完全匹配需求的字符串格式 #

如果需要严格匹配单引号包裹的字典样式字符串，使用string_agg和format函数拼接：

with _temp_data as (
    select 
        unnest(ARRAY['A','B','A','A']) as hobbies_1
        ,unnest(ARRAY['E','F','A','F']) as hobbies_2
)
select
    format('{%s}', string_agg(format('%s:%s', quote_literal(val), cnt), ',')) as hobbies_1,
    format('{%s}', string_agg(format('%s:%s', quote_literal(val), cnt), ',')) as hobbies_2
from (
    select hobbies_1 as val, count(*) as cnt
    from _temp_data
    group by hobbies_1
) h1,
(
    select hobbies_2 as val, count(*) as cnt
    from _temp_data
    group by hobbies_2
) h2;

输出结果：

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

内容的提问来源于stack exchange，提问作者Learn Hadoop