【データ抽出/SQL】商品ごとに商品毎の平均単価より高い商品を抽出する

相関サブクエリとは

WHERE句に外部クエリからの値を使用するサブクエリ。
外部クエリによって処理される各行を一行ずつ取り出して、
相関サブクエリ内の指定した位置に一行ずつ代入される。

相関サブクエリの実際の動き

相関サブクエリは、小分けしたグループ内での比較をする時などに便利。

具体例として、以下のテーブルを使用して、
各shouhin_bunrui毎の平均単価よりも、hanbai_tankaが高い商品のデータを抽出してみる。

使用クエリ

select
    shohin_mei
    , shohin_bunrui
    , hanbai_tanka
from
    shohin s1
where
    hanbai_tanka > (
                     select
                         avg(hanbai_tanka) 
                     from
                         shohin s2 
                     where 
                         s1.shohin_bunrui = s2.shohin_bunrui
                     group by
                         shohin_bunrui
                   );

抽出結果

処理の流れ

1. サブクエリの中でshohin_bunrui毎に平均hanbai_tankaを算出
2. 外部クエリで取得したデータ（=商品毎のhanbai_tanka）一つ一つを、
   サブクエリ内のshohin_bunrui毎の平均hanbai_tankaと比較していく。
   （同じshohin_bunruiのデータ同士を比較する。）
3. shohin_bunrui毎の平均値よりも、hanbai_tankaの高い商品が抽出される。

抽出結果の具体例

shohin_idが1のTシャツについて見てみる。

1. shohin_idが１のTシャツはshohin_bunruiが衣服で、hanbai_tankaが1000。
2. 相関サブクエリの中では、衣服のavg(hanbai_tanka)は2500と算出されている。
3. 相関サブクエリ内で、shohin_bunrui毎に1.の数値と2.の数値を比較している。
4. Tシャツのhanbai_tankaは、衣服の平均hanbai_tankaよりも小さいため、データが抽出されなかった。