如何用SQL按date/cd/no分区补虚拟数据实现每5条一组三类记录

原表数据（TEST表） #

date                cd          no        name
------------------------------------------------------------
2023-01-01         123          100      rec1 samp name
2023-01-02         456          200      rec2 samp name1
2023-01-02         456          200      rec2 samp name2
2023-01-02         456          200      rec2 samp name3
2023-01-02         456          200      rec2 samp name4
2023-01-02         456          200      rec2 samp name5
2023-01-02         456          200      rec2 samp name6
2023-01-02         456          200      rec2 samp name7
2023-01-04         789          300      rec3 samp name 
2023-01-04         789          300      rec3 samp name2

需求说明 #

按date、cd、no分区，每个分区内每5条记录生成top、mid、bot三种REC_TYPE分组；若分组内记录不足5条，需补充dummy虚拟数据。

期望结果 #

date                cd          no        name             REC_TYPE
----------------------------------------------------------------------------
2023-01-01         123          100      rec1 samp name      top
2023-01-01         123          100      dummy               top
2023-01-01         123          100      dummy               top
2023-01-01         123          100      dummy               top
2023-01-01         123          100      dummy               top
2023-01-01         123          100      rec1 samp name      mid
2023-01-01         123          100      dummy               mid
2023-01-01         123          100      dummy               mid
2023-01-01         123          100      dummy               mid
2023-01-01         123          100      dummy               mid
2023-01-01         123          100      rec1 samp name      bot
2023-01-01         123          100      dummy               bot
2023-01-01         123          100      dummy               bot
2023-01-01         123          100      dummy               bot
2023-01-01         123          100      dummy               bot
2023-01-02         456          200      rec2 samp name1     top
2023-01-02         456          200      rec2 samp name2     top
2023-01-02         456          200      rec2 samp name3     top
2023-01-02         456          200      rec2 samp name4     top
2023-01-02         456          200      rec2 samp name5     top
2023-01-02         456          200      rec2 samp name1     mid
2023-01-02         456          200      rec2 samp name2     mid
2023-01-02         456          200      rec2 samp name3     mid
2023-01-02         456          200      rec2 samp name4     mid
2023-01-02         456          200      rec2 samp name5     mid
2023-01-02         456          200      rec2 samp name1     bot
2023-01-02         456          200      rec2 samp name2     bot
2023-01-02         456          200      rec2 samp name3     bot
2023-01-02         456          200      rec2 samp name4     bot
2023-01-02         456          200      rec2 samp name5     bot
2023-01-02         456          200      rec2 samp name6     top
2023-01-02         456          200      rec2 samp name7     top
2023-01-02         456          200      dummy               top
2023-01-02         456          200      dummy               top
2023-01-02         456          200      dummy               top
2023-01-02         456          200      rec2 samp name6     mid
2023-01-02         456          200      rec2 samp name7     mid
2023-01-02         456          200      dummy               mid
2023-01-02         456          200      dummy               mid
2023-01-02         456          200      dummy               mid
2023-01-02         456          200      rec2 samp name6     bot
2023-01-02         456          200      rec2 samp name7     bot
2023-01-02         456          200      dummy               bot
2023-01-02         456          200      dummy               bot
2023-01-02         456          200      dummy               bot
2023-01-04         789          300      rec3 samp name      top
2023-01-04         789          300      rec3 samp name 2    top
2023-01-04         789          300      dummy               top
2023-01-04         789          300      dummy               top
2023-01-04         789          300      dummy               top
2023-01-04         789          300      rec3 samp name      mid
2023-01-04         789          300      rec3 samp name 2    mid
2023-01-04         789          300      dummy               mid
2023-01-04         789          300      dummy               mid
2023-01-04         789          300      dummy               mid
2023-01-04         789          300      rec3 samp name      bot
2023-01-04         789          300      rec3 samp name 2    bot
2023-01-04         789          300      dummy               bot
2023-01-04         789          300      dummy               bot
2023-01-04         789          300      dummy               bot

用户尝试的代码 #

SELECT * FROM ( 
    SELECT  'top' as REC_TYPE ,* FROM  TEST
    UNION ALL
    SELECT  'mid' as REC_TYPE ,* FROM  TEST
    UNION ALL
    SELECT  'bot' as REC_TYPE ,* FROM  TEST 
) RECORD 
ORDER BY  (ROW_NUMBER() OVER (PARTITION BY date, cd, no,  REC_TYPE ORDER BY CAST(REPLACE(no, 'no','') AS INT))-1) / 5 ,
          date ASC ,cd ASC  ,no ASC  ,REC_TYPE desc ,
          CAST(REPLACE(no, 'no', '') AS INT)

解决方案代码 #

WITH base_data AS (
    -- 给分区内的记录分配行号、划分5条一组的分组号
    SELECT 
        date, cd, no, name,
        ROW_NUMBER() OVER(PARTITION BY date, cd, no ORDER BY name) AS rn,
        CEIL(ROW_NUMBER() OVER(PARTITION BY date, cd, no ORDER BY name) / 5.0) AS group_num
    FROM TEST
),
row_positions AS (
    -- 生成1-5的位置编号，用于补全每组的5条记录
    SELECT 1 AS pos UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
),
rec_types AS (
    -- 生成三种REC_TYPE类型
    SELECT 'top' AS rec_type UNION ALL SELECT 'mid' UNION ALL SELECT 'bot'
),
group_frames AS (
    -- 生成所有需要的记录框架：每个分区的每个分组+每个REC_TYPE+每个位置
    SELECT 
        bd.date, bd.cd, bd.no, 
        rt.rec_type, 
        rp.pos,
        bd.group_num
    FROM base_data bd
    CROSS JOIN rec_types rt
    CROSS JOIN row_positions rp
    GROUP BY bd.date, bd.cd, bd.no, bd.group_num, rt.rec_type, rp.pos
)
-- 左连接原表数据，补全dummy虚拟数据
SELECT 
    gf.date,
    gf.cd,
    gf.no,
    COALESCE(bd.name, 'dummy') AS name,
    gf.rec_type
FROM group_frames gf
LEFT JOIN base_data bd 
    ON gf.date = bd.date 
    AND gf.cd = bd.cd 
    AND gf.no = bd.no 
    AND gf.group_num = bd.group_num 
    AND gf.pos = bd.rn
ORDER BY 
    gf.date, 
    gf.cd, 
    gf.no, 
    gf.group_num, 
    CASE gf.rec_type WHEN 'top' THEN 1 WHEN 'mid' THEN 2 WHEN 'bot' THEN 3 END,
    gf.pos;

代码逻辑说明 #

1. base_data：给每个分区内的记录编行号，同时按每5条划分组号（比如第1-5条为group_num=1，第6-10条为group_num=2）
2. row_positions：生成1到5的数字，代表每组内的5个固定位置，用于补全不足5条的空缺
3. rec_types：生成三种需要的REC_TYPE值
4. group_frames：通过交叉连接生成所有必要的记录框架，确保每个分区的每个分组、每种REC_TYPE都有5个位置
5. 最后左连接原表数据，未匹配到的位置用dummy填充，再按需求排序

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