sql-server - T-SQL 中暂时状态的尝试次数

Question

我有一个看起来像这样的表(没有尝试列):

Account      Status        Date         Attempt(*)

A            In_PROGRESS   12/12/2017   1
A            ERROR         13/12/2017   1
A            SUCCESS       15/12/2017   2
B            ERROR         10/12/2017   1
B            SUCCESS       13/12/2017   2
C            IN_PROGRESS   11/12/2017   1
C            ERROR         13/12/2017   1
C            ERROR         14/12/2017   2
C            IN_PROGRESS   15/12/2017   3

我想获取 Attempt(*) 列。 ERROR 和 SUCCESS 状态是结束状态。例如，对于账户 C，我们知道第一次尝试产生了错误，第二次尝试产生了错误，第三次尝试是 IN_PROGRESS。

我知道由于暂时状态“IN_PROGRESS”，单独使用 RANK() 是行不通的。例如，如果我要去:

RANK() OVER(PARTITION BY Account ORDER BY Date ASC)

我会得到:

Account      Status        Date         Attempt(*)

A            In_PROGRESS   12/12/2017   1
A            ERROR         13/12/2017   2
A            SUCCESS       15/12/2017   3
B            ERROR         10/12/2017   1
B            SUCCESS       13/12/2017   2
C            IN_PROGRESS   11/12/2017   1
C            ERROR         13/12/2017   2
C            ERROR         14/12/2017   3
C            IN_PROGRESS   15/12/2017   4

其中不考虑暂时状态。我将使用第二个表中的结果创建一个新列以获得所需的结果，sudo 代码将类似于:

New_Attempt[n] = if Attempt(*) = 1 then 1 else (if Attempt(*) > 1 and Status[n-1] in(ERROR, SUCCESS) then Attempt[n-1] + 1 else Attempt [n-1])

其中“n”是行号。

我对 SQL 比较陌生，我研究过其他排名函数，如 NTILE 和 DENSE_RANK，但我仍然想不出办法来做到这一点。

附言我在 SQL Server Management Studio 2008 中使用 T-SQL

谢谢! :)

Answer 1

对于每一行，计算同一帐户之前所有不是 IN_PROGRESS 的行并将结果加 1。

乘坐C帐户，例如:

Account  Status       Date        Attempt (expected values)
-------  -----------  ----------  -------
C        IN_PROGRESS  11/12/2017  1
C        ERROR        13/12/2017  1
C        ERROR        14/12/2017  2
C        IN_PROGRESS  15/12/2017  3

很明显，第一行之前没有行，也就是说有0个非IN_PROGRESS行也是。把它加 1，你会得到 1 作为 Attempt 的值。为了它。第二行也是一样:仍然没有非IN_PROGRESS它前面的行，所以 Attempt也将是 1。

现在第三行，你已经有了一个ERROR它前面的行，因此计数将为 1，最后的 Attempt因此，值为 2。

对于最后一行，有两个非 IN_PROGRESS它之前的行(两个 ERROR 行)，因此计数为 2，最终结果为 3。

您可以看到上述描述中的所有值都符合您指定的预期值。现在，如果 ORDER BY 在窗口聚合函数中对您可用(换句话说，如果您使用的是 SQL Server 2012 或更高版本)，这就是您可以如何实现逻辑:

SELECT
  Account,
  Status,
  Date,
  Attempt = 1 + COUNT(CASE WHEN Status <> 'IN_PROGRESS' THEN 1 END)
                OVER (PARTITION BY Account
                      ORDER BY Date
                      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
FROM
  dbo.YourTable
;

这是您可以在 SQL Server 2008 中解决缺少的功能的方法:

SELECT
  Account,
  Status,
  Date,
  Attempt = 1 + (SELECT
                   COUNT(*)
                 FROM
                   dbo.YourTable AS sub
                 WHERE
                   sub.Account = main.Account
                   AND sub.Date < main.Date
                   AND sub.Status <> 'IN_PROGRESS')
FROM
  dbo.YourTable AS main
;

注意:假设Date唯一标识帐户中的一行。