D-6: PIVOT · LISTAGG · Top-N — 가로로 펼치고, 한 줄로 모으고, 상위만 떼어 내기

안녕하세요, 홍순구 튜터입니다. 지난 시간엔 데이터를 세로로 차곡차곡 쌓아 누적을 만들고, ROLLUP·CUBE 로 소계와 총계를 한 번에 뽑았어요. "5월 게시물 몇 개, 6월 게시물 몇 개, 전체 몇 개" 를 한 결과에 세로로 늘어놓은 거였죠.

오늘은 방향을 90도 틀어요. 세로로 늘어놓던 걸 가로로 눕히는 PIVOT 을 배웁니다. "월" 을 행이 아니라 열로 바꿔서 5월·6월을 나란히 놓고, "카테고리" 를 가로로 펼쳐서 한눈에 보는 교차표를 만드는 거예요. 엑셀의 피벗 테이블을 떠올리면 딱 맞아요.

거기에 더해, 흩어진 여러 줄을 한 줄 문자열로 모아 주는 LISTAGG 와, 정렬한 결과에서 상위 몇 개만 똑 떼어 내는 Top-N 까지 갑니다. "이 게시물에 달린 해시태그를 여행, 제주, 바다 처럼 한 줄로" 모으고, "좋아요가 가장 많은 인기글 3개만" 뽑는 거죠.

 오늘의 여정
   ① PIVOT 이란 — 세로로 쌓던 걸 가로로 눕힌다 (요일별 좋아요)
   ② 카테고리 교차표 + UNPIVOT — 월 × 카테고리, 그리고 되돌리기
   ③ LISTAGG — 여러 줄을 한 줄 문자열로 (게시물별 해시태그)
   ④ LISTAGG 정렬과 NULL 함정
   ⑤ Top-N ① ROWNUM — 가장 오래된 방식과 그 함정
   ⑥ Top-N ② FETCH FIRST — 표준이 된 방식
   ⑦ Top-N ③ ROW_NUMBER — 가장 유연한 방식
   ⑧ 세 기법 종합 — 인기글 리포트 만들기

💡 오늘 수업의 핵심 — "PIVOT 은 행을 열로 눕혀 교차표를 만들고, LISTAGG 는 여러 줄을 한 줄 문자열로 모으며, Top-N 은 정렬한 결과에서 상위 N개만 떼어 낸다"

🎯 학습 목표

• PIVOT 으로 행을 열로 펼쳐 교차표를 만들고, UNPIVOT 으로 다시 세로로 되돌린다. 지난 시간의 세로 소계와 오늘의 가로 교차표를 대비한다. (SQLD 2과목 'SQL 활용', ★빈출)
• LISTAGG(... ) WITHIN GROUP (ORDER BY ...) 로 여러 행의 값을 한 줄 문자열로 모으고, 정렬과 NULL 처리를 다룬다. (SQLD 2과목 '그룹 함수')
• Top-N 을 ROWNUM·FETCH FIRST·ROW_NUMBER() 세 가지로 구하고, 각각의 함정과 장단점을 구분한다. (SQLD 2과목 'SQL 활용', ★★★ 빈출)

Step 1: "세로로 쌓던 걸 가로로 눕힌다 — PIVOT 첫 만남"

지난 시간 ROLLUP 을 떠올려 보세요. 카테고리별 게시물 수를 구하면 결과가 세로로 한 줄씩 내려왔어요. 여행 14, 맛집 12, 일상 13… 이렇게요. 보기엔 편하지만, "5월과 6월을 나란히 비교" 하려면 눈이 위아래로 왔다 갔다 해야 했죠.

PIVOT 은 이 세로줄을 가로로 눕혀요. 같은 항목을 행이 아니라 열(컬럼)로 펼치는 거예요. 비유하자면, 세로로 쌓인 책을 책장에 가로로 나란히 꽂는 것과 같아요.

 ROLLUP (지난 시간) — 세로로 한 줄씩          PIVOT (오늘) — 가로로 나란히
   요일    좋아요                              일  월  화  수  목  금  토
   일       2                                  2   3   5   4   4   8   6
   월       3
   화       5
   ...     ...

바로 눈으로 확인해 볼게요. 좋아요가 무슨 요일에 많이 눌리는지, 요일을 가로로 펼쳐 봅니다.

-- sql/queries/D6_pivot_listagg_topn.sql
SELECT *
FROM (SELECT TO_CHAR(liked_at, 'DY') AS dy FROM post_like)
PIVOT (COUNT(*) FOR dy IN ('SUN' AS sun, 'MON' AS mon, 'TUE' AS tue,
                           'WED' AS wed, 'THU' AS thu, 'FRI' AS fri, 'SAT' AS sat));

좋아요 32개가 요일별로 흩어진 걸, 7개 요일을 가로 열로 펼쳐 한 줄에 담았어요. 금요일(FRI)이 8개로 가장 많네요. PIVOT 의 문법을 뜯어보면 세 부분이에요.

• COUNT(*) — 각 칸에 무엇을 채울지 (집계 함수)
• FOR dy — 어떤 컬럼의 값을 열로 펼칠지
• IN ('SUN' AS sun, ...) — 어떤 값들을 열로 만들지, 그리고 그 열 이름

PIVOT 바로 앞의 인라인 뷰 (SELECT TO_CHAR(liked_at,'DY') AS dy FROM post_like) 에서 필요한 컬럼만 딱 골라 준 게 중요해요. PIVOT 은 거기 남은 컬럼들을 기준으로 행을 묶거든요. 여기선 dy 하나만 남겼으니 전체가 한 줄로 뭉쳐요.

⚠️ IN 절의 값은 코드에 직접 적어야 해요. "데이터에 있는 요일을 알아서 다 펼쳐 줘" 같은 자동 확장은 안 돼요. 펼칠 열을 미리 안다는 전제가 PIVOT 의 특징이자 한계예요. 이건 ★빈출 포인트라 꼭 기억하세요.

💡 한 줄 정리 — PIVOT (집계 FOR 컬럼 IN (값들)) 은 한 컬럼의 값을 가로 열로 펼쳐 교차표를 만든다. 펼칠 값은 IN 절에 직접 나열해야 한다.

Step 2: "월 × 카테고리 교차표 — PIVOT 응용과 UNPIVOT" ★빈출

이제 본격적인 2차원 교차표를 만들어요. 지난 시간에 게시물마다 붙여 둔 카테고리(여행·맛집·일상·음악·패션) 기억하시죠? 이걸 가로 열로 펼치고, 월을 세로 행으로 두면 "어느 달에 어느 카테고리 글이 몇 개" 인지 한눈에 들어오는 표가 나와요.

SELECT *
FROM (SELECT TO_CHAR(created_at, 'YYYY-MM') AS mon, category FROM post)
PIVOT (COUNT(*) FOR category IN ('여행' AS 여행, '맛집' AS 맛집, '일상' AS 일상,
                                 '음악' AS 음악, '패션' AS 패션))
ORDER BY mon;

이번엔 인라인 뷰에 컬럼을 두 개(mon, category) 남겼어요. PIVOT 은 펼치는 데 쓴 category 를 빼고 남은 mon 을 기준으로 행을 묶어요. 그래서 5월·6월 두 줄이 생기고, 카테고리 5종이 가로로 펼쳐진 거예요.

눈에 띄는 칸이 있죠? 6월 패션이 0 이에요. 지난 시간에 "패션은 5월에만 있다" 고 심어 둔 게 여기서 드러나요. COUNT(*) 로 셌더니 6월 패션은 해당하는 게시물이 없어 0이 됐어요. 가로 교차표는 이렇게 "어디가 비었는지" 를 한눈에 보여주는 게 강점이에요.

거꾸로 가는 길도 있어요. 가로로 펼친 걸 다시 세로로 되돌리는 UNPIVOT 이에요.

SELECT mon, category, cnt
FROM (SELECT TO_CHAR(created_at, 'YYYY-MM') AS mon, category FROM post)
     PIVOT (COUNT(*) FOR category IN ('여행' AS 여행, '맛집' AS 맛집, '일상' AS 일상,
                                      '음악' AS 음악, '패션' AS 패션))
     UNPIVOT (cnt FOR category IN (여행, 맛집, 일상, 음악, 패션))
ORDER BY mon, category;

방금 만든 가로 교차표를 UNPIVOT 으로 풀면 다시 "월·카테고리·개수" 세로 형태 10줄로 돌아와요. 가로 열이던 여행·맛집… 이 다시 category 컬럼의 값으로 내려앉는 거예요. 화면용 보고서는 가로가 편하고, 다시 계산·저장하려면 세로가 편하니, 둘을 오가는 길을 함께 알아 두면 좋아요.

💡 한 줄 정리 — PIVOT 은 행을 가로 열로 눕혀 2차원 교차표를 만들고, UNPIVOT 은 그 가로 열을 다시 세로 행으로 되돌린다. COUNT(*) 로 펼치면 빈 조합은 0으로 채워진다.

Step 3: "여러 줄을 한 줄로 모은다 — LISTAGG"

지금까지 해시태그는 게시물의 caption 안에 글과 섞여 있었어요. 제주도 여행 #여행 #제주 #바다 처럼요. C-2 에서 우리는 이걸 정규식으로 한 땀 한 땀 뽑아냈죠. 오늘은 이 해시태그를 제대로 된 표로 정규화해서, 게시물 하나에 태그 여러 개가 깔끔하게 연결되도록 만들어요.

-- sql/ddl/04_hashtag.sql
CREATE TABLE hashtag (
    hashtag_id   NUMBER       PRIMARY KEY,
    name         VARCHAR2(30) NOT NULL UNIQUE
);

CREATE TABLE post_hashtag (
    post_id      NUMBER NOT NULL,
    hashtag_id   NUMBER NOT NULL,
    PRIMARY KEY (post_id, hashtag_id),
    FOREIGN KEY (post_id)    REFERENCES post(post_id),
    FOREIGN KEY (hashtag_id) REFERENCES hashtag(hashtag_id)
);

게시물 하나에 태그 여러 개가 달리고, 태그 하나가 여러 게시물에 쓰이는 다대다(M:N) 관계예요. 그래서 둘을 잇는 연결 표 post_hashtag 를 두고, 게시물과 태그를 짝지어요. caption 에 적혀 있던 #여행 #제주 #바다 가 이제 post_hashtag 에 세 줄로 들어가 있는 거죠.

여기서 질문 하나. 한 게시물의 태그가 표에 세 줄로 흩어져 있는데, 이걸 화면에 여행, 제주, 바다 한 줄로 보여주려면 어떻게 할까요? 이때 쓰는 게 LISTAGG 예요. 여러 행의 문자열을 하나로 이어 붙여 주는 집계 함수죠.

SELECT p.post_id,
       LISTAGG(h.name, ', ') WITHIN GROUP (ORDER BY h.hashtag_id) AS tags
FROM post p
JOIN post_hashtag ph ON p.post_id = ph.post_id
JOIN hashtag h ON ph.hashtag_id = h.hashtag_id
GROUP BY p.post_id
ORDER BY p.post_id;

태그가 달린 게시물을 뽑아 보면 이렇게 나와요.

post_id 12 를 보세요. caption 에 #여행 #제주 #바다 라고 적혀 있던 게, 정규화 표를 조인해 LISTAGG 로 모으니 여행, 제주, 바다 한 줄로 딱 나왔어요. C-2 에서 정규식으로 힘겹게 뽑던 것과 같은 결과를, 이번엔 표를 조인해서 깔끔하게 얻은 거예요.

문법을 보면 LISTAGG(h.name, ', ') 는 "name 들을 쉼표+공백으로 이어 붙여라" 이고, WITHIN GROUP (ORDER BY h.hashtag_id) 는 "이어 붙일 때 hashtag_id 순서로 정렬해라" 예요. LISTAGG 는 정렬 순서를 이렇게 WITHIN GROUP 안에 따로 적는 게 특징이에요.

💡 한 줄 정리 — LISTAGG(컬럼, 구분자) WITHIN GROUP (ORDER BY 정렬) 은 그룹 안 여러 행의 문자열을 한 줄로 이어 붙인다. 정렬 기준은 WITHIN GROUP 안에 적는다.

Step 4: "정렬하고, 빈 줄을 막는다 — LISTAGG 의 정렬과 NULL"

LISTAGG 에서 정렬을 바꾸면 이어 붙는 순서가 달라져요. WITHIN GROUP (ORDER BY h.name) 으로 이름 가나다순으로 바꿔 볼게요. 그리고 이번엔 태그가 없는 게시물까지 함께 보기 위해 LEFT JOIN 으로 바꿉니다.

SELECT p.post_id,
       LISTAGG(h.name, ', ') WITHIN GROUP (ORDER BY h.name) AS tags
FROM post p
LEFT JOIN post_hashtag ph ON p.post_id = ph.post_id
LEFT JOIN hashtag h ON ph.hashtag_id = h.hashtag_id
GROUP BY p.post_id
ORDER BY p.post_id;

post_id 12 가 아까는 여행, 제주, 바다 였는데 이번엔 바다, 여행, 제주 로 바뀌었죠. ORDER BY h.name 으로 정렬 기준을 이름 가나다순으로 바꿨기 때문이에요. LISTAGG 의 출력 순서는 데이터가 들어간 순서가 아니라 WITHIN GROUP 의 ORDER BY 가 정한다는 걸 보여줘요.

그런데 post_id 3, 7 을 보세요. 태그가 하나도 없는 게시물이라 결과가 빈 값(NULL)이에요. 우리 데이터엔 태그가 0개인 게시물이 22개나 있어서, LEFT JOIN 으로 모은 이 22줄은 전부 NULL 로 나와요.

여기서 ⚠️ 함정 하나를 짚어요. LISTAGG 는 그룹 안 값이 전부 비었을 때만 NULL 을 내요. 일부만 NULL 이면 그건 건너뛰고 나머지만 이어 붙이죠. 그러니 "태그 하나라도 NULL 이면 전체가 NULL 이 된다" 는 흔한 오해는 틀렸어요. 진짜 NULL 이 나오는 건 "태그가 아예 하나도 없는 게시물" 뿐이에요.

빈 값을 보기 좋게 메우려면, C-3 에서 배운 NVL 로 감싸면 돼요.

SELECT p.post_id,
       NVL(LISTAGG(h.name, ', ') WITHIN GROUP (ORDER BY h.name), '태그 없음') AS tags
FROM post p
LEFT JOIN post_hashtag ph ON p.post_id = ph.post_id
LEFT JOIN hashtag h ON ph.hashtag_id = h.hashtag_id
GROUP BY p.post_id
ORDER BY p.post_id;

이렇게 하면 태그 없는 게시물 22개가 빈칸 대신 태그 없음 으로 채워져요. LISTAGG 결과도 결국 하나의 값이라, NULL 처리는 우리가 C-3 에서 익힌 NVL·COALESCE 가 그대로 통해요.

💡 한 줄 정리 — LISTAGG 의 출력 순서는 WITHIN GROUP (ORDER BY) 가 정한다. 그룹 전체가 비면 결과는 NULL 이고, NVL 로 기본 문자열을 채울 수 있다.

Step 5: "상위 N개만 떼어 내기 ① ROWNUM 과 그 함정" ⚠️

이제 Top-N 으로 넘어가요. "좋아요가 가장 많은 인기 게시물 3개" 처럼, 정렬한 결과에서 위쪽 몇 개만 떼어 내는 거예요. Oracle 에서 가장 오래된 방식은 ROWNUM 이에요. 조회된 행에 1, 2, 3… 순번을 매겨 주는 가짜 컬럼이죠.

그런데 여기 아주 유명한 함정이 있어요. 먼저 "이렇게 하면 될 것 같은" 쿼리를 볼게요.

SELECT post_id, cnt
FROM (SELECT post_id, COUNT(*) AS cnt FROM post_like GROUP BY post_id)
WHERE ROWNUM <= 3
ORDER BY cnt DESC;

뭔가 이상하죠? 좋아요가 가장 많은 게시물은 post_id 12(좋아요 6개)인데, 결과에 보이지도 않아요. 가장 인기 없는 1개짜리 게시물이 3등에 들어와 있고요. 이게 ROWNUM 의 함정이에요.

이유는 실행 순서에 있어요. WHERE ROWNUM <= 3 이 ORDER BY 보다 먼저 동작해요. 그러니까 정렬되기 전에 아무 순서로 들어온 3개를 먼저 잘라 버리고, 그렇게 잘린 3개만 가지고 정렬을 하는 거예요. 진짜 1등은 4번째 이후에 있었을지도 모르는데, 정렬도 하기 전에 잘려 나간 거죠.

제대로 하려면, 정렬을 먼저 끝낸 다음에 ROWNUM 으로 잘라야 해요. 정렬한 결과를 인라인 뷰로 한 번 감싸서, 그 바깥에서 ROWNUM 을 거는 거예요.

SELECT post_id, cnt
FROM (SELECT post_id, COUNT(*) AS cnt FROM post_like GROUP BY post_id ORDER BY cnt DESC)
WHERE ROWNUM <= 3;

이제 진짜 Top-3 가 나왔어요. post_id 12(6개), 1(5개), 40(4개) 순이죠. 안쪽 인라인 뷰에서 정렬을 끝내 두니, 바깥의 ROWNUM 은 이미 줄 세워진 결과에서 위 3개를 떼어 내요.

⚠️ ROWNUM 은 정렬보다 먼저 매겨진다. WHERE ROWNUM <= N 과 ORDER BY 를 같은 레벨에 쓰면 "정렬 전에 잘린 엉뚱한 N개" 가 나온다. 반드시 인라인 뷰로 정렬을 먼저 끝내고 바깥에서 잘라야 한다. 이건 SQLD 단골 함정이에요. ★빈출

💡 한 줄 정리 — ROWNUM 은 정렬 전에 순번을 매긴다. Top-N 을 구하려면 인라인 뷰에서 ORDER BY 를 먼저 끝낸 뒤, 바깥에서 WHERE ROWNUM <= N 으로 잘라야 한다.

Step 6: "표준이 된 방식 ② FETCH FIRST" 🌟

ROWNUM 의 불편함을 걷어 낸 게 FETCH FIRST 예요. Oracle 12c 부터 들어온, 여러 데이터베이스가 함께 쓰는 표준 방식이죠. 읽는 그대로예요. "정렬해서 처음 N개만 가져와라."

SELECT post_id, COUNT(*) AS cnt
FROM post_like
GROUP BY post_id
ORDER BY COUNT(*) DESC
FETCH FIRST 3 ROWS ONLY;

같은 Top-3 가 나왔는데, 쿼리가 훨씬 읽기 쉽죠? 인라인 뷰로 감쌀 필요 없이, ORDER BY 뒤에 FETCH FIRST 3 ROWS ONLY 한 줄만 붙이면 끝이에요. ROWNUM 과 달리 FETCH FIRST 는 정렬을 끝낸 뒤에 동작하도록 설계돼서, 함정도 없고요.

아까 학생 질문에서 나온 "4~6위" 도 OFFSET 으로 간단히 풀려요. "앞의 3개는 건너뛰고(OFFSET) 그다음 3개를 가져와라" 예요.

SELECT post_id, COUNT(*) AS cnt
FROM post_like
GROUP BY post_id
ORDER BY COUNT(*) DESC, post_id
OFFSET 3 ROWS FETCH FIRST 3 ROWS ONLY;

OFFSET 3 ROWS 로 1~3위를 건너뛰고, FETCH FIRST 3 ROWS ONLY 로 그다음 3개(4~6위)를 가져왔어요. 이게 바로 게시판의 "다음 페이지" 를 만드는 원리예요. 한 페이지에 3개씩 보여준다면, 2페이지는 OFFSET 3, 3페이지는 OFFSET 6 … 이렇게 건너뛰는 양만 바꾸면 되죠.

💡 한 줄 정리 — ORDER BY ... FETCH FIRST N ROWS ONLY 는 정렬 후 상위 N개를 가져오는 표준 방식이다. OFFSET M ROWS 를 앞에 붙이면 M개를 건너뛰어 페이지를 넘길 수 있다.

Step 7: "가장 유연한 방식 ③ ROW_NUMBER 와 동점 처리"

세 번째 방식은 지난 시간 윈도우 함수에서 배운 ROW_NUMBER() 예요. 그때는 등수를 매기는 데 썼는데, 오늘은 그 등수를 가지고 "N등 이하만" 골라내는 Top-N 으로 응용해요.

SELECT post_id, cnt
FROM (SELECT post_id, COUNT(*) AS cnt,
             ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rn
      FROM post_like GROUP BY post_id)
WHERE rn <= 3;

인라인 뷰 안에서 ROW_NUMBER() 로 좋아요 많은 순으로 등수(rn)를 매기고, 바깥에서 WHERE rn <= 3 으로 3등까지만 거르는 거예요. 결과는 역시 post_id 12·1·40 으로 같아요. 그런데 ROW_NUMBER() 를 쓰면 좋은 점은, 등수를 매기는 방식을 바꿔 가며 동점을 다르게 다룰 수 있다는 거예요.

좋아요 4~5위를 보면 post_id 5 와 18 이 둘 다 3개로 동점이에요. 이 동점을 세 가지 순위 함수가 어떻게 다르게 처리하는지 나란히 봅시다.

SELECT post_id, cnt,
       ROW_NUMBER() OVER (ORDER BY cnt DESC) AS rn,
       RANK()       OVER (ORDER BY cnt DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY cnt DESC) AS dr
FROM (SELECT post_id, COUNT(*) AS cnt FROM post_like GROUP BY post_id)
ORDER BY cnt DESC
FETCH FIRST 6 ROWS ONLY;

동점인 post_id 5 와 18 을 보세요. 셋이 다르게 매겨요.

• ROW_NUMBER — 동점이어도 무조건 다른 번호. 4, 5 로 갈라요.
• RANK — 동점은 같은 등수(4, 4). 대신 다음은 건너뛰어 6 이 돼요.
• DENSE_RANK — 동점은 같은 등수(4, 4). 다음은 안 건너뛰고 5 예요.

그래서 "딱 3명만" 같은 자리에선 ROW_NUMBER 가 깔끔하고, "공동 4위까지 다 포함" 하려면 RANK 나 DENSE_RANK 가 맞아요. 같은 Top-N 이라도 동점을 어떻게 대할지에 따라 함수를 골라야 한다는 게 ★빈출 포인트예요.

💡 한 줄 정리 — ROW_NUMBER() 로 등수를 매기고 WHERE rn <= N 으로 거르면 Top-N 이 된다. 동점을 다르게 다루려면 RANK(건너뜀)·DENSE_RANK(안 건너뜀)를 골라 쓴다.

Step 8: "세 기법을 한자리에 — 인기글 리포트 만들기"

오늘 배운 셋을 모아 작은 리포트를 만들어 볼게요. "좋아요가 가장 많은 인기글 3개를, 각 게시물에 달린 해시태그와 함께" 보여주는 거예요. Top-N(ROW_NUMBER)으로 상위 3개를 고르고, LISTAGG 로 그 게시물의 태그를 한 줄로 모으고, 빈 태그는 NVL 로 메워요.

SELECT t.rnk, t.post_id, p.caption, t.likes,
       NVL(LISTAGG(h.name, ', ') WITHIN GROUP (ORDER BY h.hashtag_id), '(태그 없음)') AS tags
FROM (SELECT post_id, COUNT(*) AS likes,
             ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rnk
      FROM post_like GROUP BY post_id) t
JOIN post p ON p.post_id = t.post_id
LEFT JOIN post_hashtag ph ON ph.post_id = t.post_id
LEFT JOIN hashtag h ON h.hashtag_id = ph.hashtag_id
WHERE t.rnk <= 3
GROUP BY t.rnk, t.post_id, p.caption, t.likes
ORDER BY t.rnk;

한 화면에 오늘의 모든 게 담겼어요. ROW_NUMBER 가 좋아요 순으로 1·2·3 을 매겼고, LISTAGG 가 각 게시물 태그를 한 줄로 모았죠. 재미있는 건 맨 오른쪽 두 칸이에요. caption 의 #여행 #제주 #바다 (글 속 텍스트)와 tags 의 여행, 제주, 바다 (정규화 표에서 모은 것)가 나란히 있어요. 같은 해시태그를 텍스트로도, 표로도 다룰 수 있다는 걸 한눈에 보여주죠.

한 발 더 나아가, "가장 많이 쓰인 해시태그" 도 뽑아 봐요. 이번엔 태그를 기준으로 게시물 수를 세고, 상위 3개를 골라요.

SELECT name, post_cnt
FROM (SELECT h.name, COUNT(*) AS post_cnt,
             ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC, h.name) AS rn
      FROM hashtag h JOIN post_hashtag ph ON h.hashtag_id = ph.hashtag_id
      GROUP BY h.name)
WHERE rn <= 3;

개발 태그가 4개 게시물에 쓰여 1등이에요. 같은 M:N 표를 게시물 기준으로 보면 "이 게시물의 태그들", 태그 기준으로 보면 "이 태그를 쓴 게시물 수" 가 나와요. 연결 표 하나로 양방향을 다 볼 수 있는 게 M:N 의 매력이에요.

💡 한 줄 정리 — Top-N(ROW_NUMBER) · LISTAGG · NVL 을 한 쿼리에 엮으면, "인기글 + 태그 모음" 같은 실전 리포트가 나온다. M:N 연결 표는 게시물 기준·태그 기준 양방향 집계를 모두 받쳐 준다.

마무리

오늘은 데이터를 가로로 눕히고(PIVOT), 여러 줄을 한 줄로 모으고(LISTAGG), 정렬한 결과의 위만 떼어 내는(Top-N) 세 기법을 배웠어요. 지난 시간의 세로 소계와 오늘의 가로 교차표가 짝을 이루면서, 분석 쿼리의 큰 줄기를 거의 다 잡았어요.

오늘 배운 핵심 세 가지

• 💡 PIVOT — 한 컬럼의 값을 가로 열로 눕혀 교차표를 만든다. 펼칠 값은 IN 절에 직접 적고, UNPIVOT 으로 다시 세로로 되돌릴 수 있다.
• 💡 LISTAGG — 여러 행의 문자열을 WITHIN GROUP (ORDER BY) 순서로 한 줄에 이어 붙인다. 그룹 전체가 비면 NULL 이라 NVL 로 메운다.
• 💡 Top-N — ROWNUM 은 정렬 전에 잘리는 함정이 있어 인라인 뷰가 필요하고, FETCH FIRST 는 표준이라 간결하며, ROW_NUMBER 는 동점·그룹별 상위까지 가장 유연하다.

다음 시간 예고

지금까지 우리는 데이터를 자유자재로 조회하고 분석했어요. 다음 시간엔 한 발 물러서서, 이 데이터를 어떻게 안전하고 편하게 관리할지를 봐요. 분석을 넘어 "운영" 의 영역으로 들어가는 셈이에요.

누가 어떤 데이터를 보거나 고칠 수 있는지 정하는 권한 관리(GRANT·REVOKE), 오늘처럼 복잡한 조회를 한 번 정의해 두고 이름만으로 불러 쓰는 뷰, 그리고 post_id 같은 번호를 자동으로 매겨 주는 시퀀스를 배웁니다. 복잡한 일을 여러 단계로 묶어 한 덩어리로 실행하는 프로시저 개념도 함께 짚을 거예요.

과제

오늘 배운 PIVOT·LISTAGG·Top-N 을 직접 써 보는 과제예요. 결과가 몇 줄·몇 칸 나올지, 어떤 값이 들어갈지 먼저 머릿속으로 그려 본 뒤 실행해서 맞는지 확인해 보세요.

[기초] 카테고리별 좋아요를 가로로 펼치기

post 와 post_like 를 이어, 카테고리(여행·맛집·일상·음악·패션)를 가로 열로 펼친 PIVOT 표를 만들어 보세요. (가) 각 카테고리 게시물들이 받은 좋아요 수의 합계를 PIVOT 으로 가로 교차표 한 줄에 담으세요. (나) 같은 결과를 PIVOT 없이 GROUP BY 로도 구해 보고, 두 결과가 보여주는 모양이 어떻게 다른지 한 줄로 설명해 보세요.

[응용] 회원별 해시태그 모으기

회원이 자기 게시물들에 쓴 해시태그를 한 줄로 모아 보세요. (가) member · post · post_hashtag · hashtag 를 이어, 회원별로 사용한 해시태그를 LISTAGG 로 이어 붙이세요. 같은 태그가 여러 번 나오지 않게 LISTAGG(DISTINCT ...) 를 쓰고, 이름 가나다순으로 정렬하세요. (나) 게시물이 없거나 태그를 한 번도 안 쓴 회원은 결과가 어떻게 나오는지 확인하고, NVL 로 (없음) 을 채워 보세요.

[심화] 댓글이 가장 많은 게시물 Top-3 를 세 방식으로

post_comment 를 게시물별로 세어, 댓글이 가장 많은 게시물 3개를 뽑되 세 가지 방식으로 각각 짜 보세요. (가) ROWNUM (정렬 함정 피하기), (나) FETCH FIRST, (다) ROW_NUMBER. (라) 셋의 결과가 같은지 확인하고, 만약 댓글 수가 동점인 게시물이 있다면 어느 방식이 그 동점을 어떻게 다루는지 설명해 보세요.

생각해볼 주제

1. 교차표를 데이터베이스에서 PIVOT 할까, 화면단에서 펼칠까

월별·카테고리별 게시물 수 같은 교차표를 만들 때, 오늘처럼 데이터베이스가 PIVOT 으로 가로로 펼쳐 줄 수도 있고, 세로로 묶은 결과만 받아 와 화면(또는 엑셀)에서 가로로 돌릴 수도 있어요. 펼칠 열(카테고리)이 고정이 아니라 자주 늘었다 줄었다 한다면, 두 방식 중 어느 쪽이 더 유리할까요? PIVOT 의 IN 절에 열을 직접 적어야 한다는 제약과 함께 생각해 보세요.

2. 해시태그를 caption 텍스트로 둘까, 정규화 표로 둘까

오늘 우리는 같은 해시태그를 caption 인라인 텍스트와 post_hashtag 정규화 표 양쪽에 뒀어요. 둘을 모두 유지하면 "같은 정보가 두 군데 있는" 상태가 되죠. 이 중복이 주는 이점(조회 편의)과 위험(둘이 어긋날 가능성)을 견줘 보세요. 만약 하나만 둬야 한다면, 어떤 서비스에서 어느 쪽을 택하는 게 좋을까요?

3. ROWNUM 의 함정은 왜 생겼고, 왜 아직도 시험에 나올까

ROWNUM 이 정렬보다 먼저 매겨지는 동작은 분명 헷갈리고 실수를 부르는데도, Oracle 은 이 동작을 오랫동안 유지했고 시험도 꾸준히 이걸 물어요. 이렇게 "직관과 어긋나지만 바꾸지 않는" 동작이 데이터베이스에 남아 있는 이유는 무엇일까요? 이미 그 동작에 기대어 짜인 수많은 기존 쿼리와, 새로 들어온 FETCH FIRST 표준 사이의 관계로 생각해 보세요.