这是本节的多页打印视图。 点击此处打印.

返回本页常规视图.

空白填充和插值 (GFI)

此主题中用于解释概念的示例和图形，均使用以下简单的架构：

CREATE TABLE TickStore (ts TIMESTAMP, symbol VARCHAR(8), bid FLOAT);
INSERT INTO TickStore VALUES ('2009-01-01 03:00:00', 'XYZ', 10.0);
INSERT INTO TickStore VALUES ('2009-01-01 03:00:05', 'XYZ', 10.5);
COMMIT;

在 Vertica 中，时序数据以一系列符合特定表架构的行表示，其中一列用于存储时间信息。

时序中的时间和数据状态都是连续性的。因此，随时间的推移对 SQL 查询进行评估具有挑战性，因为输入记录的间隔通常不均匀，而且会包含空白。

例如，下表中包含的两个输入行相隔五秒钟：3:00:00 和 3:00:05。

=> SELECT * FROM TickStore;
         ts          | symbol | bid
---------------------+--------+------
 2009-01-01 03:00:00 | XYZ    |   10
 2009-01-01 03:00:05 | XYZ    | 10.5
(2 rows)

根据这两个输入，如何确定时间位于这两个时间点之间的出价，例如 3:00:03 PM？ TIME_SLICE 函数会将时间戳标准化为对应的时间片；但是，TIME_SLICE 并没有解决数据中缺少输入（时间片）这一问题。不过，Vertica 提供了空白填充和插值 (GFI) 功能，可以填充缺少的数据点并将已知数据点范围内的新（缺少的）数据点添加到输出中。它通过时序聚合函数（TS_FIRST_VALUE 和 TS_LAST_VALUE）以及 SQL TIMESERIES 子句完成这些任务。 但首先，我们将从常数插值开始，介绍 Vertica 中构成空白填充和插值的组件。 以下主题中的图像使用如下图例：

  • x 轴表示时间戳 (ts) 列

  • y 轴表示出价列。

  • 垂直的蓝线用来分隔时间片。

  • 红点表示表中的输入记录 $10.0 和 $10.5。

  • 蓝色星形表示输出值，包括内插值。

1 - 常数插值

根据示例 TickStore 架构中在 03:00:00 和 03:00:05 时的已知输入时间戳，如何确定 03:00:03 时的出价？

在财务数据中，使用的常见插值方案是将出价设置为到目前为止最后一个见到的值。这种方案称为常数插值，也就是说，Vertica 将根据之前的输入记录计算新值。

回到上面问到的查询，以下是表输出，它显示了在 03:00:00 和 03:00:05 时的出价之间存在 5 秒延迟：

=> SELECT * FROM TickStore;
         ts          | symbol | bid
---------------------+--------+------
 2009-01-01 03:00:00 | XYZ    |   10
 2009-01-01 03:00:05 | XYZ    | 10.5
(2 rows)

使用常数插值时，XYZ 的内插出价在 3:00:03 时仍为 $10.0，而且 3:00:03 介于在两个已知数据输入（3:00:00 PM 和 3:00:05）之间。在 3:00:05 时，值变为 $10.5。已知数据点以红点表示，在 3:00:03 时的内插值以蓝色星形表示。

包含 3 秒时间片的 TickStore 表

要编写查询使输入行更加均匀，您首先需要了解 TIMESERIES 子句和时序聚合函数。

2 - TIMESERIES 子句和聚合

SELECT..TIMESERIES 子句和时序聚合会对数据进行标准化处理并将其放入 3 秒时间片，然后在发现空白时插入出价，以此来解决输入记录中存在空白的问题。

TIMESERIES 子句

TIMESERIES 子句 是时序分析计算的重要组件。它执行空白填充和插值 (GFI) 以生成输入记录中缺失的时间片。此子句会应用于数据中的时间戳列/表达式，其形式如下：

TIMESERIES slice_time AS 'length_and_time_unit_expression' 
OVER ( ... [ window-partition-clause[ , ... ] ] 
... ORDER BY time_expression )
... [ ORDER BY table_column [ , ... ] ]

时序聚合函数

时序聚合 (TSA) 函数 TS_FIRST_VALUE 和 TS_LAST_VALUE 随时间的推移评估给定变量集的值，并将这些值分组到窗口以进行分析和聚合。

TSA 函数负责处理属于每个时间片的数据。每个时间片会产生一行输出；如果存在分区表达式，则每个时间片的每个分区会产生一行输出。

下表显示了 3 秒时间片，其中：

  • 前两行位于第一个时间片，该时间片范围为 3:00:00 到 3:00:02。这些是 TSA 函数对应于 3:00:00 起的时间片的输出所对应的输入行。

  • 后两行位于第二个时间片，该时间片范围为 3:00:03 到 3:00:05。这些是 TSA 函数对应于 3:00:03 起的时间片的输出所对应的输入行。

    结果是每个时间片的开始时间。

    时序聚合输出

示例

以下示例比较了使用和不使用 TS_FIRST_VALUE TSA 函数时的返回值。

此示例显示了不使用 TS_FIRST_VALUE TSA 函数的 TIMESERIES 子句。

=> SELECT slice_time, bid FROM TickStore TIMESERIES slice_time AS '3 seconds' OVER(PARTITION by TickStore.bid ORDER BY ts);

此示例显示了 TIMESERIES 子句和 TS_FIRST_VALUE TSA 函数。该查询返回了 bid 列的值，这些值由指定的常数插值方案确定。

=> SELECT slice_time, TS_FIRST_VALUE(bid, 'CONST') bid FROM TickStore
   TIMESERIES slice_time AS '3 seconds' OVER(PARTITION by symbol ORDER BY ts);

Vertica 插入了最后一个已知值，填充了缺失的数据点，因此返回了对应于 3:00:03 的 10：

3 - 时序舍入

Vertica 按照与时间戳 2000-01-01 00:00:00 相关的相同间隔计算所有时序。Vertica 根据需要对时序时间戳进行四舍五入以符合此基线。对于指定的间隔，开始时间也会向下舍入为最接近的整数。

根据此逻辑，TIMESERIES 子句将生成时间戳系列，如以下部分所述。

分

分钟时序会向下舍入为整数分钟。例如，以下语句指定了 00:00:03 ‑ 00:05:50 这一时间范围：

=> SELECT ts FROM (
    SELECT '2015-01-04 00:00:03'::TIMESTAMP AS tm
      UNION
    SELECT '2015-01-04 00:05:50'::TIMESTAMP AS tm
   ) t
   TIMESERIES ts AS '1 minute' OVER (ORDER BY tm);

Vertica 将时间序列的开始时间和结束时间四舍五入到整分钟，分别为 00:00:00 和 00:05:00：


         ts
---------------------
 2015-01-04 00:00:00
 2015-01-04 00:01:00
 2015-01-04 00:02:00
 2015-01-04 00:03:00
 2015-01-04 00:04:00
 2015-01-04 00:05:00
(6 rows)

周

因为基线时间戳 2000-01-01 00:00:00 为星期六，所有周时序都从星期六开始。Vertica 相应地向下舍入了时序的开始和结束时间戳。例如，以下语句指定了 12/10/99 ‑ 01/10/00 的时间跨度：

=> SELECT ts FROM (
     SELECT '1999-12-10 00:00:00'::TIMESTAMP AS tm
       UNION
     SELECT '2000-01-10 23:59:59'::TIMESTAMP AS tm
     ) t
     TIMESERIES ts AS '1 week' OVER (ORDER BY tm);

指定的时间范围从星期五 (12/10/99) 开始，因此 Vertica 从上一个星期六 (12/04/99) 开始时序。时序结束于时间范围中的最后一个星期六，即 01/08/00：

         ts
---------------------
 1999-12-04 00:00:00
 1999-12-11 00:00:00
 1999-12-18 00:00:00
 1999-12-25 00:00:00
 2000-01-01 00:00:00
 2000-01-08 00:00:00
(6 rows)

月

月时序会相对于基线时间戳 2000-01-01 00:00:00 分为相等的 30 天间隔。例如，以下语句指定了 09/01/99 ‑ 12/31/00 这一时间范围：

=> SELECT ts FROM (
     SELECT '1999-09-01 00:00:00'::TIMESTAMP AS tm
       UNION
     SELECT '2000-12-31 23:59:59'::TIMESTAMP AS tm
   ) t
   TIMESERIES ts AS '1 month' OVER (ORDER BY tm);

Vertica 生成了一系列 30 天的间隔，其中每个时间戳都相对于基线时间戳进行了向上舍入或向下舍入：

         ts
---------------------
 1999-08-04 00:00:00
 1999-09-03 00:00:00
 1999-10-03 00:00:00
 1999-11-02 00:00:00
 1999-12-02 00:00:00
 2000-01-01 00:00:00
 2000-01-31 00:00:00
 2000-03-01 00:00:00
 2000-03-31 00:00:00
 2000-04-30 00:00:00
 2000-05-30 00:00:00
 2000-06-29 00:00:00
 2000-07-29 00:00:00
 2000-08-28 00:00:00
 2000-09-27 00:00:00
 2000-10-27 00:00:00
 2000-11-26 00:00:00
 2000-12-26 00:00:00
(18 rows)

年

年时序分为相等的 365 天间隔。如果时间范围重叠了自基线时间戳 2000-01-01 00:00:00 以来或在该基线时间戳以前的闰年，Vertica 将相应地对时序时间戳进行四舍五入。

例如，以下语句指定了 01/01/95 ‑ 05/08/09 这一时间范围，此时间范围重叠了四个闰年，包括基线时间戳：

=> SELECT ts FROM (
      SELECT '1995-01-01 00:00:00'::TIMESTAMP AS tm
        UNION
      SELECT '2009-05-08'::TIMESTAMP AS tm
    ) t timeseries ts AS '1 year' over (ORDER BY tm);

Vertica 生成了一系列时间戳，它们都相对于基线时间戳进行了向上舍入或向下舍入：

         ts
---------------------
 1994-01-02 00:00:00
 1995-01-02 00:00:00
 1996-01-02 00:00:00
 1997-01-01 00:00:00
 1998-01-01 00:00:00
 1999-01-01 00:00:00
 2000-01-01 00:00:00
 2000-12-31 00:00:00
 2001-12-31 00:00:00
 2002-12-31 00:00:00
 2003-12-31 00:00:00
 2004-12-30 00:00:00
 2005-12-30 00:00:00
 2006-12-30 00:00:00
 2007-12-30 00:00:00
 2008-12-29 00:00:00
(16 rows)

4 - 线性插值

线性插值是指 Vertica 基于指定的时间片将值内插到线性斜率中，而不是基于最后一个见到的值（常数插值）内插数据点。

后面的查询使用线性插值将输入记录放入 2 秒时间片中，然后返回每个股票代码/时间片组合的第一个出价值（时间片开始时的值）：

=> SELECT slice_time, TS_FIRST_VALUE(bid, 'LINEAR') bid FROM Tickstore
   TIMESERIES slice_time AS '2 seconds' OVER(PARTITION BY symbol ORDER BY ts);
     slice_time      | bid
---------------------+------
 2009-01-01 03:00:00 |   10
 2009-01-01 03:00:02 | 10.2
 2009-01-01 03:00:04 | 10.4
(3 rows)

下图显示了前面的查询结果，同时显示了没有产生输入记录的 2 秒时间空白（3:00:02 和 3:00:04）。请注意，XYZ 的内插出价在 3:00:02 时变为 10.2，在 3:00:03 时变为 10.3，在 3:00:04 时变为 10.4，而所有这些都介于两个已知数据输入（3:00:00 和 3:00:05）之间。在 3:00:05 时，值可能会变为 10.5。

使用 ts_first_value 时的线性插值

以下是常数和线性插值方案的横向比较。

5 - GFI 示例

此主题介绍了可以使用常数和线性插值方案编写的部分查询。

常数插值

第一个查询使用 TS_FIRST_VALUE() 和 TIMESERIES 子句将输入记录放入时长为 3 秒的时间片中，然后返回每个符号/时间片组合的第一个出价值（时间片开始时的值）。 

=> SELECT slice_time, symbol, TS_FIRST_VALUE(bid) AS first_bid FROM TickStore
   TIMESERIES slice_time AS '3 seconds' OVER (PARTITION BY symbol ORDER BY ts);

因为股票 XYZ 的出价在 3:00:03 时为 10.0，即第二个时间片的 first_bid 值。此值从 3:00:03 开始时一直为 10.0，因为 10.5 的输入值直到 3:00:05 时才产生。在这种情况下，根据在股票 XYZ 中见到的最后一个值为时间 3:00:03 推断了内插值：

     slice_time      | symbol | first_bid
---------------------+--------+-----------
 2009-01-01 03:00:00 | XYZ    |        10
 2009-01-01 03:00:03 | XYZ    |        10
(2 rows)

下一个示例将输入记录放入 2 秒时间片以返回每个股票代码/时间片组合的第一个出价值：

=> SELECT slice_time, symbol, TS_FIRST_VALUE(bid) AS first_bid FROM TickStore
   TIMESERIES slice_time AS '2 seconds' OVER (PARTITION BY symbol ORDER BY ts);

此时结果包含 2 秒时间片中的三个记录，所有这些出现在 03:00:00 时的第一个输入行和在 3:00:05 时的第二个输入行之间。请注意，第二个和第三个输出记录与不存在输入记录的时间片对应。

     slice_time      | symbol | first_bid
---------------------+--------+-----------
 2009-01-01 03:00:00 | XYZ    |        10
2009-01-01 03:00:02 | XYZ    |        10
2009-01-01 03:00:04 | XYZ    |        10
(3 rows)

使用同一个表架构时，下一个查询将 TS_LAST_VALUE() 与 TIMESERIES 结合使用以返回每个时间片最后的值（时间片结束时的值）。 

=> SELECT slice_time, symbol, TS_LAST_VALUE(bid) AS last_bid FROM TickStore
   TIMESERIES slice_time AS '2 seconds' OVER (PARTITION BY symbol ORDER BY ts);

请注意，最后一个值输出行为 10.5，因为在时间 3:00:05 时的值 10.5 是从 3:00:04 开始的 2 秒时间片中的最后一个点。

     slice_time      | symbol | last_bid
---------------------+--------+----------
 2009-01-01 03:00:00 | XYZ    |       10
 2009-01-01 03:00:02 | XYZ    |       10
 2009-01-01 03:00:04 | XYZ    |     10.5
(3 rows)

请记住，因为常数插值为默认方案，如果按下述方式使用 CONST 参数编写查询，则会返回相同的结果：

=> SELECT slice_time, symbol, TS_LAST_VALUE(bid, 'CONST') AS last_bid FROM TickStore
   TIMESERIES slice_time AS '2 seconds' OVER (PARTITION BY symbol ORDER BY ts);

线性插值

根据常数插值示例（指定了 2 秒时间片）中所列的上述输入记录，通过线性插值得出的 TS_LAST_VALUE 结果如下：

=> SELECT slice_time, symbol, TS_LAST_VALUE(bid, 'linear') AS last_bid   FROM TickStore
   TIMESERIES slice_time AS '2 seconds' OVER (PARTITION BY symbol ORDER BY ts);

在结果中，没有返回最后一行的 last_bid 值，因为查询指定了 TS_LAST_VALUE，并且在 3:00:04 时间片之后没有要插值的任何数据点。

     slice_time      | symbol | last_bid
---------------------+--------+----------
 2009-01-01 03:00:00 | XYZ    |     10.2
 2009-01-01 03:00:02 | XYZ    |     10.4
 2009-01-01 03:00:04 | XYZ    |
(3 rows)

使用多个时序聚合函数

同一个查询中可以使用多个时序聚合函数。按照 TIMESERIES 子句中的定义，它们共享同一个空白填充策略；然而，每个时序聚合函数可以指定自己的插值策略。在以下示例中，有两个常数插值和一个线性插值方案，但这三个函数全部使用三秒时间片。

=> SELECT slice_time, symbol,
       TS_FIRST_VALUE(bid, 'const') fv_c,
       TS_FIRST_VALUE(bid, 'linear') fv_l,
       TS_LAST_VALUE(bid, 'const') lv_c
   FROM TickStore
   TIMESERIES slice_time AS '3 seconds' OVER(PARTITION BY symbol ORDER BY ts);

在以下输出中，比较了原始输出与多个时序聚合函数返回的输出。

使用分析 LAST_VALUE 函数

以下是使用 LAST_VALUE() 的示例，这样您可以了解此函数与 GFI 语法之间的区别。

=> SELECT *, LAST_VALUE(bid) OVER(PARTITION by symbol ORDER BY ts)
   AS "last bid" FROM TickStore;

对于输出值，没有执行任何空白填充和内插。

         ts          | symbol | bid  | last bid
---------------------+--------+------+----------
 2009-01-01 03:00:00 | XYZ    |   10 |       10
 2009-01-01 03:00:05 | XYZ    | 10.5 |     10.5
(2 rows)

使用 slice_time

在 TIMESERIES 查询中，您无法在 WHERE 子句中使用 slice_time 列，因为 WHERE 子句的评估先于 TIMESERIES 子句，并且直到评估 TIMESERIES 之后才会生成 slice_time 列。例如，Vertica 不支持以下查询：

=> SELECT symbol, slice_time, TS_FIRST_VALUE(bid IGNORE NULLS) AS fv
   FROM TickStore
   WHERE slice_time = '2009-01-01 03:00:00'
   TIMESERIES slice_time as '2 seconds' OVER (PARTITION BY symbol ORDER BY ts);
ERROR:  Time Series timestamp alias/Time Series Aggregate Functions not allowed in WHERE clause

但是，您可以编写子查询，将 slice_time 中的谓词放在外部查询中：

=> SELECT * FROM (
      SELECT symbol, slice_time,
        TS_FIRST_VALUE(bid IGNORE NULLS) AS fv
      FROM TickStore
      TIMESERIES slice_time AS '2 seconds'
      OVER (PARTITION BY symbol ORDER BY ts) ) sq
   WHERE slice_time = '2009-01-01 03:00:00';
 symbol |     slice_time      | fv
--------+---------------------+----
 XYZ    | 2009-01-01 03:00:00 | 10
(1 row)

创建稠密时间序列

借助 TIMESERIES 子句，您可以方便地创建稠密时间序列，以便与实际数据一起用于外部联接。结果将表示所有时间点，而不只是存在数据的时间点。

随后的示例将使用空白填充和插值 (GFI)中所述的相同 TickStore 架构，并添加一个新内部表以用于创建联接：

=> CREATE TABLE inner_table (
       ts TIMESTAMP,
       bid FLOAT
   );
=> CREATE PROJECTION inner_p (ts, bid) as SELECT * FROM inner_table
   ORDER BY ts, bid UNSEGMENTED ALL NODES;
=> INSERT INTO inner_table VALUES ('2009-01-01 03:00:02', 1);
=> INSERT INTO inner_table VALUES ('2009-01-01 03:00:04', 2);
=> COMMIT;

为了返回所有时间点，可以在相关时间帧的开始和结束范围之间创建一个简单的并集。在本例中以 1 秒为时间段：

=> SELECT ts FROM (
     SELECT '2009-01-01 03:00:00'::TIMESTAMP AS time FROM TickStore
     UNION
     SELECT '2009-01-01 03:00:05'::TIMESTAMP FROM TickStore) t
   TIMESERIES ts AS '1 seconds' OVER(ORDER BY time);
         ts
---------------------
 2009-01-01 03:00:00
 2009-01-01 03:00:01
 2009-01-01 03:00:02
 2009-01-01 03:00:03
 2009-01-01 03:00:04
 2009-01-01 03:00:05
(6 rows)

下一个查询将以 500 毫秒为时间段在时间帧的开始和结束范围之间创建一个并集：

=> SELECT ts FROM (
     SELECT '2009-01-01 03:00:00'::TIMESTAMP AS time
     FROM TickStore
     UNION
     SELECT '2009-01-01 03:00:05'::TIMESTAMP FROM TickStore) t
   TIMESERIES ts AS '500 milliseconds' OVER(ORDER BY time);
          ts
-----------------------
 2009-01-01 03:00:00
 2009-01-01 03:00:00.5
 2009-01-01 03:00:01
 2009-01-01 03:00:01.5
 2009-01-01 03:00:02
 2009-01-01 03:00:02.5
 2009-01-01 03:00:03
 2009-01-01 03:00:03.5
 2009-01-01 03:00:04
 2009-01-01 03:00:04.5
 2009-01-01 03:00:05
(11 rows)

以下查询以 1 秒为时间段在相关时间帧的开始和结束范围之间创建一个并集：

=> SELECT * FROM (
     SELECT ts FROM (
       SELECT '2009-01-01 03:00:00'::timestamp AS time FROM TickStore
       UNION
       SELECT '2009-01-01 03:00:05'::timestamp FROM TickStore) t
       TIMESERIES ts AS '1 seconds' OVER(ORDER BY time) ) AS outer_table
   LEFT OUTER JOIN inner_table ON outer_table.ts = inner_table.ts;

此并集将从左联接表返回一组与右联接表中记录相匹配的完整记录。如果查询没有找到任何匹配项，它会使用 NULL 值扩展右侧列：

         ts          |         ts          | bid
---------------------+---------------------+-----
 2009-01-01 03:00:00 |                     |
 2009-01-01 03:00:01 |                     |
 2009-01-01 03:00:02 | 2009-01-01 03:00:02 |   1
 2009-01-01 03:00:03 |                     |
 2009-01-01 03:00:04 | 2009-01-01 03:00:04 |   2
 2009-01-01 03:00:05 |                     |
(6 rows)