如何在Presto SQL中按法规列分组并按状态透视统计任务数

要实现你的需求，核心是先将多列的法规字段行转列，把每条任务关联的所有有效法规（标记为Yes）拆分成独立行，之后再按法规分组统计不同状态的任务数量。以下是两种可行的实现方案：

方案一：条件聚合（兼容性更强） #

这种方式逻辑清晰，在所有Presto版本中都能正常运行：

WITH normalized_regulations AS (
    SELECT 
        task,
        reg.regulation_name,
        status
    FROM 
        your_task_table  -- 替换为你的实际表名
    UNNEST(
        ARRAY[
            CAST(ROW('Regulation1', Regulation1) AS ROW(regulation_name VARCHAR, flag VARCHAR)),
            CAST(ROW('Regulation2', Regulation2) AS ROW(regulation_name VARCHAR, flag VARCHAR)),
            CAST(ROW('Regulation3', Regulation3) AS ROW(regulation_name VARCHAR, flag VARCHAR))
        ]
    ) AS t(reg)
    WHERE reg.flag = 'Yes'  -- 只保留任务关联的有效法规
)
SELECT
    regulation_name,
    COUNT(CASE WHEN status = 'On Track' THEN 1 END) AS on_track_count,
    COUNT(CASE WHEN status = 'At Risk' THEN 1 END) AS at_risk_count,
    COUNT(CASE WHEN status = 'Blocked' THEN 1 END) AS blocked_count
FROM normalized_regulations
GROUP BY regulation_name
ORDER BY regulation_name;

代码说明： #

1. normalized_regulations 通用表表达式（CTE）：
   • 用UNNEST+ARRAY将3个法规列转换为行数据，每一行对应一个法规名称和其关联标记
   • 过滤掉标记为No的记录，只保留任务实际关联的法规
2. 主查询：
   • 按法规名称分组，通过条件聚合分别统计每个状态下的任务数量

方案二：使用Presto PIVOT语法（更简洁） #

如果你的Presto版本支持PIVOT特性，可以用更简洁的语法实现：

WITH normalized_regulations AS (
    SELECT 
        task,
        reg.regulation_name,
        status
    FROM 
        your_task_table  -- 替换为你的实际表名
    UNNEST(
        ARRAY[
            CAST(ROW('Regulation1', Regulation1) AS ROW(regulation_name VARCHAR, flag VARCHAR)),
            CAST(ROW('Regulation2', Regulation2) AS ROW(regulation_name VARCHAR, flag VARCHAR)),
            CAST(ROW('Regulation3', Regulation3) AS ROW(regulation_name VARCHAR, flag VARCHAR))
        ]
    ) AS t(reg)
    WHERE reg.flag = 'Yes'
)
SELECT *
FROM normalized_regulations
PIVOT (
    COUNT(task)
    FOR status IN ('On Track', 'At Risk', 'Blocked')
) AS p
ORDER BY regulation_name;

预期输出（基于你的示例数据） #

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