时间戳

首页 > 解决方案 > 是否可以创建一个对表格进行上采样的视图

postgresql - 是否可以创建一个对表格进行上采样的视图

问题描述

是否可以在 postgres 中创建一个表视图，该表具有与原始列相同的列，只是行及时上采样？

到目前为止，我发现的大多数示例都与平均/下采样有关。

比如说我有一个表“数据”，它有列：

+---------------------+---+---+
|        time         | x | y |
+---------------------+---+---+
| 2019-07-17 21:00:00 | 4 | 8 |
| 2019-07-17 21:10:00 | 2 | 5 |
| 2019-07-17 21:20:00 | 1 | 7 |
+---------------------+---+---+

其中 time 是 10 分钟实例中给出的 TIMESTAMP。为了与其他表格对齐，我想制作一个时间为 1 秒的视图，并且 x 和 y 在该间隔内保持 10 分钟固定值。生成一个包含 600 倍以上行的新表：

+---------------------+---+---+
|        time         | x | y |
+---------------------+---+---+
| 2019-07-17 21:00:00 | 4 | 8 |
| 2019-07-17 21:00:01 | 4 | 8 |
| 2019-07-17 21:00:02 | 4 | 8 |
...
| 2019-07-17 21:10:00 | 2 | 5 |
+---------------------+---+---+

标签： postgresql

解决方案

演示：db<>小提琴

CREATE VIEW v_data AS

SELECT
    gs,
    x, y
FROM (
    SELECT 
        mytime, 
        lead(mytime) OVER (ORDER BY mytime) AS next_time, -- 1
        x, y
    FROM data
) s,
generate_series(                                          -- 2
    mytime, 
    COALESCE(next_time - interval '1 second', mytime),    -- 3
    interval '1 second'
) AS gs
  1. lead()窗口函数允许将下一个时间值放入当前行
  2. 使用当前和下一个时间值，您可以使用以下功能生成时间序列generate_series()（在小提琴中采用分钟步骤而不是秒以提高可读性）。开始和结束都是时间戳。生成的系列（横向）连接到每个“开始”时间戳。
  3. 如果您仔细查看小提琴中的第二步，您可能会发现有两条记录 for21:10:00并且最后一个x/y对丢失了。那是因为系列生成总是增加界限。因此，21:00它21:10采用上限，即21:10。对于从21:10到的下一个区间，21:20它采用与下限相同的值。这就是为什么上限应该是前一步的原因：它被减去。最后一步也很困难。lead()窗口函数找不到下一条记录，因为它是最后一条。所以next_time值为NULL。但NULL不是计算某些系列的有效参数。为了获得最后的记录，COALESCE()采用函数，当实际上界为 时，它也将第一个参数作为上界NULL。

推荐阅读