如何在SELECT中为Zscore分区数据集添加带自定义标签的计算行？

我来帮你搞定这个需求！要在每个分组的查询结果里追加一行带自定义标签的统计行，我们可以通过**CTE（公共表表达式）**结合UNION ALL来实现，下面提供两种适配不同场景的方案：

方案1：基于分组聚合生成自定义行 #

如果你的现有查询还没用到窗口函数，先通过分组聚合计算每个组的平均值和标准差，再合并原数据和自定义行：

WITH grouped_stats AS (
    -- 第一步：计算每个分组的avg和stdev
    SELECT
        item,
        AVG(value) AS avg_val,
        STDEV(value) AS stdev_val
    FROM your_table
    GROUP BY item
),
original_records AS (
    -- 第二步：关联原数据与分组统计值，计算Z-score
    SELECT
        item,
        value,
        gs.avg_val AS avg,
        gs.stdev_val AS stdev,
        ROUND((value - gs.avg_val) / gs.stdev_val, 2) AS zscore,
        NULL AS custom_label
    FROM your_table t
    JOIN grouped_stats gs ON t.item = gs.item
)
-- 第三步：合并原数据与自定义行
SELECT * FROM original_records
UNION ALL
SELECT
    item,
    ROUND(avg_val + 1.5 * stdev_val, 2) AS value,
    avg_val AS avg,
    stdev_val AS stdev,
    NULL AS zscore,
    'My Label' AS custom_label
FROM grouped_stats
ORDER BY item, custom_label; -- 按分组排序，自定义行排在每组末尾

方案1说明： #

1. grouped_stats CTE：先按item分组计算每个组的核心统计值，避免重复计算；
2. original_records CTE：把原表数据和分组统计值关联，算出每行的Z-score，同时用NULL标记非自定义行；
3. UNION ALL：追加每个分组的自定义行，其中value为avg + 1.5*stdev，custom_label设为你要的"My Label"；
4. 用ROUND函数可以控制小数位数，和你示例里的3.2保持一致。

方案2：复用窗口函数结果生成自定义行 #

如果你的现有查询已经用窗口函数计算了avg和stdev，直接复用这些结果即可，不需要额外分组：

WITH original_data AS (
    -- 原查询：用窗口函数计算每行的avg、stdev、zscore
    SELECT
        item,
        value,
        AVG(value) OVER (PARTITION BY item) AS avg,
        STDEV(value) OVER (PARTITION BY item) AS stdev,
        ROUND((value - AVG(value) OVER (PARTITION BY item)) / STDEV(value) OVER (PARTITION BY item), 2) AS zscore,
        NULL AS custom_label
    FROM your_table
),
custom_rows AS (
    -- 去重获取每个分组的统计值，生成自定义行
    SELECT DISTINCT
        item,
        ROUND(avg + 1.5 * stdev, 2) AS value,
        avg,
        stdev,
        NULL AS zscore,
        'My Label' AS custom_label
    FROM original_data
)
-- 合并结果
SELECT * FROM original_data
UNION ALL
SELECT * FROM custom_rows
ORDER BY item, custom_label;

方案2说明： #

1. original_data CTE：就是你已经完成的查询，只是新增了custom_label字段标记非自定义行；
2. custom_rows CTE：用DISTINCT获取每个分组唯一的avg和stdev值，生成对应的自定义行；
3. 同样通过UNION ALL合并，最终每个分组都会多出一行带"My Label"的统计行。

示例输出（以apple组为例）： #

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