PostgreSQL：在一组日期范围内聚合数据。

Question

我有这个数据表

CREATE TABLE t1
(
  id           serial           NOT NULL,
  in_quantity  bigint           NULL,
  price        money            NOT NULL,
  out_quantity bigint           NULL,
  stamp        timestamp        NOT NULL
);

例如，有这样的数据（日期相同，但时间不同）。

INSERT INTO t1 (in_quantity, price, out_quantity, stamp)
VALUES
( 100, 10.00, NULL, '2014-10-20 00:00:00'), -- id =  1
( 200, 11.00, NULL, '2014-10-20 00:01:00'), -- id =  2
( 300, 12.00, NULL, '2014-10-20 00:02:00'), -- id =  3
(NULL, 13.00,  400, '2014-10-20 00:03:00'), -- id =  4
(NULL, 14.00,  500, '2014-10-20 00:04:00'), -- id =  5

( 600, 15.00, NULL, '2014-10-20 00:15:00'), -- id =  6
( 700, 16.00, NULL, '2014-10-20 00:16:00'), -- id =  7
( 800, 17.00, NULL, '2014-10-20 00:17:00'), -- id =  8
(NULL, 18.00,  900, '2014-10-20 00:18:00'), -- id =  9
(NULL, 19.00, 1000, '2014-10-20 00:19:00'), -- id = 10

(2300, 23.00, NULL, '2014-10-20 00:23:00'), -- id = 11
(2400, 24.00, NULL, '2014-10-20 00:24:00'); -- id = 12

我需要从这个表中获取特定日期范围内每天最大进出数量的行集合。

例如，给定以下集合：

( "2014-10-20 00:00:00" : "2014-10-20 00:05:00" ]
( "2014-10-20 00:05:00" : "2014-10-20 00:10:00" ]
( "2014-10-20 00:10:00" : "2014-10-20 00:15:00" ]
( "2014-10-20 00:15:00" : "2014-10-20 00:20:00" ]
( "2014-10-20 00:20:00" : "2014-10-20 00:25:00" ]

我的期望结果是，通过这个例子，能够实现以下目标。

interval begin        | interval end          | max_in_q | max_in_q_id | max_out_q | max_out_q_id 
======================+=======================+==========+=============+===========+=============
"2014-10-20 00:00:00" | "2014-10-20 00:05:00" | 300      | 3           | 400       | 4
"2014-10-20 00:05:00" | "2014-10-20 00:10:00" | NULL     | NULL        | NULL      | NULL        
"2014-10-20 00:10:00" | "2014-10-20 00:15:00" | NULL     | NULL        | NULL      | NULL        
"2014-10-20 00:15:00" | "2014-10-20 00:20:00" | 800      | 8           | 1000      | 10
"2014-10-20 00:20:00" | "2014-10-20 00:25:00" | 2400     | 12          | NULL      | NULL

所以，我可以通过以下查询生成这样的集合

SELECT
   i::timestamp AS dleft,
   i::timestamp + '1 hour' AS dright 
FROM
   generate_series('2014-10-20 00:00:00'::timestamp, '2014-10-20 23:00:00'::timestamp, '1 hour') AS i

但我想不出如何让聚合函数在每个小范围内运行，并且如何联接结果。

- Vasilly.Prokopyev

Answer 1

首先，你需要意识到，每个聚合值都需要对应它们的id，这不是在任何关系型数据库管理系统中都能轻易查询到的。

这个问题主要可以通过PostgreSQL中的 DISTINCT ON 来解决：

SELECT DISTINCT ON (s)
  s ts_start, s + '5 minutes' ts_end, in_quantity max_in_q, id max_in_id
FROM
  generate_series('2014-10-20 00:00:00'::timestamp, '2014-10-20 00:20:00'::timestamp, '5 minutes') s
LEFT JOIN
  t1 ON stamp <@ tsrange(s, s + '5 minutes', '(]')
ORDER BY
  s, in_quantity DESC NULLS LAST;

但这只允许您选择一个最大/最小值和它们所属的整行。

如果您确实需要两个最大列，您需要编写自连接和子查询，这可能不会很快。

SELECT
  lower(r) ts_start, upper(r) ts_end, max_in_q, max_in.id max_in_id, max_out_q, max_out.id max_out_id
FROM (
  SELECT
    r, max(in_quantity) max_in_q, max(out_quantity) max_out_q
  FROM
    generate_series('2014-10-20 00:00:00'::timestamp, '2014-10-20 00:20:00'::timestamp, '5 minutes') s,
    tsrange(s, s + '5 minutes', '(]') r
  LEFT JOIN
    t1 ON stamp <@ r
  GROUP BY
    r
  ORDER BY
    r
) m
LEFT JOIN
  t1 max_in ON max_in.in_quantity = max_in_q
LEFT JOIN
  t1 max_out ON max_out.out_quantity = max_out_q;

注意：使用这个第二个版本，你需要自己处理重复项，因为`in_quantity`和`out_quantity`不是唯一的。 SQLFiddle

- pozs

Answer 2

我认为借助范围类型，这可能会变得非常简单：

WITH data(in_quantity,price,out_quantity,stamp) AS (VALUES
( 100::int8, 10.00, NULL::int8, '2014-10-20 00:00:00'::timestamp), -- id =  1
( 200, 11.00, NULL, '2014-10-20 00:01:00'), -- id =  2
( 300, 12.00, NULL, '2014-10-20 00:02:00'), -- id =  3
(NULL, 13.00,  400, '2014-10-20 00:03:00'), -- id =  4
(NULL, 14.00,  500, '2014-10-20 00:04:00'), -- id =  5

( 600, 15.00, NULL, '2014-10-20 00:15:00'), -- id =  6
( 700, 16.00, NULL, '2014-10-20 00:16:00'), -- id =  7
( 800, 17.00, NULL, '2014-10-20 00:17:00'), -- id =  8
(NULL, 18.00,  900, '2014-10-20 00:18:00'), -- id =  9
(NULL, 19.00, 1000, '2014-10-20 00:19:00'), -- id = 10

(2300, 23.00, NULL, '2014-10-20 00:23:00'), -- id = 11
(2400, 24.00, NULL, '2014-10-20 00:24:00')
)
SELECT
   tsrange(i,i+INTERVAL '1h','[)') r,
   max(in_quantity)                max_in_q,
   max(out_quantity)               max_out_q
  FROM generate_series('2014-10-20 00:00:00'::timestamp,
                       '2014-10-20 23:00:00'::timestamp, '1 hour') AS i
  LEFT JOIN data d ON tsrange(i,i+INTERVAL '1h','[)') @> d.stamp
 GROUP BY r
 ORDER BY r;

查看SQL Fiddle上的内容

我在这里使用了LEFT JOIN，因为我认为您希望看到所有范围，并根据您的需求进行调整。

- vyegorov