Início / coding / Perguntas / 79349469
Accepted
DooDoo
Asked: 2025-01-12 14:50:44 +0800 CST

Usando funções de janela do SQL Server com ano e mês (período de tempo)

  • 772

Por favor, considere este script:

Declare @tbl Table
(
    F1    int,
    F2    int,
    Year  int,
    Month tinyint
)

Insert into @tbl 
values
(10, 1, 2020, 1),
(10, 1, 2020, 2),
(10, 1, 2020, 3),
(10, 1, 2020, 4),
(10, 2, 2020, 5),
(10, 1, 2020, 6),
(10, 1, 2020, 7),
(11, 1, 2020, 1),
(11, 1, 2020, 2),
(11, 2, 2020, 3),
(11, 2, 2020, 4),
(11, 1, 2020, 5),
(12, 1, 2020, 1),
(12, 1, 2020, 2),
(12, 1, 2020, 3),
(12, 1, 2020, 4)

Eu escrevi esta consulta:

Select 
    F1, F2, year, month,
    Row_number() over (partition by F1, F2 order by year, month) as rownumber,
    Rank() over (partition by F1, F2 order by year, month) as rnk
From 
    @tbl
Order by 
    f1, f2, year, month

Esta consulta retorna este conjunto de resultados:

F1 F2 Ano Mês Número da linha RNK-R
10 1 2020 1 1 1
10 1 2020 2 2 2
10 1 2020 3 3 3
10 1 2020 4 4 4
10 1 2020 6 5 5
10 1 2020 7 6 6
10 2 2020 5 1 1
11 1 2020 1 1 1
11 1 2020 2 2 2
11 1 2020 5 3 3
11 2 2020 3 1 1
11 2 2020 4 2 2
12 1 2020 1 1 1
12 1 2020 2 2 2
12 1 2020 3 3 3

Mas eu quero esse resultado:

F1 F2 Ano Mês Sequência
10 1 2020 1 1
10 1 2020 2 2
10 1 2020 3 3
10 1 2020 4 4
10 2 2020 5 1
10 1 2020 6 1
10 1 2020 7 2
11 1 2020 1 1
11 1 2020 2 2
11 2 2020 3 1
11 2 2020 4 2
11 1 2020 5 1
12 1 2020 1 1
12 1 2020 2 2
12 1 2020 3 3
12 1 2020 4 4

Ou seja, em cada conjunto do mesmo valor F1,
sequenciar por ano e depois por mês,
mas redefinir o número de sequência ano-mês a cada alteração F2 .

Como posso atingir o resultado desejado? Obrigado

sql

3 respostas

  1. Best Answer

    Isso é basicamente um problema de lacunas e ilhas disfarçado. Aqui, cada "ilha" é um grupo de registros pertencentes ao mesmo F1bloco onde o F2valor não mudou em sequência ao longo do ano e do mês. Podemos gerar um pseudogrupo para manter o controle desses registros e, em seguida, usar o ROW_NUMBER()para gerar os valores de sequência final. Observe que esse valor de pseudogrupo é redefinido para 1 quando o F1bloco termina ou os F2valores mudam.

    WITH cte AS (
    SELECT *, CASE WHEN LAG(F2) OVER (PARTITION BY F1 ORDER BY Year, Month) != F2 THEN 1 ELSE 0 END AS flag
    FROM @tbl
),
cte2 AS (
    SELECT *, SUM(flag) OVER (PARTITION BY F1 ORDER BY Year, Month) grp
    FROM cte
)

SELECT F1, F2, Year, Month,
       ROW_NUMBER() OVER (PARTITION BY F1, grp ORDER BY Year, Month) AS seq
FROM cte2
ORDER BY F1, Year, Month;

    Demonstração

    • 3

  2. Depois de determinar as bordas (momentos nas sequências agrupadas em F1 onde F2 muda), você pode subtrair a posição da última borda de todas as posições subsequentes:

    With bordered as
(
    Select 
        *,
        Row_number() over win as pos,
        case when F2 = lag(F2) over win then null else Count(1) over win - 1 end as ignore_before
    From 
        @tbl
    Window win As (partition by F1 order by year, month)
)
Select F1, F2, year, month, pos - max(ignore_before) over win Sequence
from bordered
Window win As (partition by F1 order by year, month)
Order by
    f1, year, month;

    Aqui ele roda em um SQLFiddle .

    Como você vai querer o máximo de uma posição com janela,
    você precisará de pelo menos uma passagem para cada função de janela,
    portanto, será necessário o uso de um CTE.

    Observe que usei uma definição de janela para evitar repetir seu uso (e assim evitar erros de cópia).

    • 1 
  3. WITH GroupedData
AS (SELECT F1,F2,YEAR,MONTH, 
           CASE
               WHEN F2 != LAG(F2, 1, F2) OVER (PARTITION BY F1 ORDER BY YEAR, MONTH) THEN
                   1
               ELSE
                   0
           END AS IsNewGroup
    FROM tbl),
     Groups
AS (SELECT F1,F2,YEAR,MONTH, 
           SUM(IsNewGroup) OVER (PARTITION BY F1 ORDER BY YEAR, MONTH) AS GroupID
    FROM GroupedData)
SELECT F1,F2,YEAR,MONTH, 
       ROW_NUMBER() OVER (PARTITION BY F1, GroupID ORDER BY YEAR, MONTH) AS SEQUENCE
FROM Groups
ORDER BY F1,YEAR,MONTH;

    Usamos a LAG()função para olhar o valor F2 da linha anterior (dentro do mesmo grupo F1, ordenado por Ano e Mês). Se o valor F2 for diferente da linha anterior, significa que um novo grupo está começando. Nesse caso, marcamos com 1 (chamado IsNewGroup). Se for o mesmo, marcamos com 0.

    Pegamos a IsNewGroupcoluna e calculamos um total corrente usando SUM(). Esse total corrente (chamado GroupID) nos ajuda a atribuir um número exclusivo a cada grupo de linhas que compartilham o mesmo valor F2 dentro de um grupo F1. Para cada grupo (com base em F1 e GroupID), geramos um número de sequência usando ROW_NUMBER(). Essa sequência é ordenada por Ano e Mês. Por fim, classificamos tudo por F1, Ano e Mês para que os resultados apareçam na ordem correta.

    • 1

relate perguntas