时间戳

首页 > 解决方案 > SQL server 组/分区压缩历史表

sql - SQL server 组/分区压缩历史表

问题描述

得到某人属于特定类别的日期表，如下所示：

    drop table if exists #category
    create table #category (personid int, categoryid int, startdate datetime, enddate datetime)
    insert into #category 
    select * from 
    (
    select 1 Personid, 1 CategoryID, '01/04/2010' StartDate, '31/07/2016' EndDate union
    select 1 Personid, 5 CategoryID, '07/08/2016' StartDate, '31/03/2019' EndDate union
    select 1 Personid, 5 CategoryID, '01/04/2019' StartDate, '01/04/2019' EndDate union
    select 1 Personid, 5 CategoryID, '02/04/2019' StartDate, '11/08/2019' EndDate union
    select 1 Personid, 4 CategoryID, '12/08/2019' StartDate, '03/11/2019' EndDate union
    select 1 Personid, 5 CategoryID, '04/11/2019' StartDate, '22/03/2020' EndDate union
    select 1 Personid, 5 CategoryID, '23/03/2020' StartDate, NULL EndDate union
    select 2 Personid, 1 CategoryID, '01/04/2010' StartDate, '09/04/2015' EndDate union
    select 2 Personid, 4 CategoryID, '10/04/2015' StartDate, '31/03/2018' EndDate union
    select 2 Personid, 4 CategoryID, '01/04/2018' StartDate, '31/03/2019' EndDate union
    select 2 Personid, 4 CategoryID, '01/04/2019' StartDate, '23/06/2019' EndDate union
    select 2 Personid, 4 CategoryID, '24/06/2019' StartDate, NULL EndDate 
    ) x
    order by personid, startdate

我正在尝试浓缩它，所以我得到了这个：

个人ID 类别ID 开始日期 结束日期
1 1 2010 年 1 月 4 日 2016 年 7 月 31 日
1 5 2016 年 7 月 8 日 2019 年 11 月 8 日
1 4 2019 年 12 月 8 日 2019 年 3 月 11 日
1 5 2019 年 4 月 11 日 空值
2 1 2010 年 1 月 4 日 2015 年 9 月 4 日
2 4 2015 年 1 月 4 日 空值

我遇到了像 personid 1 这样的人，他们在（例如）第 5 类，然后进入第 4 类，然后他们又回到第 5 类。

所以做类似的事情：

select
personid,
categoryid,
min(startdate) startdate,
max(enddate) enddate
from #category
group by 
personid, categoryid

给了我第 5 类第一个时期的最早日期，以及第二个时期的最晚日期——这意味着它创建了一个重叠时期。

所以我尝试用 rownum 或 rank 对其进行分区，但它仍然做同样的事情 - 即将“类别 5”视为同一组：

select
rank() over (partition by personid, categoryid order by personid, startdate) rank,
c.*
from #category c
order by personid, startdate
秩 拟人 类别ID 开始日期 结束日期
1 1 1 2010-04-01 00:00:00.000 2016-07-31 00:00:00.000
1 1 5 2016-08-07 00:00:00.000 2019-03-31 00:00:00.000
2 1 5 2019-04-01 00:00:00.000 2019-04-01 00:00:00.000
3 1 5 2019-04-02 00:00:00.000 2019-08-11 00:00:00.000
1 1 4 2019-08-12 00:00:00.000 2019-11-03 00:00:00.000
4 1 5 2019-11-04 00:00:00.000 2020-03-22 00:00:00.000
5 1 5 2020-03-23 00:00:00.000 空值
1 2 1 2010-04-01 00:00:00.000 2015-04-09 00:00:00.000
1 2 4 2015-04-10 00:00:00.000 2018-03-31 00:00:00.000
2 2 4 2018-04-01 00:00:00.000 2019-03-31 00:00:00.000
3 2 4 2019-04-01 00:00:00.000 2019-06-23 00:00:00.000
4 2 4 2019-06-24 00:00:00.000 空值

您可以在 rank 列中看到类别 5 从 1、2、3 开始，错过一行并继续 4、5 所以 obvs 在同一个分区中 - 我认为添加 order by 子句会强制它开始一个新的当类别从 5 更改为 4 并再次返回时进行分区。

有什么想法吗？

标签： sqlsql-servergroup-bypartition-by

解决方案

这是一种差距和孤岛问题。但是，如果您的数据像在示例数据中那样完美地平铺（没有间隙），那么您可以在没有任何聚合的情况下执行此操作——这应该是最有效的方法：

select personid, categoryid, startdate,
       dateadd(day, -1, lead(startdate) over (partition by personid order by startdate)) as enddate
from (select c.*,
             lag(categoryid) over (partition by personid order by startdate) as prev_categoryid
      from #category c
     ) c
where prev_categoryid is null or prev_categoryid <> categoryid;

该where子句仅选择类别更改的行。然后lead()获取下一个开始日期 - 并为您想要的减去 1 enddate。

推荐阅读