可扩展性

PostgreSQL 扩展：PL/pgSQL

PL/pgSQL 是 PostgreSQL 数据库系统中的一种可加载过程语言。它允许开发人员使用比标准 SQL 更强大、更灵活的语言编写复杂的逻辑和函数。

让我们学习如何安装和使用它。

什么是 PL/pgSQL？

PL/pgSQL 是一种块结构语言，它提供控制结构（如循环和条件语句），以及复杂的数据类型和其他编程语言特性。它特别适用于需要复杂计算或难以用标准 SQL 完成的任务。

安装 PL/pgSQL 扩展

在使用 PL/pgSQL 之前，您必须安装它。方法如下：

1. 连接到要安装扩展的 PostgreSQL 数据库。在 Timescale 上，您可以通过转到服务概述中的 操作 > 扩展 来找到可用的扩展，这也会为您提供安装说明。

2. 运行以下 SQL 命令：

CREATE EXTENSION IF NOT EXISTS plpgsql;

此命令安装 PL/pgSQL 扩展（如果尚未安装）。

使用 PL/pgSQL 扩展

要使用 PL/pgSQL，您需要使用 PL/pgSQL 语言编写函数，然后从您的 SQL 查询中调用它们。以下是一个简单的 PL/pgSQL 函数示例：

CREATE FUNCTION add_numbers(integer, integer) RETURNS integer
AS $$
BEGIN
RETURN $1 + $2;
END;
$$ LANGUAGE plpgsql;

此函数接收两个整数作为输入，并返回它们的总和。您可以像这样从 SQL 查询中调用此函数：

SELECT add_numbers(5, 3);

时间序列数据中的 PL/pgSQL 扩展用例

PL/pgSQL 特别适用于时间序列数据，因为您经常需要对一系列数据点执行复杂计算。例如，您可以使用 PL/pgSQL 计算移动平均值、执行时间序列预测或检测数据中的异常值。

将 PL/pgSQL 与 Timescale 和时间序列数据一起使用

如果您使用 Timescale 来存储时间序列数据，您可以使用 PL/pgSQL 编写复杂的查询和计算。例如，您可以使用 PL/pgSQL 编写一个函数，该函数计算时间序列数据集的移动平均值：

CREATE OR REPLACE FUNCTION moving_average(time_interval INTERVAL)
RETURNS TABLE (“time” TIMESTAMPTZ, avg DOUBLE PRECISION)
AS $$
DECLARE
   start_time TIMESTAMPTZ;
   end_time TIMESTAMPTZ;
BEGIN
   FOR start_time, end_time IN
   SELECT time_bucket(time_interval, time) AS start_time,
time_bucket(time_interval, time) + time_interval AS end_time
FROM my_table
 LOOP
      RETURN QUERY
      SELECT start_time, AVG(value)
      FROM my_table
      WHERE time >= start_time AND time < end_time;
   END LOOP;
END;
$$ LANGUAGE plpgsql;

此函数计算 my_table 中 value 列的移动平均值，使用大小为 time_interval 的滑动窗口。以下是 moving_average 函数的示例测试设置：

1. 首先，我们将创建一个包含一些示例数据的虚拟表 my_table。

2. 然后，我们将使用指定的时间间隔调用您的 moving_average 函数。

3. 最后，我们将查询结果以观察计算出的移动平均值。

-- 1. Setup: Create the my_table and insert some sample data
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table (
    time TIMESTAMPTZ,
    value DOUBLE PRECISION
);

-- Let's insert some sample data. Imagine this data represents some sensor readings taken every 10 minutes.
INSERT INTO my_table (time, value) VALUES
('2023-08-21 08:00:00', 5),
('2023-08-21 08:10:00', 6),
('2023-08-21 08:20:00', 5.5),
('2023-08-21 08:30:00', 6.5),
('2023-08-21 08:40:00', 7),
('2023-08-21 08:50:00', 6.5);


-- 2. Test: Call the moving_average function for a 30-minute time interval.
-- This will average the values over each 30-minute period.
SELECT * FROM moving_average('30 minutes'::INTERVAL) GROUP BY 1, 2 ORDER BY 1 ;
-- The result should be:
-- '2023-08-21 08:00:00', 5.5   (Average of 5, 6, 5.5)
-- '2023-08-21 08:30:00', 6.67 (Average of 6.5, 7, 6.5)

上面的测试设置清楚地展示了如何使用 moving_average 函数。您将能够观察到给定样本数据和指定时间间隔的计算出的移动平均值。

了解更多

Timescale 是一款云原生、高性能数据库，它不仅建立在 PostgreSQL 之上，而且运行和使用起来也和 PostgreSQL 一样，但它提供了出色的可扩展性。在此处了解为什么 Timescale 是 时间序列数据的最佳数据库，以及如何无限地扩展它。