Redshift中如何动态生成按分类统计点击数的SQL查询

在Redshift中有两张表articles和clicks，结构及数据如下：

articles表 #

| articleID | authorID |
| 100       |   2      |
| 101       |   2      |
| 102       |   6      |
| 103       |   7      |
| 104       |   2      |

clicks表 #

|articleID  | category |
|100        | "mail"   |
|100        | "mail"   |
|100        | "rss"    |
|101        | "rss"    |
|101        | "mail"   |
|101        | "app"    |
|101        | "app"    |

当前使用静态SQL可按articleID聚合各分类点击数及总点击数，SQL如下：

SELECT clicks.articleID,
ANY_VALUE(articles.authorID) AS authorID,

COUNT(CASE WHEN clicks.category ='rss' THEN 1 END) as rss,
COUNT(CASE WHEN clicks.category ='mail' THEN 1 END) as mail,
COUNT(CASE WHEN clicks.category ='app' THEN 1 END) as app,

COUNT(clicks.articleID) AS total
FROM clicks
INNER JOIN articles
ON clicks.articleID = articles.articleID

GROUP BY clicks.articleID

查询结果符合预期，但希望实现动态生成分类列：当clicks表新增分类（如"facebook"）时，无需修改SQL即可自动新增对应统计列。已尝试通过CTE获取所有分类：

WITH categories AS (SELECT category from clicks GROUP BY category)

但不知如何在主查询中遍历分类动态生成统计逻辑，寻求无需手动拼接SQL的优雅实现方式。

Redshift基于PostgreSQL构建，SQL解析阶段要求列数固定，因此纯静态SQL无法实现动态列生成，可通过以下两种方式满足需求：

1. Redshift存储过程自动生成动态SQL #

通过存储过程自动查询所有分类、拼接统计逻辑并执行，实现分类列的动态适配：

CREATE OR REPLACE PROCEDURE get_dynamic_click_stats()
LANGUAGE plpgsql
AS $$
DECLARE
    category_list TEXT;
    dynamic_sql TEXT;
BEGIN
    -- 拼接所有分类对应的CASE统计语句
    SELECT STRING_AGG(
        'COUNT(CASE WHEN clicks.category = ''' || category || ''' THEN 1 END) AS ' || category,
        ', '
    ) INTO category_list
    FROM (SELECT DISTINCT category FROM clicks) AS cat;

    -- 组装完整动态查询SQL
    dynamic_sql := '
        SELECT clicks.articleID,
        ANY_VALUE(articles.authorID) AS authorID,
        ' || category_list || ',
        COUNT(clicks.articleID) AS total
        FROM clicks
        INNER JOIN articles ON clicks.articleID = articles.articleID
        GROUP BY clicks.articleID
    ';

    -- 执行动态SQL
    EXECUTE dynamic_sql;
END;
$$;

调用存储过程即可获取包含所有分类列的统计结果：

CALL get_dynamic_click_stats();

当clicks表新增分类时，存储过程会自动识别并新增对应统计列，无需修改代码。

2. 行式聚合+上层工具转列 #

若不想使用存储过程，可先按articleID+category维度输出统计结果，再由前端/BI工具（如Tableau、Power BI）完成行转列的动态转换：

SELECT 
    clicks.articleID,
    ANY_VALUE(articles.authorID) AS authorID,
    clicks.category,
    COUNT(*) AS click_count,
    SUM(COUNT(*)) OVER (PARTITION BY clicks.articleID) AS total
FROM clicks
INNER JOIN articles ON clicks.articleID = articles.articleID
GROUP BY clicks.articleID, clicks.category;

这种方式SQL简洁稳定，将动态列转换逻辑交给上层工具处理，适合有BI系统支撑的场景。

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