主页 / dba / 问题 / 237660
Accepted
SQLserving
Asked: 2019-05-09 07:13:42 +0800 CST

SQL Server 中的重叠和间隙分组

  • 772

我试图在我的数据中找到差距，然后对它们进行分组。我正在使用的表如下所示： 

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 
...

我希望能够创建一个新列来标识这样的组：

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  
...

如您所见，某些部分数据的起始端是同步的，然后在 13.274 到 15.235 之间存在间隙，这是您切换新列的位置（rn）
注意：该表是大表的快照有许多 runid、years、roads 和相关的起点和终点

到目前为止，我已经做了这样的事情： 

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

这给了我这个： 

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    
...

我不确定如何为我的分区中的每个组获得更像一个 RANK 列。

测试链接在这里

sql-server sql-server-2014

1 个回答

  1. Best Answer

    一种可能的方法是使用 定义时间值何时中断，LAG()然后使用 定义组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

    输出：

    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

相关问题