SQL勉強記録 サブクエリ1

サブクエリとは

SQLの中に書く別のSQL。
値を取得⇒取得した値に対してさらに絞り込む。
エクセルの数式のネストみたいな感じ？

問題

以下の products テーブルがあります。

id	product_name	category	price
1	トマト	野菜	200
2	きゅうり	野菜	100
3	りんご	果物	300
4	バナナ	果物	200
5	牛乳	飲料	180

カテゴリごとの平均価格を計算し、それを各商品の行に表示してください。

【表示するもの】

• 商品名(product_name)
• カテゴリ(category)
• 価格(price)
• 同じカテゴリの平均価格(avg_price_by_category)←これをサブクエリとして追加したい

段階的に考えてみた

まずは表示させたい項目とテーブルを記載する

SELECT 
   product_name,
   catgory,
   price,
   ●同じカテゴリの平均価格● AS avg_price_by_category
FROM products;

●同じカテゴリの平均価格●　にサブクエリを入れる

サブクエリの中を普通に書いてみる

同じカテゴリの平均価格だけのSQLを普通に書くと

SELECT category, AVG(price) AS avg_price_by_category
FROM products
GROUP BY category;

なんだけど・・・

この結果は下記のように別の表(複数行)になるってしまう。
追加したいカラムはavg_price_by_categoryだけなので
●同じカテゴリの平均価格●にそのまま入れることはできない。

category	avg_price_by_category
野菜	150
果物	250
飲料	180

その都度、見ている商品のカテゴリの平均を取りたい

例えば、
　トマト　⇒　category = 野菜

を見ているなら

SELECT AVG(price)
FROM products
WHERE category = '野菜'

となる。

解答は

SELECT
    p1.product_name,
    p1.category,
    p1.price,
    (
        SELECT AVG(p2.price)
        FROM products p2
        WHERE p2.category = p1.category
    ) AS avg_price_by_category
FROM products p1;

え・・・p1とp2ってなんだよ・・・・

同じテーブルを2枚並べて見ているイメージ

トマトの行を表示しているとき↓
p1の表のトマト

id	product_name	category	price
1	トマト	野菜	200

テーブル全体↓
p2の表

id	product_name	category	price
1	トマト	野菜	200
2	きゅうり	野菜	100
3	りんご	果物	300
4	バナナ	果物	200
5	牛乳	飲料	180

そして
WHERE p2.category = p1.category
によって、「p1と同じカテゴリの商品だけ」に絞り込んで平均価格を計算している。

つまり

p1 = 今表示したい行
p2 = 平均を計算するための参照用テーブル