用间隔但具有重叠的时间范围组分组数据

Question

目前，我已经构造了一个按查询分组，其中返回一个间隔15分钟的时间列表及其计数聚合列表。

SELECT 
    CONVERT(DATE, DATETIME_COLUMN) AS DATE,
    CONVERT(TIME(0), DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', DATETIME_COLUMN) / 15)*15, '20000101')) AS TIME_START_INTERVAL,
    DATEADD(SECOND, -1, CONVERT(TIME(0), DATEADD(MINUTE, 60, DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', DATETIME_COLUMN) / 15)*15, '20000101')))) AS TIME_END_INTERVAL
FROM 
    TABLE
GROUP BY
    CONVERT(DATE, DATETIME_COLUMN),
    DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', DATETIME_COLUMN) / 15)*15, '20000101')

但是，这只会将它们分组15分钟。有任何方式，我可以让他们分组，如图中所示？预计数据重叠是因为时间范围重叠。

Answer 1

下面是一种可以用来计算重叠范围的技术。这种方法使用了一个包容性的开始和独占时间范围，以确保带有小数秒的日期时间值不会掉在地板上(例如，'2018-01-28 00:59:59.123')，但是仍然报告结束时间没有小数秒，就像问题中的数据一样。

WITH
    intervals_15_minutes AS (
        SELECT DISTINCT
             DATEADD(MINUTE, (DATEDIFF(MINUTE, '', DATETIME_COLUMN) / 15)*15, '') AS TIME_START_INTERVAL
        FROM dbo.YourTable
    )
SELECT
      CAST(TIME_START_INTERVAL AS date) AS DATE
    , CAST(TIME_START_INTERVAL AS time(0)) AS TIME_START_INTERVAL
    , CAST(DATEADD(second, -1, DATEADD(MINUTE, 60, TIME_START_INTERVAL)) AS time(0)) AS TIME_END_INTERVAL
    , COUNT(*) AS OVERLAPPING_COUNT
FROM  intervals_15_minutes
JOIN  dbo.YourTable ON
    YourTable.DATETIME_COLUMN >= intervals_15_minutes.TIME_START_INTERVAL
    AND YourTable.DATETIME_COLUMN < DATEADD(MINUTE, 60, TIME_START_INTERVAL)
GROUP BY
      intervals_15_minutes.TIME_START_INTERVAL;

Answer 2

声明@temp INT;

设置@temp:=0；选择date，TIME_START_INTERVAL，TIME_END_INTERVAL，@temp AS TEMP_TIME_INTERVAL，COUNT(*) FROM (选择date，TIME_START_INTERVAL，TIME_END_INTERVAL，@temp:=TIME_START_INTERVAL FROM TABLE，(选择@temp:=0))N MINUTE(TIMEDIFF(TIME_START_INTERVAL，TEMP_TIME_INTERVAL))=15组按日期分组，TIME_START_INTERVAL，TIME_END_INTERVAL