SQL의 순서

레코드에 순번 붙이기

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

윈도우 함수를 사용

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
```
- 윈도우 함수는 인덱스 스캔 1회, 상관 서브쿼리는 테이블 풀 스캔 2회
- 윈도우 함수를 사용하는 것이 성능 측면에서 좋음

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

윈도우 함수를 사용

SELECT 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

순번과 갱신

테이블에 순번 필드(seq)를 생성

윈도우 함수를 사용

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)

레코드에 순번 붙이기 응용

중앙값 구하기

집합 지향적 방법

SELECT AVG(weight)
  FROM (
SELECT W1.weight
  FROM Weights W1, Weights W2
  GROUP BY W1.weight
  HAVING SUM(CASE WHEN W2.weight >= W1.weight THEN 1 ELSE 0 END) >= COUNT(*) /2
    AND SUM(CASE WHEN W2.weight <= W1.weight THEN 1 ELSE 0 END) >= COUNT(*) /2) TMP

CASE식에 표현한 두 개의 특성 함수로 모집합 Weights를 상위 집합과 하위 집합으로 분할
코드가 복잡해서 무엇을 하고 있는지 한 번에 이해하기 어려움\
자기 결합을 수행하므로 성능이 나쁨

절차 지향적 방법 1 - 세계의 중심을 향해
```
SELECT AVG(weight)
FROM (
  SELECT W1.weight
    ROW_NUMBER() OVER (ORDER BY weight ASC, student_id ASC) hi,
    ROW_NUMBER() OVER (ORDER BY weight DESC, student_id DESC) lo,
    FROM Weights) TMP
WHERE hi IN (lo, lo+1, lo-1)
```
- IN 구문으로 홀수인 경우, 짝수인 경우를 한꺼번에 처리
- 비슷한 기능의 RANK 또는 DENSE_RANK는 중복이 발생할 수 있으므로 연속성과 유일성을 갖게 만들 수 없음
- Weights 테이블에 대한 접근이 1회로 감소, 정렬 2회
- ORDER BY의 정렬키에 기본 키인 student_id를 포함해야 함
- 포함하지 않을 경우 체중이 같은 학생이 여러 명 있을 때 NULL이 발생할 가능성이 있음
절차 지향적 방법 2 - 2 빼기 1은 1
```
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배하고 거기서 COUNT(*)를 빼서 diff를 구함
- 중간값에 해당하는 diff는 0부터 2 사이의 값을 가짐
- 이전 방법에 비해 정렬이 1회 감소하므로 성능 향상

순번을 사용한 테이블 분할

문제. 단절 구간 찾기

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

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)

간단하지만 자기 결합을 사용하여 비용이 높고 실행 계획이 불안정함

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

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 FOLLWING) - num)
  FROM numbers) TMP(num, diff)
WHERE diff <> 1

윈도우 함수로 ‘현재 레코드의 다음 레코드’를 구하고 두 레코드의 숫자 차이를 diff 필드에 저장
결합을 사용하지 않으므로 성능이 안정적

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

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

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 PRECEDING AND 1 PRECEDING) AS prev_diff,
          ROW_NUMBER() OVER (ORDER BY num) AS seq
          FROM Numbers) TMP1 ) TMP2) TMP3
 WHERE low IS NOT NULL

TMP1은 현재 레코드와 전후의 레코드에 있는 num 값의 차이를 구함
max_diff는 다음 레코드의 num에서 현재 레코드의 num을 뺀 값이고, prev_diff는 현재 레코드의 num에서 이전 레코드의 num을 뺀 값
- next_diff나 prev_diff가 1보다 크다는 것은 단절된 부분이 있음을 의미
TMP3에서 동일한 레코드에 low와 high 필드가 존재하지 않는 경우를 정리
WHERE low IS NOT NULL로 불필요한 레코드 제거
서브쿼리 스캔의 비용은 테이블의 크기에 비례하므로 성능 측면에서 집합 지향 쿼리에 비해 좋다고 단언할 수는 없음

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

시퀀스 객체, IDENTITY 필드는 최대한 사용하지 않고 꼭 필요한 부분에만 사용
IDENTITY 필드보다는 시퀀스 객체를 활용
시퀀스 객체는 MySQL이 지원하지 않고, IDENTITY 필드는 Orcale이 지원하지 않음

시퀀스 객체

CREATE SEQUENCE testseq
START WITH 1
INCREMENT BY 1
MAXVALUE 1000
MINVALUE 1
CYCLE

INSERT INTO HogeTbl VALUES(NEXT VALUE FOR nextval, 'a', 'b', ...)

시퀀스 객체의 문제점
- 표준화가 늦어서 구현에 따라 구문이 달라 이식성이 없고, 사용할 수 없는 구현도 있음
- 시스템에서 자동으로 생성되는 값이므로 실제 엔티티 속성이 아님
- 성능적인 문제 발생
시퀀스 객체로 발생하는 성능 문제
- 시퀀스 객체의 로직에 의존하는 문제
  - 시퀀스 객체는 유일성, 연속성, 순서성을 만족
  - 동시 실행을 제어하기 위하여 락을 사용하므로 성능 저하 및 오버헤드
시퀀스 객체로 발생하는 성능 문제의 대처
- CACHE: 새로운 값이 필요할 때마다 메모리에 읽어들일 필요가 있는 값의 수를 설정
  - 시스템 장애 시 연속성 보장 불가능
- NOORDER: 순서성을 담보하지 않으므로 오버헤드 감소
  - 순서성이 필요한 경우 사용할 수 없음
순번을 키로 사용할 때의 성능 문제
- 순번처럼 비슷한 데이터를 연속적으로 INSERT하면 물리적으로 같은 영역에 저장됨
- 저장소의 특정 물리적 블록에만 I/O 부하가 커지므로 성능 악화 발생 (Hot Spot, Hot Block)
- RDB 설계 특성상 이러한 물리적인 계층의 접근 패턴을 사용자가 바꿀 수 없음
순번을 키로 사용할 때의 성능 문제에 대처
- DBMS 내부에서 변화를 주어 제대로 분산할 수 있는 구조(일종의 해시)를 사용
  - INSERT 구문 자체는 빨라지지만 SELECT 구문의 성능이 나빠질 위험이 있음
- 인덱스에 복잡한 필드를 추가하여 데이터의 분산도를 높임
  - 인덱스에 불필요한 필드를 키로 추가하는 것은 좋은 설계가 아님

IDENTITY 필드

테이블의 필드로 정의하고, 테이블에 INSERT가 발생할 때마다 자동으로 순번을 붙여주는 기능
테이블에 종속적이므로 CACHE나 NOORDER를 지정할 수 없어 성능상 이점 없음

채번 테이블

시퀀스 객체나 IDENTITY 필드를 모두 지원하지 않을 경우 사용
개선 방법이 없으므로 사용할 이유가 없음