有人能解释一下这段 SQL Server 代码吗？

Question

我看到这段代码目前在一个存储过程中被使用：

    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的动作列中第一个值比其后续值具有最高优先级，则最终动作值在出现更高优先级的内容之前不会更改。

那么，有人能解释一下这段代码是如何工作的吗？

- Gaurav singh

---

@GuaravSingh . . . 需要解释什么？逻辑似乎非常清晰。您可以删除 COALESCE() 并分别返回这三列。 - Gordon Linoff

Answer 1

这个表达式包含了3个非常相似的窗口函数调用。每个函数都会搜索属于同一个ID的当前记录和之前的记录，以查找特定操作（INSERT、UPDATE、DELETE）。如果找到给定的操作，则窗口函数会返回其名称，否则返回NULL。

所以这样...

MAX(CASE WHEN [ACTION] = 'INSERT' THEN ACTION END) 
    OVER (PARTITION BY ID ORDER BY LAST_MODIFIED_DATE)

如果当前记录或另一条具有相同ID和更早的LAST_MODIFIED_DATE值的记录在列ACTION中具有'INSERT'值，则将返回字符串'INSERT'。

然后，COALESCE就像一个过滤器，优先考虑第一个搜索操作（INSERT），否则回退到第二个（UPDATE），最后是第三个（DELETE）。

结果行为就是您正确地反向工程化的行为。

- GMB