Microsoft SQL Server 2016,T-SQL:基于单个日期获取数据集的日期范围

本教程将介绍Microsoft SQL Server 2016,T-SQL:基于单个日期获取数据集的日期范围的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

Microsoft SQL Server 2016,T-SQL:基于单个日期获取数据集的日期范围 教程 第1张

问题描述

我在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:基于单个日期获取数据集的日期范围的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。