关于使用GROUP BY结合聚合函数与CASE处理混合数据的技术疑问

需求说明 #

源数据 #

|Id  |  Value 
| C1 |  0.76
| C1 |  <0.65
| C1 |  <0.35
| C2 |  0.27
| C2 |  3.7 
| C3 |  <2.5
| C3 |  <3.6

聚合规则 #

• 若某一ID下所有Value均以"<"开头：拼接为单个字符串
• 若所有Value均为数值：计算平均值
• 若ID下同时存在数值和带"<"的Value：拼接所有Value为单个字符串

预期输出 #

|Id  |  Value 
| C1 |  0.76,<0.65,<0.35
| C2 |  1.985
| C3 |  <2.5,<3.6

原查询的问题 #

你之前写的SQL存在两个核心问题：

1. 判断逻辑错误：CASE语句是基于单条记录的Value是否含"<"，而非整个ID分组的整体情况，导致同一分组内的聚合逻辑混乱。
2. 聚合函数混用逻辑错误：直接在CASE分支里分别用LISTAGG和AVG，没有基于分组的全局判断，无法区分"全数值"、"全<值"、"混合值"三种场景。

解决方案 #

先通过子查询统计每个ID的分组特征，再根据特征选择对应的聚合方式：

WITH id_group_stats AS (
    SELECT 
        Id,
        -- 标记该ID下是否存在带"<"的记录
        MAX(CASE WHEN Value LIKE '<%' THEN 1 ELSE 0 END) AS has_less_than,
        -- 标记该ID下是否所有记录都是纯数值（无"<"）
        MIN(CASE WHEN Value NOT LIKE '<%' THEN 1 ELSE 0 END) AS is_all_numeric
    FROM your_table
    GROUP BY Id
)
SELECT 
    t.Id,
    CASE 
        -- 全数值场景：计算平均值并转字符串统一输出类型
        WHEN s.is_all_numeric = 1 THEN CAST(AVG(CAST(TRIM(t.Value) AS DECIMAL(38,10))) AS VARCHAR)
        -- 混合或全<值场景：拼接所有值
        ELSE LISTAGG(TRIM(t.Value), ',') WITHIN GROUP (ORDER BY t.Value)
    END AS Value
FROM your_table t
JOIN id_group_stats s ON t.Id = s.Id
GROUP BY t.Id, s.has_less_than, s.is_all_numeric
ORDER BY t.Id;

逻辑说明 #

1. 子查询统计分组特征：
   • has_less_than：只要分组内有一条带"<"的记录，值为1；否则为0
   • is_all_numeric：只有当分组内所有记录都是纯数值时，值为1；否则为0
2. 主查询聚合：
   • 用TRIM处理Value中的空格（比如C2的3.7 ），避免转数值时出错
   • 全数值场景下计算平均值，转成字符串保证输出列类型统一
   • 其他场景直接拼接所有Value，满足混合或全<值的需求

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