多层固定分组计算
多层固定分组计算
【问题】
I want to build opening closing report –
ITEM_TRANSACTION TABLE-
Consider below data–
1.When item is prepared entry is made into table wih NULL indicator..
2. When Item is issued/sold entry is made into table with ISSUE indicator
Date Iname Iquantity Indicator
1-Apr-14 Item1 15
2-Apr-14 Item1 10 ISSUE
2-Apr-14 Item1 3
3-Apr-14 Item1 5 ISSUE
I want to generate opening closing report (where closing quantity for today will be opening for 2mrw)
Date Iname Opening_Qty Today manufactured Total_balance Issued Closing
1-Apr-14 Item1 0 15 15 0 15
2-Apr-14 Item1 15 3 18 10 8
3-Apr-14 Item1 8 0 8 5 3
【回答】
提问者有商品操作记录表，按入库和消费的动作记录日期、商品名、操作数量和操作方向。如下表，indicator 代表的就是操作方向，值为空代表入库，值为’issue’代表消费。
需求是将入库出库的数量进行计算，获取每个操作日期，每个商品的起始数量、入库数量、入库后的数量、消费数量和结束数量。这种多层固定分组算法，需要源数据要按照固定的日期（第一层），以及固定的产品（第二层）进行对齐。用 SQL 实现此算法要使用嵌套查询或结构复杂的 join 语句，再加上涉及分组汇总和行间计算，又要用到窗口函数，这就使代码更加难写。
使用 SPL 可以这样去实现
| A | |
|---|---|
| 1 | =connect(“test”) |
| 2 | =A1.query(“select _DATE, _NAME,sum(CASE _INDICATOR WHEN ‘ISSUE’ THEN 0 ELSE _QUANTITY END) TodayManufactured,sum(CASE _INDICATOR WHEN ‘ISSUE’ THEN _QUANTITY ELSE 0 END) Issued from ITEM_TRANSACTION group by _DATE, _NAME order by _NAME, _DATE”) |
| 3 | =A2.group(_NAME) |
| 4 | =A3.conj((c=0,~.derive(c:Opening_Qty,(b=c+TodayManufactured):Balance,(c=b-Issued):Closing))) |
A1: 连接 test 数据库。
A2：查原始 ITEM_TRANSACTION 表，按日期和品名分组，将某日某商品的出入情况转成数值。先按品名再按日期对结果排序。
A3：A2 按照品名分组，group 函数可以做到只分组，不聚合，这一点也优于 sql，方便了接下来对每条记录逐一计算。
A4： 分别计算 A3 中每个商品组，自己各个日期中商品数量的变化。这里用到了一个临时变量 c，方便记录商品数量变化值，并传递给下一条记录。为序表增加需求的几个列，同时计算其值。
这样，我们在每个商品的序表中增加了需求的字段，完成了这些字段值的计算，最终将多个商品的结果合并在一起。
最终我们可以在 A4 格看到要求的结果。
原表：
A2
A3
成员 1 是 item1 组，成员 2 是 item2 组
A4
