如何在vertica db (vsql)中聚合时间戳范围内的行

Question

假设我有一个包含如下数据的表：

         ts          | bandwidth_bytes
---------------------+-----------------
 2021-08-27 22:00:00 |    3792
 2021-08-27 21:45:00 |    1164
 2021-08-27 21:30:00 |    7062
 2021-08-27 21:15:00 |    3637
 2021-08-27 21:00:00 |    2472
 2021-08-27 20:45:00 |    1328
 2021-08-27 20:30:00 |    1932
 2021-08-27 20:15:00 |    1434
 2021-08-27 20:00:00 |    1530
 2021-08-27 19:45:00 |    1457
 2021-08-27 19:30:00 |    1948
 2021-08-27 19:15:00 |    1160

我需要输出如下内容：

         ts          | bandwidth_bytes
---------------------+-----------------
 2021-08-27 22:00:00 |    15,655
 2021-08-27 21:00:00 |    7166
 2021-08-27 20:00:00 |    6095

我想做一个小时的数据和bandwidth_bytes的时间戳。我特别想在vsql中实现这一点。

还有更多的列，但为了简化起见，我只显示了这两个。

Answer 1

您可以使用date_trunc()

select [date_trunc('hour', ts)][1] as ts_hh, sum(bandwidth_bytes)
from t
group by ts_hh;

Answer 2

使用Vertica可爱的函数TIME_SLICE()。

你不能只按小时计算，你也可以按2或3个小时计算，这是DATE_TRUNC()所不提供的。

您似乎希望20:00:01和21:00:00之间的所有内容都属于21:00:00的一个时间片。然而，在DATE_TRUNC()和TIME_SLICE()中，属于同一时间片的是20:00:00 to 20:59:59。因此，在应用TIME_SLICE()之前，我减去了一秒钟。

WITH
-- your in data ...
indata(ts,bandwidth_bytes) AS (
          SELECT TIMESTAMP '2021-08-27 22:00:00',3792
UNION ALL SELECT TIMESTAMP '2021-08-27 21:45:00',1164
UNION ALL SELECT TIMESTAMP '2021-08-27 21:30:00',7062
UNION ALL SELECT TIMESTAMP '2021-08-27 21:15:00',3637
UNION ALL SELECT TIMESTAMP '2021-08-27 21:00:00',2472
UNION ALL SELECT TIMESTAMP '2021-08-27 20:45:00',1328
UNION ALL SELECT TIMESTAMP '2021-08-27 20:30:00',1932
UNION ALL SELECT TIMESTAMP '2021-08-27 20:15:00',1434
UNION ALL SELECT TIMESTAMP '2021-08-27 20:00:00',1530
UNION ALL SELECT TIMESTAMP '2021-08-27 19:45:00',1457
UNION ALL SELECT TIMESTAMP '2021-08-27 19:30:00',1948
UNION ALL SELECT TIMESTAMP '2021-08-27 19:15:00',1160
)
SELECT
  TIME_SLICE(ts - INTERVAL '1 SECOND' ,1,'HOUR','END') AS ts
, SUM(bandwidth_bytes)    AS bandwidth_bytes
FROM indata
GROUP BY 1
ORDER BY 1 DESC;
         ts          | bandwidth_bytes 
---------------------+-----------------
 2021-08-27 22:00:00 |           15655
 2021-08-27 21:00:00 |            7166
 2021-08-27 20:00:00 |            6095