SQL 레벨업 - 8장

23 강 레코드에 순번 붙이기

- 윈도우 함수

- 상관 서브 쿼리

 

-> 기능적으로 동일하지만, 성능 측면에서 윈도우 함수를 사용하는 편이 좋음. 윈도우 함수에서는 스캔 횟수가 1회임. 인덱스 온리 스캔을 사용하므로 테이블에 직접적인 접은을 회피함. 상관 서브커리를 사용하면 2회의 스캔이 발생함. 

 

서브쿼리 보다는 윈도우 함수를 사용하는 것이 더 좋은 성능

1. 기본키가 한 개의 필드일 경우
2. 기본키가 여러 개의 필드일 경우
   상관 서브쿼리의 경우 다중 필드 비교를 사용.
3. 그룹마다 순번을 붙이는 경우
4. 순번과 갱신
   기존 테이블에 순번 필드를 추가하여 순번을 갱신하는 구문을 설명

1. 기본 키가 한 개의 필드일 경우

<윈도우 함수>

SELECT student_id,
        ROW_NUMBER() OVER (ORDER BY student_id) AS seq
FROM Weights;

 

<상관 서브쿼리>

SELECT student_id,
        (SELECT COUNT(*)
        FROM Weights W2
        WHERE w2.student_id <= W1.student_id) AS seq
FROM Weights W1;

 

2.기본 키가 여러 개의 필드로 구성되는 경우

<윈도우 함수>

SELECT class, student_id,
        ROW_NUMBER() OVER (ORDER BY class, student_id) AS seq
FROM Weights2;

<상관 서브쿼리>

SELECT class, student_id,
        (SELECT COUNT(*)
        FROM Weights2 W2
        WHERE (W2.class, W2.student_id) <= (W1.class, W1.student_id) ) AS seq
FROM Weights2 W1;

 

3. 그룹마다 순번을 붙이는 경우

<윈도우 함수>

SELECT class, student_id,
        ROW_NUMBER() OVER (PARTITION BY class ORDER BY student_id) AS seq
FROM Weights2;

<상관 서브쿼리>

SELECT class, student_id,
        (SELECT COUNT(*)
        FROM Weights2 W2
        WHERE W2.class = W1.class AND W2.student_id <= W1.student_id) AS seq
FROM Weights2 W1;

 

4.순번과 갱신

<윈도우 함수>

UPDATE Weights3
    SET seq = (SELECT seq
    FROM ( SELECT class, student_id,
            ROW_NUMBER() OVER (PARTITION BY class ORDER BY student_id) AS seq
            FROM Weights3) SeqTbl
    WHERE Weights3.class = SeqTbl.class
        AND Weights3.student_id = SeqTbl.student_id);

<상관 서브쿼리 사용>

UPDATE Weights3
SET seq = (SELECT COUNT(*)
        FROM Weights3 W2
        WHERE W2.class = Weights3.class AND W2.student_id <= Weights3.student_id);

24강 레코드에 순번 붙이기 응용

1. 중앙값 구하기 ( 숫자를 정렬하고 양쪽 끝에서부터 수를 세는 경우 정중앙에 오는 값)

데이터 수가 홀수일 때와 짝수일 때가 드르므로 샘플 데이터도 두 가지 패턴이 필요함

- 홀수 일 때 : 중앙의 값을 사용

- 짝수 일 때 : 중앙의 두 값을 평균내어 사용

 

집합 지향적 방법

테이블을 상위직합과 하위집합으로 분할하고 그 공통 부분을 검색하는 방법

 

SELECT AVG(weight)
FROM (SELECT W1.weight
        FROM Weights W1, Weights w2
        GROUP BY W1.weight
        -- S1(하위 집합의 조건)
        HAVING SUM(CASE 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;

- HAVING 구 와 CASE 식 함수로 상위 집합과 하위 집합 분할

- AVG 함수를 사용해 레코드 수가 짝수일 때 평균 처리

 

절차 지향적 방법 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);

 

양쪽 끝에서 레코드 하나씩 세어 중간을 찾음

 

홀수일 경우 hi=lo 가 되어 중심점이 반드시 하나만 존재

짝수일 경우 hi=lo+1 , hi=lo-1 두 개 존재

홀수와 짝수일 경우의 조건 분기를 IN연산자로 한꺼번에 수행

 

주의할 점

- RANK, DENSE_RANK 대신 ROW_NUMBER 함수를 사용해야 레코드 집합에 자연수 할당해서 연속성과 유일성 가질 수 있음
- ORDER BY 정렬 키에 weight 뿐만 아니라 student_id도 포함해야 정확한 결과 나옴

 

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

 

ROW-NUMBER 을 사용

SELECT AVG(weight) AS median
FROM (SELECT weight,
        2 * ROW_NUMBER() OVER (ORDER BY weight) - COUNT(*) OVER() AS diff
        FROM Weights) TMP
WHERE diff BETWEEN 0 AND 2;

 

ROW NUMBER 함수로 구한 순번을 2배해서 diff를 구하고, 거기에서 COUNT(*)을 빼는 것

-> 정렬이 1회 줄어듦

 

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

테이블을 여러 그룹으로 분할하기

- 단절 구간 찾기 : 비어있는 숫자를 출력하기

gap_start ~ gap_end
--------------------
      2   ~ 2
      5   ~ 6
      10  ~ 11

 

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

SELECT (N1.num+1) AS gap_start,
        '~',
        (MIN*N2.num)-1) AS gap_end
FROM Numbers N1 INNER JOIN Numbers N2 ON N2.num > N1.num
GROUP BY N1.num
HAVING (N1.num+1) < MIN(N2.num);

N2.num을 사용해 현재 레코드 값 N1.num 보다 큰 숫자의 집합을 조건으로 지정

min(N2.num)으로 N1.num의 바로 다음 숫자를 지정해 차이가 1보다 클 경우 비어있는 숫자로 간주

 

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

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;

 

3. 테이블에 존재하는 시퀀스 구하기

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

SELECT MIN(num) AS low,
        '~'
        MAX(num) AS high
FROM (SELECT N1.num,
            COUNT(N2.num) - N1.num
        FROM Numbers N1 INNER JOIN Numbers N2 ON N2.num <= N1.num
        GROUP BY N1.num) N(num, gp)
GROUP BY gp;

 

절차 지향형 방법: 다음 레코드 하나와 비교

SELECT low, high
FROM (SELECT low,
            CASE WHEN high IS NULL
            THEN MIN(high) OVER(ORDER BY seq
                                ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
            ELSE high END AS high
            FROM (SELECT CASE WHEN COALESCE(prev_diff,0) <>1
                            THEN num ELSE NULL END AS low,
                         CASE WHEN COALESCE(next_diff,0) <>1
                            THEN num ELSE NULL END AS high,
                         seq
                  FROM (SELECT num, 
                              MAX(num) OVER (ORDER BY num ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) - num AS next_diff,
                              num-MAX(num) OVER (ORDER BY num ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) - num AS prev_diff,
                              ROW_NUMBER() OVER (ORDER BY num) As seq
                        FROM numbers) TMP1) TMP2) TMP3
WHERE low IS NOT NULL;

 

25강 시퀀스 객체, IDENTITY 필드, 채번 테이블

1. 시퀀스 객체

CREATE SEQUENCE testseq
START WITH 1
INCREMENT BY 1
MAXVALUE 100000
MINVALUE 1
CYCLE;

 

- 초깃값, 증가값, 최댓값, 최솟값, 최댓값에 도달했을 때 순환 유무 등의 옵션을 지정 가능

- 시퀀스 객체가 생성하는 순번은 유일성, 연속성, 순서성을 가짐

 

2. IDENTITY 필드

- 테이블의 필드로 정의하고 테이블에 INSERT 발생할 때마다 자동으로 순번 붙여줌

- 특정한 테이블과 연결되어 여러 테이블에서 사용 불가

- CACHE, NOORDER를 지정할 수 없거나 제한적으로만 사용 가능

 

3. 채번 테이블

- 과거 사용하던 순번 생성하는 전용 테이블

- 테이블를 활용해 유사적으로 시퀀스 객체 락 메커니즘을 구현한 것