使用 Timescale查询数据

执行高级分析查询

您可以使用 Timescale 执行各种分析查询。其中一些查询是原生 PostgreSQL，而另一些则是 Timescale 提供的附加函数。本节包含最常见和最有用的分析查询。

计算中位数和百分位数

使用 percentile_cont 计算百分位数。您也可以使用此函数查找第五十百分位数或中位数。例如，要查找中位温度

SELECT percentile_cont(0.5)
  WITHIN GROUP (ORDER BY temperature)
  FROM conditions;

您还可以使用 Timescale 工具包查找 近似百分位数。

计算累积和

使用 sum(sum(column)) OVER(ORDER BY group) 查找累积和。例如

SELECT location, sum(sum(temperature)) OVER(ORDER BY location)
  FROM conditions
  GROUP BY location;

计算移动平均值

对于简单的移动平均值，请使用 OVER 窗口函数跨越一定数量的行，然后计算这些行的聚合函数。例如，要查找设备的平滑温度，方法是平均最近十次读数

SELECT time, AVG(temperature) OVER(ORDER BY time
      ROWS BETWEEN 9 PRECEDING AND CURRENT ROW)
    AS smooth_temp
  FROM conditions
  WHERE location = 'garage' and time > NOW() - INTERVAL '1 day'
  ORDER BY time DESC;

计算值的增加

要计算值的增加，您需要考虑计数器重置。如果主机重新启动或容器重新启动，可能会发生计数器重置。此示例查找已发送的字节数，并考虑计数器重置

SELECT
  time,
  (
    CASE
      WHEN bytes_sent >= lag(bytes_sent) OVER w
        THEN bytes_sent - lag(bytes_sent) OVER w
      WHEN lag(bytes_sent) OVER w IS NULL THEN NULL
      ELSE bytes_sent
    END
  ) AS "bytes"
  FROM net
  WHERE interface = 'eth0' AND time > NOW() - INTERVAL '1 day'
  WINDOW w AS (ORDER BY time)
  ORDER BY time

计算变化率

与 增加 一样，速率适用于单调递增计数器的场景。如果您的采样间隔是可变的，或者您在不同系列之间使用不同的采样间隔，则将值归一化为通用时间间隔有助于使计算出的值具有可比性。此示例查找每秒发送的字节数，并考虑计数器重置

SELECT
  time,
  (
    CASE
      WHEN bytes_sent >= lag(bytes_sent) OVER w
        THEN bytes_sent - lag(bytes_sent) OVER w
      WHEN lag(bytes_sent) OVER w IS NULL THEN NULL
      ELSE bytes_sent
    END
  ) / extract(epoch from time - lag(time) OVER w) AS "bytes_per_second"
  FROM net
  WHERE interface = 'eth0' AND time > NOW() - INTERVAL '1 day'
  WINDOW w AS (ORDER BY time)
  ORDER BY time

计算增量

在许多监控和物联网用例中，设备或传感器报告的指标不会经常更改，任何更改都被视为异常。当您查询这些值随时间的变化时，通常不希望传输所有值，而只传输观察到更改的值。这有助于最大程度地减少发送的数据量。您可以使用窗口函数和子选择组合来实现这一点。此示例使用差异来过滤值未更改的行，并且只传输值已更改的行

SELECT time, value FROM (
  SELECT time,
    value,
    value - LAG(value) OVER (ORDER BY time) AS diff
  FROM hypertable) ht
WHERE diff IS NULL OR diff != 0;

计算组内指标的变化

要按某个字段对数据进行分组，并计算每个组内指标的变化，请使用 LAG ... OVER (PARTITION BY ...)。例如，给定一些天气数据，计算每个城市的温度变化

SELECT ts, city_name, temp_delta
FROM (
  SELECT
    ts,
    city_name,
    avg_temp - LAG(avg_temp) OVER (PARTITION BY city_name ORDER BY ts) as temp_delta
  FROM weather_metrics_daily
) AS temp_change
WHERE temp_delta IS NOT NULL
ORDER BY bucket;

将数据分组到时间段中

Timescale time_bucket 函数扩展了 PostgreSQL date_bin 函数。Time bucket 接受任意时间间隔，以及可选的偏移量，并返回时间段的开始时间。例如

SELECT time_bucket('5 minutes', time) AS five_min, avg(cpu)
  FROM metrics
  GROUP BY five_min
  ORDER BY five_min DESC LIMIT 12;

获取列中的第一个或最后一个值

Timescale first 和 last 函数允许您获取按另一个列排序的某一列的值。这通常用于聚合中。这些示例查找组的最后一个元素

SELECT location, last(temperature, time)
  FROM conditions
  GROUP BY location;
SELECT time_bucket('5 minutes', time) five_min, location, last(temperature, time)
  FROM conditions
  GROUP BY five_min, location
  ORDER BY five_min DESC LIMIT 12;

生成直方图

Timescale histogram 函数允许您生成数据的直方图。此示例定义了一个直方图，它在 60 到 85 的范围内定义了五个时间段。生成的直方图有七个箱体；第一个用于低于 60 的最小阈值的值，中间五个箱体用于范围内指定的值，最后一个用于高于 85 的值

SELECT location, COUNT(*),
    histogram(temperature, 60.0, 85.0, 5)
   FROM conditions
   WHERE time > NOW() - INTERVAL '7 days'
   GROUP BY location;

此查询输出的数据如下所示

location   | count |        histogram
------------+-------+-------------------------
 office     | 10080 | {0,0,3860,6220,0,0,0}
 basement   | 10080 | {0,6056,4024,0,0,0,0}
 garage     | 10080 | {0,2679,957,2420,2150,1874,0}

填充时间序列数据中的间隙

您可以显示选定时间范围内的记录，即使该范围的一部分不存在数据。这通常称为间隙填充，通常涉及记录任何缺失数据的空值的运算。

在此示例中，将使用交易数据，包括 time 时间戳、正在交易的 asset_code、资产的 price 和正在交易的资产的 volume。

为 9 月份每天交易的 'TIMS' 资产的交易量创建查询

SELECT
    time_bucket('1 day', time) AS date,
    sum(volume) AS volume
  FROM trades
  WHERE asset_code = 'TIMS'
    AND time >= '2021-09-01' AND time < '2021-10-01'
  GROUP BY date
  ORDER BY date DESC;

此查询输出的数据如下所示

date          | volume
------------------------+--------
 2021-09-29 00:00:00+00 |  11315
 2021-09-28 00:00:00+00 |   8216
 2021-09-27 00:00:00+00 |   5591
 2021-09-26 00:00:00+00 |   9182
 2021-09-25 00:00:00+00 |  14359
 2021-09-22 00:00:00+00 |   9855

您可以从输出中看到，没有包含 09-23、09-24 或 09-30 的记录，因为这些天没有记录交易数据。要包含每个缺失天的时间记录，您可以使用 TimescaleDB time_bucket_gapfill 函数，该函数根据给定时间间隔在时间范围内生成一系列时间段。在此示例中，时间间隔为一天，跨越 9 月份

SELECT
  time_bucket_gapfill('1 day', time) AS date,
  sum(volume) AS volume
FROM trades
WHERE asset_code = 'TIMS'
  AND time >= '2021-09-01' AND time < '2021-10-01'
GROUP BY date
ORDER BY date DESC;

此查询输出的数据如下所示

date          | volume
------------------------+--------
 2021-09-30 00:00:00+00 |
 2021-09-29 00:00:00+00 |  11315
 2021-09-28 00:00:00+00 |   8216
 2021-09-27 00:00:00+00 |   5591
 2021-09-26 00:00:00+00 |   9182
 2021-09-25 00:00:00+00 |  14359
 2021-09-24 00:00:00+00 |
 2021-09-23 00:00:00+00 |
 2021-09-22 00:00:00+00 |   9855

您还可以使用 Timescale time_bucket_gapfill 函数生成也包含时间戳的数据点。这对于需要均匀空值才能具有时间戳以准确绘制图形中的间隙的图形库很有用。在此示例中，您将在过去两周内生成 1080 个数据点，用空值填充间隙，并为每个空值赋予时间戳

SELECT
  time_bucket_gapfill(INTERVAL '2 weeks' / 1080, time, now() - INTERVAL '2 weeks', now()) AS btime,
  sum(volume) AS volume
FROM trades
WHERE asset_code = 'TIMS'
  AND time >= now() - INTERVAL '2 weeks' AND time < now()
GROUP BY btime
ORDER BY btime;

此查询输出的数据如下所示

btime          | volume
------------------------+----------
 2021-03-09 17:28:00+00 |  1085.25
 2021-03-09 17:46:40+00 |  1020.42
 2021-03-09 18:05:20+00 |
 2021-03-09 18:24:00+00 |  1031.25
 2021-03-09 18:42:40+00 |  1049.09
 2021-03-09 19:01:20+00 |  1083.80
 2021-03-09 19:20:00+00 |  1092.66
 2021-03-09 19:38:40+00 |
 2021-03-09 19:57:20+00 |  1048.42
 2021-03-09 20:16:00+00 |  1063.17
 2021-03-09 20:34:40+00 |  1054.10
 2021-03-09 20:53:20+00 |  1037.78

通过向前推移最后一个观测值来填充间隙

如果您的数据集合只在实际值更改时记录行，那么您的可视化可能仍然需要所有数据点才能正确显示结果。在这种情况下，您可以向前推移最后一个观测值来填充间隙。例如

SELECT
  time_bucket_gapfill(INTERVAL '5 min', time, now() - INTERVAL '2 weeks', now()) as 5min,
  meter_id,
  locf(avg(data_value)) AS data_value
FROM my_hypertable
WHERE
  time > now() - INTERVAL '2 weeks'
  AND meter_id IN (1,2,3,4)
GROUP BY 5min, meter_id

查找每个唯一项目的最后一个点

您可以查找数据库中每个唯一项目的最后一个点。例如，每个物联网设备的最后一次记录的测量值、每个资产跟踪中项目的最后一个位置或证券的最后一个价格。为了最大程度地减少要搜索最后一个点的數據量，标准方法是使用时间谓词来严格限制要遍历的时间量或块数。这种方法只有在所有项目在时间范围内至少有一条记录的情况下才有效。更稳健的方法是使用最后一个点查询来确定每个唯一项目的最后一条记录。

在此示例中，这对于资产跟踪或车队管理很有用，您为要跟踪的每辆车创建一个元数据表，以及第二个时间序列表，其中包含车辆在给定时间的位置

CREATE TABLE vehicles (
  vehicle_id INTEGER PRIMARY KEY,
  vin_number CHAR(17),
  last_checkup TIMESTAMP
);


CREATE TABLE location (
  time TIMESTAMP NOT NULL,
  vehicle_id INTEGER REFERENCES vehicles (vehicle_id),
  latitude FLOAT,
  longitude FLOAT
);


SELECT create_hypertable('location', by_range('time'));

您可以使用第一个表（它提供一组不同的车辆）对位置表执行 LATERAL JOIN

SELECT data.* FROM vehicles v
  INNER JOIN LATERAL (
    SELECT * FROM location l
      WHERE l.vehicle_id = v.vehicle_id
      ORDER BY time DESC LIMIT 1
  ) AS data
ON true
ORDER BY v.vehicle_id, data.time DESC;


            time            | vehicle_id | latitude  |  longitude
----------------------------+------------+-----------+-------------
 2017-12-19 20:58:20.071784 |         72 | 40.753690 |  -73.980340
 2017-12-20 11:19:30.837041 |        156 | 40.729265 |  -73.993611
 2017-12-15 18:54:01.185027 |        231 | 40.350437 |  -74.651954

这种方法要求保留一个单独的表来存放不同的项目标识符或名称。您可以通过从超级表到元数据表的外部键来实现这一点，如示例中的 REFERENCES 定义所示。

元数据表可以通过业务逻辑填充，例如，当车辆首次在系统中注册时。或者，您可以在对超级表执行插入或更新时使用触发器动态地填充它。例如

CREATE OR REPLACE FUNCTION create_vehicle_trigger_fn()
  RETURNS TRIGGER LANGUAGE PLPGSQL AS
$BODY$
BEGIN
  INSERT INTO vehicles VALUES(NEW.vehicle_id, NULL, NULL) ON CONFLICT DO NOTHING;
  RETURN NEW;
END
$BODY$;


CREATE TRIGGER create_vehicle_trigger
  BEFORE INSERT OR UPDATE ON location
  FOR EACH ROW EXECUTE PROCEDURE create_vehicle_trigger_fn();

您也可以通过对 location 超级表执行 松散索引扫描 来实现此功能，尽管这需要更多的计算资源。或者，您可以通过将 SELECT DISTINCT 查询结构化为 TimescaleDB 可以使用其 SkipScan 功能的方式来加快查询速度。

关键词

查询超级函数分析

在这个页面上发现问题了吗？报告问题 或者 在 GitHub 中编辑此页面。

上一页使用 SkipScan 执行 DISTINCT 查询下一页故障排除

相关内容

查询数据
在 Timescale 中查询数据
超函数
超函数帮助您快速执行关键的时间序列查询
SELECT 数据
使用标准 SELECT 命令从超表中查询数据
使用 SkipScan 加速 DISTINCT 查询
通过设置超表来利用 SkipScan 加速 DISTINCT 查询
关于查询数据
如何在 Timescale 中查询数据
关于 Timescale 超函数
了解 Timescale 超函数，它们有助于数据分析