使用 Timescale查询数据

执行高级分析查询

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

计算中位数和百分位数

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

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

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

计算累积总和

使用 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

计算增量

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

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 的范围内定义。生成的直方图有七个 bin；第一个 bin 用于低于 60 的最小阈值的值，中间五个 bin 用于规定范围内的值，最后一个 bin 用于高于 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

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

您可以查找数据库中每个唯一项的最后一个点。例如，来自每个 IoT 设备的最后记录的测量值、资产跟踪中每个项目的最后位置或证券的最后价格。最大限度地减少要搜索的最后一个点的数据量的标准方法是使用时间谓词来严格限制要遍历的时间量或块数。除非所有项目在时间范围内都至少有一条记录，否则此方法不起作用。更稳健的方法是使用最后一个点查询来确定每个唯一项的最后一条记录。

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

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();

您也可以通过执行松散索引扫描来实现此功能，而无需单独的元数据表，但这需要更多计算资源。或者，您可以通过构造 SELECT DISTINCT 查询来加速它们，以便 TimescaleDB 可以使用其SkipScan 功能。

关键词

查询超级函数分析

在此页面上发现问题？报告问题 或 在 GitHub 上编辑此页面。

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

相关内容

查询数据
在 Timescale Cloud 中使用 SELECT 查询数据，使用 SkipScan 加速查询，并运行高级分析 - 所有这些都同时获得 Timescale Cloud 架构和查询计划的额外好处
超级函数
运行实时分析工作负载的终极工具，Timescale Cloud 超级函数确保您以接近零延迟和零复杂性获得所需内容
SELECT 数据
在 Timescale Cloud 中使用标准 SELECT 命令从超表查询数据
使用 SkipScan 加速 DISTINCT 查询
通过设置超表以利用 SkipScan 来加速 DISTINCT 查询
关于查询数据
在 Timescale Cloud 中查询数据的工作方式与在 PostgreSQL 中查询数据的工作方式完全相同，这意味着您可以重用来自另一个 PostgreSQL 数据库的现有查询
关于 Timescale 超级函数
Timescale Cloud 超级函数使您能够在时间序列和其他数据上运行复杂的实时分析。了解什么是超级函数以及如何在您的服务中使用它们