如何基于连续Number值分组Size并生成可读聚合字符串？

原始数据 #

需求 #

为每个ID生成一个字符串列，规则：

• 仅对Number连续且Size相同的行进行分组
• 分组内数量大于1时标注(xn)，数量为1时省略该标注
• 示例：ID=8的期望输出为 1.625 x 1.03125 (x3) x 1.625 x 1 x 1.625 (x3)

实现方案（验证你的思路） #

你的思路方向完全正确，以下是具体SQL实现（适配SQL Server/PostgreSQL，不同数据库语法略有差异）：

步骤1：生成连续相同Size的分组标识 #

利用LAG()窗口函数判断当前行是否与上一行属于同一分组，生成分组ID：

WITH grouped_data AS (
    SELECT 
        ID,
        Number,
        Size,
        -- 当Size与上一行不同，或Number不连续时，开启新分组
        SUM(CASE 
            WHEN LAG(Size) OVER (PARTITION BY ID ORDER BY Number) = Size 
                 AND LAG(Number) OVER (PARTITION BY ID ORDER BY Number) + 1 = Number
            THEN 0 
            ELSE 1 
        END) OVER (PARTITION BY ID ORDER BY Number) AS group_id
    FROM your_table
),

步骤2：统计每个分组的Size和数量 #

基于ID和分组ID聚合，得到每个分组的Size值和行数：

group_stats AS (
    SELECT 
        ID,
        group_id,
        MAX(Size) AS size_val, -- 同分组Size一致，取MAX/MIN均可
        COUNT(*) AS quantity
    FROM grouped_data
    GROUP BY ID, group_id
)

步骤3：拼接目标字符串 #

使用STRING_AGG()聚合函数，根据数量决定是否添加计数标注：

SELECT 
    ID,
    STRING_AGG(
        CASE 
            WHEN quantity > 1 THEN CONCAT(size_val, ' (x', quantity, ')')
            ELSE CAST(size_val AS VARCHAR(20))
        END, ' x '
    ) WITHIN GROUP (ORDER BY group_id) AS size_summary
FROM group_stats
GROUP BY ID;

性能优化建议 #

1. 索引优化：在ID和Number上创建联合索引（CREATE INDEX idx_id_number ON your_table(ID, Number);），窗口函数PARTITION BY ID ORDER BY Number会直接利用该索引，避免排序开销。
2. 浮点精度处理：如果Size是浮点类型，转换为字符串时可用FORMAT(size_val, 'N5')控制精度，避免出现科学计数法或多余小数位。
3. 大数据量适配：若数据量极大，可考虑先按ID分区处理，减少单次窗口函数的计算范围。

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