[빅데이터 및 지식관리시스템] Nested query와 Aggregate Operations

Nested query

Nested query와 join을 이용한 query의 차이

Nested query는 in, not in을 통해 존재의 여부를 판단하는 것이기 때문에 중복을 인정하지 않는다.

반면, join은 중복을 인정한다.

속도 측면에서는 join이 더 빠른데 그 이유는 DBMS 옵티마이저 덕분이다.

 

join에서 column기준으로 묶어서 비교했는데, Nested query에서는 비교하고자 하는 column을 select 하여 in/not in 연산을 실행하면 된다.

Correlated subquery

Exist는 in과 다르게 tuple 하나하나 비교해가면서 True/False 값을 출력한다.

Unique

Uique는 중복이 있는지 판단해서 중복된 tuple이 있으면 False, 없으면 True를 출력한다.

여기서 문제는 SELECT *을 하는 것인데

예를 들어 예약이 몇 종류가 있는지 물을 때,

*을 사용할 경우 한명이 103을 여러 번 예약했을 경우 *로 인해 전체 column을 보고 중복을 판단한다.

따라서 이럴 경우 중복으로 처리하여 false를 출력한다.

 

반면 R.bid로 Select하는 column을 정확하게 구체화하면 103들이 여러개가 뜰 텐데 중복 처리를 해줘 103 1개만 남아 unique는 True처리를 한다.

 

따라서 비교할 column을 구체화하면 정확한 조건 표현이 가능하다.

Any vs All

Any는 비교연산중 하나라도 맞으면 true이고 all은 모두 맞아야 true이다.

subquery가 비어있을 경우, any는 false를 출력하고 all은 true를 출력한다.

division

division문제에서는 주로 모두 ~하는을 찾을 때 사용한다.

예를 들어 Find sailors who've reserved all boats. 라 할 때,

 

 

(1)의 경우 차집합 역할을 하는 Except를 이용한다.

모든 보트의 정보와 특정 선원이 예약한 보트의 차집합을 구한다.

이때, set이 empty이면 그 선원은 모든 보트를 예약했다고 생각할 수 있다.

따라서 Not Exists를 사용하여 empty인지 판단하고 empty인 S.name 만 출력한다.

 

(2)의 경우 이중부정 느낌인데, 예약하지 않은 보트가 없는 존재하지 않음을 확인한다.

따라서, 안쪽 NOT EXISTS가 '특정 선원이 예약하지 않은'이 되고 바깥 NOT EXISTS가 '존재하지 않는다'를 말한다.

 

Aggregate Operations

Group by

tuple 별로 조건을 만족하는지 판단하고 싶을 때가 있다.

for문을 통해 각 rating마다 조건을 만족하는지 판단할 수 있다. 하지만 rating level이 몇 개나 있는지?, value는 어떤 값인지 알지 못하기 때문에 일반적으로 이런 식으로 판단하는 것은 쉽지 않다.

 

그래서 group by라는 기능을 사용하는데..

보는 것처럼 S.rating에 대해 tuple들을 group으로 만들고 판단할 수 있다.

또한 각 행에 where을 통해 추가적으로 조건을 걸 수도 있다.

 

이 내용에 대해 자세하게 살펴보면

• SELECT

어떤 칼럼을 출력할지 지정한다.

여기에는 집계함수도 포함 가능하다.(sum, avg, count)

특히 그룹 기준 칼럼들의 경우 반드시 Group by에도 포함되야 한다.(subset of grouping-list)

• Having

그룹 전체에 대한 조건이다. 따라서 group by를 통해 그룹핑을 하고 그 그룹에 조건을 건다.

Having도 subquery를 포함할 수 있다.

• Where

그룹핑 전에 개별 행(tuple) 단위에 조건을 거는 것이다.

Conceptual evaluation

• From절에서 cross-product를 하여 모두 엮는다.
• Where절을 통해 관계없는 tuple을 다 거른다.
• Group By로 그룹을 묶고
• Having으로 그룹별로 필터링하고 통과한 그룹마다 결과 튜플을 생성한다.

첫 번째의 경우 red boat를 예약을 안 했을 때 Join 결과에 안 나타난다.

Count=0인경우도 표시하고 싶을 수도 있는데 이때는 outer join을 이용한다.

 

두 번째의 경우 Having에는 group에 대한 조건만 표현할 수 있다. 

B.color는 table의 일반 column이다.

 

Aggregate operation의 특징 중 하나가 nested 될 수 없다. 즉 AVG문안에 MIN 또는 MAX와 같은 Aggregate operation을 할 수없다.

따라서 이를 표현하고 싶으면 집계 > 테이블로 분리(예: Temp table 생성) > Temp에 대해 집계를 한다.

rating 별 AVG를 먼저 계산하여 Temp를 만들고 Temp에서 MIN 계산을 하여 평균 중 최소인 값을 구한다.

Null Values

Null은 값을 모르거나 적용 불가능할 때 사용한다.

• IS NULL, IS NOT NULL

null 여부를 확인할 수 있다.

• NVL(attr, const)

값이 null일 경우, const값으로 치환할 수 있다.

• where 절은 true만 통과시키고 false, unknown(NULL)은 제거한다.

Count(*)는 계산 시 NULL을 포함한다.

Count(attr), AVG, SUM, MAX, MIN은 NULL을 무시한다.

• INNER JOIN과 OUTER JOIN

INNER JOIN은 join 조건이 NULL이면 결과에서 제외하고 OUTER JOIN은 NULL값도 살려서 결과에 포함시킨다.

OUTER JOIN은 S.sid=R.sid(+)으로 표현할 수 있다.

• IN과 Exist의 관계

not in 연산은 만약 null값이 있으면 무조건 False를 출력한다.

따라서 in과 exist는 같은 기능을 하지만 not in과 not exist는 서로 다른 기능을 한다.

not exist가 더 안전적이고 일반적인 경우이다.