[SQL] 12. SQL 레코드 순서 응용1 (예제 - 레코드에 순번 붙이기)

[SQL 레코드 순서 응용1]

- 레코드 안에 순번을 매기는 예제

 이번 포스팅부터는 이제까지 배운 SQL의 기초 함수를 활용한 다양한 예제 풀이를 통해 응용된 쿼리를 익혀보도록 하겠다.

 

우선 순서 조작의 기초 쿼리를 이해하며, 아래의 4가지 과정을 통해 레코드에 순번을 붙이는 방법을 살펴보려 한다.

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

2. 기본 키가 여러 개의 필드일 경우

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

4. SEQ 필드에 순번 갱신하기

 

 

 

 

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

기본 키가 한 개의 필드일 경우 순번(seq)를 매기는 예시를 살펴보려한다. 예시를 위해 아래의 Weights 테이블을 생성하였다. 

 

- Weights 테이블 생성 -

 

 

 

 

- WINDOW 함수 활용

ROW_NUMBER 함수를 사용할 수 있는 환경(Mysql은 지원하지 않음)이라면 아래처럼 WINDOW 함수를 활용하여 구현할 수 있다.

 

- 기본키가 한 개일때 순번 매기기 (WINDOW 함수 활용) -

 

 

 

- 상관 서브쿼리 사용

Mysql처럼 ROW_NUMBER 함수를 지원하지 않는 경우에는 상관 서브쿼리를 사용하여 작성해야 한다.

Weights 테이블을 두 번 불러와 W1,W2에 각각 할당시키고, W1의 student_id의 레코드값보다 작거나 같은 W2의 student_id의 개수를 센다. COUNT로 세어진 W2의 레코드 수는 W1의 seq의 value 값이 된다.

 

- 기본키가 한 개일때 순번 매기기 (상관 서브쿼리 활용) -

 

 

 

 

 

2. 기본 키가 여러 개의 필드일 경우

이번에는 위의 예제를 응용한 버전이다. 기본 키가 학급(class)과 학생 ID(student_id)인 경우로, 아래 Weights2 테이블을 활용하여 순번을 매겨볼 것이다.

 

- Weights 테이블에서 class 필드가 더해진 Weights2 테이블 생성 -

 

 

 

- WINDOW 함수 활용

ROW_NUMBER를 사용할 수 있는 환경에서는 필드가 여러 개여도 ORDER BY 키에 필드를 추가하면 되므로, 쿼리가 복잡하지 않다.

 

- 기본키가 여러 개일 떄 순번 매기기 (WINDOW 함수 활용) -

 

 

 

- 상관 서브쿼리 활용

이 예시에서 서브쿼리를 사용할 때는 '다중 필드 비교'를 사용하는데, 복합적인 필드를 하나의 값으로 연결하고 한번에 비교를 할 수 있게 하는 기능이다. 위의 예시와 다른 점은 다중 필드를 괄호로 묶어 비교하는 것으로, 개념은 다르지 않다.

 

- 기본키가 여러 개일 떄 순번 매기기 (상관 서브쿼리 활용) -

 

 

 

 

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

이번 예시에서는 학급마다 따로 순번을 붙이는 경우이다. 학급(class)레코드 별로 그룹을 나눈 뒤, 그 안에서 순번을 매기는 쿼리를 만들어야 한다.

 

 

- WINDOW 함수 활용

WINDOW 함수를 활용할 때는 앞서 배웠던 PARTITION BY 옵션을 사용하여 구현하면 된다.

 

- Weights2 테이블에서 그룹마다 순번을 매기는 경우 (WINDOW 함수 활용) -

 

 

 

- 상관 서브쿼리 활용

상관 서브쿼리에서는 1번 예시인 '기본 키가 한개일 경우'의 쿼리와 비슷하다.

 

- Weights2 테이블에서 그룹마다 순번을 매기는 경우 (서브 상관쿼리 활용) -

 

 

 

 

4. SEQ 필드에 순번 갱신하기

이번 예시에서는 검색이 아닌 갱신을 함으로써 순번을 매겨보겠다. 우선, 순번을 갱신할 seq 필드에 Null값을 지정하여 Weights3 테이블을 새로 생성하였다. 

 

- Weights2 테이블에서 seq 필드(value는 Null)를 추가한 Weights3 테이블 생성 -

 

 

 

 

- WINDOW 함수 활용

기본적으로, 테이블에 value값을 갱신할 때는 UPDATE - SET 구를 활용해야 한다. UPDATE 할 테이블을 불러오는 SELECT 구문 안에, 순번을 매기는 조건이 담긴 쿼리를 담는 서브쿼리 구조가 필요로하다.

 

- Weights3 테이블의 seq 필드에 순번을 갱신 (WINDOW 함수 활용) -

 

 

 

 

- 상관 서브쿼리 활용

갱신을 하는 경우에는 오히려 상관 서브쿼리의 경우가 더 단순하다. 위의 예시와 같은 구조로 순번을 매겨 그대로 UPDATE - SET 구에 넣으면 된다.

 

- Weights3 테이블의 seq 필드에 순번을 갱신 (상관 서브쿼리 활용) -

 

 

다음 포스팅에서는 이렇게 활용했던 순번을 활용해보는 예제를 살펴볼 것이다.