sql, lateral을 사용하여 직관적인 쿼리를 작성해보자.

lateral

• 요구사항이 복잡한 통계를 요구하는 경우, with를 사용하거나 혹은 2-3 중의 서브쿼리와 함께 복잡한 조건절을 필요로 한다. 이런 경우 상황에 따라 lateral이 문제 해결에 도움이 될 수 있다.
• 아래는 school 테이블과 students 테이블이 있다. 요구 사항으로는 학교 별로 1,2,3등을 출력하고 그 순서로 나열하는 것이다.
• lateral을 몰랐던 나는 아래와 같이 rank 함수와 join을 사용하여 쿼리를 작성할 것 같다.

create table school (
  id INTEGER PRIMARY KEY,  
  name TEXT NOT NULL
); 

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  sid INTEGER,
  name TEXT NOT NULL,
  gender TEXT NOT NULL,
  score int
);

INSERT INTO school (id, name) values (1, '부산초'), (2, '서울초');

INSERT INTO students(id, sid, name, gender, score) VALUES 
    (1, 1, 'Ryan', 'M', 60)
    , (2, 1, 'Jane', 'F', 70)
    , (3, 1, 'Kinda', 'F', 95)
    , (4, 1, 'Brown', 'M', 50)
    , (5, 2, '진혁', 'M', 60)
    , (6, 2, '영준', 'M', 90)
    , (7, 2, '아영', 'F', 40)
    , (8, 2, '하나', 'F', 80);
;

select sc.name, tb.name, tb.score
from (
    select st.sid
    , st.score
    , rank() over(partition by st.sid order by st.score desc) rk
    , st.name
    from students st
) tb
join school sc on sc.id = tb.sid
where tb.rk < 4
order by tb.sid asc, tb.rk asc;
;

name	name	score
부산초	Kinda	95
부산초	Jane	70
부산초	Ryan	60
서울초	영준	90
서울초	하나	80
서울초	진혁	60

• lateral을 사용하면 다음과 같이 좀 더 직관적인 쿼리를 작성할 수 있다. students의 로직이 외부에 노출되지 않고 lateral 서브쿼리 내부에서 온전하게 표현된다.
• 이러한 쿼리를 작성할 수 있는 이유는, lateral 쿼리가 메인 쿼리의 FROM에 있는 테이블 school sc를 참조할 수 있기 때문이다.

SELECT sc.name, tb.name, tb.score
FROM school sc,
LATERAL ( -- INNER JOIN LATERAL로 바꿀 수 있다.
    SELECT st.sid,
           st.score,
           st.name
    FROM students st
    WHERE sc.id = st.sid
    order by st.score desc 
    LIMIT 3
) AS tb;