Início / dba / Perguntas / 212713
Accepted
Jon of All Trades
Asked: 2018-07-20 13:25:08 +0800 CST

Posso calcular ROW_NUMBER() apenas para registros consecutivos?

  • 772

Eu preciso calcular números de sequência para valores consecutivos. Isso soa como um trabalho para ROW_NUMBER()!

DECLARE @Data TABLE
    (
    Sequence    TINYINT NOT NULL PRIMARY KEY,
    Subset  CHAR(1) NOT NULL
    )
INSERT INTO @Data (Sequence, Subset) VALUES
    (1, 'A'),
    (2, 'A'),
    (3, 'A'),
    (4, 'B'),  -- New subset
    (5, 'B'),
    (6, 'A')   -- New subset

SELECT
    Sequence, Subset,
    ROW_NUMBER() OVER (PARTITION BY Subset ORDER BY Sequence) AS SeqWithinGroup
FROM
    @Data

Eu esperava que a PARTITIONcláusula redefinisse a contagem a cada alteração em Subset, mas, em vez disso, o SQL Server coleta todos os valores para um determinado Subsetvalor e os numera. Aqui está o que eu esperava e o que recebi:

Sequence Subset Expected Actual
-------- ------ -------- -----
1        A      1        1
2        A      2        2
3        A      3        3
4        B      1        1
5        B      2        2
6        A      *1*      *4*

Quando o SQL atinge a linha 6, ele retoma a numeração do subconjunto "A", enquanto eu o vejo como a primeira linha de um novo subconjunto que também é chamado de "A".

Existe uma maneira de fazer a ROW_NUMBER()partição estritamente, em vez do comportamento padrão?

Há várias perguntas aqui e em outros lugares sobre a contagem de valores consecutivos com SQL. No entanto, ainda não vi um que aborda valores repetidos no PARTITION BY(s) campo(s). A maioria lida apenas com valores crescentes, geralmente datas.

sql-server sql-server-2017

3 respostas

  1. Best Answer

    Consegui resolver isso usando LAG():

    SELECT
    Sequence, Subset,
    CASE WHEN Sequence = 1 OR Subset <> LAG(Subset, 1) OVER (ORDER BY Sequence)
        THEN 'New subset'
        ELSE 'Continuation'
        END
FROM
    @Data

    Isso retorna "Novo subconjunto" para os registros #1, #4 e #6. Aparentemente LAG()particiona um pouco diferente do ROW_NUMBER().

    Obviamente, isso não fornece números de linha, mas me ajudou a atingir o objetivo de identificar sequências consecutivas de números, quando o identificador do subconjunto pode se repetir.

    • 3

  2. O que fazemos aqui é,

    1. Calcular redefinições (coluna rstno código)
    2. sum()para obter grupos (coluna grpno código)
    3. Obtenha o row_number()do agrupamento.

    Código,

    SELECT row_number() OVER (PARTITION BY grp ORDER BY sequence) AS number,
  sequence,
  subset
FROM (
  SELECT count(rst) OVER (ORDER BY sequence) AS grp, *
  FROM (
    SELECT CASE WHEN subset != lag(subset) OVER (ORDER BY sequence) THEN 1 END AS rst, *
    FROM foo
  ) AS t1
) AS t2;

    Você pode ver o DBFiddle com resultados aqui

    • 3

  3. Eu usei minha resposta deste post e modifiquei para o seu problema.

    --Demo setup

;DECLARE @Data TABLE
    (
    Sequence    TINYINT NOT NULL PRIMARY KEY,
    Subset  CHAR(1) NOT NULL
    )
;INSERT INTO @Data (Sequence, Subset) VALUES
    (1, 'A'),
    (2, 'A'),
    (3, 'A'),
    (4, 'B'),  -- New subset
    (5, 'B'),
    (6, 'A')   -- New subset

--The solution
--Create a grouping for consecutive values called grp
;WITH _cte
AS (
    SELECT *
        ,DATEADD(DAY, - ROW_NUMBER() OVER (
                PARTITION BY Subset ORDER BY [Sequence]
                ), [Sequence]) AS grp
    FROM @Data
    )
   ,AddedRn    --add a row number for each entry in the group
AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY grp ORDER BY sequence
            ) AS rn
    FROM _cte
    )
--Select result ordering by Sequence
SELECT Sequence, Subset, grp, rn as SeqWithinGroup    
FROM AddedRn
order by Sequence

    | Sequence | Subset | grp                     | SeqWithinGroup |
|----------|--------|-------------------------|----------------|
| 1        | A      | 1900-01-01 00:00:00.000 | 1              |
| 2        | A      | 1900-01-01 00:00:00.000 | 2              |
| 3        | A      | 1900-01-01 00:00:00.000 | 3              |
| 4        | B      | 1900-01-04 00:00:00.000 | 1              |
| 5        | B      | 1900-01-04 00:00:00.000 | 2              |
| 6        | A      | 1900-01-03 00:00:00.000 | 1              |
    • 1

relate perguntas