sql - 当日期不完全连续时，按连续日期对记录进行分组

Question

我有一些包含日期的数据。我试图按连续日期对数据进行分组，但是，日期并不完全连续。这是一个例子:

DateColumn              | Value
------------------------+-------
2017-01-18 01:12:34.107 | 215426 <- batch no. 1
2017-01-18 01:12:34.113 | 215636
2017-01-18 01:12:34.623 | 123516
2017-01-18 01:12:34.633 | 289926
2017-01-18 04:58:42.660 | 259063 <- batch no. 2
2017-01-18 04:58:42.663 | 261830
2017-01-18 04:58:42.893 | 219835
2017-01-18 04:58:42.907 | 250165
2017-01-18 05:18:14.660 | 134253 <- batch no. 3
2017-01-18 05:18:14.663 | 134257
2017-01-18 05:18:14.667 | 134372
2017-01-18 05:18:15.040 | 181679
2017-01-18 05:18:15.043 | 226368
2017-01-18 05:18:15.043 | 227070

数据是批量生成的，批量内的每一行都需要几毫秒的时间来生成。我尝试将结果分组如下:

Date1                   | Date2                   | Count
------------------------+-------------------------+------
2017-01-18 01:12:34.107 | 2017-01-18 01:12:34.633 | 4
2017-01-18 04:58:42.660 | 2017-01-18 04:58:42.907 | 4
2017-01-18 05:18:14.660 | 2017-01-18 05:18:15.043 | 6

可以安全地假设，如果两个连续行相距超过 1 分钟，则它们属于不同的批处理。

我尝试了涉及ROW_NUMBER函数的解决方案，但它们适用于连续日期(两行之间的日期差异是固定的)。当差异模糊时如何才能达到预期的结果？

<小时/>

请注意，批处理可能会比一分钟长得多。例如，一个批处理可能由从 2017-01-01 00:00:00 开始到 2017-01-01 00:05:00 结束的行组成，由约 3000 行组成，每行间隔几十或几百毫秒。可以肯定的是，批处理之间至少相隔 1 分钟。

Answer 1

试试这个:

select min(t.dateColumn) date1, max(t.dateColumn) date2, count(*)
from (
    select t.*, sum(val) over (
            order by t.dateColumn
            ) grp
    from (
        select t.*, case 
                when datediff(ms, lag(t.dateColumn, 1, t.dateColumn) over (
                            order by t.dateColumn
                            ), t.dateColumn) > 60000
                    then 1
                else 0
                end val
        from your_table t
        ) t
    ) t
group by grp;

产品:

使用分析函数lag()根据datecolumn与上一批的差异来标记下一批的开始，然后使用分析sum() 在其上创建批处理组，然后按其分组以查找所需的聚合。

由于 DATETIME 的舍入问题，组中可能会出现一些错误分类。来自 MSDN ,

datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.

<小时/>

这是使用 CTE 重写的相同查询:

WITH cte1(DateColumn, ValueColumn) AS (
    -- Insert your query that returns a datetime column and any other column
    SELECT
        SomeDate,
        SomeValue
    FROM SomeTable
    WHERE SomeColumn IS NOT NULL
), cte2 AS (
    -- This query adds a column called "val" that contains
    -- 1 when current row date - previous row date > 1 minute
    -- 0 otherwise
    SELECT
        cte1.*,
        CASE WHEN DATEDIFF(MS, LAG(DateColumn, 1, DateColumn) OVER (ORDER BY DateColumn), DateColumn) > 60000 THEN 1 ELSE 0 END AS val
    FROM cte1
), cte3 AS (
    -- This query adds a column called "grp" that numbers 
    -- the groups using running sum over the "val" column
    SELECT
        cte2.*,
        SUM(val) OVER (ORDER BY DateColumn) AS grp
    FROM cte2
)
SELECT
    MIN(DateColumn) Date1,
    MAX(DateColumn) Date2,
    COUNT(ValueColumn) [Count]
FROM cte3
GROUP BY grp