集計関数

複数の集計関数の結果が必要な場合

例: 映画毎のカテゴリ、俳優の人数、在庫数、レンタル回数

SELECT
  f.film_id,
  f.title,
  f.rating,
  (
    SELECT
      c.name
    FROM
      category c
      INNER JOIN
        film_category fc ON c.category_id = fc.category_id
      WHERE
        fc.film_id = f.film_id
  ) category_name,
  (
    SELECT
      count(*)
    FROM
     film_actor fa
    WHERE
      fa.film_id = f.film_id
  ) film_actor_count,
  (
    SELECT
      count(*)
    FROM
      inventory i
    WHERE
      i.film_id = f.film_id
  ) count_inventory,
  (
    SELECT
      count(*)
    FROM
      inventory i
      INNER JOIN
        rental r ON i.inventory_id = r.inventory_id
    WHERE
      i.film_id = f.film_id
  ) count_rental
FROM
  film f
ORDER BY
  count_rental DESC, rating DESC;

例: 国毎の売上

SELECT内での相関サブクエリの場合

SELECT
  c.country,
  (
    SELECT
      SUM(p.amount)
    FROM
      city ct
    INNER JOIN
      address a ON ct.city_id = a.city_id
    INNER JOIN
      customer cst ON a.address_id = cst.address_id
    INNER JOIN
      payment p ON cst.customer_id = p.customer_id
    WHERE
      ct.country_id = c.country_id
  ) sum_amount
FROM
  country c
ORDER BY
  sum_amount DESC;

INNER JOINの場合

SELECT
  ctr.country,
  SUM(p.amount) sum_amount
FROM
  payment p
  INNER JOIN
    customer c ON p.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
  INNER JOIN
    country ctr ON ct.country_id = ctr.country_id
GROUP BY
  ctr.country_id
ORDER BY
  sum_amount DESC;

例: 俳優毎のレーティング毎の出演映画数

# 一意なrating
SELECT
  DISTINCT rating
FROM
  film
ORDER BY
  rating;

# 俳優毎のレーティング毎の出演映画数
SELECT
  a.actor_id,
  a.first_name,
  a.last_name,
  (
    SELECT
      COUNT(fa.actor_id)
    FROM
      film_actor fa
      INNER JOIN
        film f ON fa.film_id = f.film_id
    WHERE
      fa.actor_id = a.actor_id
      AND
      f.rating = 'G'
  ) g_count,
  (
    SELECT
      COUNT(fa.actor_id)
    FROM
      film_actor fa
      INNER JOIN
        film f ON fa.film_id = f.film_id
    WHERE
      fa.actor_id = a.actor_id
      AND
      f.rating = 'PG'
  ) pg_count,
  (
    SELECT
      COUNT(fa.actor_id)
    FROM
      film_actor fa
      INNER JOIN
        film f ON fa.film_id = f.film_id
    WHERE
      fa.actor_id = a.actor_id
      AND
      f.rating = 'PG-13'
  ) pg_13_count,
  (
    SELECT
      COUNT(fa.actor_id)
    FROM
      film_actor fa
      INNER JOIN
        film f ON fa.film_id = f.film_id
    WHERE
      fa.actor_id = a.actor_id
      AND
      f.rating = 'R'
  ) r_count,
  (
    SELECT
      COUNT(fa.actor_id)
    FROM
      film_actor fa
      INNER JOIN
        film f ON fa.film_id = f.film_id
    WHERE
      fa.actor_id = a.actor_id
      AND
      f.rating = 'nc-17'
  ) nc_17_count
FROM
  actor a
ORDER BY
  nc_17_count DESC, r_count DESC, pg_13_count DESC, pg_count DESC, g_count DESC;

# actor_idが1のものを確認
SELECT
  f.rating,
  COUNT(*)
FROM
  actor a
  INNER JOIN
    film_actor fa ON a.actor_id = fa.actor_id
  INNER JOIN
    film f ON fa.film_id = f.film_id
WHERE
  a.actor_id = 1
GROUP BY
  f.rating
ORDER BY
  f.rating;

映画毎の在庫状況

# 全店舗
SELECT
  i.film_id,
  f.title,
  COUNT(i.film_id) film_inventory_count
FROM
  inventory i
  INNER JOIN
    film f ON i.film_id = f.film_id
GROUP BY
  i.film_id
ORDER BY
  f.film_id;

# 店舗毎
SELECT
  i.film_id,
  f.title,
  COUNT(i.film_id) film_inventory_count,
  i.store_id
FROM
  inventory i
  INNER JOIN
    film f ON i.film_id = f.film_id
GROUP BY
  i.store_id, i.film_id
ORDER BY
  f.film_id, film_inventory_count DESC;