如何使用递归查询转换时间间隔(以时间点为单位)

Question

我正在尝试使用SQL SERVER 2008创建一个函数，该函数为给定间隔内计数的每个时间检查点返回一行。
换句话说，给定一个表，告诉我某个对象在一定的时间间隔内处于活动状态

[ID]   [TIME FROM]      [TIME TO]
23     12:34:00         13:14:00

我试图获得一个结果集，如下所示的表：

[ID]   [TimeCheck]      [flag]
23     12:00:00        0  
23     12:15:00        0  
23     12:30:00        1  
23     12:45:00        1  
23     13:00:00        1  
23     13:15:00        0     

我

可以用来链接到表格，为我提供每个ID的间隔(见下面的最终目标(
原因是我想计算一些统计数据来创建图表，我想计算在给定的一组检查点时间检查的 ID 数量。
我可以使用光标，但这听起来很粗糙，我希望有人能提出更好的建议。
我在考虑 CTE 或递归函数，因为这听起来是一个理想的递归问题，但我无法解决

感谢您的建议！----编辑---当然，我可以使用这样的函数：

CREATE FUNCTION test_GetTimePoints (@ID INT, @timeFrom TIME, @timeTo TIME ,@intervalMinutes INT)
RETURNS @result TABLE ( [ID] INT , LasTimeCheck TIME , flag BIT)
AS
BEGIN
DECLARE @curTick INT = DATEDIFF(MINUTE,'00:00:00',@timeFrom)/@intervalMinutes+1
DECLARE @endTick INT = DATEDIFF(MINUTE,'00:00:00',@timeTo)/@intervalMinutes
WHILE ( @curTick < = @endTick )
BEGIN
  INSERT INTO @result
  SELECT @ID, DATEADD(MINUTE,@curTick*@intervalMinutes,0),1
  SET @curTick = @curTick + 1
END
RETURN
END

最终目标是做这样的事情：

select ID,TS_In,TS_end, A.* FROM VEH M LEFT OUTER JOIN (SELECT * FROM 
dbo.test_GetTimePoints(ID,M.ts_In,M.ts_end,15) )A ON A.ID=M.ID

这显然不起作用，因为我无法ID,M.ts_In,M.ts_end,15作为函数的参数。
有什么想法可以达到相同的结果吗？

Answer 1

CREATE FUNCTION test_GetTimePoints
(
    @ID INT, 
    @timeFrom TIME, 
    @timeTo TIME,
    @intervalMinutes INT
)
RETURNS TABLE AS RETURN
(
    WITH C (LasTimeCheck) AS
    (
      SELECT DATEADD(MINUTE, @intervalMinutes, @timeFrom)
      UNION ALL
      SELECT DATEADD(MINUTE, @intervalMinutes, LasTimeCheck)
      FROM C
      WHERE LasTimeCheck < @timeTo
    )
    SELECT @ID AS ID,
           LasTimeCheck,
           1 AS flag
    FROM C         
)

Answer 2

如果你已经有一个函数，最简单的方法是使用外部应用：

SELECT ID,TS_In,TS_end, A.* 
  FROM VEH M 
 OUTER APPLY
 (
   SELECT * 
     FROM dbo.test_GetTimePoints(ID, M.ts_In, M.ts_end, 15) A
 ) A

但是该函数看起来很可疑，因为它没有考虑对象活动间隔之外的时间。您可以将其更改为仅生成某个间隔(一天？(的时间列表，使用 VEH 进行交叉连接并标记 15 分钟周期开始和结束之间的时间：

SELECT ID, TS_In, TS_end, 
       CASE WHEN M.TS_In <= dateadd (minute, 15, A.LastTimeCheck )
             AND M.TS_end >= A.LastTimeCheck
            THEN 1
            ELSE 0
        END Flag
  FROM VEH M 
 CROSS JOIN dbo.test_GetTimePoints('07:00:00', '23:00:00', 15) A

为了完全消除函数，可以使用递归 CTE 来生成时间表：

; WITH test_GetTimePoints (LastTimeCheck) AS
(
    SELECT CAST('07:00:00' as time)
    UNION ALL
    SELECT dateadd(minute, 15, LastTimeCheck)
      FROM test_GetTimePoints
       WHERE LastTimeCheck < CAST ('23:00:00' as time)
)
SELECT ID, TS_In, TS_end, 
       CASE WHEN M.TS_In <= dateadd (minute, 15, A.LastTimeCheck )
             AND M.TS_end >= A.LastTimeCheck
            THEN 1
            ELSE 0
        END Flag
  FROM VEH M 
 CROSS JOIN test_GetTimePoints A

更新：

此查询与其他查询一样 - 您可以从应用程序发送参数，即@interval int会做得很好。您仍然可以使用 CTE 变体，但您应该过滤掉不需要的时间：

; WITH test_GetTimePoints (LastTimeCheck) AS
(
    SELECT CAST('00:00:00' as time)
    UNION ALL
    SELECT dateadd(minute, @interval, LastTimeCheck)
      FROM test_GetTimePoints
       WHERE LastTimeCheck < dateadd (minute, - @interval, 
                                      CAST ('00:00:00' as time))
)
SELECT ID, TS_In, TS_end
  FROM VEH M 
 CROSS JOIN test_GetTimePoints A
 WHERE M.TS_In <= dateadd (minute, @interval, A.LastTimeCheck )
   AND M.TS_end >= A.LastTimeCheck
-- Default = 100, but we need to produce up to 1440 records
-- So we turn the limit off with zero.
OPTION (maxrecursion 0)

更好的解决方案是有一个日历表(在本例中为一天中的所有分钟(并使用交叉应用：

SELECT ID,TS_In,TS_end, A.LastTimeCheck 
  FROM VEH M 
 OUTER APPLY
 (
   SELECT LastTimeCheck 
     FROM TimeTable A
    WHERE (datediff (minute, 0, LastTimeCheck) % @interval = 0)
      AND M.TS_In <= dateadd (minute, @interval, A.LastTimeCheck )
      AND M.TS_end >= A.LastTimeCheck
 ) A

可以使用上面的 CTE 填充时间表，@interval设置为 1。