技术分析数据库开发：如何查找列中的standing off value？

我正在构建一个技术分析数据库，目前卡在了如何从列中筛选出Standing Off Value的问题上，完全不知道从哪入手。

测试表数据 #

期望筛选结果 #

解决方案 #

从示例数据和结果来看，Standing Off Value指的是偏离整体趋势的孤立值——比如起始的低位点，或是局部序列中的低谷值。可以利用数据库的窗口函数（LAG/LEAD）来实现筛选，以下是通用SQL示例（以PostgreSQL为例）：

SELECT ID, Values
FROM (
    SELECT 
        ID,
        Values,
        -- 获取前一行的Values值
        LAG(Values) OVER (ORDER BY ID) AS prev_val,
        -- 获取后一行的Values值
        LEAD(Values) OVER (ORDER BY ID) AS next_val,
        -- 获取后两行的Values值，用于判断首行趋势
        LEAD(Values, 2) OVER (ORDER BY ID) AS next_next_val
    FROM test_table
) AS sub_query
WHERE 
    -- 首行判断：后续连续两个值都大于当前值，说明当前值偏离后续上涨趋势
    (prev_val IS NULL AND next_val > Values AND next_next_val > next_val)
    -- 中间行判断：当前值同时小于前后值，形成局部低谷
    OR (prev_val IS NOT NULL AND next_val IS NOT NULL AND Values < prev_val AND Values < next_val);

逻辑说明 #

1. 首行（ID=1）：无前置值，通过判断后续连续两个值均上涨，确定当前值是偏离趋势的孤立点；
2. 中间行（ID=7）：通过对比前后值，确认当前值是局部低谷，属于Standing Off Value。

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