PostgreSQL的先前和下一组值

问题描述：

假设，我有这样视图的表（它是我的工作表中的一个子样品）：

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

我需要添加两个新列

prev包含col1以前的值不等于当前
含有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 |

我会很感激的任何帮助。

使用lag（）和lead（）over（order by）窗口函数 –

https://www.postgresql.org/docs/current/static/tutorial-window.html –

SQL表示代表* unordered *集合。没有“上一个”或“下一个”值，除非列指定了排序。您的数据中没有任何列看起来像排序数据的明显列。 –

答

你可以试试这个使用的窗口功能lead，lag，first_value，last_value和sum组合。

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;

它发现每行组的行数/滞后。

答

如果我假设你有一个指定排序的id列，那么这是可能的。我不确定这很容易用窗口函数来表达。

你可以使用相关子查询：

select t.*, 
     (select t2.col1 
     from t t2 
     where t2.id < t.id and t2.col1 <> t.col1 
     order by t2.id desc 
     fetch first 1 row only 
     ) as prev_col1, 
     (select t2.col1 
     from t t2 
     where t2.id > t.id and t2.col1 <> t.col1 
     order by t2.id asc 
     fetch first 1 row only 
     ) as prev_col2 
from t;

您可以缺少一个和下一个值添加coalece()。这不是问题的有趣部分。

答

WITH cte AS (
SELECT row_number() over() rowid, * 
    FROM unnest(array[1,1,2,2,2,1,1,3,1], array['a2','b2','c2','d2','e2','f2','g2','h2','j2']) t(col1,col2) 
) 
SELECT t.col1, 
     t.col2, 
     COALESCE(prev.col1,t.col1) prev, 
     COALESCE("next".col1,t.col1) "next" 
    FROM cte t 
    LEFT JOIN LATERAL (SELECT prev.col1 
         FROM cte prev 
         WHERE prev.rowid < t.rowid 
          AND prev.col1 != t.col1 
         ORDER BY prev.rowid DESC 
         LIMIT 1 
        ) prev ON True 
    LEFT JOIN LATERAL (SELECT "next".col1 
         FROM cte "next" 
         WHERE "next".rowid > t.rowid 
          AND "next".col1 != t.col1 
         ORDER BY "next".rowid ASC 
         LIMIT 1 
        ) "next" ON True

PostgreSQL的先前和下一组值

相关推荐