PostgreSQL无tablefunc扩展权限时实现国家维度交叉表透视

需求背景 #

原始数据格式为国家 校验结果JSON 计数结构，样例如下：

Algeria '{"validated": "correct"}' 5
Algeria '{"validated": "incorrect"}' 1
USA '{"validated": "correct"}' 486
USA '{"validated": "incorrect"}' 21

需要透视成按国家为行、校验结果为列的交叉表，预期输出：

Country Correct Incorrect
Algeria 5       1 
USA     486     21

原有实现依赖tablefunc扩展的crosstab函数，但当前账号无创建扩展权限，需要无依赖的替代写法。

替代方案：标准SQL条件聚合 #

不需要依赖任何扩展，直接使用标准SQL支持的CASE WHEN+聚合函数即可实现完全一致的透视效果，改写后的SQL如下：

SELECT
    country,
    COUNT(CASE WHEN validate_obj = '{"validated": "correct"}' THEN 1 END) AS Correct,
    COUNT(CASE WHEN validate_obj = '{"validated": "incorrect"}' THEN 1 END) AS Incorrect
FROM (
    SELECT
        adg.article_id,
        to_timestamp(adg.ts_start),
        adv.validate_obj,
        regexp_replace(location_name, '.*,', '') AS country
    FROM table1 adg
    INNER JOIN table2 ade ON adg.article_id = ade.article_id
    INNER JOIN table3 adv ON adg.article_id = adv.article_id
    WHERE adv.ts_end != 0
) AS rollup_table
GROUP BY country
ORDER BY country;

写法说明 #

• 内层子查询和原crosstab逻辑完全一致，不需要修改原有表关联、过滤、字段生成的逻辑
• 按country字段分组后，通过CASE WHEN分别匹配两种校验结果值，匹配成功返回非空值、不匹配返回空值，COUNT函数会自动忽略空值，直接统计出对应分类的计数，和原crosstab返回结果完全一致
• 该写法为ANSI标准SQL，无任何扩展依赖和特殊权限要求，所有支持SQL的关系型数据库都可以运行
• 后续如果需要新增其他校验结果的统计列，只需要新增对应CASE WHEN的统计字段即可，调整成本比crosstab更低

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