postgres prev/next 컬럼 select 하기

게시판 공부중에, 이전/다음 게시글을 불러오는 방법에 대해 생각해 보았는데,
지금 수준으로는 db에서 무식하게 불러오는 수준밖에 떠오르지 않았기 때문에, 좀 더 원할한 솔루션이 있을거라 막연하게 판단하여 검색을 했었고, 좋은 예시를 하나 찾아서 기록을 위해 남겨둡니다.






우선 datebase는
create table blog(
 num int primary key,
 title text
)
insert into blog values(1,'hello world1');
insert into blog values(3,'hello world2');
insert into blog values(4,'hello world3');
insert into blog values(6,'hello world4');


이며 게시글 3번을 눌렀을때, 이전글인 1번과 4번을 출력하고 싶습니다.



sql문에서 select문을 두번 사용하는데, 처음은 각 row의 이전/다음 num값을 출력합니다.
select
  num,
  lag(num) over (order by num desc rows between current row and unbounded following) as prev,
  lead(num) over(order by num desc rows between current row and unbounded following) as nextt
from blog;

Postgres Function 에 따르면,
lag 는 offset 된 값중에서, 현제 선택된 행의 값의 이전 row를 불러오며
lead 는 반대로 이후 값의 row를 불러온다 정도로 해석됩니다.
over는 window function을 사용할 때에 반드시 따라오게 되는 문법적 용어이며,
그 뜻은, 정렬의 방법을 정의한다.
해당 컬럼의 정렬방식은 num의 desc(오름차순) 정렬이며,
between은 풀어쓰자면 where a > 1 or a < 3 처럼 1과 3사이 로 쓸 수 있다고 한다.
between 조건1 and 조건2 를 작성하여 조건1과 조건2 사이의 값을 저의 할 수 있다고 한다.
current row 는 영어 그대로 이해했고,
and절 뒤의 unbounded following 은 모든 파티션을 위해서 쓴다고 합니다.
보통은 order by 절을 사용하면 모든 파티션을 검색하는것 이지만, 이번 케이스는 order by 문이 쓰였기 때문에, 모두 라는것을 저의하기 위해 쓰였다 합니다.

이제 prev 와 next컬럼을 얻었으니, 3번 게시물의 prev 와 next가 무엇인지 알기 위해 sql을 한번 더 날립니다.
select * fron blog where 3 in (prev, nextt);
where 절의 in은 = 과 동일한 역활을 합니다.
prev = 3 or nextt = 3 이라고도 굳이 풀어 쓸 수 있습니다.

이제 이것들을 하나의 sql문으로 작성하면 아래와 같이 됩니다.
select *
from (
 select
  num,
  lag(num) over(order by num desc rows between current row and unbounded following) as prev,
  lead(num) over(order by num desc rows between current row and unbounded following) as nextt
 from blog
) as x
where 3 in (prev,next);


실제 구동 예시




sql에서 이정도로 깊게 파고들어 본 적이 없었는데(맨날 select 2중..끽해야 3중이 전부), 어려우면서도 복잡하게 코드로 쓸걸 sql로 쉽게 작성이 가능한 걸 보니 배울 의지가 점점 생깁니다.
실질적으로 대량의 데이터를 가지고 필드테스트를 하면 위의 방식도 맞는게 아닐지 도 모르겠으나, 간만에 흥미로운 공부가 되었습니당.