LoginSignup
0
0
@raisack8(raisack)

Postgres徹底勉強　～JOIN, GROUPを使ったSQL完成までの道のり～

Posted at

ここでは、実務で行き詰ったSQL
テーブル同士を結合して集計する方法について解説していきます。
失敗ケースも含め1段1段順序立てて解説しています。

キーワードは
JOIN, GROUP, COUNT, AS

まずは環境を整えよう。

環境構築

DockerでVolumeを準備する。

Postgres実行環境があるなら飛ばす

どこでもいいので空のdocker-compose.ymlを作成、下記をコピペ

INSERT INTO purchase_history (user_id, product_id, coupon_used, quantity, purchase_date)
SELECT
    u.id,
    p.id,
    CASE WHEN random() < 0.5 THEN TRUE ELSE FALSE END,
    CAST(random() * 10 + 1 AS INTEGER),
    TIMESTAMP '2023-01-01 00:00:00' + (random() * (TIMESTAMP '2023-02-19 11:00:00' - TIMESTAMP '2023-01-01 00:00:00'))
FROM
    (SELECT id FROM users ORDER BY random() LIMIT 50) AS u,
    (SELECT id FROM products ORDER BY random() LIMIT 50) AS p;

dockerを起動

docker-compose build;
docker-compose up;

学習用データを準備する。

Postgres実行環境があるなら飛ばす

image.png

PgAdminで先ほど立ち上げたDBに接続

下記SQLたちを全て実行してデータを作成

user.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    age INTEGER,
    gender BOOLEAN
);

INSERT INTO users (name, age, gender)
VALUES
    ('Alice', 25, TRUE),
    ('Bob', 30, TRUE),
    ('Charlie', 35, TRUE),
    ('David', 40, TRUE),
    ('Eve', 22, FALSE),
    ('Frank', 28, TRUE),
    ('Grace', 33, FALSE),
    ('Henry', 45, TRUE),
    ('Ivy', 27, FALSE),
    ('Jack', 32, TRUE),
    ('Kate', 29, FALSE),
    ('Liam', 38, TRUE),
    ('Mia', 24, FALSE),
    ('Noah', 31, TRUE),
    ('Olivia', 26, FALSE),
    ('Peter', 41, TRUE),
    ('Quinn', 36, FALSE),
    ('Ryan', 23, TRUE),
    ('Sophia', 39, FALSE),
    ('Tom', 34, TRUE),
    ('Uma', 37, FALSE),
    ('Victor', 42, TRUE),
    ('Wendy', 28, FALSE),
    ('Xander', 43, TRUE),
    ('Yara', 30, FALSE),
    ('Zach', 44, TRUE),
    ('Aaron', 29, TRUE),
    ('Bella', 31, FALSE),
    ('Chris', 27, TRUE),
    ('Diana', 32, FALSE);
product.sql
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    price DECIMAL(10, 2),
    category INTEGER CHECK (category BETWEEN 1 AND 10),
    is_original BOOLEAN,
    origin VARCHAR(255),
    purchase_date TIMESTAMP
);

INSERT INTO products (name, price, category, is_original, origin, purchase_date)
VALUES
    ('Apple', 1.99, 1, FALSE, 'USA', '2023-01-01 10:00:00'),
    ('Banana', 0.99, 2, FALSE, 'Brazil', '2023-01-02 11:00:00'),
    ('Orange', 1.49, 3, FALSE, 'Spain', '2023-01-03 12:00:00'),
    ('Milk', 2.49, 4, TRUE, 'Local', '2023-01-04 13:00:00'),
    ('Bread', 1.99, 5, TRUE, 'Local', '2023-01-05 14:00:00'),
    ('Eggs', 3.99, 6, FALSE, 'Local', '2023-01-06 15:00:00'),
    ('Cheese', 5.99, 7, TRUE, 'France', '2023-01-07 16:00:00'),
    ('Tomato', 0.79, 8, FALSE, 'Italy', '2023-01-08 17:00:00'),
    ('Potato', 0.49, 9, FALSE, 'Local', '2023-01-09 18:00:00'),
    ('Chicken', 7.99, 10, TRUE, 'Local', '2023-01-10 19:00:00'),
    ('Pork', 6.99, 1, TRUE, 'Local', '2023-01-11 20:00:00'),
    ('Beef', 9.99, 2, TRUE, 'Local', '2023-01-12 21:00:00'),
    ('Fish', 8.49, 3, TRUE, 'Local', '2023-01-13 22:00:00'),
    ('Shrimp', 12.99, 4, FALSE, 'Local', '2023-01-14 23:00:00'),
    ('Rice', 3.49, 5, FALSE, 'Japan', '2023-01-15 00:00:00'),
    ('Pasta', 2.99, 6, TRUE, 'Italy', '2023-01-16 01:00:00'),
    ('Pizza', 7.99, 7, TRUE, 'Italy', '2023-01-17 02:00:00'),
    ('Ice Cream', 4.99, 8, TRUE, 'Local', '2023-01-18 03:00:00'),
    ('Chocolate', 1.99, 9, FALSE, 'Switzerland', '2023-01-19 04:00:00'),
    ('Coffee', 5.49, 10, TRUE, 'Brazil', '2023-01-20 05:00:00'),
    ('Tea', 3.99, 1, TRUE, 'China', '2023-01-21 06:00:00'),
    ('Beer', 2.49, 2, TRUE, 'Germany', '2023-01-22 07:00:00'),
    ('Wine', 8.99, 3, TRUE, 'France', '2023-01-23 08:00:00'),
    ('Whiskey', 19.99, 4, TRUE, 'Scotland', '2023-01-24 09:00:00'),
    ('Vodka', 15.99, 5, TRUE, 'Russia', '2023-01-25 10:00:00'),
    ('Rum', 12.49, 6, TRUE, 'Caribbean', '2023-01-26 11:00:00'),
    ('Cigarettes', 7.99, 7, FALSE, 'Local', '2023-01-27 12:00:00'),
    ('Lighter', 2.99, 8, TRUE, 'China', '2023-01-28 13:00:00'),
    ('Shampoo', 4.49, 9, TRUE, 'Local', '2023-01-29 14:00:00'),
    ('Soap', 1.99, 10, TRUE, 'Local', '2023-01-30 15:00:00'),
    ('Towel', 5.99, 1, TRUE, 'Local', '2023-01-31 16:00:00'),
    ('Bed Sheet', 19.99, 2, TRUE, 'Local', '2023-02-01 17:00:00'),
    ('Pillow', 9.99, 3, TRUE, 'Local', '2023-02-02 18:00:00'),
    ('Blanket', 29.99, 4, TRUE, 'Local', '2023-02-03 19:00:00'),
    ('Lamp', 12.99, 5, TRUE, 'Local', '2023-02-04 20:00:00'),
    ('Desk', 49.99, 6, TRUE, 'Local', '2023-02-05 21:00:00'),
    ('Chair', 19.99, 7, TRUE, 'Local', '2023-02-06 22:00:00'),
    ('Table', 79.99, 8, TRUE, 'Local', '2023-02-07 23:00:00'),
    ('Sofa', 149.99, 9, TRUE, 'Local', '2023-02-08 00:00:00'),
    ('TV', 399.99, 10, TRUE, 'Local', '2023-02-09 01:00:00'),
    ('Computer', 999.99, 1, TRUE, 'Local', '2023-02-10 02:00:00'),
    ('Smartphone', 699.99, 2, TRUE, 'Local', '2023-02-11 03:00:00'),
    ('Headphones', 299.99, 3, TRUE, 'Local', '2023-02-12 04:00:00'),
    ('Speaker', 199.99, 4, TRUE, 'Local', '2023-02-13 05:00:00'),
    ('Camera', 499.99, 5, TRUE, 'Local', '2023-02-14 06:00:00'),
    ('Watch', 199.99, 6, TRUE, 'Local', '2023-02-15 07:00:00'),
    ('Glasses', 99.99, 7, TRUE, 'Local', '2023-02-16 08:00:00'),
    ('Bag', 49.99, 8, TRUE, 'Local', '2023-02-17 09:00:00'),
    ('Shoes', 79.99, 9, TRUE, 'Local', '2023-02-18 10:00:00'),
    ('Hat', 29.99, 10, TRUE, 'Local', '2023-02-19 11:00:00');
purchase_log.sql
CREATE TABLE purchase_history (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    product_id INTEGER REFERENCES products(id),
    coupon_used BOOLEAN,
    quantity INTEGER,
    purchase_date TIMESTAMP
);

-- ランダムな購入履歴を作成
-- 作りたいレコード分だけコピペ

INSERT INTO purchase_history (user_id, product_id, coupon_used, quantity, purchase_date)
VALUES
    (FLOOR(RANDOM() * 30) + 1, FLOOR(RANDOM() * 50) + 1, RANDOM() < 0.5, FLOOR(RANDOM() * 10) + 1, TIMESTAMP '2023-01-01 00:00:00' + (RANDOM() * (TIMESTAMP '2023-02-19 11:00:00' - TIMESTAMP '2023-01-01 00:00:00'))),
;

簡単にテーブルを準備する。例として
・ユーザーテーブル
・商品テーブル
・商品購入履歴テーブル

の3つを作成してDB操作を学んでいく

SQL操作

基本

ユーザーテーブル参照
SELECT * FROM public.users
WHERE gender = true
ORDER BY age DESC

image.png

購入ログテーブル参照
SELECT id, user_id, purchase_date 
FROM public.purchase_history
ORDER BY purchase_date DESC

必要そうなカラムのみを表示
image.png

結合(INNER)

image.png

内部(INNER) は両テーブルデータあり、
外部(OUTER) は片方にデータがなくても問題ない。

外部結合と内部結合の違い
  • 内部結合（Inner Join）:

共通する値を持つ行のみを結合する。
結果には、結合元の両方のテーブルに存在する行だけが含まれる。
結合条件に合致しない行は結果に含まれない。

  • 外部結合（Outer Join）:

少なくとも1つのテーブルのすべての行を結果に含む。
結合条件に合致する行がない場合は、NULL値で埋められる。
結合元のいずれかのテーブルに存在する行が含まれる。
外部結合の種類とそれぞれの違い:

  • 左外部結合（LEFT OUTER JOIN）:

左側のテーブル（左テーブル）のすべての行を含む。
右側のテーブル（右テーブル）の該当する行が存在する場合は結合し、存在しない場合はNULL値を返す。

  • 右外部結合（RIGHT OUTER JOIN）:

右側のテーブル（右テーブル）のすべての行を含む。
左側のテーブル（左テーブル）の該当する行が存在する場合は結合し、存在しない場合はNULL値を返す。

  • 完全外部結合（FULL OUTER JOIN）:

左側のテーブルと右側のテーブルのすべての行を含む。
どちらかのテーブルにしか存在しない行は、対応する側のテーブルの列はNULL値になる。

内部結合を試してみる

内部結合
SELECT *
FROM public.purchase_history
INNER JOIN products.id ON purchase_history.product_id

購入ログだけの場合は以下

購入ログだけ
SELECT * FROM public.products

image.png

購入ログに商品名を紐づけたものがこちら

購入ログだけ
SELECT purchase_history.*, products.name 
FROM purchase_history
INNER JOIN products ON products.id = purchase_history.product_id;

image.png

手順
  • 参照したいテーブルAのSELECT文を記載
  • INNER JOINの後にテーブルAに紐づけたいテーブルBを記載
  • ONを書いてテーブルAとテーブルBとで
    何が一致していればいいのか の条件を記載

ユーザーIDを指定したい場合はJOINの後に記載

購入ログだけ
SELECT purchase_history.*, products.name FROM purchase_history
INNER JOIN products ON products.id = purchase_history.product_id
WHERE user_id=30;

image.png

INNER と OUTER の違い

実験

試しに履歴に載っていない商品データを作ってみる。

INSERT INTO 
    products (name, price, category, is_original, origin, purchase_date)
VALUES
    ('Unknown', 100.00, 1, TRUE, 'Japan', '2023-01-01 10:00:00')

INNERは両テーブルに共通している場合に返す。
OUTERは両テーブル共通していなくても、片方にデータがあれば返す。
LEFTかRIGHTはどちらのテーブルの値を優先的に取得するか？
言葉で説明するのは難しい。

以下は商品テーブルと、それに紐づいた購入履歴テーブルを表示したものである。
商品履歴が紐づいた分だけレコードが表示される

INNER JOIN : 商品1に紐づいた履歴を全て表示
SELECT * FROM public.products
INNER JOIN public.purchase_history 
ON products.id = purchase_history.product_id
WHERE products.id = 1

やっていることは

SELECT * FROM public.purchase_history WHERE products.id = 1

と同じだけど、ここでは便宜上...
image.png

これを履歴に存在しない商品で条件づけると...

INNER JOIN : 履歴に存在しない商品
SELECT * FROM public.products
INNER JOIN public.purchase_history 
ON products.id = purchase_history.product_id
WHERE products.id = 51

image.png

何も表示されない。(返されない)

LEFT OUTER JOINだと

LEFT OUTER JOIN : 履歴に存在しない商品
SELECT * FROM public.products
LEFT OUTER JOIN public.purchase_history 
ON products.id = purchase_history.product_id
WHERE products.id = 51

image.png

レコードが1件返ってきた。が、履歴分のデータは空。

では、RIGHT OUTER JOINだと

RIGHT OUTER JOIN : 履歴に存在しない商品
SELECT * FROM public.products
RIGHT OUTER JOIN public.purchase_history 
ON products.id = purchase_history.product_id
WHERE products.id = 51

image.png

返ってこない。
ONの条件の右辺/左辺という意味でもないっす。

ちなみに、FULL OUTER JOINだと

FULL OUTER JOIN : 履歴に存在しない商品
SELECT * FROM public.products
FULL OUTER JOIN public.purchase_history 
ON products.id = purchase_history.product_id
WHERE products.id = 51

image.png

返ってくる。

LEFT/RIGHTってなんなのか

恐らくLEFTは結合される側
RIGHTは結合する側
と捉えられるだろう。

どちらのテーブルにもデータが存在していないといけない内部結合に対し、
外部結合はどちらかにデータがあれば問題ない。

ただ、どちらのテーブルに基準を合わせて、
データがないと判断すればいいのかは、
LEFT/RIGHTによる。

この場合では

LEFT 結合される側 商品テーブル
RIGHT 結合する側 購入履歴テーブル

となり、id=51は
商品テーブルLEFTにはあり、
購入履歴テーブルRIGHTにはない。

だからLEFT OUTER JOINではレコードが返って来、
RIGHT OUTER JOINでは返ってこないのである。

個数 COUNT

Practice

全体の行数を数える
SELECT COUNT(*) FROM table_name;
特定の列の非NULL値の数を数える
SELECT COUNT(column_name) FROM table_name;

COUNTの中にカラム名を入れてあげればいい。

特定の条件に合致する行の数を数える
SELECT COUNT(*) FROM table_name WHERE condition;

ユーザー1が購入した履歴を数えるには以下

ユーザー1が購入した履歴を数える
SELECT COUNT(*) FROM public.purchase_history 
WHERE user_id = 1

Challenge1

各商品が売れた個数を知るには
まず、IDごとにグループ化しないといけない。

IDごとにグループ化
SELECT COUNT(*)
FROM purchase_history
GROUP BY product_id;

image.png

ただ、このままでは何の個数なのかがわからない。
なのでidを表示してあげる。

IDごとにグループ化
SELECT product_id, COUNT(*)
FROM purchase_history
GROUP BY product_id;

image.png

countというカラム名はデフォルトでつけられるが、
合計値カラムを自分で好みのカラム名にしたい場合は、
エイリアスのASを使って以下のように書く。

エイリアスのASを使って書く。
SELECT product_id, COUNT(*) AS sold_count
FROM purchase_history
GROUP BY product_id;

image.png

Challenge2

IDだけでは実際なんの商品なのかがわからない。
その場合に、商品テーブルから名前を持ってくることができる。

商品名+合計売上個数
SELECT p.name AS product_name, COUNT(ph.product_id) AS sold_count
FROM products p
LEFT JOIN purchase_history ph ON p.id = ph.product_id
GROUP BY p.name
ORDER BY sold_count DESC;

image.png

やばいので分解します。

まず基盤となるSQL
商品リストを取得

基盤となるSQL
SELECT * FROM public.products

image.png

次に商品テーブルと購入履歴テーブルを結合します。
どちらもデータがある前提なので、右でも左でも。

商品テーブルと購入履歴テーブルを結合
SELECT * FROM public.products
LEFT OUTER JOIN 
	public.purchase_history 
ON purchase_history.product_id = products.id

image.png

ちなみにエイリアスを使うといちいちテーブル名を記述しなくても
簡単に書けます。

エイリアス使用
SELECT * FROM public.products as p
LEFT OUTER JOIN 
	public.purchase_history ph
ON ph.product_id = p.id

asはつけてもつけなくても大丈夫そう。
別名を使いたいテーブルの真後ろに記述すればよさそう。

一つの商品に対して複数の履歴がついていることが
直感的にわかりづらいので一旦ソートします。

一旦ソート(失敗)
SELECT * FROM public.products as p
LEFT OUTER JOIN 
	public.purchase_history ph
ON ph.product_id = p.id
ORDER BY id ASC

image.png

ちなみに上記SQLでは、idを昇順ソートしようとしていますが、
テーブルが2つ登場しているので、
どちらのテーブルのidなのかがわからないと怒られています。
下記のように明確にしてあげます。

一旦ソート(成功)
SELECT * FROM public.products as p
LEFT OUTER JOIN 
	public.purchase_history ph
ON ph.product_id = p.id
ORDER BY p.id ASC

image.png

そうしたら一つの商品に対して複数の履歴が紐づいていることがわかります。

これらを商品名でGROUP化します。

商品名でGROUP化(失敗)
SELECT * FROM public.products as p
LEFT OUTER JOIN 
	public.purchase_history ph
ON ph.product_id = p.id
GROUP BY p.name
ORDER BY p.id ASC

image.png

すると、エラーが発生します。
GROUPを作る際には以下のようなルールがあるので、*でSELECTすることはできません。

When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions, since there would be more than one possible value to return for an ungrouped column.

GROUP BY が存在する場合、グループ化されていない列に対して返される可能性のある値が複数あるため、集計関数内を除き、SELECT リスト式でグループ化されていない列を参照することは無効です。

idもグループ化されていないので、ソートを削除する必要があります。

商品名でGROUP化(成功)
SELECT p.name FROM public.products as p
LEFT OUTER JOIN 
	public.purchase_history ph
ON ph.product_id = p.id
GROUP BY p.name

image.png

あとはcountをしてあげるだけです。

商品名でGROUP化(成功)
SELECT p.name, count(ph.product_id)
FROM public.products as p
LEFT OUTER JOIN 
	public.purchase_history ph
ON ph.product_id = p.id
GROUP BY p.name

image.png

何をcountの引数に入れればいいかという判断は
少し難しいかも。。
数を数えたいのは、あくまでも紐づいている履歴の数なので、
テーブルはph
商品テーブルと紐づいているのはproduct_idだから、
countにはph.product_idを入れてあげるといった塩梅だろうか。

あとは、表をどういう順序で並べればいいか。

countが大きいものから並び替える
SELECT p.name, count(ph.product_id)
FROM public.products as p
LEFT OUTER JOIN 
	public.purchase_history ph
ON ph.product_id = p.id
GROUP BY p.name
ORDER BY count DESC

image.png

エイリアスはつけてもつけなくても構いません、

エイリアスつけて完成
SELECT p.name, count(ph.product_id) sold_count
FROM public.products as p
LEFT OUTER JOIN 
	public.purchase_history ph
ON ph.product_id = p.id
GROUP BY p.name
ORDER BY sold_count DESC

image.png

これで理想の結果を得ることができました！

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0