[데이터베이스] #8 SQL V (랭킹, 기타 기능)

목차

1. 랭킹
   1. 랭킹
   2. 널 값 랭크
   3. 파티션 랭킹
   4. 다른 랭킹 함수
2. 기타 기능
   1. 기존 스키마/데이터 재활용
   2. 대용량 객체 타입
   3. 내장 날짜 타입
   4. 사용자 정의 타입
   5. 도메인
   6. 트랜잭션
   7. 색인(index)

---

1. 랭킹

상위 또는 하위 몇 개(또는 퍼센트)만의 터플을 반환

1. 랭킹

Select ID, rank() over (order by GPA desc) as sRank
from studentGrades;

랭크만 부여

Select ID, rank() over (order by GPA desc) as sRank
from studentGrades
order by sRank;

랭크 부여 후 랭크 순으로 정렬

(GPA는 평균 학점)

rank(), dense_rank()

100점, 100점, 80점, 80점, 60점의 데이터가 있을 때,

• rank(): 중복 값 상관 없이 개별적인 터플 당 랭크를 계산
• → 1등, 1등, 3등, 3등, 5등
• dense_rank(): 아래 쿼리는 값이 중복인 터플의 랭크를 하나로 계산
• → 1등, 1등, 2등, 2등, 3등

rank() 함수를 사용하지 않는다면 correlated된 서브질의 형태이므로 실행이 비효율적

2. 널 값 랭크

널 값은 가장 처음에 위치하거나 가장 나중에 위치할 수 있음

• NULLS LAST if the sort is ASC
• NULLS FIRST if the sort is DESC
• 지정되지 않으면 NULLS LAST, sort ASC

직접 지정도 가능

Select ID,
rank() over (order by GPA desc nulls last) as sRank
from studentGrades

3. 파티션 랭킹

Select ID, deptName,
rank() over (partition by deptName order by GPA desc)
								as deptRank
from studentGrades
order by deptName, deptRank;

• deptName 오름차순
• 이후 deptName으로 나눈 각 그룹에 대하 GPA 내림차순으로 랭킹

4. 다른 랭킹 함수

percent_rank()

• (r-1)/(n-1)
• 터플의 개수 n, 터플의 랭크 r (null if n = 1)
• 예제 n=4
  1. r=1, percent _rank() = 0/3 = 0
  2. r=2, percent_rank() = 1/3 = 0.33
  3. r=3, percent_rank() = 2/3 = 0.66
  4. r=4, percent_rank() = 3/3 = 1.00

cume_dist() //cumlative distribution

• p/n
• p는 순서 값이 앞에 오거나 같은 튜플의 개수이고, n은 튜플의 개수

row_number()

• 정렬 후 각 행에 1부터 시작하는 고유 번호 부여
• 중복에 대해 결정하지 않음

ntile()

• 해당 속성 값을 기준으로 전체 테이블을 균등하게 n 등분

Select ID, ntile(4) over (order by GPA desc) as quartile
from studentGrades;

 

---

2. 기타 기능

1. 기존 스키마/데이터 재활용

Create table t1 like professor;

일부 제약사항은 복제되지 않음 ex) primary key, foregin key, indexes

Create table t2 as (select * from professor) with data;

Create table t3
as (select * from professor where deptName = 'SW');

like 대신 as를 사용하여 전체 또는 일부 복제 가능

SQL: 2003 표준에 의하면 with data가 없으면 데이터는 복제되지 않으나 많은 상용 시스템은 with data와 무관하게 데이터도 복제함

2. 대용량 객체 타입

대용량 객체: 사진, 비디오 등

데이터베이스 시스템은 이를 위해 두 가지 타입 제공(blob, clob)

• blob: 이진 데이터
• clob: 문자 데이터

3. 내장 날짜 타입

• date - ‘2024-5-12’
• time - ‘09:00:30’ ‘09:00:30.75’
• timestamp - ‘2024-5-12 09:00:30.75’
• interval - 시간 기간, 위 세 타입을 가감하여 지정

4. 사용자 정의 타입

create type 사용

Create type Dollars as numeric (12,2) final;

Create table myDepartment
					(deptName varchar(20),
					building varchar(15),
					budget Dollars);

final: 서브 타입이 생성될 수 없음

not final: 서브 타입이 생성될 수 있음

5. 도메인

create domain 사용

Create domain degreeLevel varchar(10)
constraint degreeLevelTest
check (value in ('Bachelor', 'Master', 'Doctorate'));

타입과 사용법이 유사하나,

도메인은 무결성 제약을 가질 수 있고 타입은 가질 수 없음

6. 트랜잭션

ACID를 가지는 데이터베이스 연산의 나열

• atomicity
• consistency
• isolation
• durability

DML 문장으로 시작, “commit” 또는 “rollback”으로 종료

대부분 SQL 문장 하나하나를 트랜잭션으로 처리

7. 색인(index)

SQL 표준은 색인에 관련되는 기능 제공하지 않음

상용 데이터베이스는 색인 제공

Create index myCourseIndex on course(cID);

course 테이블의 cID에 대해 색인 생성