sql - 使用排名函数查找重复出现

Question

请帮助我生成以下查询，我已经苦苦挣扎了一段时间。假设我有一个简单的表格，其中包含月份编号和在这个特定月份是否有任何失败事件的信息

下面是生成示例数据的脚本:

WITH DATA(Month, Success) AS
(
    SELECT  1, 0 UNION ALL
    SELECT  2, 0 UNION ALL
    SELECT  3, 0 UNION ALL
    SELECT  4, 1 UNION ALL
    SELECT  5, 1 UNION ALL
    SELECT  6, 0 UNION ALL
    SELECT  7, 0 UNION ALL
    SELECT  8, 1 UNION ALL
    SELECT  9, 0 UNION ALL
    SELECT 10, 1 UNION ALL
    SELECT 11, 0 UNION ALL
    SELECT 12, 1 UNION ALL
    SELECT 13, 0 UNION ALL
    SELECT 14, 1 UNION ALL
    SELECT 15, 0 UNION ALL
    SELECT 16, 1 UNION ALL
    SELECT 17, 0 UNION ALL
    SELECT 18, 0
)

鉴于“重复失败”的定义:

如果在任何 6 个月期间至少 4 个月内发生事件失败，则出现此类失败的最后一个月是“重复失败”，我的查询应返回以下输出

Month   Success RepeatedFailure
1       0   
2       0   
3       0   
4       1   
5       1   
6       0       R1
7       0       R2
8       1   
9       0   
10      1   
11      0       R3
12      1   
13      0   
14      1   
15      0   
16      1   
17      0
18      0       R1

在哪里:

R1 - 第 6 个月第 -1 次重复故障(过去 6 个月内发生 4 次故障)。

R2 - 第 7 个月第 -2 次重复失败(过去 6 个月中有 4 次失败)。

R3 - 第 11 个月第 -3 次重复失败(过去 6 个月中有 4 次失败)。

R1 - 再次在第 18 个月出现第 1 次重复故障，因为在最近 6 个报告周期中首次出现新的重复故障时，应从头开始重新编号

重复失败是连续编号的，因为根据它的数量，我必须应用适当的乘数:

第一次重复失败 - X2

第二次重复失败 - X4

第 3 次和更多次重复失败 -X5。

Answer 1

我相信这可以改进，但它有效。我们基本上进行了两次传递——第一次确定重复失败，第二次确定每次失败的类型。请注意 Intermediate2绝对可以取消，我只是为了清楚起见才把它分开。所有的代码都是一个语句，我的解释是交错的:

;WITH DATA(Month, Success) AS
-- assuming your data  as defined (with my edit)
,Intermediate AS 
(
SELECT
    Month,
    Success,
    -- next column for illustration only
    (SELECT SUM(Success) 
     FROM DATA hist 
     WHERE curr.Month - hist.Month BETWEEN 0 AND 5) 
        AS SuccessesInLastSixMonths,
    -- next column for illustration only
    6 - (SELECT SUM(Success) 
     FROM DATA hist 
     WHERE curr.Month - hist.Month BETWEEN 0 AND 5) 
        AS FailuresInLastSixMonths,
    CASE WHEN 
            (6 - (SELECT SUM(Success) 
                    FROM DATA hist 
                    WHERE curr.Month - hist.Month BETWEEN 0 AND 5)) 
            >= 4 
            THEN 1
            ELSE 0 
    END AS IsRepeatedFailure
FROM DATA curr 
-- No real data until month 6
WHERE curr.Month > 5
)

在这一点上，我们通过计算直到并包括它在内的六个月内的故障来确定每个月是否是重复故障。

,Intermediate2 AS
(
SELECT 
    Month,
    Success,
    IsRepeatedFailure,
    (SELECT SUM(IsRepeatedFailure) 
        FROM Intermediate hist 
        WHERE curr.Month - hist.Month BETWEEN 0 AND 5) 
        AS RepeatedFailuresInLastSixMonths
FROM Intermediate curr
)

现在我们已经统计了 的数量重复 截至目前的六个月内的失败

SELECT
    Month,
    Success,
    CASE IsRepeatedFailure 
        WHEN 1 THEN 'R' + CONVERT(varchar, RepeatedFailuresInLastSixMonths) 
        ELSE '' END
    AS RepeatedFailureText
FROM Intermediate2

所以我们可以说，如果这个月是重复失败，那么重复失败的基数是多少。

结果:

Month       Success     RepeatedFailureText
----------- ----------- -------------------------------
6           0           R1
7           0           R2
8           1           
9           0           
10          1           
11          0           R3
12          1           
13          0           
14          1           
15          0           
16          1           
17          0           
18          0           R1

(13 row(s) affected)

性能考虑将取决于您实际拥有的数据量。