基于NIF分组的分区与子分区生成需求（含累计值重置规则）

需求说明 #

现有仅包含NIF、FILE_、FILESIZE列的数据表my_table，需新增PARTITION和SUBPARTITION列，规则如下：

• PARTITION从1开始，每当SUBPARTITION数量达到8时递增1
• 单个NIF的所有记录必须归属同一个PARTITION（最严格约束）
• 按记录顺序累计FILESIZE，累计值达到10时，SUBPARTITION递增1，同时累计值重置

示例输出 #

NIF  FILE FILESIZE    PARTITION   SUBPARTITION
  ----------------------------------------------
   A     C1        1            1              1
   A     C2        1            1              1
   A     C3        2            1              1
   A     C4        1            1              1
   B     C5        5            1              2
   B     C6        1            1              2
   C     C7        2            1              2
   C     C8        1            1              2
   D     C9        4            1              3
   D    C10        5            1              3
   D    C11        1            1              3
   D    C12        2            1              4
   D    C13        3            1              4
   D    C14        4            1              4
   D    C15        5            1              5
   E    C16        3            1              6
   E    C17        2            1              6
   E    C18        3            1              6
   E    C19        4            1              7
   F    C20        6            2              1
   F    C21        2            2              1

数据表创建及插入脚本 #

DROP TABLE my_table;
-- 创建表
CREATE TABLE my_table (
  NIF VARCHAR2(10),
  FILE_ VARCHAR2(10),
  FILESIZE NUMBER
);

-- 插入测试数据
INSERT INTO my_table (NIF, FILE_, FILESIZE)
    SELECT 'A', 'C1', 1 FROM DUAL UNION ALL
    SELECT 'A', 'C2', 1 FROM DUAL UNION ALL
    SELECT 'A', 'C3', 2 FROM DUAL UNION ALL
    SELECT 'A', 'C4', 1 FROM DUAL UNION ALL
    SELECT 'B', 'C5', 5 FROM DUAL UNION ALL
    SELECT 'B', 'C6', 1 FROM DUAL UNION ALL
    SELECT 'C', 'C7', 2 FROM DUAL UNION ALL
    SELECT 'C', 'C8', 1 FROM DUAL UNION ALL
    SELECT 'D', 'C9', 4 FROM DUAL UNION ALL
    SELECT 'D', 'C10', 5 FROM DUAL UNION ALL
    SELECT 'D', 'C11', 1 FROM DUAL UNION ALL
    SELECT 'D', 'C12', 2 FROM DUAL UNION ALL
    SELECT 'D', 'C13', 3 FROM DUAL UNION ALL
    SELECT 'D', 'C14', 4 FROM DUAL UNION ALL
    SELECT 'D', 'C15', 5 FROM DUAL UNION ALL
    SELECT 'E', 'C16', 3 FROM DUAL UNION ALL
    SELECT 'E', 'C17', 2 FROM DUAL UNION ALL
    SELECT 'E', 'C18', 3 FROM DUAL UNION ALL
    SELECT 'E', 'C19', 4 FROM DUAL UNION ALL
    SELECT 'F', 'C20', 6 FROM DUAL UNION ALL
    SELECT 'F', 'C21', 2 FROM DUAL;
    
COMMIT;

解决方案 #

方案一：单SQL查询实现 #

利用Oracle分析函数计算累计值，通过嵌套查询逐步生成符合规则的分区列：

WITH step1 AS (
    -- 计算全局累计FILESIZE，标记每个NIF的起始记录位置
    SELECT 
        t.*,
        SUM(FILESIZE) OVER (ORDER BY ROWID) AS global_cumulative,
        MIN(ROWID) OVER (PARTITION BY NIF) AS nif_min_rowid
    FROM my_table t
),
step2 AS (
    -- 初步划分SUBPARTITION：累计值每满10开启新分组
    SELECT 
        s1.*,
        FLOOR((global_cumulative - 1) / 10) + 1 AS raw_subpartition
    FROM step1 s1
),
step3 AS (
    -- 确保同一NIF的记录起始子分区统一，避免跨PARTITION
    SELECT 
        s2.*,
        MIN(raw_subpartition) OVER (PARTITION BY NIF) AS nif_min_subpart
    FROM step2 s2
),
step4 AS (
    -- 调整同一NIF内的SUBPARTITION，保证连续且不跨分区
    SELECT 
        s3.*,
        nif_min_subpart + FLOOR((SUM(FILESIZE) OVER (PARTITION BY NIF ORDER BY ROWID) - 1) / 10) AS subpartition
    FROM step3 s3
),
step5 AS (
    -- 根据SUBPARTITION生成PARTITION：每8个子分区升一级
    SELECT 
        s4.*,
        FLOOR((subpartition - 1) / 8) + 1 AS partition
    FROM step4 s4
)
SELECT 
    NIF,
    FILE_,
    FILESIZE,
    PARTITION,
    SUBPARTITION
FROM step5
ORDER BY ROWID;

方案二：PL/SQL游标实现 #

通过游标遍历记录，手动维护累计值、子分区和分区的状态逻辑：

DECLARE
    CURSOR c_my_table IS 
        SELECT NIF, FILE_, FILESIZE FROM my_table ORDER BY ROWID;
    v_current_nif VARCHAR2(10);
    v_cumulative NUMBER := 0;
    v_subpartition NUMBER := 1;
    v_partition NUMBER := 1;
    v_nif_subpart_start NUMBER := 1;
BEGIN
    -- 创建临时表存储结果
    CREATE GLOBAL TEMPORARY TABLE temp_result (
        NIF VARCHAR2(10),
        FILE_ VARCHAR2(10),
        FILESIZE NUMBER,
        PARTITION NUMBER,
        SUBPARTITION NUMBER
    ) ON COMMIT PRESERVE ROWS;
    
    FOR rec IN c_my_table LOOP
        -- 切换NIF时，确保新NIF的子分区不跨PARTITION
        IF rec.NIF != v_current_nif THEN
            v_current_nif := rec.NIF;
            v_cumulative := 0;
            -- 计算当前NIF的起始子分区，保证其所有子分区在同一个PARTITION内
            WHILE FLOOR((v_subpartition - 1)/8) != FLOOR((v_subpartition + CEIL((SUM(rec.FILESIZE) OVER (PARTITION BY rec.NIF))/10) -1)/8) LOOP
                v_subpartition := v_subpartition + 1;
            END LOOP;
            v_nif_subpart_start := v_subpartition;
        END IF;
        
        v_cumulative := v_cumulative + rec.FILESIZE;
        -- 累计值达标则切换子分区，重置累计值
        IF v_cumulative >= 10 THEN
            v_subpartition := v_subpartition + 1;
            v_cumulative := v_cumulative - 10;
            -- 子分区满8个则升级PARTITION
            IF MOD(v_subpartition - 1, 8) = 0 THEN
                v_partition := v_partition + 1;
            END IF;
        END IF;
        
        -- 插入结果到临时表
        INSERT INTO temp_result VALUES (
            rec.NIF,
            rec.FILE_,
            rec.FILESIZE,
            v_partition,
            v_subpartition
        );
    END LOOP;
    
    -- 输出结果
    DBMS_OUTPUT.PUT_LINE('  NIF  FILE FILESIZE    PARTITION   SUBPARTITION');
    DBMS_OUTPUT.PUT_LINE('  ----------------------------------------------');
    FOR res_rec IN (SELECT * FROM temp_result ORDER BY ROWID) LOOP
        DBMS_OUTPUT.PUT_LINE(RPAD(' ' || res_rec.NIF, 6) || RPAD(res_rec.FILE_, 6) || RPAD(res_rec.FILESIZE, 10) || RPAD(res_rec.PARTITION, 14) || res_rec.SUBPARTITION);
    END LOOP;
    
    -- 清理临时表
    DROP TABLE temp_result;
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE = -942 THEN
            NULL;
        ELSE
            RAISE;
        END IF;
END;
/

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