sql - 如何在此 SQL 查询中获取每个帐户的第一个结果？

Question

我正在尝试编写遵循以下逻辑的查询:

查找先前状态代码为 X 的帐户的第一个后续状态代码。

所以如果我有一张表:

id    account_num    status_code
64        1               X
82        1               Y
72        2               Y
87        1               Z
91        2               X
103       2               Z

结果是:

id   account_num     status_code
82        1               Y
103       2               Z

我已经提出了几个解决方案，但我对 SQL 并不是那么好，所以到目前为止它们都非常不优雅。我希望这里有人能够为我指明正确的方向。

查看:

SELECT account_number, id
FROM   table
WHERE  status_code = 'X'

查询:

SELECT account_number, min(id)
FROM   table
INNER JOIN view
ON table.account_number = view.account_number
WHERE table.id > view.id

此时我已经有了所需的 ID，但我必须编写另一个使用该 ID 来查找 status_code 的查询。

编辑:为了添加一些上下文，我正在尝试查找状态代码为 X 的调用。如果调用的状态代码为 X，我们希望下次以不同的方式调用它我们尝试一下。此查询的目的是提供一个报告，如果第一次拨号结果为 X 状态代码，该报告将显示第二次拨号的结果。

Answer 1

这是一个 SQL Server 解决方案。

更新

这个想法是要避免 Olaf 提出的许多 NESTED LOOP 连接，因为它们大致具有 O(N * M) 复杂度，因此对您的性能非常不利。 MERGED JOINS 的复杂度为 O(NLog(N) + MLog(M))，这对于现实世界的场景来说要好得多。

下面的查询工作如下:

RankedCTE 是一个子查询，它为每个按帐户划分的 id 分配一个行号，并按代表时间的 id 排序。所以对于这个输出下面的数据

SELECT 
    id, 
    account_num, 
    status_code,
    ROW_NUMBER() OVER (PARTITION BY account_num ORDER BY id DESC) AS item_rank
FROM dbo.Test

会是:

id          account_num status_code item_rank
----------- ----------- ----------- ----------
87          1           Z           1
82          1           Y           2
64          1           X           3
103         2           Z           1
91          2           X           2
72          2           Y           3

一旦我们对它们进行编号，我们就可以像这样将结果自身连接起来:

WITH RankedCTE AS
(
    SELECT 
        id, 
        account_num, 
        status_code,
        ROW_NUMBER() OVER (PARTITION BY account_num ORDER BY id DESC) AS item_rank
    FROM dbo.Test
)
SELECT 
    *
FROM
    RankedCTE A
    INNER JOIN RankedCTE B ON 
            A.account_num = B.account_num
            AND A.item_rank = B.item_rank - 1    

这将在同一个表中为我们提供一个事件和一个先前的事件

id          account_num status_code item_rank   id          account_num status_code item_rank
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
87          1           Z           1           82          1           Y           2
82          1           Y           2           64          1           X           3
103         2           Z           1           91          2           X           2
91          2           X           2           72          2           Y           3

最后，我们只需要获取代码为“X”的前一个事件和代码为“X”的非事件:

    WITH RankedCTE AS
    (
        SELECT 
            id, 
            account_num, 
            status_code,
            ROW_NUMBER() OVER (PARTITION BY account_num ORDER BY id DESC) AS item_rank
        FROM dbo.Test
    )
    SELECT 
        A.id, 
        A.account_num, 
        A.status_code
    FROM 
        RankedCTE A
        INNER JOIN RankedCTE B ON 
            A.account_num = B.account_num
            AND A.item_rank = B.item_rank - 1
            AND A.status_code <> 'X'
            AND B.status_code = 'X'

此查询的查询计划和@Olaf Dietsche 解决方案(其中一个版本)如下。

数据设置脚本

CREATE TABLE dbo.Test
(
    id int not null PRIMARY KEY,
    account_num int not null,
    status_code nchar(1)
)
GO

INSERT dbo.Test (id, account_num, status_code)
SELECT 64 ,       1,               'X' UNION ALL
SELECT 82 ,       1,               'Y' UNION ALL
SELECT 72 ,       2,               'Y' UNION ALL
SELECT 87 ,       1,               'Z' UNION ALL
SELECT 91 ,       2,               'X' UNION ALL
SELECT 103,       2,               'Z'