根据标志变量中断row_number（）序列 [英] break row_number() sequence based on flag variable

问题描述

希望找到一些东西。

我有如下所示的数据

id  month flag
111 jan   1
111 feb   1
111 mar   1
111 apr   0
111 may   0
111 jun   1
222 jan   1
222 feb   1
222 mar   0
222 apr   0
222 may   0
222 jun   1

我在寻找以下输出

id  month flag order
111 jan   1    1
111 feb   1    2
111 mar   1    3
111 apr   0    1
111 may   0    2
111 jun   1    1
222 jan   1    1
222 feb   1    2
222 mar   0    1
222 apr   0    2
222 may   0    3
222 jun   1    1

我尝试了 row_number（）但问题出在是我们无法打破顺序并重新开始。从总体上讲，当标志变量从0更改为1或从1更改为0时，我需要分别从1开始为每个id计数

I tried row_number() but the problem is we cannot break the sequence and start over. At an overall level, when ever there is a change in flag variable from 0 to 1 or 1 to 0 I need to start counting from 1 for each id separately

推荐答案

假设使用SQL Server，下面是一个示例：

Assuming SQL Server, here is an example:

DECLARE @T table (id int, [month] char(3), flag bit)

INSERT INTO @T
VALUES
(111, 'jan', 1)
,(111, 'feb', 1)
,(111, 'mar', 1)
,(111, 'apr', 0)
,(111, 'may', 0)
,(111, 'jun', 1)
,(222, 'jan', 1)
,(222, 'feb', 1)
,(222, 'mar', 0)
,(222, 'apr', 0)
,(222, 'may', 0)
,(222, 'jun', 1)

SELECT
    id
    , [month]
    , flag
    , ROW_NUMBER() OVER (PARTITION BY id, section ORDER BY monthNum) [order]
FROM
    (
        SELECT
            id
            , [month]
            , monthNum
            , flag
            , SUM(CASE WHEN newValue = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY id ORDER BY monthNum) section
        FROM
            (
                SELECT
                    id
                    , [month]
                    , monthNum
                    , flag
                    , CASE WHEN LAG(flag, 1, ABS(flag - 1)) OVER (PARTITION BY id ORDER BY monthNum) = flag THEN 0 ELSE 1 END newValue
                FROM
                    (
                        SELECT
                            id
                            , [month]
                            , MONTH(CAST('1 ' + [month] + ' 17' AS datetime)) monthNum
                            , flag
                        FROM @T
                    ) Q
            ) Q2
    ) Q3

这篇关于根据标志变量中断row_number（）序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！