sql-server - 在 SQL 中计算 "previous"行的有效方法是什么？

Question

很难说出这个标题。

我有一个数据表，其中包含每张发票的一行。例如:

| Invoice ID | Customer Key |    Date    | Value | Something |
| ---------- | ------------ | ---------- | ------| --------- |
|     1      |     A        | 08/02/2019 |  100  |     1     |
|     2      |     B        | 07/02/2019 |  14   |     0     |
|     3      |     A        | 06/02/2019 |  234  |     1     |
|     4      |     A        | 05/02/2019 |  74   |     1     |
|     5      |     B        | 04/02/2019 |  11   |     1     |
|     6      |     A        | 03/02/2019 |  12   |     0     |

我需要添加另一列来计算每个 CustomerKey 之前的行数，但前提是“Something”等于 1，以便它返回:

| Invoice ID | Customer Key |    Date    | Value | Something | Count |
| ---------- | ------------ | ---------- | ------| --------- | ----- |
|     1      |     A        | 08/02/2019 |  100  |     1     |   2   |
|     2      |     B        | 07/02/2019 |  14   |     0     |   1   |
|     3      |     A        | 06/02/2019 |  234  |     1     |   1   |
|     4      |     A        | 05/02/2019 |  74   |     1     |   0   |
|     5      |     B        | 04/02/2019 |  11   |     1     |   0   |
|     6      |     A        | 03/02/2019 |  12   |     0     |   0   |

我知道我可以使用像这样的 CTE 来做到这一点...

(
select
    count(*)
from table
where
    [Customer Key] = t.[Customer Key]
    and [Date] < t.[Date]
    and Something = 1
)

但我有很多数据，而且速度很慢。我知道我也可以使用交叉应用来实现相同的目标，但据我所知，这并没有比仅使用 CTE 更好。

所以;有没有更有效的方法来实现这一点，还是我只是把它收起来？

编辑:我最初发布此内容时并没有要求只计算 Something = 1 的行。 Mea culpa - 我急忙问。不幸的是，我认为这意味着我不能使用 row_number() over (partition by [Customer Key])

Answer 1

假设您使用的是 SQL Server 2012+，您可以使用窗口函数:

COUNT(CASE WHEN Something = 1 THEN CustomerKey END) OVER (PARTITION BY CustomerKey ORDER BY [Date]
                                                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -1 AS [Count]

新要求逻辑之前的旧答案:

COUNT(CustomerKey) OVER (PARTITION BY CustomerKey ORDER BY [Date]
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -1 AS [Count]