将时间段分成几部分

Question

我将时间定义为从现在开始的20个小时，并将其分成更小的时间段。我需要为20小时做一些计算，所以我的@End_Date = GetDate()和@Start_Date = DATEADD(Hour， - 20, GetDate())

| Start_Date          | End_date           |Group       |
| 2023-01-01 13:00:00 | 2023-01-01 20:00:00| 1          |  --desired output
| 2023-01-01 12:00:00 | 2023-01-01 13:00:00| 1          |
| 2023-01-01 10:00:00 | 2023-01-01 12:00:00| 1          |
| 2023-01-01 09:00:00 | 2023-01-01 10:00:00| 1          |
| 2023-01-01 06:00:00 | 2023-01-01 09:00:00| 1          |
| 2023-01-01 02:00:00 | 2023-01-01 06:00:00| 1          | --last input from query
| 2023-01-01 00:00:00 | 2023-01-01 02:00:00| 1          | --desired output

对于正在进行的最后一个片段，End_Date将为NULL，因此在这种情况下我可以添加case语句CASE WHEN End_Date IS NULL THEN @End_Date ELSE End_Date END)，但对于第一个部分(标记为"最后输入查询")，我需要添加一些计算，将插入额外的行，其中START_DATE = @Start_Date和End_Date = START_DATE。我认为它必须是联合的但我不知道如何定义条件。此外，这些周期是为少数组计算的

下面可以看到一些代码:

CREATE TABLE tabName
(
Start_Date datetime,
End_Date datetime,
[Group] int
)
INSERT INTO table_name ('Start_Date', 'End_Date', 'Group') VALUES ('2023-01-01 13:00:00', '2023-01-01 20:00:00', '1') 
INSERT INTO table_name ('Start_Date', 'End_Date', 'Group') VALUES ('2023-01-01 12:00:00', '2023-01-01 13:00:00', '1') 
INSERT INTO table_name ('Start_Date', 'End_Date', 'Group') VALUES ('2023-01-01 10:00:00', '2023-01-01 12:00:00', '1') 
INSERT INTO table_name ('Start_Date', 'End_Date', 'Group') VALUES ('2023-01-01 09:00:00', '2023-01-01 10:00:00', '1') 
INSERT INTO table_name ('Start_Date', 'End_Date', 'Group') VALUES ('2023-01-01 06:00:00', '2023-01-01 09:00:00', '1') 
INSERT INTO table_name ('Start_Date', 'End_Date', 'Group') VALUES ('2023-01-01 02:00:00', '2023-01-01 06:00:00', '1') 
INSERT INTO table_name ('Start_Date', 'End_Date', 'Group') VALUES ('2023-01-01 18:00:00', '2023-01-01 20:00:00', '2') 
INSERT INTO table_name ('Start_Date', 'End_Date', 'Group') VALUES ('2023-01-01 16:00:00', '2023-01-01 18:00:00', '2') 
INSERT INTO table_name ('Start_Date', 'End_Date', 'Group') VALUES ('2023-01-01 14:00:00', '2023-01-01 16:00:00', '2') 
INSERT INTO table_name ('Start_Date', 'End_Date', 'Group') VALUES ('2023-01-01 05:00:00', '2023-01-01 14:00:00', '2') 
INSERT INTO table_name ('Start_Date', 'End_Date', 'Group') VALUES ('2023-01-01 04:00:00', '2023-01-01 05:00:00', '2') 
INSERT INTO table_name ('Start_Date', 'End_Date', 'Group') VALUES ('2023-01-01 01:00:00', '2023-01-01 04:00:00', '2') 

我正在使用Microsoft SQL Server

Answer 1

您可以使用以下代码

declare @start_date datetime =cast('2023-01-01 00:00:00' as datetime);
declare @end_date datetime =cast('2023-01-01 20:00:00' as datetime);
SELECT
v.*,
t.[Group]
FROM (
SELECT *,
rn = ROW_NUMBER() OVER (PARTITION BY [Group] ORDER BY Start_Date)
FROM tabName
WHERE Start_Date < @end_date
AND (End_Date > @start_date OR End_Date IS NULL)
) t
CROSS APPLY (
SELECT
@start_date,
t.Start_Date
WHERE t.rn = 1
AND t.Start_Date > @start_date
UNION ALL
SELECT
CASE WHEN t.Start_Date < @start_date THEN @start_date ELSE t.Start_Date END,
ISNULL(t.End_Date, @end_date)
) v(Start_Date, End_Date);

,db&lt的在小提琴

逻辑如下:

• 首先筛选到我们想要的行，根据我们的间隔进行比较。注意使用间隔比较，这也允许我们得到只有部分重叠的行。
• 根据起始日期计算行号
• 在APPLY中，输出我们已经拥有的数据(调整部分重叠行)…
• . .并且有条件地增加一行，我们位于集合的第一行，它与@start_date无关，输出当前的Start_Date作为End_Date。

Answer 2

我举个例子。我们将通过与辅助表的连接添加必要的行。当n=0时，我们保留原来的行。对于n=1，我们在特定条件下添加额外的行。这些条件应在连接条件on (n=1 and <other conditions> ...)中描述。这个示例更多地演示了如何添加行，而不是计算所需的值。

declare @start_date datetime =cast('2023-01-01 00:00:00' as datetime);
declare @end_date datetime =cast('2023-01-01 20:00:00' as datetime);
CREATE TABLE tabName (Start_Date datetime,End_Date datetime,[Group] int)
INSERT INTO tabname (Start_Date, End_Date, [Group]) VALUES 
('2023-01-01 13:00:00', '2023-01-01 20:00:00', '1') 
,('2023-01-01 12:00:00', '2023-01-01 13:00:00', '1') 
,('2023-01-01 10:00:00', '2023-01-01 12:00:00', '1') 
,('2023-01-01 09:00:00', '2023-01-01 10:00:00', '1') 
,('2023-01-01 06:00:00', '2023-01-01 09:00:00', '1') 
,('2023-01-01 02:00:00', '2023-01-01 06:00:00', '1') 
,('2023-01-01 18:00:00', '2023-01-01 20:00:00', '2') 
,('2023-01-01 16:00:00', '2023-01-01 18:00:00', '2') 
,('2023-01-01 14:00:00', '2023-01-01 16:00:00', '2') 
,('2023-01-01 05:00:00', '2023-01-01 14:00:00', '2') 
,('2023-01-01 04:00:00', '2023-01-01 05:00:00', '2') 
,('2023-01-01 01:00:00', '2023-01-01 04:00:00', '2')
;
with rankData as(
select *
,row_number()over(partition by [group] order by start_date) rn
,count(*)over(partition by [group] ) maxrn
,lead(start_date)over(partition by [group] order by start_date) nextStart
from tabName t
)
select 
case when n=0 then start_date   
else @start_date    --value for added row
end start_date
,case when n=0 then 
CASE WHEN End_Date IS NULL THEN @End_Date ELSE End_Date END
else nextStart  --value for added row
end end_date
,[group] 
,n,rn,maxrn
from rankData t
left join (values(0),(1))nn(n)
on n=0
or (n=1 and -- there conditions to add 1 row
(  (rn=1 and start_date<>@start_date)  -- for first row of group
or(rn=maxrn and end_date<>@end_date)  -- for last row of group
)
)
order by [group],start_date