PostgreSQL：如何使用窗口函数跳过特定值的前一行

需求说明 #

需要新增_prev_element字段，显示当前行之前最近的非30的score值，所有连续的30行都要跳过，直接取最近的有效非30值。普通的lag(score) over(order by id)只能取上一行值，无法满足跳过多行30的需求。

样本数据 #

id|score|
--+-----+
 1|   10|
 2|   20|
 3|   30|
 4|   40|
 5|   50|
 6|   60|
 7|   30|
 8|   30|
 9|   90|
10|  100|

期望输出 #

id|score|_prev_element|
--+-----+-------------+
 1|   10|         NULL|
 2|   20|           10|
 3|   30|           20|
 4|   40|           20|
 5|   50|           40|
 6|   60|           50|
 7|   30|           60|
 8|   30|           60|
 9|   90|           60|
10|  100|           90|

解决方案 #

方法1：分组关联法（通用多数数据库） #

通过给非30行分组，再关联前序组的有效值：

WITH grouped_data AS (
    SELECT 
        id,
        score,
        -- 给非30行分配递增组ID，30行继承前一个非30行的组ID
        SUM(CASE WHEN score != 30 THEN 1 ELSE 0 END) OVER (ORDER BY id) AS group_id
    FROM your_table
),
group_last_values AS (
    SELECT 
        group_id,
        MAX(CASE WHEN score !=30 THEN score END) AS last_non_30_score
    FROM grouped_data
    GROUP BY group_id
)
SELECT 
    gd.id,
    gd.score,
    glv_prev.last_non_30_score AS _prev_element
FROM grouped_data gd
LEFT JOIN group_last_values glv_prev ON gd.group_id -1 = glv_prev.group_id
ORDER BY gd.id;

原理：

1. 分组标记：用累加窗口函数给每个非30行生成新的组ID，连续的30行会被归到最近的非30行所在组。
2. 提取组内有效值：每个分组的有效值就是该组第一个非30行的score，用MAX提取即可。
3. 关联前序组：通过当前组ID减1，关联到前一个组的有效值，得到目标字段。

方法2：LAST_VALUE简化法（支持窗口函数的数据库） #

直接用LAST_VALUE结合条件窗口，自动跳过NULL（即score=30的行）：

SELECT
    id,
    score,
    LAST_VALUE(CASE WHEN score !=30 THEN score END) IGNORE NULLS OVER (
        ORDER BY id 
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS _prev_element
FROM your_table;

原理：

窗口范围设置为从第一行到当前行的前一行，LAST_VALUE会忽略CASE返回的NULL（score=30时返回NULL），自动取范围内最后一个非NULL的score值，也就是最近的非30值。

注：部分数据库（如MySQL）默认忽略NULL，可省略IGNORE NULLS；PostgreSQL需要显式添加该关键字。

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