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

SQL 레벨업

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

- 윈도우 함수를 사용

-- 기본 키가 한 개의 필드일 경우(ROW_NUMBER)
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. 기본 키가 여러 개의 필드로 구성되는 경우

- 윈도우 함수를 사용

-- 기본 키가 여러 개의 필드로 구성되는 경우(ROW_NUMBER)
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개 이상일 때도 간단하게 확장 할 수 있다.

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

- 윈도우 함수를 사용

-- 학급마다 순번 붙이기(ROW_NUMBER)
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. 순번과 갱신

- 윈도우 함수를 사용

-- 순번 갱신(ROW_NUMBER)
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 Wights3
  SET seq = (SELECT COUNT(*)
                FROM Weights3 W2
                WHERE W2.class = Weights3.class
                  AND W2.student_id <= Weights3.student_id);