我看到这段代码目前在一个存储过程中被使用:
COALESCE(MAX(CASE WHEN [ACTION] = 'INSERT' THEN ACTION END) OVER (PARTITION BY ID ORDER BY LAST_MODIFIED_DATE),
MAX(CASE WHEN [ACTION] = 'UPDATE' THEN ACTION END) OVER (PARTITION BY ID ORDER BY LAST_MODIFIED_DATE),
MAX(CASE WHEN [ACTION] = 'DELETE' THEN ACTION END) OVER (PARTITION BY ID ORDER BY LAST_MODIFIED_DATE)) AS FINAL_ACTION
FROM #A
它的作用是使用“ACTION”列中的值,并为它们分配一定的优先级。
INSERT (1st priority)
UPDATE (2nd priority)
DELETE (3rd priority)
根据last_modified的值和ACTION列给定的优先级,创建一个名为FINAL_ACTION的新列。
例如,如果我的输入数据如下所示:
ID ACTION LAST_MODIFIED_DATE
------------------------------
1 INSERT 11-Feb
1 UPDATE 11-Mar
1 DELETE 14-Mar
2 UPDATE 12-Apr
2 INSERT 15-Apr
2 DELETE 19-Apr
3 UPDATE 2-Jan
3 DELETE 4-Jan
3 INSERT 7-Jan
那么输出结果会像这样:
ID ACTION LAST_MODIFIED_DATE FINAL ACTION
--------------------------------------------
1 INSERT 11-Feb INSERT
1 UPDATE 11-Mar INSERT
1 DELETE 14-Mar INSERT
2 UPDATE 12-Apr UPDATE
2 INSERT 15-Apr INSERT
2 DELETE 19-Apr INSERT
3 UPDATE 2-Jan UPDATE
3 DELETE 4-Jan UPDATE
3 INSERT 7-Jan INSERT
基本上,如果每个id的动作列中第一个值比其后续值具有最高优先级,则最终动作值在出现更高优先级的内容之前不会更改。
那么,有人能解释一下这段代码是如何工作的吗?
COALESCE()
并分别返回这三列。 - Gordon Linoff