SQLの重複行のまとめ方に関して

はじめに

仕事で勉強になったクエリの書き方だったので、ここで記録しておきます。

まったく同じクエリは書けないので簡略化して書きます。

以下のような、テーブルがあるとします。

• 商品テーブル services
• 購入テーブル payments
• 顧客テーブル customers

Schema (MySQL v8.0)

CREATE TABLE services (
  id INT NOT NULL PRIMARY KEY auto_increment,
  name TEXT,
  category_id INT
);

INSERT INTO services VALUES
    (1, 'cookie', 1),
    (2, 'candy', 1),
    (3, 'ice', 2),
    (4, 'rice', 3),
    (5, 'ball', 4),
    (6, 'cake', 1);
    
CREATE TABLE customers (
  id INT NOT NULL PRIMARY KEY auto_increment,
  name TEXT
);

INSERT INTO customers VALUES
    (1, 'brown'),
    (2, 'rally'),
    (3, 'boggie'),
    (4, 'tashiro'),
    (5, 'yamada'),
    (6, 'takeda');
    
CREATE TABLE payments (
  id INT NOT NULL PRIMARY KEY auto_increment,
  customer_id INT,
  service_id INT
);

INSERT INTO payments VALUES
    (1, 1, 1),
    (2, 1, 2),
    (3, 2, 1),
    (5, 3, 4),
    (6, 3, 3),
    (8, 4, 1);

以下のような結果を得たいとしましょう（お題）。

お題

「甘い食べ物（sweets category_id: 1）を購入した顧客は、一人当たり何個購入したか？」

この時、自分はクエリ1の発想しかありませんでした。

つまり、購入した甘い食べ物（sweets category_id: 1）のidをDISTINCTで重複を削除して、COUNTする方法。

DISTINCTは一般にソートが発生する負荷の高い処理です。

という主張もあるそうです。

実際はEXPLAINして確かめたり、以下👇のような使い分けを考慮する必要がありそうです。

いずれにせよ、DISTINCT以外の書き方も心得ておきます。

それがクエリ2（GROUP BYを用いた集計）です。

クエリ1

SELECT
   payments.customer_id,
   COUNT(DISTINCT(services.id))
FROM payments
INNER JOIN services ON services.id = payments.service_id
   AND services.category_id = 1
GROUP BY 1

Results

customer_id	COUNT(DISTINCT(services.id))
1	2
2	1
4	1

クエリ2

WITH paid_sweets_ids AS (
	SELECT
          service_id,
          customer_id
	FROM payments
	INNER JOIN services ON services.id = payments.service_id
		AND services.category_id = 1
   GROUP BY 1, 2
)

SELECT
	customers.id AS customer_id,
	paid_sweets_counts.value AS paid_sweets_count
FROM customers
INNER JOIN (
	SELECT customer_id, count(*) AS value FROM paid_sweets_ids GROUP BY 1
) AS paid_sweets_counts ON paid_sweets_counts.customer_id = customers.id

Results

customer_id	COUNT(DISTINCT(services.id))
1	2
2	1
4	1

どちらも結果は同じです。が、パフォーマンスを考慮する際は状況に応じて書き換えるスキルが必要です。

DISTINCTだけでなく、甘い食べ物（sweets category_id: 1）の商品idをGROUP BYして、それを購入した顧客idをGROUP BYする方法も検討できるようにしておきます。

本日は、以上です。

アウトプット100本ノック実施中