乾学院 53 号会员
1 回帖  •  20 浏览  • 
     ...

从 SQL 到 SPL：用合计倒推出初始日期

 

某库表记录了一些特定日期计划的入库量和入库后的合计库存，比如 2 月 26 日计划入库 0.6 后合计库存为 3。

ITEM LOC NEEDDATE QTY CUSTQTY
ABC XYZ 2024-02-13 00:00:00 4.8 3
ABC XYZ 2024-02-14 00:00:00 0.6 3
ABC XYZ 2024-02-15 00:00:00 0.6 3
ABC XYZ 2024-02-16 00:00:00 0.6 3
ABC XYZ 2024-02-19 00:00:00 0.6 3
ABC XYZ 2024-02-20 00:00:00 0.6 3
ABC XYZ 2024-02-21 00:00:00 0.6 3
ABC XYZ 2024-02-22 00:00:00 0.6 3
ABC XYZ 2024-02-23 00:00:00 0.6 3
ABC XYZ 2024-02-26 00:00:00 0.6 3
ABC XYZ 2024-02-27 00:00:00 0.6 3
ABC XYZ 2024-02-28 00:00:00 0.6 3
ABC XYZ 2024-02-29 00:00:00 0.6 3

现在要根据给定的日期，用合计库存倒推出初始日期，也就是零库存或负库存的那一天，要补上每一天消耗后的入库量 UPDATED_QTY 和原库存 UPDATED_CUSTQTY。比如给定 2 月 26 日，可知当日入库前的原库存是 3-0.6=2.4；往前的日期是 2 月 23 日，当日原库存是 2.4-0.6=1.8；直到 2 月 20 日，当日原库存是 0。

ITEM LOC NEEDDATE QTY CUSTQTY UPDATED_QTY UPDATED_CUSTQTY
ABC XYZ 2024-02-29 00:00:00 0.6 3 0.6
ABC XYZ 2024-02-28 00:00:00 0.6 3 0.6
ABC XYZ 2024-02-27 00:00:00 0.6 3 0.6
ABC XYZ 2024-02-26 00:00:00 0.6 3 0 2.4
ABC XYZ 2024-02-23 00:00:00 0.6 3 0 1.8
ABC XYZ 2024-02-22 00:00:00 0.6 3 0 1.2
ABC XYZ 2024-02-21 00:00:00 0.6 3 0 0.6
ABC XYZ 2024-02-20 00:00:00 0.6 3 0 0.0
ABC XYZ 2024-02-19 00:00:00 0.6 3 0.6
ABC XYZ 2024-02-16 00:00:00 0.6 3 0.6
ABC XYZ 2024-02-15 00:00:00 0.6 3 0.6
ABC XYZ 2024-02-14 00:00:00 0.6 3 0.6
ABC XYZ 2024-02-13 00:00:00 4.8 3 4.8

SQL：

SELECT t.*,
       LEAST(
         GREATEST(
           COALESCE(
             SUM(
               CASE WHEN needdate < TRUNC(to_date('2024-02-26')) + 1 THEN qty END
             ) OVER (PARTITION BY item, loc ORDER BY needdate DESC) - custqty,
             qty
           ),
           0
         ),
         qty
       ) AS updated_qty,
       CASE
       WHEN needdate > TRUNC(to_date('2024-02-26'))
       THEN NULL
       WHEN SUM(
              CASE WHEN needdate < TRUNC(to_date('2024-02-26')) + 1 THEN qty END
            ) OVER (
              PARTITION BY item, loc
              ORDER BY needdate DESC
              ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            ) >= custqty
       THEN NULL
       ELSE GREATEST(
              custqty
              - SUM(
                CASE WHEN needdate < TRUNC(to_date('2024-02-26')) + 1 THEN qty END
              ) OVER (PARTITION BY item, loc ORDER BY needdate DESC),
              0
           )
       END AS updated_custqty
FROM   table_name t

SQL 要用多个窗口函数间接实现有序计算，代码复杂难懂。SPL 可以直接表示相对位置或绝对位置：https://try.esproc.com/splx?3FZ


 　A
1 $select *, QTY as UPDATED_QTY, null as UPDATED_CUSTQTY from table_name.txt order by NEEDDATE desc
2 =A1.select(NEEDDATE<=date("2024-02-26"))
3 =A2.run(UPDATED_CUSTQTY=round(if(#==1,CUSTQTY-QTY,if((t=UPDATED_CUSTQTY[-1]-QTY)>=0,t)),1), if(UPDATED_CUSTQTY>=0,UPDATED_QTY=0))
4 return A1

A1：加载数据，按日期逆序排序，新增 2 个计算列：消耗后的入库量 UPDATED_QTY，初始值是入库量；原库存 UPDATED_CUSTQTY，初始值是 null。

A2：过滤出指定日期之前的记录。

A3：修改记录：如果当前是第 1 条记录，则原库存 = 合计库存 - 入库量，否则原库存 = 上一条原库存 - 入库量，结果保留一位小数。如果原库存 >=0，则消耗后的入库量 =0。[-1] 表示上一条记录。

问题来源：https://stackoverflow.com/questions/78060568/why-i-can-not-use-lag-function-in-this-case