Window Function + CTE

~16 min · window-functions, cte, advanced-sql

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

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

Window function 이 현재 row 와 관련된 row 에 걸쳐 값 계산, 그룹으로 collapse 안 함. CTE (Common Table Expression) 가 subquery 이름 + 재사용; 재귀 CTE 가 계층 데이터.

Window function 모양:

row_number() OVER (ORDER BY ...) — 정렬마다 sequential 번호.
rank()/dense_rank() — competition-style 랭킹.
sum(...) OVER (...)/avg(...) OVER (...) — running total, moving average.
lag(col)/lead(col) — 이전/다음 row 값.
partition by — partition 마다 window reset.

Tip: 재귀 CTE 가 SQLite 의 계층 일반 도구: 조직도, 파일 트리, 댓글 thread. 모양 항상 WITH RECURSIVE name AS (anchor UNION ALL recursive_step) SELECT .... 패턴 내재화하면 application-side traversal 코드 많이 교체.

Code

Window function — conversation 별 running 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 까지

External links

Exercise

messages 테이블에서 window-function query 3 개: (1) per-conversation running message length total, (2) lag 로 conversation 의 연속 message 사이 시간 gap, (3) subquery 의 row_number 로 conversation 별 top-3 가장 긴 message. 그 다음 진짜 조직도 모양에 manager-chain 재귀 CTE 빌드.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousJSON 지원 — json_extract, json_each, JSONB Next →Generated Column

이 페이지에서 버그를 발견하셨거나 피드백이 있으세요?문제 신고

🔔 답글 알림 (로그인 필요)

로그인 — 댓글을 남기려면 로그인해 주세요.

아직 댓글이 없어요. 첫 댓글을 남겨보세요.