DB

20240617 월. 상관관계 서브쿼리

select 'small', 0 low_limit, 74.99 high_limit
union all
select 'middle', 75 low_limit, 149.99 high_limit
union all
select 'big', 150 low_limit, 9999999.99 high_limit;

select payGroupInfo.name, count(*) num_cus
    from
        (
            select customer_id, sum(amount) payments_tot
            from payment p
            group by  customer_id
        ) payinfo

        inner join (
            select 'small' name, 0 low_limit, 74.99 high_limit
            union all
            select 'middle'name, 75 low_limit, 149.99 high_limit
            union all
            select 'big' name, 150 low_limit, 9999999.99 high_limit
        ) as payGroupInfo
        ON payinfo.payments_tot between Low_limit and high_limit
        group by payGroupInfo.name;

/*공통화 작업을 한 SQL*/
select c.first_name, c.last_name, ct.city,
       payInfo.pay_tot, payInfo.rental_tot_cnt
       from (select customer_id,
             sum(p.amount)pay_tot, count(*) rental_tot_cnt
             from payment p group by  customer_id) payInfo
inner join customer c
on payInfo.customer_id = c.customer_id
inner join address a
on c.address_id = a.address_id
inner join city ct
on a.city_id = ct.city_id;

공통테이블 표현식, CTE

• 서브쿼리의 규모가 큰 경우
• 실제 수행해야 할 main query와 sub query 를 구분할때 유용함.

WITH actors_s AS
         (SELECT actor_id, first_name, last_name
          FROM actor
          WHERE last_name LIKE 'S%'
         ),
     actors_s_pg AS
         (SELECT s.actor_id, s.first_name, s.last_name,
                 f.film_id, f.title
          FROM actors_s s
                   INNER JOIN film_actor fa
                              ON s.actor_id = fa.actor_id
                   INNER JOIN film f
                              ON f.film_id = fa.film_id
          WHERE f.rating = 'PG'
         ),
     actors_s_pg_income AS
         (SELECT spg.first_name, spg.last_name, p.amount
          FROM actors_s_pg spg
                   INNER JOIN inventory i
                              ON i.film_id = spg.film_id
                   INNER JOIN rental r
                              ON i.inventory_id = r.inventory_id
                   INNER JOIN payment p
                              ON r.rental_id = p.rental_id
         ) -- end of With clause
SELECT spg_income.first_name, spg_income.last_name,
       sum(spg_income.amount) tot_revenue
FROM actors_s_pg_income spg_income
GROUP BY spg_income.first_name, spg_income.last_name
ORDER BY 3 desc;