Microsoft SQL Server 2016，T-SQL：基于单个日期获取数据集的日期范围
我在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
相关文章