[DB] 04. SQL 2

Aggregate Functions (집계 함수)

데이터베이스 시스템은 5개의 집계 함수를 지원

관계의 속성값에 작용하고 값을 반환

• avg : 평균값
• min : 최솟값
• max : 최댓값
• sum : 값의 합
• count : 값의 개수

---

집계 함수 예제

---

Group By 절

---

Having 절

‘group by’ 절로 생성된 그룹에 대하여 임의 조건을 명시하는 절

 

having 절의 조건문은 그룹 형성 이후에 적용

where 절의 조건문은 그룹 형성 이전에 적용

---

having 절과 where 절의 차이 예제

---

Null 값과 집계 함수

count(*)를 제외한 모든 집계 함수는 집계된 속성의 null 값을 가진 tuple을 무시

모든 값이 null인 경우, count는 0을 반환

다른 모든 집계 함수는 null을 반환

---

Joined Relations

조인 연산 : 두 개의 관계를 가져와 결과로 다른 하나의 관계를 반환하는 연산

일반적으로 from 절의 서브쿼리 표현으로 사용

 

Join Type은 각 관계에서 일치하지 않는 tuple을 어떻게 처리할지를 정의

• Inner join
• Left outer join
• Right outer join
• Full outer join

외부 조인 (Outer Join)

• 정보 손실을 피하기 위한 조인 연산의 확장
• 조인 연산 후 하나의 관계에 속하지 않은 tuple을 조인 결과에 추가 (null 값을 이용) 

조인 조건 (Join Conditions)

• 조인 조건은 두 관계에서 어떤 tuple이 매치되며 조인 결과에 어떤 속성이 있는지를 정의
• natural : 공통 속성이 조인 속성 / 공통 속성은 결과에 한 번만 나타남
• on <predicate> : 조인 조건을 명시하는 방법
• using (A1, …, An) : 자연 조인과 유사 / 조인 속성은 모든 공통 속성이 아니라 (A1, …, An) 속성임

---

Nested Subqueries (중첩 서브질의) 

SQL은 서브쿼리의 중첩을 제공

서브쿼리 : 다른 쿼리 내에 중첩된 select-from-where 표현식

서브쿼리의 일반적인 사용 : 집합 멤버십과 집합 비교를 위한 테스트를 수행

---

Single-row 서브쿼리 예제

서브 쿼리는 단일 tuple을 반환 (scalar subquery라고도 함)

---

IN 연산자

단일 값이 다수 값에 속하는지를 검사

---

서브쿼리 예제

---

비교 연산자 (Comparison Operators)

• 단일 값 간의 비교는 간단
• 단일 값과 여러 값 간의 비교는 복잡
• SQL은 연산자(some, any, all, in)을 제공

---

집합 비교 예제

---

상관 서브 쿼리 (Correlated Subqueries)

• 외부 쿼리의 변수는 where 절의 서브 쿼리에서 사용될 수 있음
• 외부 쿼리의 상관 이름을 사용하는 서브 쿼리를 상관 서브 쿼리라고 함
• 서브 쿼리는 변수의 각 값에 대해 평가됨
• 시간이 많이 소요되는 평가

---

“exists” 구성 요소

• “exists” 구조는 인자인 서브 쿼리가 비어있지 않을 때 ‘참’을 반환
• "exists r"는 "r ≠ Ø"인 경우 -> 참
• "not exists r"은 "r = Ø"인 경우 -> 참

“exists” 예제

---

“for all” 쿼리(질의)

---

“unique” 구성 요소

“unique” 구조는 서브 쿼리의 결과에 중복된 tuple이 있는지 검사

“unique”는 관계에 두 개의 tuple t1과 t2가 포함되어 있고 t1 = t2인 경우에만 실패
또는 tuple의 속성이 1개라도 null 값을 가지면 동일하지 않다고 판별하므로 ‘참’을 반환 ("unique(Ø)" : true)

---

“from” 절 서브 쿼리

서브 쿼리 표현을 “from” 절에서 사용 가능

---

“lateral” 절

"from" 절에 "lateral" 키워드를 사용하여 선행 관계(테이블) 또는 "from" 절 안의 서브 쿼리를 참조하게 함

---

“with” 절

쿼리 내에서만 사용 가능한 일시적인 뷰를 정의할 수 있는 방법을 제공 (임시적으로 저장하는 효과)

---

Scalar 서브 쿼리

• 스칼라 서브 쿼리 : 단일 속성을 포함한 오직 하나의 tuple만을 반환하는 서브 쿼리
• “select”, “where”, “having” 절에서 사용 가능
• 스칼라 서브 쿼리가 실행될 때, 하나보다 많은 tuple이 반환되면 run-time error가 발생

---

Ranking

Top, Limit, Rownum 절

 

SQL Server

• top 절은 테이블에서 상위 N개(백분율)의 레코드를 가져오는 데 사용

 

MySQL

• select * from professor order by salary desc limit 3; 
• select * from professor order by salary desc limit 3 offset 1;

Oracle

• select * from (select * from professor order by salary desc) where ROWNUM <= 3;

---

Ranking

Ranking은 “order by” 명세와 함께 사용

 

rank()" 함수는 순위 간에 빈 칸을 남겨 두지만, "dense_rank()" 함수는 빈 칸을 남기지 않음

 

랭킹은 기본 SQL 집계를 사용하여 수행할 수 있지만 결과 쿼리가 매우 비효율적

 

---

Ranks with Null

SQL:1999에서 사용자가 "nulls first" 또는 "nulls last"를 지정할 수 있게 허용

---

Ranking within Partition

데이터의 파티션 내에서 순위 지정 가능

 

하나의 select 절에서 여러 개의 rank 절을 사용 가능
순위를 매기고 데이터를 파티션으로 나누는 것은 "group by" 절을 적용한 후에 이루어짐

이를 사용하여 상위 n개 결과를 찾을 수 있음 (다른 DB에서 지원하는 "limit n" 절보다 더 일반적으로 사용할 수 있음)

---

Ranking 함수

• percent_rank() : 순위를 분수 형태로 반환 / 만약 n개의 tuple이 있고 해당 tuple의 순위가 r이라면, 
• percent_rank()는 (r-1)/(n-1)으로 정의됩니다 (n=1일 경우 null) (n=4, r=2면 percent_rank() = 1/3) - cume_dist(): p/n을 반환 / p는 주어진 정렬 값 이전 또는 해당 값과 동일한 값들의 tuple 수이며, n은 전체 tuple 수
• row_number(): 행을 정렬하고 각 행에 1부터 시작하는 고유 번호를 부여 / 중복된 행이 있는 경우 비결정적

---

ntile( ) Ranking

ntile(n) 랭크는 해당 속성값을 기준으로 전체 테이블을 균등하게 n등분함

---

More Features

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

• Create table t1 like professor;

=> professor와 같은 스키마를 가지는 테이블 t1을 생성
=> like 키워드는 테이블에 관련된 모든 제약 사항이 함께 복제되지는 않음 (주 키, 외래 키, 인덱스 등은 복사 X)

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

=> 같은 스키마와 데이터를 가지는 테이블 t2를 생성 (as를 사용함)

• Create table t3 as (select * from professor where deptName=‘SW’);

=> t3은 deptName이 'SW'인 professor 테이블의 데이터를 가짐

---

대용량 객체 타입

• blob (바이너리 대형 객체) : 해석이 데이터베이스 시스템 외부의 응용 프로그램에 맡겨진 대용량의 이진 데이터 모음
• clob (문자 대형 객체) : 대용량의 문자 데이터 모음 
• 쿼리가 대형 객체를 반환할 때, 대형 객체 자체 대신에 포인터가 반환
• 대형 객체의 예시 : 'bookReview clob(10KB)', 'myImage blob(10MB)', 'myMovie blob(2GB)' 등

내장 날짜 타입 (Built-in Date Type)

• date : 연도(4자리), 월, 일을 포함하는 날짜 (예시 : date '2015-7-27')
• time : 시간, 분, 초로 된 하루 중 시간 (예시 : time '09:00:30', time '09:00:30.75')
• timestamp : 날짜와 하루 중 시간을 합친 값 (예시 : timestamp '2015-7-27 09:00:30.75')
• interval: 시간 간격 (예시 : interval '1' day)
• 날짜/시간/타임스탬프 값에서 다른 날짜/시간/타임스탬프 값을 빼면 간격 값(interval)이 생성
• 간격 값(interval)은 날짜/시간/타임스탬프 값에 더할 수 있음

---

사용자 정의 타입 (User-Defined Types)

• “create type” 구조는 사용자 정의 형식을 생성

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

• "final"은 주어진 형식에서 서브 타입을 생성할 수 없음을 의미
• "not final"은 서브 타입을 생성할 수 있음을 나타냄

---

도메인 (Domains)

"create domain" 구조는 사용자 정의 도메인 유형을 생성

 

타입과 도메인으니 유사

도메인은 무결성 제약을 가질 수 있음

---

트랜잭션 (Transactions)

• 트랜잭션 : ACID 속성을 가진 데이터베이스 연산의 나열 (데이터베이스 작업의 논리적 단위)
• 트랜잭션은 암시적으로 시작되며 “commit work” 또는 “rollback work”로 끝남
   - SQL:1999에서는 "begin atomic"과 "end" 사이에 여러 문을 묶을 수 있음
• 대부분의 데이터베이스에서는 기본적으로 각 SQL 문이 자동으로 커밋 (auto_commit)
• DDL 문은 자체적으로 커밋

색인 (인덱스) (Indices)

• SQL은 인덱스 관련 기능을 공식적으로 제공하지 않음
• 상용 DBMS는 인덱스의 생성을 지원
   - Create index myCourseIndex on course(cID);