GROUP BY

構文

GROUP BY
  カラムA, カラムB...

※カラムはSELECT句で算出した年・四半期・月等のエイリアスを使える

GROUP_BYで指定したカラムはSELECTで全て指定する

select @@session.sql_mode;
ONLY_FULL_GROUP_BY

In aggregated query without GROUP BY,
expression #1 of SELECT list contains nonaggregated column 'table.column';
this is incompatible with sql_mode=only_full_group_by

GROUP_CONCAT

SELECT
  カラム名A,
  GROUP_CONCAT(カラム名B),
  GROUP_CONCAT(DISTINCT カラム名B ORDER BY カラム名B DESC)
FROM
  テーブル名 t
GROUP BY
  カラム名A
;
※NULLの挙動を確認しておく(含まれない)
※重複を省く場合はDISTINCT
※最大長はデフォルトで1024: SHOW VARIABLES LIKE '%group_concat_max_len%';
※group_concat_max_lenの設定の最大長はデフォルトで67108864: SHOW VARIABLES LIKE '%max_allowed_packet%';
※PostgreはSTRING_AGG

評価順

該当のidのcountを取得するとき等に使う
SELECT
  customer_id,
  COUNT(*) as count
FROM
  rental
GROUP BY
  customer_id
ORDER BY
  count DESC;

GROUP BYの後にWHEREが評価されるので、count等での絞り込みはHAVING句を使う
SELECT
  customer_id,
  COUNT(*) as count
FROM
  rental
GROUP BY
  customer_id
HAVING
  count >=40
ORDER BY
  count DESC;

集計関数

MAX(), MIN(), AVG(), SUM(), COUNT()

GROUP BYと共に使用される
GROUP BYがない場合のグループはテーブル全体になる

※NULLや0が含まれるときの挙動を確認しておく

# count(*)で0になるものが除外される場合はLEFT OUTER JOINを使って、count(JOINするテーブル.id)にする
# filmにあって、inventoryにないレコードが存在する
SELECT
  f.film_id,
  f.title,
- count(*) as count
+ count(i.inventory_id) as count
FROM
  film f
  LEFT OUTER JOIN inventory i
    ON f.film_id = i.film_id
GROUP BY
  f.film_id
HAVING
  count = 0
ORDER BY
  f.film_id;

ID毎に集計

SELECT
  customer_id,
  MAX(amount),
  MIN(amount),
  AVG(amount),
  SUM(amount),
  COUNT(*) num_payments
FROM
  payment
GROUP BY
  customer_id;

合計ユーザー数と一意なユーザー数

SELECT
  COUNT(customer_id) as customer_count,
  COUNT(DISTINCT customer_id) distinct_customer_count
FROM
  payment;

期間でグルーピング

SELECT
  customer_id,
  MAX(datediff(return_date, rental_date)) as own_date
FROM
  rental
GROUP BY
  customer_id
HAVING
  own_date >= 10
ORDER BY
  own_date DESC;

nullはどう集計されるかを確認しておく

基本的に無視される
※COUNT(*)には含まれるがCOUNT(カラム名)には含まれない

複数列によるカウント

※GROUP BYに指定する列はSELECTにも指定する

# actor_idとratingの組み合わせ(重複あり)
SELECT
  fa.actor_id,
  f.rating
FROM
  film_actor fa
  INNER JOIN film f
    ON fa.film_id = f.film_id
ORDER BY
  fa.actor_id, f.rating;

# actor_idとratingの組み合わせとカウント
SELECT
  fa.actor_id,
  f.rating,
  count(*) as actor_rating_count
FROM
  film_actor fa
  INNER JOIN film f
    ON fa.film_id = f.film_id
GROUP BY
  fa.actor_id, f.rating
ORDER BY
  f.rating DESC, actor_rating_count DESC;

式によるグルーピング

# 年毎のレンタル数
SELECT
  extract(YEAR FROM rental_date) year,
  COUNT(*) as count
FROM
  rental
GROUP BY
  year;

ユーザー毎に集計

ユーザー毎の合計支払額と合計レンタル数(customerメイン: 約30ms)

# paymentテーブルを集計してからJOINしているので速い
# もっとシンプルにできる
SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  ct.city,
  p.sum_amount,
  p.count_rentals
FROM
  customer c
  INNER JOIN
    (
      SELECT
        customer_id,
        count(*) count_rentals,
        sum(amount) sum_amount
      FROM
        payment
      GROUP BY
        customer_id
    ) p
    ON c.customer_id = p.customer_id
  INNER JOIN
    address a ON c.address_id = a.address_id
  INNER JOIN
    city ct ON a.city_id = ct.city_id
ORDER BY
  p.sum_amount DESC, p.count_rentals DESC;

ユーザー毎の合計支払額と合計レンタル数(paymentメイン: 約60ms)

# group byがcustomer_idではないので遅い
# paymentテーブルを集計してからJOINしていないのでメモリを余分に使い、遅い
# customer_idをSELECT句に入れることができない
SELECT
  c.first_name,
  c.last_name,
  ct.city,
  sum(p.amount) sum_amount,
  count(*) count_payment
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
GROUP BY
  c.first_name, c.last_name, ct.city
ORDER BY
  sum_amount DESC, count_payment DESC;

ユーザー毎の合計支払額と合計レンタル数(paymentメイン: 約30ms)

# paymentテーブルを集計してからJOINしているので速い
# シンプル
SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  ct.city,
  p.sum_amount,
  p.count_payment
FROM
  (
    SELECT
      customer_id,
      sum(amount) as sum_amount,
      count(*) as count_payment
    FROM
      payment
    GROUP BY
      customer_id
  ) 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
ORDER BY
  p.sum_amount DESC, p.count_payment DESC;

ユーザー毎の合計支払額と合計レンタル数(paymentメイン: 約30ms)

# シンプル(WITH使用)
WITH gp AS (
    SELECT
      customer_id,
      sum(amount) as sum_amount,
      count(*) as count_payment
    FROM
      payment
    GROUP BY
      customer_id
  ),
  gp_cac AS (
    SELECT
      c.customer_id,
      c.first_name,
      c.last_name,
      ct.city,
      gp.sum_amount,
      gp.count_payment
    FROM
      gp
      INNER JOIN
        customer c ON gp.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
  )
SELECT
  gp_cac.customer_id,
  gp_cac.first_name,
  gp_cac.last_name,
  gp_cac.city,
  gp_cac.sum_amount,
  gp_cac.count_payment
FROM
  gp_cac
ORDER BY
  gp_cac.sum_amount DESC, gp_cac.count_payment DESC;

ユーザー毎の合計支払額と合計レンタル数(paymentメイン: 約30ms)

# SELECT句にサブクエリ: 使わない
SELECT
  (SELECT c.first_name FROM customer c WHERE c.customer_id = p.customer_id) first_name,
  (SELECT c.last_name FROM customer c WHERE c.customer_id = p.customer_id) last_name,
  (
    SELECT
      ct.city
    FROM
      customer c
      INNER JOIN
        address a ON c.address_id = a.address_id
      INNER JOIN
        city ct ON a.city_id = ct.city_id
    WHERE
      c.customer_id = p.customer_id
  ) city,
  sum(p.amount) sum_amount,
  count(*) count_payment
FROM
  payment p
GROUP BY
  p.customer_id
ORDER BY
  sum_amount DESC, count_payment DESC;

ユーザーを分類して集計

合計支払額でユーザーをグルーピング

# SQLのみでの集計のときだけ
# APIの場合はINNER JOIN部分はAPIで行う(グループと閾値はパラメーターで指定できるようにする)
SELECT
  pg.name,
  count(*) as count_customers
FROM
  (
    SELECT
      customer_id,
      count(*) count_rentals,
      sum(amount) sum_amount
    FROM
      payment
    GROUP BY
      customer_id
  ) p
  INNER JOIN
    (     SELECT 'small' as name, 0 as low_limit, 74.99 as high_limit
    UNION ALL
    SELECT 'average' as name, 75 as low_limit, 149.99 as high_limit
    UNION ALL
    SELECT 'high' as name, 150 as low_limit, 9999999.99 as high_limit
  ) pg
    ON sum_amount BETWEEN  pg.low_limit AND pg.high_limit
GROUP BY
  pg.name
ORDER BY
  count_customers DESC;

俳優毎に分類して集計

出演映画数で俳優をグルーピング

SELECT
  fag.actor_id,
  fag.first_name,
  fag.last_name,
  fag.actor_film_count,
  grps.level
FROM
  (
    SELECT
      fa.actor_id,
      a.first_name,
      a.last_name,
      count(*) actor_film_count
    FROM
      film_actor fa
  INNER JOIN
     actor a ON a.actor_id = fa.actor_id
    GROUP BY
      actor_id
  ) fag
  INNER JOIN (
    SELECT 'Star' level, 30 min, 99999 max
    UNION ALL
    SELECT 'Average' level, 20 min, 29 max
    UNION ALL
    SELECT 'New' level, 1 min, 19 max
  ) grps ON fag.actor_film_count BETWEEN grps.min AND grps.max
ORDER BY
  fag.actor_film_count DESC;