[sqlite3] 이전 행의 데이터를 현재 행에 가져와야 할 때, lead() 함수

데이터를 조회하다보면, 특정 행의 데이터를 관련된 이전 행의 데이터와 비교해야 하는 일들이 생깁니다. 이때 사용할 수 있는 sqlite3 함수가 lead()입니다. 

 

테이블 생성 및 데이터 삽입

우선 설명을 위해서 간단한 테이블을 생성(create)하고 데이터를 넣어(insert)주겠습니다. 한 학생의 영어, 수학, 국어 시험 성적을 담고 있는 테이블을 하나 만들었습니다. 이 학생이 과목 별로 다른 시기에 시험 본 결과를 담임 선생님이 정리하기 위해 만든 테이블이라고 가정하겠습니다.

 

create table test_score (
subject TEXT not null,
score INTEGER not null,
time TIMESTAMP not null
);

INSERT INTO test_score VALUES ('영어', 85, '2019-05-05 13:00:00');
INSERT INTO test_score VALUES ('수학', 68, '2019-05-06 17:00:00');
INSERT INTO test_score VALUES ('국어', 79, '2019-05-07 14:00:00');
INSERT INTO test_score VALUES ('국어', 83, '2020-07-02 09:00:00');
INSERT INTO test_score VALUES ('영어', 91, '2020-07-03 11:00:00');
INSERT INTO test_score VALUES ('영어', 87, '2021-03-05 09:30:00');
INSERT INTO test_score VALUES ('수학', 77, '2021-03-05 11:30:00');
INSERT INTO test_score VALUES ('수학', 85, '2021-06-05 13:50:00');
INSERT INTO test_score VALUES ('영어', 92, '2021-06-06 15:00:00');
INSERT INTO test_score VALUES ('국어', 88, '2021-06-07 13:00:00');

 

 

바로 전 해당 과목 성적이 함께 보이게 하기

담임 선생님은 이 학생의 과목별 성적이 바로 전 시험 대비 올랐는지, 떨어졌는지 궁금한 상황입니다. 이럴 때 사용할 수 있는 함수가 lead() 함수입니다. 

 

SELECT 
    subject, 
    score, 
    LEAD(score, 1) OVER (PARTITION BY subject ORDER BY time DESC) AS score_last, 
    time 
FROM test_score;

 

- LEAD(score, 1)에서 1은 하나 밑에 있는 행의 score 컬럼에서 데이터를 가져오겠다는 뜻입니다. LEAD 함수의 두번째 인수가 2였다면, 두 개 아래 있는 행의 데이터를 가져옵니다.

 

- PARTITION BY subject는 GROUP BY와 유사하게 과목별로 그룹을 지어주겠다는 뜻입니다. 국어 성적은 국어 성적끼리 비교해야하기 때문입니다. 

 

- ORDER BY time DESC는 시간 역순으로 데이터를 정렬시키겠다는 뜻입니다. 시간 순 또는 시간 역순으로 정렬이 되어야 시간의 흐름에 따른 학생의 성장도를 확인할 수 있기 때문입니다. 

 

 

보시는 것처럼 하나 이전의 시험 성적이 score_last 컬럼에 있습니다. 이전 성적이 없는 경우에는 null 값이 들어갔습니다. 이렇게 조회해보니 이전 시험과의 성적과 한 눈에 비교할 수 있네요. 

 

점수 증감 확인하기

만약 점수의 증감을 또 다른 컬럼에 보여지게 하고 싶다면, 서브쿼리를 활용할 수 있습니다.  

 

SELECT t.subject, AVG(t.score - t.score_last) AS score_increment_avg 
FROM 
    (SELECT 
        subject, 
        score, 
        LEAD(score, 1) OVER (PARTITION BY subject ORDER BY time DESC) AS score_last, 
        time 
    FROM test_score) AS t;

 

 

점수 증감의 평균 확인하기

또한 점수 증감의 평균을 확인한다면, 이 학생이 얼마나 꾸준하게 성장해왔는지 한 번에 체크가 가능할 것 같습니다. 

 

SELECT t.subject, AVG(t.score - t.score_last) AS score_increment_avg 
FROM 
    (SELECT 
        subject, 
        score, 
        LEAD(score, 1) OVER (PARTITION BY subject ORDER BY time DESC) AS score_last, 
        time 
    FROM test_score) AS t 
GROUP BY t.subject;

 

 

이렇게 조회하니 수학 성적이 가장 큰 폭으로 꾸준하게 좋아진 것을 쉽게 알 수 있네요. 

 

관련 글

- [pandas] 이전 데이터와 비교할 때 유용한 데이터프레임 shift() 메소드