sql - 如何在 30 分钟窗口中选择具有最高时间戳的行？

Question

我有一个名为 table1 的 SQL Server 表，它有一个时间戳列 column_ts 和一些其他列 column1、column2、column3

所以表格看起来像:

column_ts                   column1     column2     column3
2016-09-30 00:04:00.000     number1     string1     integer1
2016-09-30 00:24:00.000     number2     string2     integer2
2016-09-30 00:29:00.000     number3     string3     integer3
2016-09-30 00:44:00.000     number4     string4     integer4
2016-09-30 00:48:00.000     number5     string5     integer5
2016-09-30 01:04:00.000     number6     string6     integer6
2016-09-30 01:24:00.000     number7     string7     integer7
2016-09-30 01:54:00.000     number8     string8     integer8
2016-09-30 01:59:00.000     number9     string9     integer9

首先，我将选择记录 where column_ts >= 2016-09-30 00:00:00.000。然后，我只想从 column_ts 的每个 30 分钟窗口中选择具有最高时间戳的一行。

因此对于给定的数据，查询应该只选择以下行:

column_ts                   column1     column2     column3
2016-09-18 00:29:00.000     number3     string3     integer3
2016-09-18 00:48:00.000     number5     string5     integer5
2016-09-18 01:24:00.000     number7     string7     integer7
2016-09-18 01:59:00.000     number9     string9     integer9

在某种程度上，我想制作像

这样的 column_ts 的 30 分钟窗口

1) 2016-09-30 00:00:00.000 - 2016-09-30 00:30:00.000
2) 2016-09-30 00:30:00.000 - 2016-09-30 01:00:00.000
3) 2016-09-30 01:00:00.000 - 2016-09-30 01:30:00.000
4) 2016-09-30 01:30:00.000 - 2016-09-30 02:00:00.000

最后想从这 30 分钟的每个窗口中选择 column_ts 值最高的一行。

我无法弄清楚如何生成可以从中选择 MAX(column_ts) 的 30 分钟窗口。请建议我如何做到这一点。

Answer 1

您可以从一个纪元中获取以分钟为单位的日期差异，然后将其除以 30 以按 30 分钟的间隔进行分组。

此查询将给出每个 30 分钟的时间段以及该时间段的最大 column_ts:

select dateadd(minute, datediff(minute, '1970-1-1',column_ts)/30*30,'1970-1-1') as timegroup,
       MAX(column_ts) as max_time
from table1 where column_ts >= '2016-09-30 00:00:00.000'
group by datediff(minute, '1970-1-1', column_ts) / 30

以上产生:

timegroup                   max_time
2016-09-30 00:00:00.000     2016-09-30 00:29:00.000
2016-09-30 00:30:00.000     2016-09-30 00:48:00.000
2016-09-30 01:00:00.000     2016-09-30 01:24:00.000
2016-09-30 01:30:00.000     2016-09-30 01:59:00.000

一旦你有了它，你就可以在子查询中使用它来获得你想要的结果:

select groups.timegroup, t.column_ts, t.column1, t.column2, t.column3 
from (
    select dateadd(minute, datediff(minute, '1970-1-1',column_ts)/30*30,'1970-1-1') as timegroup,MAX(column_ts) as max_time
    from table1 where column_ts >= '2016-09-30 00:00:00.000'
    group by datediff(minute, '1970-1-1', column_ts) / 30
) as groups
inner join table1 t on t.column_ts = groups.max_time

产生

timegroup                   column_ts                   column1   column2   column3
2016-09-30 00:00:00.000     2016-09-30 00:29:00.000     number3   string3   integer3
2016-09-30 00:30:00.000     2016-09-30 00:48:00.000     number5   string5   integer5
2016-09-30 01:00:00.000     2016-09-30 01:24:00.000     number7   string7   integer7
2016-09-30 01:30:00.000     2016-09-30 01:59:00.000     number9   string9   integer9