时间戳

首页 > 解决方案 > SQL Server 聚合窗口的区别

sql - SQL Server 聚合窗口的区别

问题描述

我想通过从开始时间创建 15 分钟的窗口来从 SQL Server 表中获取一些数据，并在这些窗口中找到某个列（值）的 MAX 和 MIN 之间的差异。

SQL 表存储每分钟捕获的一些遥测数据。

是否有任何 T-SQL 功能可用于在不运行循环（窗口聚合排序等）的情况下获得此类输出？

我可以使用循环来实现这一点，但这非常慢，因为它会从每个窗口中获取数据并将其添加到临时表中。下面是一段简化的代码片段：

WHILE(@startTime <@endTimeInput)
BEGIN
    INSERT INTO #energyDataTable
    SELECT  MIN(timeStamp) as startTime, MAX(timeStamp) as endTime, ROUND(MAX (value) - MIN (value),2) as value
    FROM PLANT1.DATA_TABLE as ts
    WHERE ts.unixTimestamp>=@startTime and ts.unixTimestamp<= @endTime
    GROUP BY ts.logicalId
    
    --Increment startTime and endTime to goto next window
    SET @startTime = @endTime;
    SET @endTime = @startTime + 15 * 60000; --converting 15 mins to millisecs
END
时间戳 价值 Unix时间
2021-11-08 00:00:09.000 1527.6 1636329609000
2021-11-08 00:01:09.000 1528.1 1636329669000
2021-11-08 00:02:09.000 1528.6 1636329729000
…… …… ……
2021-11-08 00:13:09.000 1534.5 1636330389000
2021-11-08 00:14:09.000 1535.3 1636330479000
2021-11-08 00:15:09.000 1535.5 1636330509000
2021-11-08 00:16:09.000 1536.0 1636330569000
2021-11-08 00:17:09.000 1528.6 1636330629000
…… …… ……
2021-11-08 00:28:09.000 1542.5 1636331289000
2021-11-08 00:29:09.000 1543.3 1636331379000

需要转换为（我不一定需要 end_time: 添加以便更好地理解）：

开始时间 时间结束 价值
2021-11-08 00:00:09.000 2021-11-08 00:14:39.000 7.7
2021-11-08 00:15:09.000 2021-11-08 00:29:39.000 7.8

标签： sqlsql-server

解决方案

更新

解决方案是从 15 分钟部分获取第一行和最后一行

可以这样做：

create table #t
(
     ts datetime2
    ,val dec(9,2)
)

insert into #t
values
 ('2021-11-08 00:00:09.000', 1527.6)
,('2021-11-08 00:01:09.000', 1528.1)
,('2021-11-08 00:02:09.000', 1528.6)
,('2021-11-08 00:13:09.000', 1534.5)
,('2021-11-08 00:14:09.000', 1535.3)
,('2021-11-08 00:15:09.000', 1535.5)
,('2021-11-08 00:16:09.000', 1536.0)
,('2021-11-08 00:17:09.000', 1528.6)
,('2021-11-08 00:28:09.000', 1542.5)
,('2021-11-08 00:29:09.000', 1543.3)

;with
    cte_partitions as
    (
        select *
        from
            (
                select   *
                    ,row_number() over(partition by M15.[partition] order by T.ts) as row_asc_id -- first row
                    ,row_number() over(partition by M15.[partition] order by T.ts desc) as row_desc_id -- last row
                from #t T
                    outer apply
                    (
                        -- 15 minutes partitions = hours + minutes / 15
                        select  format(ts, 'yyyyMMddHH') + cast(datepart(minute, ts) / 15 as char(1)) as [partition]
                    ) M15
            ) T
        where   T.row_asc_id = 1
            or  T.row_desc_id = 1
    )
select PF.ts
    ,PL.val - PF.val
from cte_partitions PF
    inner join cte_partitions PL
    on PF.[partition] = pl.[partition]
where PF.row_asc_id = 1
and PL.row_desc_id = 1

尽管它与您的示例完全一致，但只有当您每分钟都没有秒等时，它才会起作用。

请看一下

create table #t
(
     ts datetime2
    ,val int
)

insert into #t
values
 ('2021-11-09T01:01:00.000Z', 100)
,('2021-11-09T01:02:00.000Z', 102)
,('2021-11-09T01:03:00.000Z', 103)
,('2021-11-09T01:04:00.000Z', 105)
,('2021-11-09T01:05:00.000Z', 107)
,('2021-11-09T01:06:00.000Z', 108)
,('2021-11-09T01:07:00.000Z', 120)
,('2021-11-09T01:08:00.000Z', 123)
,('2021-11-09T01:09:00.000Z', 128)
,('2021-11-09T01:10:00.000Z', 135)

select  format(ts, 'hh:mm:ss') + ' - ' + format(next_ts, 'hh:mm:ss')
        ,cast(next_val - val as varchar(10)) + ' (' + cast(next_val as varchar(10)) + ' - ' + cast(val as varchar(10)) + ')'
from
    (
        select   ts
                ,val
                ,lead(ts) over(order by ts) as next_ts
                ,lead(val) over(order by ts) as next_val
        from
            (
                select *, (row_number() over(order by ts) - 1) % 4 as row_id
                from #t
            ) SQ
        where row_id = 0
    ) SQ
where next_ts is not null

推荐阅读