PostgreSQL中识别数据集峰值谷值的内置函数或金融扩展查询

嘿，我懂你要做的是揪出折线图里交替出现的峰值和谷值，PostgreSQL确实没有像部分其他数据库那样内置的模式匹配函数直接搞定这事，但咱们可以用窗口函数自己实现逻辑，也有一些金融/时序扩展能帮上忙。下面分两种方式给你唠唠：

一、用窗口函数自定义实现 #

这种方法不用额外装扩展，全靠PostgreSQL自带功能，核心是用窗口函数对比当前点和前后点的值，再用状态机思路筛选出符合交替规则的峰谷点。

步骤1：准备测试数据 #

先把你的补充数据集导入表中：

CREATE TABLE test_data (
    data INT PRIMARY KEY,
    value NUMERIC
);

INSERT INTO test_data VALUES
(1,23), (2,24), (3,25), (4,22), (5,21), (6,20),
(7,30), (8,32), (9,36), (10,22), (11,25), (12,28),
(13,30), (14,45), (15,30), (16,34), (17,35);

步骤2：标记峰、谷和普通点 #

用LAG()和LEAD()窗口函数获取前后值，给每个点打上类型标签：

WITH labeled_points AS (
    SELECT
        data,
        value,
        CASE
            -- 第一个点标记为起始点
            WHEN LAG(data) OVER (ORDER BY data) IS NULL THEN 'start'
            -- 最后一个点标记为结束点
            WHEN LEAD(data) OVER (ORDER BY data) IS NULL THEN 'end'
            -- 峰值：当前值大于前后值
            WHEN value > LAG(value) OVER (ORDER BY data) AND value > LEAD(value) OVER (ORDER BY data) THEN 'highest high'
            -- 谷值：当前值小于前后值
            WHEN value < LAG(value) OVER (ORDER BY data) AND value < LEAD(value) OVER (ORDER BY data) THEN 'lowest low'
            ELSE 'normal'
        END AS point_type
    FROM test_data
),

步骤3：筛选交替的峰谷点 #

接下来按交替规则筛选：起始点之后，先找第一个极值，然后交替找谷/峰，直到结束点。这里用SUM()窗口函数跟踪当前要找的类型：

filtered_points AS (
    SELECT
        data,
        value,
        point_type,
        -- 跟踪状态：1=找峰，2=找谷，起始后默认先找第一个极值
        SUM(
            CASE
                WHEN point_type IN ('start', 'highest high', 'lowest low') THEN
                    CASE
                        WHEN point_type = 'start' THEN 1
                        WHEN point_type = 'highest high' THEN 2
                        WHEN point_type = 'lowest low' THEN 1
                    END
                ELSE 0
            END
        ) OVER (ORDER BY data) AS state
    FROM labeled_points
    WHERE point_type != 'normal' OR point_type IN ('start', 'end')
)
-- 最终筛选符合交替规则的点
SELECT
    CONCAT(point_type, ' (', data, ',', value, ')') AS result
FROM filtered_points
WHERE
    point_type = 'start'
    OR point_type = 'end'
    OR (state = 1 AND point_type = 'highest high')
    OR (state = 2 AND point_type = 'lowest low')
ORDER BY data;

运行这段SQL后，就能得到你预期的输出：

start (1,23)
highest high (3,25)
lowest low (6,20)
highest high (9,36)
lowest low (10,22)
highest high (14,45)
lowest low (15,30)
end (17,35)

二、金融/时序扩展推荐 #

要是不想自己写复杂SQL，可以考虑装PostgreSQL的第三方扩展：

• TimescaleDB：专注时间序列的扩展，它的time_bucket()函数结合聚合函数能快速定位区间内的峰谷，虽然没有直接的交替峰谷函数，但能简化不少逻辑。
• pg_ta_lib：基于TA-Lib（专业技术分析库）的扩展，TA-Lib本身就有现成的极值识别函数，能直接调用获取你要的摆动点。

不过这些扩展需要额外安装，你可以用PostgreSQL的CREATE EXTENSION命令来安装（前提是系统层面已经装了对应依赖库）。

内容的提问来源于stack exchange，提问作者Gee