[SQL_스터디_2팀] 5주차_강의노트(2)

작성자: 16기 유우혁

본 게시글은 datacamp의 Joining Data in SQL 강의를 기반으로 작성되었습니다.

 

Correlated queries, nested queries, and common table expressions

 

correlated query란?1. outer query에서 온 값을 이용해서 결과를 만들어내는 것!2. final data set에 있는 것으로 row마다 계속 re-run할 수 있다3. filtering과 evaluating data할 때 용이하다고 한다.4. main query에 의존해서 작동된다5. loop를 돌아서 값이 나오는 것이라 속도가 느리다.

 

그러면 어떻게 쓰는지 보자먼저 simple subquery다.이 것은 한번만 evaluate되고, main query와 독립적이다.

where안에 있는 subquery를 보면 sub라는 table을 쓰고 있지만 main query에 있는 main이라는 table과 sub table의 값을 비교한 것을 볼 수 있다. 이 것이 correlated subquery!

 

다음은 nested subquery다.

이 것은 subquery안에 nested된 것이다. 만약에 여러번의 transformation을 하고싶으면 쓴다.

EXTRACT라는 keyword를 사용!

 

LEFT JOIN하는 상황을 보면 SELECT로 한번 subquery를 하려는 것을볼 수 있다. 그런데 여기서 FROM을 보면 다시한번 subquery를 들어간다. 그래서 해당 조건에 맞는 subquery를 뽑은 table에서 select해서 join해주는 형식이다.

다음은 common table expression으로 CTE라고 불린다.

WITH라는 키워드와 같이 쓰인다. 이 것은 c언어에서 main함수 전에 정의되는 것처럼 main query전에 먼저 선언된다.

WITH 뒤에 cte의 name을 써주고 as로 원하는 과정을 수행한다. 쉼표를 통해서 여러개의 cte를 만들어낼 수 있다. 이것은 한번만 수행되고 메모리에 저장되어서 계속 reference할 수 있다. 

 

with 뒤에 cte이름을 써주고(여기서는 match_list) AS 뒤에 수행하고 싶은 과정을 쓴다. WITH 와 subquery를 같이 쓴 것을 볼 수 있다!

 

이 기술들은 비슷하면서 다르다. 특징과 기술을 적어보면

1. join

table을 합침(2개 이상)

simple operations / aggregations

이미 있는 테이블을 사용해야함

table이 여러개면 중요함

ex) what is the total sales per employee?

 

2. correlated subquery

match subquery & table

avoid limits of joins

간단하게 만듬

오래걸림

matching data 다른 column에 있는

ex) who does each employee report to in a company?

 

3. nest subquery

data requires multi-step transformation

improve accuracy and reproductability

ex) what is the average deal size closed by each sales representative in the quarter?

 

4.common table expressions(CTE)

organize subquery sequentially

reference로 계속 이용 가능

 

이러한 특징들을 가지고 있다 

 

다음 Window function을 알아보자

window function은 OVER()라는 키워드로 대표된다.

aggregate을 하면은 원래 non-aggregate 함수와 값을 비교할 수 없다. 그런데 over를 통해서 값을 비교해줄 수 있게 된다.

AVG, RANK와 같은 함수를 같이 써주게 된다.

 

OVER()를 보면 order by로 순서 매겨주고 RANK()로 순위를 매겨주고 있다.

OVER()를 쓰고 AVG를 통해 aggregate calculation을 해주고 있다.

 

또한, PARTITION BY를 통해서 카테고리별로 값을 다르게 구해줄 수 있다. 조건을 걸어주는 것이라 생각하면 되는데 ㅇ러개의 조건을 걸어 줄 수 있다.

 

PARTITION BY를 통해서 season별로 평균을 구해고, extract를 통해 month를 추출했는데 이를 통해 season, month별로 평균을 구하게 된다.

 

마지막으로 sliding windows를 통해 row별로 계산을 진행해줄 수 있다.

이를 통해서 running total, 평균을 구할 수 있는데 이때 ROW BETWEEN <start> AND <finish>문법을 쓴다

이때 start와 finish에는

1. CURRENT ROW

   stop calculation at current row

2. PRECEDING

3. FOLLOWING

4. UNBOUNDED PRECEDING

5. UNBOUNDED FOLLOWING

   4번 5번은 want to include every row since the beginning or end

이 다섯가지가 올 수 있다.

 

rows between을 보면 현재 row부터 맨 마지막까지 구하라는 것을 볼 수 있다.!!