如何无需创建大量基于metric的CTE，向数据集添加堆叠计算字段

问题背景 #

现有长格式指标数据集如下：

需计算每月**CTOR（clicks/opens）**并合并到原数据集，且后续要生成多个类似衍生指标，需避免创建大量按metric拆分的CTE。

实现方法 #

方法1：宽表聚合+转长表合并 #

先将长表转成宽表（按月份聚合，提取opens和clicks值），再计算衍生指标，最后转成长表与原数据合并。此方法逻辑清晰，便于扩展多个指标：

-- 原数据（如果是真实表可直接引用，无需CTE）
WITH raw_data AS (
    SELECT metric, month, value FROM your_source_table
),
-- 按月份聚合，获取各核心指标值
monthly_agg AS (
    SELECT
        month,
        MAX(CASE WHEN metric = 'opens' THEN value END) AS opens,
        MAX(CASE WHEN metric = 'clicks' THEN value END) AS clicks
    FROM raw_data
    GROUP BY month
),
-- 生成所有衍生指标的长表
derived_metrics AS (
    -- CTOR指标
    SELECT 'ctor' AS metric, month, ROUND(clicks / opens::NUMERIC, 2) AS value
    FROM monthly_agg
    WHERE opens > 0 -- 避免除以0
    
    -- 新增其他指标只需加UNION ALL
    UNION ALL
    SELECT 'click_percentage' AS metric, month, ROUND((clicks * 100) / opens::NUMERIC, 1) AS value
    FROM monthly_agg
    WHERE opens > 0
)
-- 合并原数据与衍生指标
SELECT metric, month, value FROM raw_data
UNION ALL
SELECT metric, month, value FROM derived_metrics
ORDER BY month, metric;

方法2：窗口函数直接计算 #

无需转宽表，通过窗口函数按月份获取对应指标值，直接计算衍生指标，代码更简洁：

-- 保留原数据
SELECT metric, month, value FROM your_source_table
UNION ALL
-- 生成CTOR指标
SELECT
    'ctor' AS metric,
    month,
    ROUND(
        MAX(CASE WHEN metric = 'clicks' THEN value END) OVER (PARTITION BY month)
        / MAX(CASE WHEN metric = 'opens' THEN value END) OVER (PARTITION BY month)::NUMERIC,
        2
    ) AS value
FROM your_source_table
GROUP BY month

-- 新增其他指标只需继续UNION ALL
UNION ALL
SELECT
    'click_percentage' AS metric,
    month,
    ROUND(
        (MAX(CASE WHEN metric = 'clicks' THEN value END) OVER (PARTITION BY month) * 100)
        / MAX(CASE WHEN metric = 'opens' THEN value END) OVER (PARTITION BY month)::NUMERIC,
        1
    ) AS value
FROM your_source_table
GROUP BY month

ORDER BY month, metric;

核心优势 #

• 两种方法都不需要为每个原始metric单独创建CTE，只需维护聚合或窗口逻辑
• 新增衍生指标时，只需在derived_metrics（方法1）或新增UNION ALL块（方法2）中添加计算逻辑即可，无需重复处理原数据
• 避免了大量重复的CTE定义，代码更易维护和扩展

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