Início / dba / Perguntas / 237660
Accepted
SQLserving
Asked: 2019-05-09 07:13:42 +0800 CST

Sobreposições e agrupamento de lacunas no SQL Server

  • 772

Estou tentando encontrar lacunas nos meus dados e agrupá-las. A tabela com a qual estou trabalhando é assim: 

runid|year |road|start   |end     
01010|  9  |2   |0.000   |0.585    
01100|  9  |2   |0.585   |4.980    
01100|  9  |2   |4.980   |7.777  
01100|  9  |2   |7.777   |11.857   
01100|  9  |2   |11.857  |13.274   
01100|  9  |2   |15.235  |21.021  
01100|  9  |2   |21.021  |25.333  
01100|  9  |3   |0.000   |7.777  
01100|  9  |3   |7.777   |13.274  
01100|  9  |3   |13.274  |25.333 
...

Eu quero poder criar uma nova coluna que identifique o grupo assim:

runid|year |road|start   |end    |rn    
01010|  9  |2   |0.000   |0.585  |1  
01100|  9  |2   |0.585   |4.980  |1  
01100|  9  |2   |4.980   |7.777  |1
01100|  9  |2   |7.777   |11.857 |1  
01100|  9  |2   |11.857  |13.274 |1  
01100|  9  |2   |15.235  |21.021 |2
01100|  9  |2   |21.021  |25.333 |2 
01100|  9  |3   |0.000   |7.777  |1
01100|  9  |3   |7.777   |13.274 |1 
01100|  9  |3   |13.274  |25.333 |1  
...

Como você pode ver, o início e o fim estão sincronizados para alguma parte dos dados e, em seguida, há uma lacuna entre 13.274 e 15.235, que é onde você alterna a nova coluna (rn)
Nota: A tabela é um instantâneo da tabela grande com vários runid, anos, estradas e pontos de início e fim relevantes

Eu fiz algo assim até agora: 

with cte as (  
select distinct runid,year,road,start,end, LAG(end) over (partition by runid,year,road order by start) rn from dbo.runners
)
select *,CASE WHEN rn <> start then 1 when rn is null then 2 else 0 end chk from cte order by runid,year,road,start

Isso me dá isso: 

runid|year |road|start   |end    |rn    |chk   
01010|  9  |2   |0.000   |0.585  |NULL  |2  
01100|  9  |2   |0.585   |4.980  |0.585 |0  
01100|  9  |2   |4.980   |7.777  |4.980 |0  
01100|  9  |2   |7.777   |11.857 |7.777 |0  
01100|  9  |2   |11.857  |13.274 |11.857|0    
01100|  9  |2   |15.235  |21.021 |13.274|1  
01100|  9  |2   |21.021  |25.333 |21.021|0   
01100|  9  |3   |0.000   |7.777  |NULL  |2  
01100|  9  |3   |7.777   |13.274 |7.777 |0  
01100|  9  |3   |13.274  |25.333 |13.274|0    
...

Não tenho certeza de como obter mais como uma coluna RANK para cada grupo nas minhas partições.

Link do teste aqui

sql-server sql-server-2014

1 respostas

  1. Best Answer

    Uma abordagem possível é definir quando há quebras nos valores de tempo usando LAG()e, em seguida, definir os grupos usando SUM():

    ;WITH ChangedCTE AS (
   SELECT
      *,
      CASE 
         WHEN [start] = LAG([end]) OVER (PARTITION BY [runid], [year], [road] ORDER BY [start]) THEN 0
         ELSE 1
      END AS Changed
   FROM [dbo].[runners]
)
SELECT
   [runid], [year], [start], [end], [road],
   SUM([Changed]) OVER (PARTITION BY [runid], [year], [road] ORDER BY [start]) AS Groups
FROM ChangedCTE

    Resultado:

    runid   year    start   end road    Groups
01010000    9   0.000   0.585   2   1
01010000    9   0.585   4.980   2   1
01010000    9   4.980   7.777   2   1
01010000    9   7.777   11.857  2   1
01010000    9   11.857  13.274  2   1
01010000    9   15.235  21.021  2   2
01010000    9   21.021  22.142  2   2
01010000    9   22.142  25.946  2   2
01010000    9   0.000   7.777   3   1
01010000    9   7.777   11.857  3   1
01010000    9   11.857  13.274  3   1
01010000    9   15.235  21.021  3   2
01010000    9   21.021  22.142  3   2
01010000    9   22.142  25.946  3   2
    • 3

relate perguntas