按指定数量对NAME列分组并生成GROUP_ID的实现方案问询

原始数据 #

需求 #

按NAME列排序，为每条数据分配连续的GROUP_ID，规则如下：

• 同一NAME的记录若超过3条，拆分到多个GROUP_ID，每组最多3条
• GROUP_ID按顺序连续编号

预期结果 #

可以通过嵌套窗口函数和预统计分组数的方式实现，核心思路是先拆分每个NAME内部的子组，再通过累加前置NAME的子组总数得到全局连续的GROUP_ID。

高效实现代码 #

WITH name_group_counts AS (
    -- 统计每个NAME需要拆分的总组数
    SELECT 
        NAME,
        CEIL(COUNT(*) / 3.0) AS total_groups
    FROM your_table
    GROUP BY NAME
),
cumulative_prev_groups AS (
    -- 计算每个NAME之前所有NAME的累计组数
    SELECT 
        NAME,
        total_groups,
        SUM(total_groups) OVER (ORDER BY NAME) - total_groups AS cumulative_prev
    FROM name_group_counts
),
record_sub_groups AS (
    -- 给每条记录分配其在NAME内的子组编号
    SELECT 
        ID,
        NAME,
        CEIL(ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY ID) / 3.0) AS sub_group_num
    FROM your_table
)
-- 拼接得到全局连续的GROUP_ID
SELECT 
    rs.ID,
    rs.NAME,
    cp.cumulative_prev + rs.sub_group_num AS GROUP_ID
FROM record_sub_groups rs
JOIN cumulative_prev_groups cp ON rs.NAME = cp.NAME
ORDER BY rs.NAME, rs.ID;

代码说明 #

1. name_group_counts：先按NAME分组，计算每个NAME需要拆分成多少个3条一组的子组，用CEIL(COUNT(*) / 3.0)向上取整得到组数。
2. cumulative_prev_groups：用窗口函数SUM()累加前置NAME的总组数，得到当前NAME之前已经占用的GROUP_ID数量。
3. record_sub_groups：给每个NAME内的记录按顺序编号，再计算每条记录属于该NAME的第几个子组。
4. 最后将前置累计组数和当前子组编号相加，得到全局连续的GROUP_ID。

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