Início / dba / Perguntas / 159767
Accepted
Jason
Asked: 2017-01-03 12:57:40 +0800 CST

Código elegante para encontrar período de tempo contínuo e gerar id

  • 772

Eu tenho uma tabela com dois campos: hora e valor, ambos são inteiros e quero encontrar o período contínuo com valores maiores que 0 e rotular o período "valor>0" com inteiros contínuos.

Por exemplo, se eu tiver a tabela de entrada assim:

Tabela_de_entrada

Eu gostaria da tabela de saída assim:

tabela_saída

Usando três funções CTE e duas funções row_number(), consegui fazer isso. Mas acho a consulta muito complicada. Alguém tem um código mais elegante para fazer isso?

Estou usando o SQL Server 2016 Developer Edition.

Aqui está o meu código:

CREATE TABLE #test1(
    [time] [int] NULL,
    [value] [int] NULL
)

insert into #test1
values(1,0),(2,0),(3,1),(4,0)
    ,(5,1),(6,2),(7,0),(8,0)
,(9,1),(10,2),(11,3),(12,0)
,(13,0),(14,0),(15,1),(16,0);

;with a1 as
(select *, [time] -  row_number() over (order by [time]) as group_num
    from #test1
where value>0),
a2 as 
(select distinct group_num from a1
),
a3 as
(select group_num, row_number() over (order by group_num) as group_id
from a2)
select a1.*, a3.group_id
    from a1 left join a3
on a1.group_num = a3.group_num
sql-server t-sql

1 respostas

  1. Best Answer

    Você só precisa se candidatar DENSE_RANKao resultado do primeiro CTE - Demo

    WITH T
     AS (SELECT *,
                [time] - row_number() OVER (ORDER BY [time]) AS group_num
         FROM   #test1
         WHERE  value > 0)
SELECT *,
       DENSE_RANK() OVER (ORDER BY group_num) AS group_id
FROM   T
    • 5

relate perguntas