SQL实现QTY列小数部分逐行结转累加的逻辑求助

输入数据 #

INSERT INTO INPUT_TABLE (ITEM,LOC,NEEDDATE,QTY)
    VALUES('ITEM100087','LOC000987','01-APR-2024',1.38428);
INSERT INTO INPUT_TABLE (ITEM,LOC,NEEDDATE,QTY)
    VALUES('ITEM100087','LOC000987','02-APR-2024',1.38428);
INSERT INTO INPUT_TABLE (ITEM,LOC,NEEDDATE,QTY)
    VALUES('ITEM100087','LOC000987','03-APR-2024',1.38428);
INSERT INTO INPUT_TABLE (ITEM,LOC,NEEDDATE,QTY)
    VALUES('ITEM100087','LOC000987','04-APR-2024',1.38428);
INSERT INTO INPUT_TABLE (ITEM,LOC,NEEDDATE,QTY)
    VALUES('ITEM100087','LOC000987','05-APR-2024',1.38428);
INSERT INTO INPUT_TABLE (ITEM,LOC,NEEDDATE,QTY)
    VALUES('ITEM100087','LOC000987','06-APR-2024',1.38428);

需求说明 #

• 将每条记录的QTY与上一条结转的小数部分相加，拆分出整数部分作为NEWQTY，剩余小数部分继续结转到下一条记录
• 具体规则示例：
  • 第一条记录：QTY=1.38428 → NEWQTY=1.00000，结转小数=0.38428
  • 第二条记录：1.38428+0.38428=1.76856 → NEWQTY=1.00000，结转小数=0.76856
  • 第三条记录：1.38428+0.76856=2.15284 → NEWQTY=2.00000，结转小数=0.15284
  • 后续记录以此类推循环处理

尝试的SQL（未达预期） #

Sum(QTY+cast(substr(qty, instr(qty, '.')) as float)) Over(Partition By ITEM, LOC Order By needdate Rows Between Unbounded Preceding And Current Row) as NEWQTY

解决方案 #

由于需求是逐行依赖的递推计算，普通窗口函数无法处理这种前后行的依赖关系，需要使用递归CTE来实现：

WITH ordered_data AS (
    -- 先给每条记录按日期排序，生成序号用于递归
    SELECT 
        ITEM,
        LOC,
        NEEDDATE,
        QTY,
        ROW_NUMBER() OVER (PARTITION BY ITEM, LOC ORDER BY NEEDDATE) AS rn
    FROM INPUT_TABLE
),
recursive_calc AS (
    -- 初始化：第一条记录的计算
    SELECT 
        ITEM,
        LOC,
        NEEDDATE,
        QTY,
        FLOOR(QTY) AS NEWQTY,
        QTY - FLOOR(QTY) AS CARRY_DECIMAL
    FROM ordered_data
    WHERE rn = 1
    
    UNION ALL
    
    -- 递归处理后续每条记录
    SELECT 
        od.ITEM,
        od.LOC,
        od.NEEDDATE,
        od.QTY,
        FLOOR(od.QTY + rc.CARRY_DECIMAL) AS NEWQTY,
        (od.QTY + rc.CARRY_DECIMAL) - FLOOR(od.QTY + rc.CARRY_DECIMAL) AS CARRY_DECIMAL
    FROM ordered_data od
    JOIN recursive_calc rc ON od.ITEM = rc.ITEM AND od.LOC = rc.LOC AND od.rn = rc.rn + 1
)
-- 最终输出结果
SELECT 
    ITEM,
    LOC,
    NEEDDATE,
    QTY,
    NEWQTY,
    CARRY_DECIMAL AS DECIMALQTY
FROM recursive_calc
ORDER BY NEEDDATE;

代码说明 #

1. ordered_data CTE：先对数据按ITEM、LOC分区，NEEDDATE排序，生成行号，确保递归能按顺序处理每一条记录
2. recursive_calc CTE：
   • 初始化部分处理第一条记录，拆分出整数部分NEWQTY和结转小数CARRY_DECIMAL
   • 递归部分将当前记录的QTY加上上一条的结转小数，再拆分出新的整数部分和结转小数
3. 最终查询输出所有字段，包括原始QTY、计算后的NEWQTY和结转小数DECIMALQTY

验证结果 #

针对输入数据，运行后会得到如下结果（保留五位小数）：

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