ROW_NUMBER, RANK, CTE

~14 min · queries, advanced

Level 0스키마 새싹

0 XP0/86 lessons0/10 achievements

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

순위를 붙이는 세 방식

ROW_NUMBER는 동점이어도 1, 2, 3, 4처럼 고유 번호를 붙여. RANK는 공동 1위 다음을 3위로 건너뛰고, DENSE_RANK는 공동 1위 뒤를 2위로 이어. 목적에 맞는 동점 규칙을 골라.

그룹마다 상위 행을 고른다

PARTITION BY로 그룹을 나누고 번호를 붙인 뒤 WHERE rn <= 3으로 상위 3개를 남길 수 있어. 1·2·2·4와 1·2·2·3처럼 두 순위 함수의 결과 차이를 직접 비교해봐.

CTE는 질의의 단계에 이름을 붙여

WITH로 중간 결과를 이름 붙이면 복잡한 질의를 읽기 쉬운 단계로 나눌 수 있어. PostgreSQL 12부터는 필요에 따라 CTE를 본문 안으로 합칠 수 있고, 강제로 한 번 계산하려면 MATERIALIZED를 쓸 수 있어. 같은 CTE를 여러 번 참조한다면 실행 계획을 확인해 본문에 합치는 편이 나은지, 한 번 계산해 재사용하는 편이 나은지 판단해.

Code

ROW_NUMBER로 그룹별 상위 N개 고르기·sql

SELECT *
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (
           PARTITION BY o.customer_id
           ORDER BY o.placed_at DESC
         ) AS rn
  FROM orders o
) ranked
WHERE rn <= 3;

여러 CTE를 잇는 처리 단계·sql

WITH active_users AS (
  SELECT DISTINCT user_id
  FROM   orders
  WHERE  placed_at >= now() - INTERVAL '90 days'
),
user_spend AS (
  SELECT user_id, SUM(total) AS total_spent
  FROM   orders
  WHERE  user_id IN (SELECT user_id FROM active_users)
  GROUP  BY user_id
)
SELECT u.name, us.total_spent
FROM   user_spend us
JOIN   users u ON u.id = us.user_id
ORDER  BY us.total_spent DESC
LIMIT  10;

재귀 CTE로 트리 순회하기·sql

-- 조직도: 모든 직원 + 루트로부터 깊이
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM   employees
  WHERE  manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM   employees e
  JOIN   org o ON e.manager_id = o.id
)
SELECT REPEAT('  ', depth-1) || name AS tree, depth
FROM   org
ORDER  BY depth, name;

External links

Exercise

고객마다 금액이 큰 주문 3개를 반환하는 질의를 작성해. 순위 계산은 CTE에 두고 최종 표시는 바깥 SELECT에서 맡겨. 추가로 자기 참조 category parent 트리를 순회하는 재귀 CTE를 만들어.

Progress

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

← Previous행을 접지 않는 윈도 함수 Next →퀴즈 · 5 questions

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

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

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

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