时间戳

首页 > 解决方案 > db 中过去 24 小时的 10 分钟聚合

sql-server - db 中过去 24 小时的 10 分钟聚合

问题描述

以下查询将给定的时间范围数据拆分为 5 分钟，但它从提供的时间范围开始执行此操作

With MNE
AS
(
SELECT *,DATEDIFF(dd,0,t.datetime) AS dayoffset,
DATEDIFF(ss,MIN(t.datetime) OVER (PARTITION BY DATEDIFF(dd,0,t.datetime)),t.datetime)/60 AS MinOffset
FROM cw.datas t
 WHERE t.DATETIME <= GETDATE()
 AND t.DATETIME > DATEADD(SECOND, -DATEDIFF(SECOND, CAST(CAST(GETDATE() AS DATE) AS DATETIME),
 GETDATE()) % (60 * 5), DATEADD(DAY, - 1,GETDATE()))
)

SELECT  MIN(z.datetime) AS StartDatetime,
 MAX(z.datetime) AS ENdDatetime,
 sum(z.value) AS TotalSum
FROM MNE z
GROUP BY dayoffset,(MinOffset-1)/10
order by StartDatetime

当前时间为2020-09-14 12:28:43.793，输出方式如下：

StartDatetime,ENdDatetime,TotalSum
2020-09-13 12:25:03.000,    2020-09-13 12:31:00.000,    63763.51
2020-09-13 12:31:03.000,    2020-09-13 12:36:00.000,    48348.34
2020-09-13 12:36:03.000,    2020-09-13 12:41:00.000,    54387.69
--
--
2020-09-14 12:16:00.000,    2020-09-14 12:20:57.000,    54353.72
2020-09-14 12:21:00.000,    2020-09-14 12:25:57.000,    53780.48
2020-09-14 12:26:00.000,    2020-09-14 12:28:42.000,    25328.56

我想要从当前时间戳聚合拆分，即2020-09-14 12:28:43.793回到过去 24 小时。

Expected result :

StartDatetime,ENdDatetime,TotalSum
--
--
2020-09-14 11:58:43.000,    2020-09-14 12:08:43.000,    354654
2020-09-14 12:08:43.000,    2020-09-14 12:18:43.000,    354353
2020-09-14 12:18:43.000,    2020-09-14 12:28:43.000,    354665

帮助表示赞赏。

样本数据 ：

datas

datetime,   abc,    def,    ghi,    value
2020-09-11 12:22:36.000,    AYSH,   mains,  SAE,    363.12
2020-09-11 12:22:39.000,    AYSH,   mains,  SAE,    358.2
2020-09-11 12:22:42.000,    AYSH,   mains,  SAE,    353.66
2020-09-11 12:22:45.000,    AYSH,   mains,  SAE,    349.14
2020-09-11 12:22:48.000,    AYSH,   mains,  SAE,    344.84
2020-09-11 12:22:51.000,    AYSH,   mains,  SAE,    340.63
2020-09-11 12:22:54.000,    AYSH,   mains,  SAE,    336.45

标签： sql-serverdatetime

解决方案

如果数据密集，那么实际上没有必要使用递归查询。只需计算自 以来经过的秒数startTime。并且这种形式可以让您轻松调整区间的范围开始和大小。

with timeRelatedFields as (
    select
        dateadd(hour, -24,
            dateadd(millisecond, -datepart(millisecond, getdate()), 
                getdate())) as startTime,
        600 as intervalSeconds
), tableTimeRelatedFields as (   
    select *,
        d.value as consideredValue,
        datediff(second, st.startTime, d."datetime") / intervalSeconds as interval
    from cw.datas as d cross apply timeRelatedFields as st
    where d."datetime" >= startTime and d."datetime" < getdate()
)
select
    dateadd(second, intervalSeconds *  interval,      startTime) as StartDateTime,
    dateadd(second, intervalSeconds * (interval + 1), startTime) as EndDateTime,
    sum(consideredValue) as TotalSum      
from tableTimeRelatedFields
group by interval;

推荐阅读