[PostgreSQL] 중복된 row 중 이전 데이터, 다음 데이터 확인하기

컬럼을 지정한 다음 중복된 데이터라면 그 이전 데이터의 값과 다음 데이터의 값을 확인하려고 할 때 사용할 수 있습니다.

테스트 데이터

먼저 테스트 대상 테이블입니다.

Client | Rate | StartDate|EndDate A | 1000 | 2005-1-1 |2005-12-31 A | 2000 | 2006-1-1 |2006-12-31 A | 3000 | 2007-1-1 |2007-12-31 B | 5000 | 2006-1-1 |2006-12-31 B | 8000 | 2008-1-1 |2008-12-31 C | 2000 | 2006-1-1 |2006-12-31

다음은 원하고자 하는 결과 테이블입니다.

Client | Rate | StartDate|EndDate |Pre Rate | Pre StartDate |Pre EndDate A | 1000 | 2005-1-1 |2005-12-31 | | | A | 2000 | 2006-1-1 |2006-12-31 | 1000 | 2005-1-1 |2005-12-31 A | 3000 | 2007-1-1 |2007-12-31 | 2000 | 2006-1-1 |2006-12-31 B | 5000 | 2006-1-1 |2006-12-31 | | | B | 8000 | 2008-1-1 |2008-12-31 | 5000 | 2006-1-1 |2006-12-31 C | 2000 | 2006-1-1 |2006-12-31

결과

window function을 사용하여 아래와 같이 작성합니다.

SELECT client, rate, startdate, enddate, lag(rate) over client_window as pre_rate, lag(startdate) over client_window as pre_startdate, lag(enddate) over client_window as pre_enddate FROM the_table WINDOW client_window as (partition by client order by startdate) ORDER BY client, stardate;

해당 결과는 이전 데이터만 확인하는 lag() 함수를 사용했습니다. 만약 다음 데이터를 확인하고 싶은 경우엔 lead()함수를 사용하면 됩니다.

응용

이전 데이터 뿐만 아니라, 이전전의 데이터와 다음다음의 데이터를 확인하고 싶으면 아래와 같이 작성합니다. (위의 테스트 테이블과 별개의 쿼리)

select *, lag(previous_id) over w as pre_previous_id, lag(previous_title) over w as pre_previous_title, lead(next_id) over w as next_next_id, lead(next_title) over w as next_next_title from ( select *, lag(id) over w as previous_id, lag(title) over w as previous_title, lead(id) over w as next_id, lead(title) over w as next_title from the_table window w as (partition by category order by insert_time) ) as onestep_table window w as (partition by category order by insert_time)