[database] SQL representation (group by와 having, aggregation, null values 처리)

본 게시글은

서울대학교 데이터사이언스대학원 이상원 교수님의

데이터사이언스 응용을 위한 빅데이터 및 지식기반시스템 강의를

학습을 목적으로 재구성하였습니다

---

저번시간에 이어서 계속해서

SQL의 표현법에 대해서 알아보자

 

 

첫 번째로 나오는 개념은 subquery(혹은 nested query)이다

nested query는 쿼리가 안에 있는데

그 안에 또 다른 쿼리가 nesting된다는 뜻으로

main query와 그 아래에 subquery가 있는 것이다

 

왼쪽이 그냥 일반 main query만 있는 것이고

오른쪽이 subquery로 where의 조건절을 만든 것이다

 

그렇다면 둘 중에 어떤 것이 더 효과적일까?

교수님 말씀에 따르면 오른쪽의

subquery를 이용한 쿼리가 더 효율적이라고한다

 

왼쪽은 그냥 join을 사용한 것이고

DBMS의 입장에서는 오른쪽이 더 간단한데

subquery는 그냥 그 자체로 하나의 쿼리라서

DBMS에서는 subquery를 먼저 구한 다음

S.sid가 그 안에 포함되는지 체크만 하면 되기 때문이다

 

 

 

Subquery를 작성하는데 correlation이 되는 것에 대해서 알아보자

위 예시는 앞에서 봤던 예시와 동일한데

IN 대신에 EXISTS를 사용한 SQL문이다

 

일반 쿼리와 다른점이 뭐냐면

subquery 내부에서

상위의 table인 S를 참조하기 때문에

상위의 쿼리를 참조하는 것이 된다

 

또한, EXISTS는 모든 row에 대해서 해당 쿼리를 실행한다는 점이다

IN을 사용하면 그냥 단순하게

subquery를 먼저 실행한 다음

포함되는지 여부만 보는데

EXISTS를 사용하면 모든 row에 대해서

subquery를 수행해서 true가 나오면 결과로 나오고

그렇지 않으면 결과로 나오지 않는 것이다

 

이런걸 inner query(main query)와

outer query(subquery)가 서로 상관관계가 있다고해서

nested query with correlation이라고 부른다

 

여기서 EXISTS 대신에 UNIQUE가 들어가게 된다면

SELECT 옆에 *를 R.bid로 바꿔야한다

왜냐하면 UNIQUE는 해당 subquery에서 중복되지 않는 것만

TRUE로 반환하는데 *를 하면 전부다 TRUE가 될 수 있기 때문이다

 

 

 

IN, EXISTS, UNIQUE 이외에도

다른 연산자에는 ANY(SOME)이 있고

ALL도 있다

 

아래 노란 박스에 들어있는 쿼리를 살펴보면

이름이 Horatio인 사람의 점수보다 높은

sailor를 찾는 쿼리문이다

 

그런데 여기에 사용된 연산자가 ANY이면

서브쿼리의 결과 중 하나라도 만족하면 TRUE가 되는 것인데

만약 inner query의 결과가 empty라면

당연히 FALSE가 나오게 된다

 

하지만 만약 ANY가 아니고 ALL이라면

서브쿼리의 모든 값보다 크면 TRUE, 하나라도 작으면 FALSE가 나오는데

inner query의 결과가 empty면 당연히 ALL은 TRUE가 된다

 

 

 

and 관계로 조건을 필터링하는 쿼리를

IN을 이용해서도 작성할 수 있다고 한다

뭐 .. 위에 나와있는대로 작성하면 된다고한다

 

마찬가지로 EXCEPT도 NOT IN으로 대체할 수 있다고한다

뭐 나머지 부분은 간단하게 읽어보라고 하셨다

 

 

 

SQL에서 division을 작성해보자

저번 시간에 배운 division을 바탕으로

all disqualified rows를 제거해주는 방법으로 작성해준다

 

모든 배를 빌린 선원을 찾는 쿼리에 대해서는

Boat table에서 S.sid를 가진 특정 선원이

예약한 배가 아닌 배의 bid를 찾는다

만약 bid가 단 한개도 나오지 않는다면

해당 선원은 모든 배를 빌린 것이고

bid가 한 개라도 나온다면 해당 선원은

모든 배를 빌리지 않은 것이다

 

이와 같은 방식으로 division 연산을 수행해준다

 

 

 

이번엔 Aggregate Operators를 살펴보자

aggregate operators에는

COUNT, SUM, AVG, MAX, MIN 등이 있다

COUNT는 총 개수를 반환하는 것이고

SUM은 합,

AVG는 평균,

MAX와 MIN은 최댓값 최솟값이다

 

 

 

Group by와 having이라는 연산자가있다

 

위 ppt에서 각 rating level 중에서

가장 나이가 어린 선원을 찾으라는 질문이 있다

 

위 노란 박스에 담겨있는 쿼리로도 가능하지만

문제가 있을 수도 있다

따라서 이런 문제를 해결하기위해 나온게

바로 group by이다

 

 

위와같이 각 rating을 group by를 한 다음

AVG(S.age)를 해주면 각 rating level별

나이의 평균이 나온다

 

이렇게 되면 s.rating은 개별 튜플이 아닌

어떤 그룹을 대변하는 값이 된다

 

 

 

group by와 Having을 사용한 쿼리이다

 

target list에는 group by를 대표하는 속성과

group by 내 데이터를 이용해서 계산하는 함수가 들어갈 수 있다

 

Having은 group by를 적용한 후

그룹화된 결과에 의해서 조건을 필터링하는 구문이다

WHERE과 다른 점은

WHERE는 group by를 수행하기 전에 필터를 한다고하면

Having은 group by 수행 이후에 필터를 한다

 

그래서 그룹화된 결과 내에서만 having을 이용해

필터를 걸 수 있다

 

 

 

having과 group by 쿼리의 예시를 한 번 살펴보자

 

우선 1번은 where까지만 적용한 결과이다

age가 18 이상인 것들만 필터가 되어서

Zorba 이외의 모든 튜플만 리턴된 것을 볼 수 있다

 

group by를 적용한 후가 2번이다

group by를 적용해서

rating을 기준으로 각 tuple들이 묶인 것을 확인할 수 있다

 

이제 마지막으로 aggregate와 having을 적용시킨

3번을 보자

aggregate를 적용해서 각 rating 그룹 중

age가 가장 작은 튜플만 살아 남았고

rating으로 group by한 것 중에서 개수가 1개보다 많은 것들만 살아남았다

 

 

각 rating 그룹의 평균 나이가

전체 rating 중에서 가장 작은 결과를 찾고싶다

 

이럴 때 생각나는 가장 보편적인 방법은 MIN(AVG(s.age))와 같이 해주는 것인데

DBMS에 따라서 aggregate operation은

nested하게 작성이 안되는 경우가 있다

 

따라서 아래의 correct solution같이 작성해줄 수 있다고한다

Oracle의 경우 앞에 WITH으로

subquery를 이용한 임시 테이블을 만들어주면

쿼리의 가독성을 훨씬 더 높일 수 있다

 

 

다음으로 Null Values에 대해서 배워보자

null value가 존재할 때 숙지하고있어야할 것들이 있다고한다

 

 

 

첫 번째로 어떤 column의 값이 null인지 아닌지 체크할 때는

IS NULL, IS NOT NULL과 같이 한다고 한다

== Null 이렇게는 안된다

 

NVL(attr, const)와 같이 작성하면

null이 아닌 경우는 그냥 valid한 값이

만약 값이 Null인 경우는 const 값이 나오게된다

 

여기서 rating 필드가 Null인데 

rating > 8을 조건으로 넣으면

true가 될까 false가 될까?

 

정답은 unknown이 나온다고한다

여기서 DBMS의 3-valued logic을 알아야하는데

우리는 true와 false만 있다고 생각하겠지만

사실은 unknown도 포함되어있다

 

그래서 Null인 value에 대해서 비교를 실행하면

unknown이 나온다고 한다

그리고 where절의 조건은 최종적인 질의가

true가 되는 것만을 내뱉는다고한다

 

 

 

aggregation의 경우 null value에 대한 처리이다

값 전체가 null이면 avg, sum, min, max를 적용해도

null 값이 반환된다

 

sum이나 avg 같은 경우에서 중간 중간에

null value가 있는 경우 null value는 무시하고

valid한 값들만 계산해서 결과를 내준다

 

 

 

null value와 join 연산에 대해서 알아보자

 

우리가 지금까지 보통 배운 join은 inner join이다

inner join은 두 테이블 모두에서

매칭되는 튜플이 존재할때만 결과로 나온다

따라서 매칭되는 column의 값에

null value가 있으면 자동으로 제외가 된다

 

하지만 outer join의 경우는 다르다

크게 left outer join, right outer join

그리고 그냥 (full) outer join이 있는데

left outer join은 왼쪽 테이블의 모든 행을 포함하는 것이고

right outer join은 오른쪽 테이블의 모든 행을

outer join은 양쪽 모든 테이블의 행을 포함하는 것이다

 

위 ppt에서 예시를 보면 

R.sid(+)를 해서 right outer join을 해줬는데

31은 매칭되는게 없지만 null로 리턴이 된 것을 볼 수 있다

 

 

 

마지막으로 null value에 대해 살펴보고

오늘 수업 정리를 마쳐보려고 한다

 

null value를 가질 수 없는 column들이 있다

column을 생성할 때 NOT NULL이라고 명시해준경우

해당 column에는 null value가 들어갈 수 없다

또한 pk는 당연히 null value가 들어갈 수 없다

또한, UNIQUE constraint가 들어가있는 column 또한

null이 들어갈 수가 없다

 

마지막으로 집합 안에 null이 있을 때

포함여부를 살펴보자

 

1과 null의 value를 가진 집합이 있고

여기에 1 in {1, null}을 하면 당연히 true,

2 in {1, null}을 하면 당연히 false가 나온다

 

not in의 경우

1 not in {1, null}의 경우 false인데

2 not in {1, null}의 경우 unknown이 나와서

결과값에서 제외된다

 

따라서 in은 exists와 동일하지만

not in은 not exists와 다른 결과를 내뱉는데

not exists의 경우 위의 경우에서

null과 관련없이 결과를 내뱉는다

 

따라서 not in 보다 not exists가

더 성능도 좋고 정확한 결과를 내뱉어

추천된다고한다