如何在SQL中计算时间
如何在SQL中计算时间
提问于 2017-10-06 03:45:21
我有张像这样的桌子
ID DTTM
123456789 2017-10-05 08:00:00.000
123456789 2017-10-05 08:05:00.000
123456789 2017-10-05 08:07:00.000
123456789 2017-10-05 08:15:00.000
123456789 2017-10-05 08:25:00.000
123456789 2017-10-05 09:00:00.000
123456789 2017-10-05 09:01:00.000
123456789 2017-10-05 09:02:00.000
123456789 2017-10-05 09:03:00.000
123456789 2017-10-05 11:00:00.000我需要根据时间间隔(日期无关紧要)创建一个标志，它必须是15分钟间隔，才能将标志设置为1，因此在本例中，行1,4,6,10将是标志，如果每次interval >= 15 minutes再次启动，则该ID的总数将为4，直到下一次，我尝试了这样的方法；使用myLead作为
(
Select top 100 percent
ID,
DTTM,
LEAD(DTTM,1) over (partition by ID order by DTTM) as NextDTTM
From Example
Order by ID
), myCount
AS
(
Select Top 100 percent
ID,
DTTM,
NextDTTM,
DateDiff("MINUTE",DTTM,NextDTTM) as Interval
from myLead
)
Select ID,
DTTM,
NextDTTM,
Interval,
Case When Interval >= 15 then 1 else 0 END as CountFlag
From myCount
Where Interval is not NULL
Order by ID但它不像预期的那样起作用？我该怎么解决这个问题。
谢谢你，奥德·多罗
回答 2
Stack Overflow用户
回答已采纳
发布于 2017-10-08 06:53:09
我不知道您如何需要该标志的外观，但使用SQL Server 2012，它们是两个棘手的部分。首先，要获得下一行和前一行之间的时间差：
DECLARE @DataSource TABLE
(
[ID] BIGINT
,[DTTM] DATETIME2
);
INSERT INTO @DataSource ([ID], [DTTM])
VALUES (123456789, '2017-10-05 08:00:00.000')
,(123456789, '2017-10-05 08:05:00.000')
,(123456789, '2017-10-05 08:07:00.000')
,(123456789, '2017-10-05 08:15:00.000')
,(123456789, '2017-10-05 08:25:00.000')
,(123456789, '2017-10-05 09:00:00.000')
,(123456789, '2017-10-05 09:01:00.000')
,(123456789, '2017-10-05 09:02:00.000')
,(123456789, '2017-10-05 09:03:00.000')
,(123456789, '2017-10-05 11:00:00.000');
SELECT *
,DATEDIFF(MINUTE,CONVERT(TIME, [DTTM]),LEAD(CONVERT(TIME, [DTTM]), 1, NULL) OVER (PARTITION BY [ID] ORDER BY [DTTM])) AS [Minutes]
FROM @DataSource
ORDER BY [DTTM];
然后，我们需要将从over乞求到当前行的分钟数进行sum，并将它们除以15。
WITH DataSource AS
(
SELECT *
,DATEDIFF(MINUTE,LAG(CONVERT(TIME, [DTTM]), 1, NULL) OVER (PARTITION BY [ID] ORDER BY [DTTM]), CONVERT(TIME, [DTTM])) AS [Minutes]
FROM @DataSource
)
SELECT *
,SUM(ISNULL([Minutes], 0)) OVER (PARTITION BY [ID] ORDER BY [DTTM] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / 15 AS [MinutesRank]
FROM DataSource;
有了这个，你可以做你需要做的，因为我们现在有多少分钟，直到每一行。例如：
WITH DataSource AS
(
SELECT *
,DATEDIFF(MINUTE,LAG(CONVERT(TIME, [DTTM]), 1, NULL) OVER (PARTITION BY [ID] ORDER BY [DTTM]), CONVERT(TIME, [DTTM])) AS [Minutes]
FROM @DataSource
)
, DataSourceMinutesTotal AS
(
SELECT *
,SUM(ISNULL([Minutes], 0)) OVER (PARTITION BY [ID] ORDER BY [DTTM] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / 15 AS [MinutesRank]
FROM DataSource
)
SELECT [ID], [DTTM]
,DENSE_RANK() OVER (PARTITION BY [ID] ORDER BY ISNULL([MinutesRank], 0)) AS [IntervalID]
FROM DataSourceMinutesTotal
ORDER BY [DTTM];
Stack Overflow用户
发布于 2017-10-06 05:05:47
如果使用MySQL，则可以尝试使用TIMEDIFF(param1, param2) * param1和param2可以是时间或日期时间值。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/46597950
复制相关文章
相似问题
腾讯云开发者
