我有一个基本上是移动图的表格，例如

CREATE TABLE MoveGraph
(
   Start varchar(10),
   End varchar(10),
   UpdateDate smalldatetime
)

问题是我想知道最终目的地（即链中的最后一行的值），并用它们来添加列 UltEnd 和 UltDate，以便回头更新过渡行。

我创建了 | 分离的移动图（即 a|b|c|d，它们从a 开始，先到b，再到c，最终到d），我一直在摆弄窗口函数，试图跟踪这条链并使UPDATE语句可用。

沮丧的是，获取链中倒数第二个和最后一个这对值。

我想获取

(a,b), (c,d)
(b,c), (c,d)

对于上面的示例，或对于 'a|b|c'

(a,b), (b,c)

到目前为止我找到的所有关于“倒数第二个”的帖子都只是假设从末尾提取数据，而不是混合搭配这些组合。

FIRST_VALUE 和 LAST_VALUE ROW/RANGE 子句只接受无符号字面量，而我的图是变长的。

LEAD 和 LAG 会标记一个变量或表达式，但如果该表达式变为负数，就会抛出错误——即使你在周围放一个 CASE 来避免按这种方式调用它。

有人知道如何获取倒数第二个以及其他所有线索吗？

SELECT TOP 1000 *
FROM (
    SELECT mt.chain, c.maxi, c.i,

        -- get the (start,end) pairs in the graph 
        c.value start
        , LEAD(value) OVER(PARTITION BY mt.Chain ORDER BY (c.i)) e1

    -- here are the things I tried to get 2nd to last
        , CASE WHEN c.maxi > c.i THEN LEAD(value, c.maxi - c.i - 1) ELSE NULL END lt1

    -- give LEAD a reverse window and try to get next one, no dice
        , LEAD(value,2) OVER(PARTITION BY mt.Chain ORDER BY c.i desc) lt1
        , LEAD(value,1) OVER(PARTITION BY mt.Chain ORDER BY c.i desc) lt1

    -- try the reverse window with FIRST_VALUE.  Works when graph has 3 nodes only
        , FIRST_VALUE(value) OVER(PARTITION BY mt.Chain ORDER BY c.i desc ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) lastT

    -- LAST_VALUE always works to get the tail
        , LAST_VALUE(value) OVER(PARTITION BY mt.Chain ORDER BY (c.i) ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) finalT
    FROM #multitrans mt  -- temp table holding the |-sep graphs
    CROSS APPLY (SELECT i, value, MAX(i) OVER (Order by (select null)) maxi
        FROM(
        SELECT ROW_NUMBER() OVER (PARTITION BY CHAIN ORDER BY (SELECT NULL)) i, value 
        FROM STRING_SPLIT(Chain,'|')
        ) b
        ) c
    ) x
    -- <need to figure out end condition>
    ORDER BY chain, i

解决方案

这是你需要的答案吗？

SELECT TOP 1000 *
FROM (
    SELECT
        mt.chain, c.maxi, c.i,
        CONCAT('(', 
            c.value, ',',
            LEAD(value) OVER(PARTITION BY mt.Chain ORDER BY (c.i)),
            ')') AS Edge,
        CONCAT('(', 
            MAX(CASE WHEN c.i = c.maxi - 1 THEN value END) OVER(PARTITION BY mt.Chain),
            ',',
            MAX(CASE WHEN c.i = c.maxi THEN value END) OVER(PARTITION BY mt.Chain),
            ')') AS LastEdge,
        CONCAT('(', 
            MAX(CASE WHEN c.i = c.maxi - 1 THEN value END) OVER(PARTITION BY mt.Chain),
            ',',
            LAST_VALUE(value) OVER(
                 PARTITION BY mt.Chain
                 ORDER BY c.i
                 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -- Important!
                 ),
            ')') AS AlsoLastEdge
    FROM #multitrans mt  -- temp table holding the |-sep graphs
    CROSS APPLY (
        SELECT i, value, MAX(i) OVER () AS maxi
        FROM(
            SELECT
                ROW_NUMBER() OVER (
                    ORDER BY CHARINDEX('|' + value + '|', '|' + chain + '|')
                    ) AS i,
                value 
            FROM STRING_SPLIT(Chain,'|')
        ) b
    ) c
) x
where x.i < x.maxi  -- Omit last item that has no follower
-- where x.i < x.maxi - 1  -- Omit the last edge too?
order by chain, i

结果：

以上使用条件聚合来选择最后一个和倒数第二个节点。条件聚合是在聚合函数内放置一个 CASE 表达式或 IIF() 函数，以排除所有但所需的值。在本例中，MAX() 仅抓取所选的（非空）值。

LAST_VALUE() 也可用于获取最后一个节点，但你必须包含 ROWS 限定符以强制窗口向分区末端查看。否则，默认窗口是 RANGE BETWEEN UNLIMITED PRECEDING AND CURRENT ROW。

我还将 ORDER BY(SELECT NULL) 替换为在 ROW_NUMBER() 计算中更有意义的 CHARINDEX(...) 表达式。只有在你不在乎顺序时才应使用 ORDER BY(SELECT NULL)。它在小数据集上可能看起来可行，但这并不能保证，数据规模增大时可能会出错（这是最容易出错的那类问题之一）。

CHARINDEX('|' + value + '|', '|' + chain + '|') 假设链中的值是唯一的。添加的定界符是为了防止像 'B' 与 'ABC' 这类错误匹配。

如果值并非唯一（例如 'a|b|c|b|a'），则需要另一种变通方法。请参阅这个回答 此处答案，它使用 OPENJSON() 来同时获得一个值和一个索引（键）。同样，不要在这种情况下使用 ORDER BY (SELECT NULL)。

    CROSS APPLY (
        SELECT
            j.[key] + 1 AS i,
            j.value,
            MAX(j.[key] + 1) OVER () AS maxi
        FROM OPENJSON(
            '["' +  REPLACE(REPLACE(mt.chain, '"', '\"'), '|', '","') + '"]'
        ) j
    ) c

如果使用SQL Server 2022或更高版本，可以使用 STRING_SPLIT() 及其 enable_ordinal 选项。

    CROSS APPLY (
        SELECT ss.ordinal AS i, ss.value, MAX(ss.ordinal) OVER () maxi
        FROM STRING_SPLIT(mt.chain, '|', 1) ss
    ) c

对于 maxi 的计算，我把 OVER(...) 子句简化为仅仅 OVER()。由于这是在一个 CROSS APPLY 内，因此不需要 PARTITION BY。由于这是一个应用于整个集合的 MAX() 运算，因此也不需要 ORDER BY。仍然需要裸露的 OVER() 以使其成为一个窗口聚合。

请参见 这个db<>fiddle 以查看演示。