我有一个带有 、 和 的表的transactionsSQL Server 数据库。client_iddateis_cancelled
我正在尝试获取在标记为 is_cancelled的行中有 3 个或更多事务的 client_ids ，以及 in_a_row 计数。我已经得到了以下内容，当 is_cancelled 支持是连续的时，is_same 的值为 1，并且取消的事务总数为 1（这不是我所需要的）
SELECT
client_id,
date,
is_same,
SUM(is_same) OVER (PARTITION BY client_id ORDER BY date) AS sum_same,
transCancelled
FROM
(
SELECT
client_id,
LAG(is_cancelled) OVER (PARTITION BY client_id ORDER BY date) AS previous_cancelled,
CASE
WHEN is_cancelled = LAG(is_cancelled) OVER (PARTITION BY client_id ORDER BY date)
THEN 1
ELSE 0
END as is_same,
date,
is_cancelled
FROM transactions
WHERE deleted_at IS NULL -- Ignore soft-deleted rows
) AS t_01
WHERE previous_cancelled = 1
ORDER BY date
摆弄示例数据：https ://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=a0c9b12203ab2d0c83f73604ccc9d0a0
预期数据（client_id，count）
1, 3
3, 6