sql - PostgreSQL 上一个和下一个组值

Question

问题如下:

假设，我有一个这样的 View 表(它是我正在使用的表的子样本):

| col1 | col2 |
|------|------|
|   1  |  a2  |
|   1  |  b2  |
|   2  |  c2  |
|   2  |  d2  |
|   2  |  e2  |
|   1  |  f2  |
|   1  |  g2  |
|   3  |  h2  |
|   1  |  j2  |

我需要添加两个新列

• prev 包含 col1 中的前一个值不等于当前值
• next 包含 col1 中的下一个值不等于当前值

如果没有以前的值，prev 应该包含当前 col1 的值，如果 next 应该包含当前值不存在下一个值。

结果应具有以下形式:

| col1 | col2 | prev | next |
|------|------|------|------|
|   1  |  a2  |   1  |   2  |
|   1  |  b2  |   1  |   2  |
|   2  |  c2  |   1  |   1  |
|   2  |  d2  |   1  |   1  |
|   2  |  e2  |   1  |   1  |
|   1  |  f2  |   2  |   3  |
|   1  |  g2  |   2  |   3  |
|   3  |  h2  |   1  |   1  |
|   1  |  j2  |   3  |   1  |

我将不胜感激。

Answer 1

您可以结合使用窗口函数 lead、lag、first_value、last_value 和 求和。

select
    t.col1, t.col2, n,
    coalesce(first_value(y) over (partition by x order by col2), col1) prev_val,
    coalesce(last_value(y2) over (partition by x order by col2 
        rows between current row and unbounded following), col1) next_val
from (
    select
        t.*,
        case when col1 <> lag(col1) over (order by col2) then lag(col1) over (order by col2) end y,
        case when col1 <> lead(col1) over (order by col2) then lead(col1) over (order by col2) end y2,
        sum(n) over (order by col2) x
    from (
        select
            t.*,
            case when col1 <> lag(col1) over (order by col2) then 1 else 0 end n
        from t
    ) t
) t;

它找到每组行的超前/滞后。