Microsoft SQL Server 2016，T-SQL：基于单个日期获取数据集的日期范围 [英] Microsoft SQL Server 2016, T-SQL : obtain date range for a dataset based on individual dates

问题描述

我在SQL Server 2016中有一个有趣的情况。我正在使用T-SQL语言。

我有一个名为(#DataSet)的数据集：

名为ContinuousDates的最后一列将始终具有连续的日期值，没有间隔，例如从2021年1月1日到2021年12月31日。它永远不会有相同ID或名称的重复日期，即一个人在给定的一天只能有一行数据。(在本例中，我只显示了一个人，ID=1，姓名=X。在我的实际数据中，我有多个人)。

请注意，纽约市在数据集中出现在较早的位置，并在最后4行中重复出现。

我需要根据日期范围获取以下数据集：

我尝试对数据集使用简单的最小值和最大值，但我意识到有时可能会得到错误的输出，如下所示：

我尝试了使用RANK()和Dense_RANK()函数的一些选项，但无法找到解决方案。有人能为我提供帮助吗？

我在这里附上了代码：

CREATE TABLE #dataset

(

ID int,
Name varchar(20),
City varchar(20),
ContinuousDates date

)


INSERT INTO #dataset
VALUES(1,'X','NYC','1/1/2021')

INSERT INTO #dataset
VALUES(1,'X','NYC','1/2/2021')

INSERT INTO #dataset
VALUES(1,'X','NYC','1/3/2021')

INSERT INTO #dataset
VALUES(1,'X','SFO','1/4/2021')

INSERT INTO #dataset
VALUES(1,'X','SFO','1/5/2021')

INSERT INTO #dataset
VALUES(1,'X','PHY','1/6/2021')

INSERT INTO #dataset
VALUES(1,'X','PHY','1/7/2021')

INSERT INTO #dataset
VALUES(1,'X','PHY','1/8/2021')

INSERT INTO #dataset
VALUES(1,'X','NYC','1/9/2021')

INSERT INTO #dataset
VALUES(1,'X','NYC','1/10/2021')

INSERT INTO #dataset
VALUES(1,'X','NYC','1/11/2021')

INSERT INTO #dataset
VALUES(1,'X','NYC','1/12/2021')




SELECT *
FROM #dataset
ORDER BY ContinuousDates

为了更好地演示，我有一组新代码：

CREATE TABLE #dataset

(

ID int,
Name varchar(20),
City varchar(20),
ContinuousDates date

)


INSERT INTO #dataset
VALUES(1,'X','NYC','1/1/2021')

INSERT INTO #dataset
VALUES(1,'X','NYC','1/2/2021')

INSERT INTO #dataset
VALUES(1,'X','NYC','1/3/2021')

INSERT INTO #dataset
VALUES(1,'X','SFO','1/4/2021')

INSERT INTO #dataset
VALUES(1,'X','SFO','1/5/2021')

INSERT INTO #dataset
VALUES(1,'X','PHY','1/6/2021')

INSERT INTO #dataset
VALUES(1,'X','PHY','1/7/2021')

INSERT INTO #dataset
VALUES(1,'X','PHY','1/8/2021')

INSERT INTO #dataset
VALUES(1,'X','NYC','1/9/2021')

INSERT INTO #dataset
VALUES(1,'X','NYC','1/10/2021')

INSERT INTO #dataset
VALUES(1,'X','NYC','1/11/2021')

INSERT INTO #dataset
VALUES(1,'X','NYC','1/12/2021')

INSERT INTO #dataset
VALUES(2,'Y','MEL','1/13/2021')

INSERT INTO #dataset
VALUES(3,'Z','SYD','1/14/2021')

INSERT INTO #dataset
VALUES(3,'Z','SYD','1/15/2021')

INSERT INTO #dataset
VALUES(3,'Z','PER','1/16/2021')

INSERT INTO #dataset
VALUES(4,'A',NULL,'1/16/2021')

INSERT INTO #dataset
VALUES(4,'A', NULL,'1/17/2021')



SELECT *
FROM #dataset
ORDER BY ID, ContinuousDates

推荐答案

解决方案步骤：

• 对ID和名称按日期(Row_Id)排序的节进行编号
• 按日期(P_Row_Id)对ID、名称和城市进行编号
• 计算row_id-p_row_id

现在，您有了唯一值集合中每个期间的组号。

您只需按此号码、ID、姓名和城市进行分组

ID	名称	城市	连续日期	p_row_id	row_id	row_id-p_row_id
1	X	纽约市	2021-01-01	1	1	0
1	X	纽约市	2021-01-02	2	2	0
1	X	纽约市	2021-01-03	3	3	0
1	X	SFO	2021-01-04	1	4	3
1	X	SFO	2021-01-05	2	5	3
1	X	物理层	2021-01-06	1	6	5
1	X	物理层	2021-01-07	2	7	5
1	X	物理层	2021-01-08	3	8	5
1	X	纽约市	2021-01-09	4	9	5
1	X	纽约市	2021-01-10	5	10	5
1	X	纽约市	2021-01-11	6	11	5
1	X	纽约市	2021-01-12	7	12	5

select
     CD.ID
    ,CD.[Name]
    ,CD.City
    ,min(CD.ContinuousDates) as DateStart
    ,max(CD.ContinuousDates) as DateEnd
from
    (
        select *
            ,row_number() over(partition by CD.ID, CD.[Name], CD.City order by CD.ContinuousDates) as p_row_id
            ,row_number() over(partition by CD.ID, CD.[Name] order by CD.ContinuousDates) as row_id
        from #dataset CD
    ) CD
group by CD.row_id - CD.p_row_id
        ,CD.ID
        ,CD.[Name]
        ,CD.City
order by DateStart

---

多列模板：

select
     CD.GroupColumn1
    ,CD.GroupColumn2
    ..
    ,CD.Column1
    ,CD.Column2
    ,CD.Column3
    ,CD.Column4
    ..
    ,min(CD.ContinuousDates) as DateStart
    ,max(CD.ContinuousDates) as DateEnd
from
    (
        select *
            ,row_number() over(partition by
                 CD.GroupColumn1
                ,CD.GroupColumn2
                ..
                ,CD.Column1
                ,CD.Column2
                ,CD.Column3
                ,CD.Column4
                ..
                order by CD.ContinuousDates) as p_row_id
            ,row_number() over(partition by
                 CD.GroupColumn1
                ,CD.GroupColumn2
                ..
                order by CD.ContinuousDates) as row_id
        from #dataset CD
    ) CD
group by CD.row_id - CD.p_row_id
        ,CD.GroupColumn1
        ,CD.GroupColumn2
        ..
         CD.Column1
        ,CD.Column2
        ,CD.Column3
        ,CD.Column4
        ..
order by DateStart

这篇关于Microsoft SQL Server 2016，T-SQL：基于单个日期获取数据集的日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！