sql - 在具有多列的单行中显示来自多行的数据

Question

我有一个 PostgreSQL 数据库，其中包含一些带有时间戳的记录。

有没有简单的方法可以在一段时间内(例如一个月，使用 PRECEDING 和 FOLLOWING 表达式)在窗口中收集数据，然后创建包含该月所有记录的多个列(例如每个月的每一天 30 个列)？

让我们考虑这个简单的案例:一个包含两列timestamp 和measurement 的表格。我想要的是获得 31 列的表:时间戳和 measurement1、measurement2 ... measurement30，其中measurement_i 是时间戳前 i 天的测量。

Answer 1

如果您希望在结果中多行，我会使用 crosstab()功能。

但根据您的描述，您需要单行。我会用 window functions lead() or lag() 解决这个问题.
主要技巧是在应用 WHERE 子句并将结果缩小到单行之前使用子查询或 CTE 生成所有列。我会使用 CTE这里:

给出下表(您应该提供):

CREATE TABLE tbl(
  tbl_id serial PRIMARY KEY
 ,ts timestamp NOT NULL
 ,value int
);

保证每天一行

查询可能如下所示:

WITH x AS (
   SELECT tbl_id, ts, value
         ,lag(value, 1) OVER w AS value_day_before_1
         ,lag(value, 2) OVER w AS value_day_before_2
   --    ...
         ,lead(value, 1) OVER w AS value_day_after_1
         ,lead(value, 2) OVER w AS value_day_after_2
   --    ...
   FROM   tbl
   WINDOW w AS (ORDER BY ts) 
   )
SELECT *
FROM   x
WHERE  ts = '2013-02-14 0:0'::timestamp

每天最多一行，但天数可以缺失:

时间戳也可以是一天中的任何时间。

使用 generate_series() 生成天数列表和 LEFT JOIN 你的表到它:

WITH x AS (
   SELECT tbl_id, ts, value
         ,lag(value, 1) OVER w AS value_day_before_1
         ,lag(value, 2) OVER w AS value_day_before_2
   --    ...
         ,lead(value, 1) OVER w AS value_day_after_1
         ,lead(value, 2) OVER w AS value_day_after_2
   --    ...
   FROM   (
       SELECT generate_series ('2013-02-01'::date
                              ,'2013-02-28'::date
                              ,interval '1d') AS day
       ) d
   LEFT JOIN tbl t ON date_trunc('day', t.ts) = d.day
   WINDOW w AS (ORDER BY day) 
   )
SELECT *
FROM   x
WHERE  ts = '2013-02-14 0:0'::timestamp;

->sqlfiddle