需要在条件发生变化时拆分数据,而无需使用循环。

huangapple go评论65阅读模式
英文:

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

fiddle

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

fiddle

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

huangapple
  • 本文由 发表于 2023年6月6日 16:02:39
  • 转载请务必保留本文链接:https://go.coder-hub.com/76412556.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定