MySQL에서 이전 레코드의 값을 참조하기. 이전 레코드와 비교하는 방법. MySQL 5.7 방식과 MySQL 8 방식

DB 데이터를 조회할 때 이전 레코드와의 차이를 구하고 싶다면 어떻게 하면 좋을까요?

일단 조회 한 다음에 이전 레코드와의 차이를 구하는 프로그램을 작성하는 방법이 있겠습니다.

그런데 그냥 DB 쿼리로 한 번에 확인할 수는 없을까요? SQL로 이전 레코드와 비교하는 방법을 알아보겠습니다.

 

다음과 같은 데이터가 있다고 가정합니다. 최소한의 데이터만 넣었는데 published는 기사를 발행한 시각(단위: 초)입니다. 테이블명은 articles입니다.

위의 데이터를 생성하는 쿼리문입니다.

# test dataset
CREATE TABLE articles (
    id INTEGER NOT NULL auto_increment,
    published bigint,
    PRIMARY KEY (`id`)
    )
    ;
    
CREATE index idx_published on articles(published);

INSERT INTO articles VALUES
(1, 0),
(2, 10000),
(3, 20000),
(4, 21000),
(5, 22000),
(6, 30000),
(7, 100000);

 

변수를 이용한 방식 - MySQL 5.7 방식

변수를 이용해서 손쉽게 이전 값을 다음 레코드에서 참조할 수 있습니다. 주의할 점은 변수를 미리 null로 초기화 해 주어야 한다는 것입니다. 초기화 하지 않으면 처음 실행시 변수가 제대로 할당되지 않고, 두 번째 실행시에는 마지막에 조회된 값이 첫번째 데이터에서 출력되어 버리는 문제가 발생합니다.

# prev published
set @prevPublished := null;

SELECT id, 
       published,
       @prevPublished as prev_published,
       @prevPublished := published
FROM articles
ORDER BY published asc

published를 조회하고 @prevPublished 변수에 published값을 할당하고 있습니다.

prev_published 를 출력해 보면 다음과 같이 이전값이 잘 나오고 있는 것을 확인할 수 있습니다. @prevPublished에 할당되는 값도 나오고 있네요.

 

published 에 @prevPublished를 빼주면 이전 값과의 차이를 구할 수 있습니다.

# diff
set @prevPublished := null;

SELECT id,
       published,
       @prevPublished as prev_published,
       published - @prevPublished diff_published,
       @prevPublished := published
FROM articles
ORDER BY published asc

 

 

다음 글이 30분 이내에 발행된 글의 수를 세어보겠습니다. 기존 쿼리를 감싸서 Inline View로 만들고 다음 기사의 발행 시각의 차이인 diff_published가 0~1800인 데이터의 개수를 count함수를 이용하여 세어줍니다.

set @prevPublished := null;

SELECT count(*) diff_count
FROM (
SELECT published - @prevPublished diff_published,
       @prevPublished := published
FROM articles
ORDER BY published asc
) t1
WHERE diff_published BETWEEN 0 and 1800

결과가 잘 나왔습니다. 3번 기사 다음에 4번 기사가 1000초만에 작성 되었고, 4번 기사 다음에 5번 기사가 1000초만에 작성되었기 때문에 1800초 이내에 작성된 기사의 수는 2개 입니다.

LAG, OVER 함수 방식 - MySQL 8 방식

앞서 살펴본 변수를 이용한 방식도 그럭저럭 쓸 만 합니다만 쿼리를 두 번 실행해야 되는 불편함이 있습니다. 그리고 뭔가 변수를 잘 못 사용하다가 실수를 유발할 거 같은 기분도 듭니다.

MySQL 8 부터는 앞의 레코드나 뒤의 레코드를 참조할 때 사용할 수 있는 유용한 함수를 제공합니다. 앞서 나온 예제 처럼 이전 값을 참조 하려면 LAG, 다음 값을 참조 하려면 LEAD 함수를 사용하면 됩니다.

SELECT count(*) diff_count
FROM (
SELECT id,
       published - LAG(published) OVER (order by published asc) AS 'diff_published'
FROM articles) t1
WHERE diff_published between 0 and 1800

변수에 할당할 필요 없이 LAG함수와 OVER함수를 이용해 이전 값을 가져올 수 있습니다.

결과도 동일하게 잘 나왔습니다.

이 뿐만 아니라 파티션을 나눠서 참조하는 영역을 나누는 등 여러 기능을 손쉽게 사용할 수 있습니다. 자세한 사용방법은 공식문서를 참고해 보시기 바랍니다.

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

MySQL :: MySQL 8.0 Reference Manual :: 12.21.1 Window Function Descriptions