使用 Timescale查询数据

执行高级分析查询

Timescale Cloud：性能、规模、企业级

自托管产品

MST

您可以将 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

计算差值

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

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 函数。时间桶接受任意时间间隔以及可选偏移量，并返回桶的开始时间。例如：

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；第一个用于低于最小阈值 60 的值，中间五个 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 的交易数据。

创建一个查询，用于查询九月份每天交易的资产“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 函数，该函数根据给定时间间隔在时间范围内生成一系列时间桶。在此示例中，间隔为一天，涵盖九月份：

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
) WITH (
  tsdb.hypertable,
  tsdb.partition_column='time'
);

如果您自托管 TimescaleDB v2.19.3 及更早版本，请创建一个 PostgreSQL 关系表，然后使用 create_hypertable 进行转换。然后，通过调用 ALTER TABLE 启用 hypercore。

您可以使用第一个表（提供一组独特的车辆）来对位置表执行 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 超表执行 松散索引扫描，尽管这需要更多的计算资源。或者，您可以通过对查询进行结构化，使 TimescaleDB 可以使用其 SkipScan 功能，从而加快 SELECT DISTINCT 查询的速度。

关键词

查询超函数分析

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

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

相关内容

查询数据
使用 SELECT 查询 Timescale Cloud 中的数据，通过 SkipScan 加快查询速度，并运行高级分析——同时从 Timescale Cloud 架构和查询计划中获得额外的好处
超函数
Timescale Cloud 超函数是运行实时分析工作负载的终极工具，可确保您以近乎零的延迟和零复杂性获得所需的结果
SELECT 数据
使用标准 SELECT 命令从 Timescale Cloud 中的超表查询数据
使用 SkipScan 加快 DISTINCT 查询速度
通过设置超表以利用 SkipScan 来加快 DISTINCT 查询速度
查询故障排除
针对查询中常见问题的故障排除建议
关于查询数据
在 Timescale Cloud 中查询数据就像在 PostgreSQL 中查询数据一样，这意味着您可以重用现有 PostgreSQL 数据库中的查询