024. 레코드에 순번 붙이기 응용 - llighter/database GitHub Wiki

SQL 레벨업

테이블의 레코드에 순번을 붙일 수 있다면,SQL에서 자연 수열(순번)의 성질을 활용한 다양한 테크닉을 사용할 수 있다.

1. 중앙값 구하기

통계의 지표로 사용되는 중앙값(median, 중간값)을 구하는 방법을 생각해보자.

중앙값은 평균과 단순 평균(mean)과 다르게 아웃라이어에 영향을 받지 않는다는 장점이 있다.

- 집합 지향적 방법

-- 중앙값 구하기(집합 지향적 방법): 모집합을 상위와 하위로 분할
SELECT AVG(weight)
   FROM (SELECT W1.weight
           FROM Weights W1, Weights W2
           GROUP BY W1.weight
           -- S1(하위 집합)의 조건
           HAVING SUM(CASSE WHEN W2.weight >= W1.weight THEN 1 ELSE 0 END)
                    >= COUNT(*) / 2
           -- S2(상위 집합)의 조건
             AND SUM(CASE WHEN W2.weight <= W1.weight THEN 1 ELSE 0 END)
                    >= COUNT(*) / 2) TMP;

위 방법의 두 가지 단점

1. 코드가 복잡해서 무엇을 하고 있는 것인지 이해하기 힘들다.
2. 성능이 나쁘다.

- 절차 지향적 방법 1 - 세계의 중심을 향해

-- 중앙값 구하기(절차 지향형 1): 양쪽 끝에서 레코드 하나씩 세어 중간을 찾음
SELECT AVG(weight) AS median
FROM (SELECT weight,
          ROW_NUMBER() OVER(ORDER BY weight ASC, student_id ASC) AS hi,
          ROW_NUMBER() OVER(ORDER BY weight DESC, student_id DESC) AS lo
        FROM Weights) TMP
WHERE hi In(lo, lo+1, lo-1);

- 절차 지향적 방법 2 - 2 빼기 1은 1

ROW_NUMBER를 사용한 것 보다 성능을 개선하기

-- 중앙값 구하기(절차 지향적 방법) 2 : 반환점 발견
SELECT AVG(weight)
FROM (SELECT weight,
        2 * ROW_NUMBER() OVER(ORDER BY weight)
          - COUNT(*) OVER() AS diff
      FROM Weights) TMP
WHERE diff BETWEEN 0 AND 2;

2. 순번을 사용한 테이블 분할

- 단절 구간 찾기

레스토랑에서 테이블 번호가 빠져 있는 것은 예약된 테이블이라는 것이다.

빠져 있는 테이블을 gap_start ~ gap_end 와 같은 테이블 형식으로 구하라.

- 집합 지향적 방법 - 집합의 경계선

-- 비어있는 숫자 모음을 표시
SELECT (N1.num + 1) AS gap_start,
    '~',
    (MIN(N2.num) - 1) AS gap_end
  FROM Numbers N1 INNER JOIN Numbers N2
    ON N2.num > N2.num
  GROUP BY N1.num
HAVING (N1.num + 1) < MIN(M2.num);

- 절차 지향적 방법 - '다음 레코드'와 비교

-- 다음 레코드와 비교
SELECT num + 1 AS gap_start
       '-',
       (num + diff -1) AS gap end
FROM (SELECT num,
             MAX(num)
               OVER(ORDER BY num
                   ROWS BETWEEN 1 FOLLOWING
                          AND 1 FOLLOWING - num
      FROM Numbers) TMP(num, diff)
WHERE diff <> 1;