[SQL] SUBQUERIES and CTEs

Independent Subquery

- 실행할 때마다 같은 값을 출력 

- 그 값을 기억해뒀다가 반복해서 출력함(계산은 1번만 실행) 

cf) Correlated Subquery: 실행 위치에 따라 출력값이 달라짐 

ex. 영화 전체의 평균: Independent Subquery 

      그 해(year)의 평균: Correlated Subquery 

-- 전체 영화들 중 평점이나 수익이 평균보다 높은 영화의 리스트 구하기 


SELECT title FROM movies
  WHERE rating > AVG(rating);
--- !!!에러에러!!!
  

SELECT title, rating, (SELECT AVG(rating) FROM movies) AS avg_rating FROM movies
  WHERE rating > (SELECT AVG(rating) FROM movies);
-- 성능 문제: 전체 DB를 2번 검색한다 
-- AVG(ratinig)을 구하기 위해 서브쿼리를 반복하고 있다 
-- (SELECT AVG(rating) FROM movies): 😀Independent Subqueries😀 
-- AVG(rating)의 값은 상수(독립적)

 

 

CTE: Common Table Expression 

Independent Subquery를 재사용 할 수 있도록 해준다 

ex. 위의 사례에서 AVG(rating)을 재사용하고 + 함께 반환하고 싶음 

WITH avg_rating_cte AS (SELECT AVG(rating) FROM movies) 

SELECT title, rating, (SELECT * FROM avg_rating_cte) AS avg_rating FROM movies
  WHERE rating > (SELECT * FROM avg_rating_cte);
-- CTE 선언 후 -> SELECT 구문

WITH movie_avg_per_year AS (SELECT AVG(inner_movies.rating) FROM movies AS inner_movies 
                            	WHERE inner_movies.release_date = main_movies.release_date) 
SELECT main_movies.title,
			 main_movies.director,
       main_movies.rating,
       main_movies.release_date,
       (SELECT * FROM movie_avg_per_year) AS year_average
       FROM movies AS main_movies 
       WHERE main_movies.release_date > 2020 AND main_movies.rating > (	SELECT AVG(inner_movies.rating) FROM movies AS inner_movies
       WHERE inner_movies.release_date = main_movies.release_date);

SQLite의 CTE: 아래에서 생성된 요소들을 참조할 수 있다

 

-- 전체 감독의 평균 revenue 보다 높은 revenue를 버는 감독 구하기 


SELECT director, SUM(revenue) AS career_revenue FROM movies 
  WHERE director IS NOT NULL AND revenue IS NOT NULL 
  GROUP BY director;
-- 우선 감독별 career revenue를 구하고(감독별 총 revenue의 합) 


WITH directors_revenues AS (SELECT director, SUM(revenue) AS career_revenue FROM movies 
  WHERE director IS NOT NULL AND revenue IS NOT NULL 
  GROUP BY director) 
SELECT director, SUM(revenue) AS total_revenue FROM movies
WHERE director IS NOT NULL AND revenue IS NOT NULL 
GROUP BY director 
HAVING total_revenue > (SELECT AVG(career_revenue) FROM directors_revenues);  
-- 위에서 만든 감독별 career revenue를 CTE로 넣어주고 
-- CTE에서 만든 career_revenue의 전체 평균을 구해서 total_revenue와 비교

CREATE INDEX inx_director ON movies (director);


WITH director_stats AS (SELECT director, AVG(rating) AS avg_rating, COUNT(*) AS total_movies,
MAX(rating) AS best_rating, MIN(rating) AS worst_rating, MAX(budget) AS highest_budget, MIN(budget) AS lowest_budget
FROM movies WHERE director IS NOT NULL AND budget IS NOT NULL AND rating IS NOT NULL GROUP BY director)
SELECT director, avg_rating, total_movies, best_rating, worst_rating, highest_budget, lowest_budget,
 (SELECT title FROM movies WHERE rating IS NOT NULL AND budget IS NOT NULL AND director = ds.director
ORDER BY rating DESC LIMIT 1) AS best_rated_movie,
 (SELECT title FROM movies WHERE rating IS NOT NULL AND budget IS NOT NULL AND director = ds.director 
ORDER BY rating ASC LIMIT 1 ) AS worst_rated_movie,
 (SELECT title FROM movies WHERE rating IS NOT NULL AND budget IS NOT NULL AND director = ds.director
ORDER BY budget DESC LIMIT 1 ) AS most_expensive_movie,
 (SELECT title FROM movies WHERE rating IS NOT NULL AND budget IS NOT NULL AND director = ds.director
ORDER BY budget ASC LIMIT 1 ) AS least_expensive_movie
FROM director_stats AS ds;

INDEX 없이 아래 코드를 실행하면 엄청난 시간이 걸린다.. 
INDEX에 대해서는 다음 글에서