Window Function + CTE

리포트를 query 하나로 만드는 고급 SQL

Window function은 지금 row와 관련된 row들을 훑어서 값을 계산하되, 그룹으로 접지는 않아. CTE(Common Table Expression)는 subquery에 이름을 붙여 재사용하게 해주고, 재귀 CTE는 계층 데이터를 다뤄.

window function은 이런 모양들로 써.

row_number() OVER (ORDER BY ...) — 정렬 순서대로 번호를 매겨.
rank()와 dense_rank() — 대회식 순위를 매겨.
sum(...) OVER (...)와 avg(...) OVER (...) — 누적 합이나 이동 평균을 내.
lag(col)과 lead(col) — 앞뒤 row의 값을 끌어와.
partition by — 구간마다 window를 처음부터 다시 시작해.

Tip: SQLite에서 계층을 다루는 만능 도구는 재귀 CTE야. 조직도, 파일 트리, 댓글 타래에 다 써. 모양은 늘 WITH RECURSIVE name AS (anchor UNION ALL recursive_step) SELECT ...야. 이 패턴이 몸에 붙으면 앱 쪽에서 트리를 순회하던 코드를 상당 부분 걷어낼 수 있어.

Code

Window function — conversation별 누적 message count·sql

SELECT id, conversation_id, role, created_at,
       row_number() OVER (PARTITION BY conversation_id
                          ORDER BY created_at) AS msg_index,
       count(*)     OVER (PARTITION BY conversation_id) AS conv_total
FROM   messages
ORDER  BY conversation_id, created_at;

재귀 CTE — manager chain·sql

WITH RECURSIVE chain(id, name, manager_id, depth) AS (
  SELECT id, name, manager_id, 0
  FROM   employees
  WHERE  id = 42                              -- 시작 employee
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.depth + 1
  FROM   employees e
  INNER JOIN chain c ON e.id = c.manager_id
)
SELECT depth, name FROM chain ORDER BY depth;
-- 0 | Alice (IC)
-- 1 | Bob (그녀의 manager)
-- 2 | Carol (그의 manager)
-- ... CEO 까지

Exercise

messages 테이블에서 window function query를 세 개 써봐. conversation마다 message 길이의 누적 합, lag로 구하는 연속 message 사이의 시간 간격, 그리고 subquery 안의 row_number로 뽑는 conversation별 가장 긴 message 세 개. 그다음 실제 조직도 모양에 manager chain을 따라 올라가는 재귀 CTE를 만들어봐.

리포트를 query 하나로 만드는 고급 SQL

Code

External links

Exercise

Progress

댓글 0