Ranks and Trends With Window Functions

목표

row_number, rank, dense_rank, 누적 합, lag, 비중, ntile 을 써서 순위와 추세를 구하고, 윈도우 함수를 언제 쓰는지 판단할 수 있게 됩니다.

왜 중요한가

집계는 행을 접습니다. 접고 나면 개별 행 정보가 사라지죠. 그런데 실무 질문의 상당수는 "각 행이 자기 그룹 안에서 몇 번째인가", "직전 값과 비교하면 어떤가", "전체 대비 몇 퍼센트인가"처럼 개별 행을 유지한 채 주변을 참조해야 답할 수 있습니다.

윈도우 함수는 정확히 이 자리를 채웁니다. 같은 일을 자기 조인이나 상관 서브쿼리로도 할 수 있지만, 코드가 길어지고 표를 여러 번 읽게 됩니다. 윈도우 함수는 한 번의 스캔으로 끝납니다.

한 가지 제약만 기억하면 됩니다. 윈도우 함수 결과는 같은 SELECT 의 WHERE 에서 쓸 수 없습니다. 처리 순서상 WHERE 가 먼저이기 때문입니다. 그래서 "상위 3개만"처럼 순위로 거르려면 서브쿼리나 CTE 로 한 겹 감싸야 합니다.

단계

1. 상품을 카테고리별로 가격 내림차순(동점은 id 오름차순) 정렬한 순번을 담는 뷰 v_ranked_products 를 만듭니다. 컬럼은 category, id, price, rn 입니다.
2. 전체 상품을 가격 내림차순으로 두 가지 방식의 순위를 매긴 뷰 v_rank_compare 를 만듭니다. 컬럼은 id, price, rnk(동점 뒤 번호를 건너뜀), drnk(건너뛰지 않음)입니다.
3. 완료 상태 주문의 월별 매출과 누적 매출을 담는 뷰 v_running_revenue 를 만듭니다. 컬럼은 month, revenue, cum_revenue 이며 월 기준은 AT TIME ZONE 'Asia/Seoul' 입니다.
4. 같은 월별 집계에 전월 매출과 증감을 붙인 뷰 v_mom 을 만듭니다. 컬럼은 month, revenue, prev_revenue, diff 이며 첫 달의 prev_revenue 는 비어 있어야 합니다.
5. 채널별 매출과 전체 대비 비중을 담는 뷰 v_channel_share 를 만듭니다. 컬럼은 channel, revenue, share_pct 이며 비중은 소수점 두 자리로 반올림하고 합이 100 이 되어야 합니다.
6. 카테고리별 가격 상위 3개 상품을 담는 뷰 v_top3_per_category 를 만듭니다. 컬럼은 category, id, price 입니다.
7. 상품을 가격 오름차순(동점은 id 오름차순)으로 4등분한 뷰 v_price_quartile 을 만듭니다. 컬럼은 id, price, quartile 입니다.
8. 고객별 첫 주문을 담는 뷰 v_first_order 를 만듭니다. 컬럼은 customer_id, order_id, ordered_at 이며, 같은 시각이면 id 가 작은 주문을 첫 주문으로 봅니다.

참고

• 기본 형태: 함수() OVER (PARTITION BY 그룹 ORDER BY 정렬)
• OVER () 처럼 괄호를 비우면 전체가 하나의 창이 됩니다.
• 흔한 실수 1: 순위 결과를 같은 SELECT 의 WHERE 에 쓰면 오류가 납니다.
• 흔한 실수 2: 정렬 기준에 동점이 있으면 결과가 실행마다 달라질 수 있으니 tie-break 컬럼을 넣으세요.

카테고리별 가격 순번 매기기

상품을 카테고리별로 가격 내림차순(동점은 id 오름차순) 정렬한 순번을 담는 뷰 v_ranked_products 를 만듭니다. 컬럼은 category, id, price, rn 입니다.

창을 나누는 절과 창 안의 순서를 정하는 절을 함께 씁니다. 동점은 id 로 가릅니다.

두 가지 순위 함수 비교하기

전체 상품을 가격 내림차순으로 두 가지 방식의 순위를 매긴 뷰 v_rank_compare 를 만듭니다. 컬럼은 id, price, rnk(동점 뒤 번호를 건너뜀), drnk(건너뛰지 않음)입니다.

동점 뒤의 번호를 건너뛰는 함수와 건너뛰지 않는 함수가 따로 있습니다.

누적 매출 구하기

완료 상태 주문의 월별 매출과 누적 매출을 담는 뷰 v_running_revenue 를 만듭니다. 컬럼은 month, revenue, cum_revenue 이며 월 기준은 AT TIME ZONE 'Asia/Seoul' 입니다.

월별 집계를 먼저 만들고 그 결과 위에서 창을 엽니다. 순서를 반대로 하면 집계와 창이 섞입니다.

전월 대비 증감 구하기

같은 월별 집계에 전월 매출과 증감을 붙인 뷰 v_mom 을 만듭니다. 컬럼은 month, revenue, prev_revenue, diff 이며 첫 달의 prev_revenue 는 비어 있어야 합니다.

직전 행의 값을 가져오는 함수가 있습니다. 첫 달에는 값이 없어 비어 있어야 합니다.

전체 대비 비중 구하기

채널별 매출과 전체 대비 비중을 담는 뷰 v_channel_share 를 만듭니다. 컬럼은 channel, revenue, share_pct 이며 비중은 소수점 두 자리로 반올림하고 합이 100 이 되어야 합니다.

OVER 괄호를 비우면 전체가 하나의 창이 됩니다. 쿼리를 두 번 돌릴 필요가 없습니다.

그룹별 상위 3개 뽑기

카테고리별 가격 상위 3개 상품을 담는 뷰 v_top3_per_category 를 만듭니다. 컬럼은 category, id, price 입니다.

창 함수 결과는 같은 SELECT 의 WHERE 에서 쓸 수 없습니다. 한 겹 감싸야 합니다.

가격 사분위 나누기

상품을 가격 오름차순(동점은 id 오름차순)으로 4등분한 뷰 v_price_quartile 을 만듭니다. 컬럼은 id, price, quartile 입니다.

행을 n 등분해 번호를 붙이는 함수가 있습니다. 정렬 기준을 정확히 맞추세요.

고객별 첫 주문 찾기

고객별 첫 주문을 담는 뷰 v_first_order 를 만듭니다. 컬럼은 customer_id, order_id, ordered_at 이며, 같은 시각이면 id 가 작은 주문을 첫 주문으로 봅니다.

고객별로 시간 순 순번을 매기고 첫 번째만 남깁니다. 주문이 없는 고객은 나오지 않아야 합니다.