时间戳

首页 > 解决方案 > 在 SCD2 表中合并跨时间跨度的行

sql - 在 SCD2 表中合并跨时间跨度的行

问题描述

我的下表来自 SCD2 表。从这个源表中，我只选择了几列，这导致几行看起来完全相似。我想删除不必要的行，那些包含相同数据的行，并让 ValidFrom 列显示第一个值，而 ValidTo 列显示“时间跨度组”中的最后一个值。

源数据：

| Item     | Color      | ValidFrom     | ValidTo    |
| -------- | ---------- | ------------- | ---------- |
| Ball     | Red        | 2020-01-01    | 2020-03-24 |
| Ball     | Blue       | 2020-03-25    | 2020-04-12 |
| Ball     | Blue       | 2020-04-13    | 2020-05-07 |
| Ball     | Blue       | 2020-05-08    | 2020-11-14 |
| Ball     | Red        | 2020-11-15    | 9999-12-31 |
| Doll     | Yellow     | 2020-01-01    | 2020-03-24 |
| Doll     | Green      | 2020-03-25    | 2020-04-12 |
| Doll     | Green      | 2020-04-13    | 2020-05-07 |
| Doll     | Green      | 2020-05-08    | 2020-11-14 |
| Doll     | Pink       | 2020-11-15    | 9999-12-31 |

我想要完成的是：

| Item     | Color      | ValidFrom     | ValidTo    |
| -------- | ---------- | ------------- | ---------- |
| Ball     | Red        | 2020-01-01    | 2020-03-24 |
| Ball     | Blue       | 2020-03-25    | 2020-11-14 |
| Ball     | Red        | 2020-11-15    | 9999-12-31 |
| Doll     | Yellow     | 2020-01-01    | 2020-03-24 |
| Doll     | Green      | 2020-03-25    | 2020-11-14 |
| Doll     | Pink       | 2020-11-15    | 9999-12-31 |

请注意，项目球最初的颜色是红色，然后是蓝色，然后又变回红色。根据我所学到的，这使事情变得更加复杂。

谢谢你的帮助。

标签： sqlsql-servertsqlgaps-and-islandsscd2

解决方案

这是孤岛和差距问题。

您可以按如下方式使用分析功能：

Select item, color,
       min(validfrom) as validfrom,
       Max(validto) as validto
  From
(Select t.*,
       Sum(case when lged between validfrom and validto then 0 else 1 end) 
           over (partition by item, color order by validfrom) as sm
  From
(Select t.*,
       Lag(validto) over (partition by item, color order by validfrom) as lged
  From t) t) t
Group by item, color, sm

推荐阅读