sql - 有人可以解释这个 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 day 值和赋予 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 的操作列中的第一个值比它的后续值具有最高优先级，那么最终操作值将不会改变，直到出现最高优先级的内容。

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

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)

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

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

由此产生的行为是您正确逆向设计的行为。