在Snowflake中实现带1440字符限制的list_agg分组

要实现按distributor_name分组拼接ID，同时控制每个拼接字符串长度不超过1440字符，可以通过分步计算子组序号再分组聚合的方式实现，具体SQL如下：

WITH ranked_ids AS (
    -- 给每个分发商下的ID排序，保证拼接顺序稳定
    SELECT 
        distributor_name,
        id,
        ROW_NUMBER() OVER (PARTITION BY distributor_name ORDER BY id) AS rn
    FROM your_table_name  -- 替换成你的实际表名
),
grouped_ids AS (
    -- 计算每个ID所属的子组：每个ID占10字符，逗号占1字符，131个ID拼接后总长度刚好1440
    SELECT 
        distributor_name,
        id,
        rn,
        FLOOR((11 * rn - 2) / 1440) + 1 AS group_num
    FROM ranked_ids
)
SELECT 
    CONCAT(distributor_name, '_', group_num) AS "group",
    COUNT(id) AS id_count,
    LISTAGG(id, ',') WITHIN GROUP (ORDER BY rn) AS list_agg
FROM grouped_ids
GROUP BY distributor_name, group_num
ORDER BY distributor_name, group_num;

逻辑说明 #

1. 排序ID：通过ROW_NUMBER()给每个distributor_name下的ID分配序号，确保拼接顺序一致。
2. 划分子组：
   • 每个ID拼接后占10字符，ID间的逗号占1字符，因此n个ID拼接后的总长度为10*n + (n-1)*1 = 11n -1。
   • 当总长度不超过1440时，11n -1 ≤1440 → n≤131，即每个子组最多容纳131个ID。
   • 用FLOOR((11*rn -2)/1440)+1计算子组序号，确保每个子组的拼接字符串长度不超限制。
3. 分组聚合：按distributor_name和子组序号分组，用LISTAGG()拼接ID，同时统计子组内的ID数量。

输出字段说明 #

• group：由分发商名称和子组序号组成的唯一标识（如distributorA_1）
• id_count：该子组内的ID总数
• list_agg：拼接后的ID字符串，长度不超过1440字符

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