时间戳

首页 > 解决方案 > 如何根据 10 分钟内的日期和时间列对数据进行分组和选择？

sql - 如何根据 10 分钟内的日期和时间列对数据进行分组和选择？

问题描述

如何根据 10 分钟内的日期和时间列对数据进行分组和选择？

选择：

SELECT *
FROM tbl
WHERE datecollect IN (
SELECT generate_series(
        (SELECT min(date_trunc('minute', datecollect)) FROM tbl), 
        (SELECT max(date_trunc('minute', datecollect)) FROM tbl), 
        '10 minute'::interval));

桌子：

CREATE TABLE tbl (
  valuex varchar,
  datecollect timestamp);

INSERT INTO tbl (valuex,datecollect) VALUES ('teste','2020-02-25 01:00:41');
INSERT INTO tbl (valuex,datecollect) VALUES ('teste','2020-02-25 01:05:36');
INSERT INTO tbl (valuex,datecollect) VALUES ('teste','2020-02-25 01:10:41');
INSERT INTO tbl (valuex,datecollect) VALUES ('teste','2020-02-25 01:15:11');
INSERT INTO tbl (valuex,datecollect) VALUES ('teste','2020-02-25 03:00:11');
INSERT INTO tbl (valuex,datecollect) VALUES ('teste','2020-02-25 03:05:41');
INSERT INTO tbl (valuex,datecollect) VALUES ('teste','2020-02-25 03:10:11');
INSERT INTO tbl (valuex,datecollect) VALUES ('teste','2020-02-25 03:15:11');

SQL 视图

输出：

0 records

标签： sqldatabasepostgresql

解决方案

您需要将JOIN生成的系列从系列tbl中datecollect的时间开始在 10 分钟间隔内的位置。这将为您提供给定间隔的所有行；然后，您可以根据需要聚合，按pstart. 如果要获得 0 个值，其中没有匹配的间隔值，请使用 aLEFT JOIN代替。

SELECT *
FROM (
  SELECT generate_series(
            MIN(date_trunc('minute', datecollect)), 
            MAX(date_trunc('minute', datecollect)), 
            '10 minute'::interval
         ) AS pstart
  FROM tbl
) dt
JOIN tbl ON tbl.datecollect > dt.pstart AND tbl.datecollect < dt.pstart + interval '10 minute'

输出（用于您的样本数据）：

pstart                  valuex  datecollect
2020-02-25T01:00:00Z    teste   2020-02-25T01:00:41Z
2020-02-25T01:00:00Z    teste   2020-02-25T01:05:36Z
2020-02-25T01:10:00Z    teste   2020-02-25T01:10:41Z
2020-02-25T01:10:00Z    teste   2020-02-25T01:15:11Z
2020-02-25T03:00:00Z    teste   2020-02-25T03:00:11Z
2020-02-25T03:00:00Z    teste   2020-02-25T03:05:41Z
2020-02-25T03:10:00Z    teste   2020-02-25T03:10:11Z
2020-02-25T03:10:00Z    teste   2020-02-25T03:15:11Z

SQLFiddle 上的演示

推荐阅读