gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-12-02 02:52:35 29 4
gpt4 key购买 nike

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

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 分钟。

最佳答案

试试这个:

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;

产品:

enter image description here

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

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

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

enter image description here

<小时/>

这是使用 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

关于sql - 当日期不完全连续时,按连续日期对记录进行分组,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/41721245/

29 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com