When to use

Subqueries as Data Sources

• Data Fabrication
  • 例如：将连续变量分为区段

• code

  SELECT pymnt_grps.name, count(*) num_custmors
  FROM
  (SELECT customer_id, count(*) num_rental, sum(amount) tot_payments
    FROM payment
    GROUP BY customer_id) pymnt
  INNER JOIN
    (SELECT 'Small Fry' name, 0 low_limit, 74.99 high_limit
    UNION ALL
    SELECT 'Average Fry' name, 75 low_limit, 149.99 high_limit
    UNION ALL
    SELECT 'Heavy Fry' name, 150 low_limit, 9999999.99 high_limit
    ) pymnt_grps
  ON pymnt.tot_payments BETWEEN pymnt_grps.low_limit and pymnt_grps.high_limit
  GROUP BY pymnt_grps.name;
  

• Task-oriented subqueries

• Common table expressions

  • What is Common table expression?

    • 一系列的subqueries且，从上到下，下面的subquery会引用上面的subquery
    • demo

      WITH $tbl_name_1 AS 
        ($subquery_1),
        $tbl_name_2 AS
        ($subquery_2),
        ...
        $tbl_name_n AS
        ($subquery_n)
      $query;
      

    • example

      WITH actor_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 actor_s s
            INNER JOIN film_actor fa
            ON fa.actor_id = s.actor_id
            INNER JOIN film f
            on fa.film_id = f.film_id
            WHERE f.rating = 'PG'
            ),
        actor_s_pg_revenue 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
             )
      SELECT spg_rev.first_name, spg_rev.last_name,
           sum(spg_rev.amount) tot_revenue
      FROM actor_s_pg_revenue spg_rev
      GROUP BY spg_rev.first_name, spg_rev.last_name
      ORDER BY 3 desc;
      

Subqueries as Expression Generators

• cloud be used after SELECT
• could be used after ORDER BY, just for sort