英文:
Need split the data when condition is getting changed without loop
问题
以下是您的表格:
如果存在(select top 1 1 from sys.tables where name='ObjInfo')
drop table ObjInfo
create table ObjInfo(id int identity
,ObjNumber int
,ObjDate datetime
,ObjConditionId int)
insert into ObjInfo(ObjNumber,ObjDate,ObjConditionId)
values(1,'2014-01-03',1)
,(1,'2014-01-05',1)
,(1,'2014-01-06',1)
,(1,'2014-01-08',2)
,(1,'2014-01-13',1)
,(1,'2014-01-15',1)
,(1,'2014-01-25',4)
,(2,'2014-01-01',1)
,(2,'2014-01-05',1)
,(2,'2014-01-07',2)
,(2,'2014-01-08',2)
,(2,'2014-01-12',2)
,(2,'2014-01-14',3)
,(2,'2014-01-15',4)
我的任务是按ObjectNumbers方式显示条件何时更改以及时间段是什么。
我的期望输出如下:
ObjNumber ObjConditionId ConditionBeg ConditionEnd
1 1 2014-01-03 2014-01-08
1 2 2014-01-08 2014-01-13
1 1 2014-01-13 2014-01-25
1 4 2014-01-25 getdate()
2 1 2014-01-01 2014-01-07
2 2 2014-01-07 2014-01-14
2 3 2014-01-14 2014-01-15
2 4 2014-01-15 getdate()
我正在尝试以下代码,但不清楚如何实现这一目标:
WITH ConditionCTE AS (
SELECT ObjNumber, ObjConditionId, ObjDate AS ConditionBeg,
LEAD(ObjDate, 1, GETDATE()) OVER (PARTITION BY ObjNumber ORDER BY ObjDate) AS ConditionEnd
FROM ObjInfo
)
SELECT ObjNumber, ObjConditionId, ConditionBeg,
CASE WHEN CAST(ConditionEnd as date)= GETDATE() THEN 'getdate()' ELSE ConditionEnd END AS ConditionEnd
FROM ConditionCTE
ORDER BY ObjNumber, ConditionBeg;
英文:
Below is my table
if exists(select top 1 1 from sys.tables where name='ObjInfo')
drop table ObjInfo
create table ObjInfo(id int identity
,ObjNumber int
,ObjDate datetime
,ObjConditionId int)
insert into ObjInfo(ObjNumber,ObjDate,ObjConditionId)
values(1,'2014-01-03',1)
,(1,'2014-01-05',1)
,(1,'2014-01-06',1)
,(1,'2014-01-08',2)
,(1,'2014-01-13',1)
,(1,'2014-01-15',1)
,(1,'2014-01-25',4)
,(2,'2014-01-01',1)
,(2,'2014-01-05',1)
,(2,'2014-01-07',2)
,(2,'2014-01-08',2)
,(2,'2014-01-12',2)
,(2,'2014-01-14',3)
,(2,'2014-01-15',4)
My Job is to display ObjectNumbers wise when condition was changed and for which time period.
My expected output is as below
ObjNumber ObjConditionId ConditionBeg ConditionEnd
1 1 2014-01-03 2014-01-08
1 2 2014-01-08 2014-01-13
1 1 2014-01-13 2014-01-25
1 4 2014-01-25 getdate()
2 1 2014-01-01 2014-01-07
2 2 2014-01-07 2014-01-14
2 3 2014-01-14 2014-01-15
2 4 2014-01-15 getdate()
I am trying below code but not getting how can I achieve this.
WITH ConditionCTE AS (
SELECT ObjNumber, ObjConditionId, ObjDate AS ConditionBeg,
LEAD(ObjDate, 1, GETDATE()) OVER (PARTITION BY ObjNumber ORDER BY ObjDate) AS ConditionEnd
FROM ObjInfo
)
SELECT ObjNumber, ObjConditionId, ConditionBeg,
CASE WHEN CAST(ConditionEnd as date)= GETDATE() THEN 'getdate()' ELSE ConditionEnd END AS ConditionEnd
FROM ConditionCTE
ORDER BY ObjNumber, ConditionBeg;
答案1
得分: 4
这是一个经典的间隙和岛屿问题;您可以使用行号之间的差异来识别组。
这为每个岛屿提供了其开始和结束日期:
选择 ObjNumber, ObjConditionId,
min(ObjDate) MinObjDate,
max(ObjDate) MaxObjDate
从 (
选择 o.*,
行数() over(partition by ObjNumber order by ObjDate) rn1,
行数() over(partition by ObjNumber, ObjConditionId order by ObjDate) rn2
从 ObjInfo o
) o
组合 ObjNumber, ObjConditionId, rn1 - rn2
顺序按 ObjNumber, MinObjDate
如果您希望将下一个岛屿的开始日期作为 MaxObjDate
,就像您期望的结果中显示的那样,我们可以在此基础上使用 lead()
(三参数形式允许我们提供默认值):
选择 ObjNumber, ObjConditionId,
min(ObjDate) MinObjDate,
lead(min(ObjDate), 1, getdate()) over(partition by ObjNumber order by min(ObjDate)) MaxObjDate
从 (
选择 o.*,
行数() over(partition by ObjNumber order by ObjDate) rn1,
行数() over(partition by ObjNumber, ObjConditionId order by ObjDate) rn2
从 ObjInfo o
) o
组合 ObjNumber, ObjConditionId, rn1 - rn2
顺序按 ObjNumber, MinObjDate
ObjNumber | ObjConditionId | MinObjDate | MaxObjDate |
---|---|---|---|
1 | 1 | 2014-01-03 00:00:00.000 | 2014-01-08 00:00:00.000 |
1 | 2 | 2014-01-08 00:00:00.000 | 2014-01-13 00:00:00.000 |
1 | 1 | 2014-01-13 00:00:00.000 | 2014-01-25 00:00:00.000 |
1 | 4 | 2014-01-25 00:00:00.000 | 2023-06-06 08:46:17.283 |
2 | 1 | 2014-01-01 00:00:00.000 | 2014-01-07 00:00:00.000 |
2 | 2 | 2014-01-07 00:00:00.000 | 2014-01-14 00:00:00.000 |
2 | 3 | 2014-01-14 00:00:00.000 | 2014-01-15 00:00:00.000 |
2 | 4 | 2014-01-15 00:00:00.000 | 2023-06-06 08:46:17.283 |
英文:
This is a classic gaps-and-island problem; you can use the difference between row numbers to identify groups.
This gives you for each island, with its start and end dates:
select ObjNumber, ObjConditionId,
min(ObjDate) MinObjDate,
max(ObjDate) MaxObjDate
from (
select o.*,
row_number() over(partition by ObjNumber order by ObjDate) rn1,
row_number() over(partition by ObjNumber, ObjConditionId order by ObjDate) rn2
from ObjInfo o
) o
group by ObjNumber, ObjConditionId, rn1 - rn2
order by ObjNumber, MinObjDate
If you want to the start of the next island as MaxObjDate
instead, as shown in your expected results, we can use lead()
on top of this (the three-arguments form allows us to provide a default value):
select ObjNumber, ObjConditionId,
min(ObjDate) MinObjDate,
lead(min(ObjDate), 1, getdate()) over(partition by ObjNumber order by min(ObjDate)) MaxObjDate
from (
select o.*,
row_number() over(partition by ObjNumber order by ObjDate) rn1,
row_number() over(partition by ObjNumber, ObjConditionId order by ObjDate) rn2
from ObjInfo o
) o
group by ObjNumber, ObjConditionId, rn1 - rn2
order by ObjNumber, MinObjDate
ObjNumber | ObjConditionId | MinObjDate | MaxObjDate |
---|---|---|---|
1 | 1 | 2014-01-03 00:00:00.000 | 2014-01-08 00:00:00.000 |
1 | 2 | 2014-01-08 00:00:00.000 | 2014-01-13 00:00:00.000 |
1 | 1 | 2014-01-13 00:00:00.000 | 2014-01-25 00:00:00.000 |
1 | 4 | 2014-01-25 00:00:00.000 | 2023-06-06 08:46:17.283 |
2 | 1 | 2014-01-01 00:00:00.000 | 2014-01-07 00:00:00.000 |
2 | 2 | 2014-01-07 00:00:00.000 | 2014-01-14 00:00:00.000 |
2 | 3 | 2014-01-14 00:00:00.000 | 2014-01-15 00:00:00.000 |
2 | 4 | 2014-01-15 00:00:00.000 | 2023-06-06 08:46:17.283 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论