postgreSQL查找具有相邻编号的行

Question

我有一个表，看起来或多或少像这样：

+----------+-------+
| position | group |
+----------+-------+
| 1        | a     |
+----------+-------+
| 5        | b     |
+----------+-------+
| 6        | b     |
+----------+-------+
| 7        | c     |
+----------+-------+
| 8        | b     |
+----------+-------+

我想要SELECT在同一组中具有相邻位置的行的组合。例如，给定上表，查询的输出应该是：

+----------+-------+
| position | group |
+----------+-------+
| 5        | b     |
+----------+-------+
| 6        | b     |
+----------+-------+

性能有点问题，因为表有15亿行，但位置和组都是索引的，所以速度相对较快。关于如何编写此查询，有什么建议吗？我不知道从哪里开始，因为我不知道如何编写涉及多行输出的WHERE语句。

Answer 1

只需使用lag()和lead():

select t.*
from (select t.*,
lag(group) over (order by position) as prev_group,
lead(group) over (order by position) as next_group
from t
) t
where prev_group = group or next_group = group;

如果你所说的"相邻"是指位置相差一(而不是最接近的值(，那么我会选择exists:

select t.*
from t
where exists (select 1 from t t2 where t2.group = t.group and t2.position = t.position - 1) or
exists (select 1 from t t2 where t2.group = t.group and t2.position = t.position + 1);

Answer 2

select distinct T1.position, T2.group from
T as T1
inner join
T as T2
on(T1.group=T2.group and t2.position=t1.position+1)

Answer 3

我会使用这样的子选择：

select * from mytable m1
where (select count(*) from mytable m2 where m2.position = m1.position + 1 and m2.group = m1.group) > 0