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

从 SQL 到 SPL：Track Production Operations Outcome Progression with Conditional NULLs

 

SQL Server 某库表描述了一个生产过程的多个操作的执行进度，当一个 OP 的 Outcome 为 0 时，表示执行失败，应当继续执行，直到 Outcome 为 1 为止，这表示执行成功，应当继续按顺序执行下一个 OP。

id

OP

Outcome

1

1

0

2

1

0

3

1

1

4

2

1

5

3

1

6

4

0

7

4

0

8

4

1

9

5

0

10

5

1

现在要跟踪操作的执行进度：每个操作占一个字段，字段顺序表示操作的执行进度；当一个操作执行失败时，应进入下一个跟踪阶段，即生成一条新记录；新记录先复制之前成功操作的进度 1（如果有的话），再继续执行本操作；如果本操作这次执行成功，则在当前跟踪阶段里继续执行下一操作。

OP1

OP2

OP3

OP4

OP5

0



0



1

1

1

0

1

1

1

0

1

1

1

1

0

1

1

1

1

1

SQL：

  SELECT  CASE WHEN max(OP) > 1 THEN 1 ELSE MAX(CASE WHEN OP = 1 THEN Outcome END) END AS OP1
,   CASE WHEN max(OP) > 2 THEN 1 ELSE MAX(CASE WHEN OP = 2 THEN Outcome END) END AS OP2
,   CASE WHEN max(OP) > 3 THEN 1 ELSE MAX(CASE WHEN OP = 3 THEN Outcome END) END AS OP3
,   CASE WHEN max(OP) > 4 THEN 1 ELSE MAX(CASE WHEN OP = 4 THEN Outcome END) END AS OP4
,   CASE WHEN max(OP) > 5 THEN 1 ELSE MAX(CASE WHEN OP = 5 THEN Outcome END) END AS OP5
FROM    (
    SELECT  *
    ,   sum(flag) OVER(ORDER BY OP, Outcome rows BETWEEN unbounded preceding AND CURRENT row) AS counter
    
    FROM    (
        SELECT  *
        ,   CASE WHEN (lag(outcome) OVER(ORDER BY op,outcome) = outcome AND outcome = 1) OR (lag(outcome) OVER(ORDER BY op,outcome) <> outcome AND outcome = 0)
                THEN 0
                ELSE 1
            END AS flag
        FROM    Operations
        ) x
    ) x
GROUP BY x.counter

SQL 没有按条件有序分组的机制，将原记录分为多个跟踪阶段，要用嵌套子查询 + 多个窗口函数打标记。普通 SQL 也缺乏动态生成字段的语法，只能死板地为每个字段都写一句 case when；动态生成字段就要用到动态 SQL。

SPL 代码要简单易懂得多：

　A

1

=mssql.query("select OP, Outcome from Operations order by OP,Outcome")

2

=create(${A1.max(OP).("OP" / ~).concat@c()})

3

=A1.group@i(Outcome[-1]==0)

4

=A3.(A2.record((~.min(OP)-1).(1)|~.(Outcome)))

5

return A2

A1：加载数据。

A2：动态生成二维表。

A3：按条件分组，当上次的执行结果失败时新分一组，[-1] 表示上一条。注意这里没有立刻汇总。

A4：循环各组数据，按规则每次向二维表添加一条记录，前面填好一些 1，后面是本组的 Outcome 的序列。

问题来源：https://stackoverflow.com/questions/78232342/sql-query-to-track-production-operations-outcome-progression-with-conditional-nu