■■■サブクエリを使いこなす■■■
■サブクエリとは
例）
・テーブル
item_name|item_category|action_date|user_id|price|
---------|-------------|-----------|-------|-----|
コーヒー |beverage | 2019-01-29|UID0001| 280|
緑茶 |food | 2019-01-29|UID0001| 280|
緑茶 |food | 2019-01-30|UID0004| 200|
コーヒー |beverage | 2019-01-30|UID0004| 750|
砂糖 |food | 2019-01-30|UID0011| 200|
コーヒー |beverage | 2019-01-30|UID0011| 750|
メイプルシロップ |food | 2019-02-01|UID0011| 450|
メイプルシロップ |food | 2019-02-02|UID0011| 450|
ティーカップ |zakka | 2019-02-03|UID0002| 980|
砂糖 |food | 2019-02-04|UID0006| 280|
・SQL
SELECT
item_name,
item_category,
price
FROM
item_purchase_log
WHERE
item_category = (
SELECT
item_category
FROM
item_purchase_log
WHERE
item_name = '緑茶' AND user_id ='UID0004');
・結果
item_name|item_category|price|
---------|-------------|-----|
緑茶 |food | 280|
緑茶 |food | 200|
砂糖 |food | 200|
メイプルシロップ |food | 450|
メイプルシロップ |food | 450|
砂糖 |food | 280|
■階層を増やしたサブクエリ
例）
・SQL
SELECT
item_name, AVG(price)
FROM
(SELECT
item_name, item_category, action_date, price
FROM
item_purchase_log
WHERE
item_category = (
SELECT
item_category
FROM
item_purchase_log
WHERE
item_name = '緑茶' AND user_id ='UID0004'
)
) AS special_list
GROUP BY item_name
HAVING AVG(price) > 300;
・結果
item_name|AVG(price)|
---------|----------|
メイプルシロップ | 450.0000|
■WITH句でサブクエリを見やすく
・SQL
WITH special_list AS(
SELECT
item_name, item_category, action_date, price
FROM
item_purchase_log
WHERE
item_category = (
SELECT
item_category
FROM
item_purchase_log
WHERE
item_name = '緑茶' AND user_id = 'UID0004')
)
SELECT
item_name, AVG(price)
FROM
special_list
GROUP BY
item_name
HAVING AVG(price) > 300;
・結果
item_name|AVG(price)|
---------|----------|
メイプルシロップ | 450.0000|
■スカラサブクエリでスカラ値を扱う
例）購買履歴テーブルから平均価格よりも高い商品名を取得
ー WHERE句でスカラサブクエリを利用
・SQL
SELECT
item_name, price
FROM
item_purchase_log
WHERE
price > (
SELECT
AVG(price)
FROM
item_purchase_log);
・結果
item_name|price|
---------|-----|
コーヒー | 750|
コーヒー | 750|
ティーカップ | 980|
例）平均価格カラムの追加
ー SELECT句でスカラサブクエリを使う
・SQL
SELECT
item_name,
price,
(
SELECT
AVG(price)
FROM
item_purchase_log
) AS avg_price
FROM
item_purchase_log;
・結果
item_name|price|avg_price|
--------|-----|---------|
コーヒー | 280| 462.0000|
緑茶 | 280| 462.0000|
緑茶 | 200| 462.0000|
コーヒー | 750| 462.0000|
砂糖 | 200| 462.0000|
コーヒー | 750| 462.0000|
メイプルシロップ | 450| 462.0000|
メイプルシロップ | 450| 462.0000|
ティーカップ | 980| 462.0000|
砂糖 | 280| 462.0000|
■HAVING句でスカラサブクエリを使う
例）HAVING句の中で、全体の平均価格と、カテゴリごとの平均価格を比較し、商品カテゴリごとの平均価格が高いカテゴリを抽出
・SQL
SELECT
item_category, AVG(price)
FROM
item_purchase_log
GROUP BY
item_category
HAVING AVG(price) > (
SELECT
AVG(price)
FROM
item_purchase_log);
・結果
item_category|AVG(price)|
-------------|----------|
beverage | 593.3333|
zakka | 980.0000|
■相関サブクエリでサブクエリ以外のデータを参照する
相関サブクエリ：サブクエリの中で、自分自身のクエリの外側にあるクエリのテーブル/データを参照するようなサブクエリ
例）商品カテゴリごとの平均価格より高い価格の商品の抽出
・SQL
SELECT
item_category, item_name, price
FROM
item_purchase_log AS ipl1
WHERE
price > (
SELECT
AVG(price)
FROM
item_purchase_log AS ipl2
WHERE
ipl1.item_category = ipl2.item_category
GROUP BY
item_category);
・結果
item_category|item_name|price|
------------|---------|-----|
beverage |コーヒー | 750|
beverage |コーヒー | 750|
food |メイプルシロップ | 450|
food |メイプルシロップ | 450|
■■■INとEXISTSによるデータの調査■■■
■IN述語とサブクエリを組み合わせる
例）IN述語で都道府県を指定して、そのユーザーの購買商品を表示
・テーブル
item_purchase_log
item_name|item_category|action_date|user_id|price|
---------|-------------|-----------|-------|-----|
コーヒー |beverage | 2019-01-29|UID0001| 280|
緑茶 |food | 2019-01-29|UID0001| 280|
緑茶 |food | 2019-01-30|UID0004| 200|
コーヒー |beverage | 2019-01-30|UID0004| 750|
砂糖 |food | 2019-01-30|UID0011| 200|
コーヒー |beverage | 2019-01-30|UID0011| 750|
メイプルシロップ |food | 2019-02-01|UID0011| 450|
メイプルシロップ |food | 2019-02-02|UID0011| 450|
ティーカップ |zakka | 2019-02-03|UID0002| 980|
砂糖 |food | 2019-02-04|UID0006| 280|
mst_user_place
user_id|pref_name|city_name|
-------|---------|---------|
UID0001|東京都 |中央区 |
UID0002|東京都 |世田谷区 |
UID0003|福岡県 |福岡市 |
UID0004|東京都 |渋谷区 |
UID0005|神奈川県 |横浜市 |
UID0006|愛知県 |名古屋市 |
UID0007|東京都 |北区 |
UID0008|福岡県 |北九州市 |
UID0009|東京都 |渋谷区 |
UID0010|愛知県 |名古屋市 |
UID0011|神奈川県 |川崎市 |
・SQL
SELECT
item_name, price
FROM
item_purchase_log
WHERE
user_id IN(
SELECT
user_id
FROM
mst_user_place
WHERE
pref_name = '東京都');
・結果
item_name|price|
---------|-----|
コーヒー | 280|
緑茶 | 280|
ティーカップ | 980|
緑茶 | 200|
コーヒー | 750|
■EXISTS述語でデータを検査する
ーEXISTSでデータの有無を調べる
例）EXISTS述語で都道府県を指定して、そのユーザーの購買商品を表示
・SQL
SELECT
item_name, price
FROM
item_purchase_log AS ipl
WHERE
EXISTS(
SELECT
*
FROM
mst_user_place AS mup
WHERE
mup.pref_name = '東京都' AND mup.user_id = ipl.user_id );
・結果
item_name|price|
---------|-----|
コーヒー | 280|
緑茶 | 280|
ティーカップ | 980|
緑茶 | 200|
コーヒー | 750|
例）NOT EXISTSにより、上の例の逆の結果を求める
・SQL
SELECT
item_name, price
FROM
item_purchase_log AS ipl
WHERE
NOT EXISTS(
SELECT
*
FROM
mst_user_place AS mup
WHERE
mup.pref_name = '東京都' AND mup.user_id = ipl.user_id );
・結果
item_name|price|
---------|-----|
砂糖 | 200|
コーヒー | 750|
メイプルシロップ | 450|
メイプルシロップ | 450|
砂糖 | 280|
■■■SQLで基本統計量を求める■■■
■HAVING句で最頻値、中央値や分位数を求める
・テーブル
store_id|sales|
--------|-----|
A | 4350|
B | 550|
C | 400|
D | 420|
E | 450|
F | 420|
G | 400|
H | 320|
I | 320|
J | 400|
例）最頻値を求めるSQL
SELECT
sales
FROM
store_sales
GROUP BY
sales
HAVING count(*) >= (
SELECT
MAX(group_count)
FROM
(
SELECT
COUNT(*) AS group_count
FROM
store_sales
GROUP BY
sales
) AS sq
);
・結果
sales|
-----|
400|
例）HAVING句で中央値を求めるSQL
SELECT
AVG(DISTINCT sales)
FROM
(
SELECT
sa1.sales
FROM
store_sales sa1
JOIN
store_sales sa2
GROUP BY
sa1.sales
HAVING
SUM(
CASE
WHEN sa2.sales >= sa1.sales THEN 1
ELSE 0
END
) >= COUNT(*) / 2
AND
SUM(
CASE
WHEN sa2.sales <= sa1.sales THEN 1
ELSE 0
END
) >= COUNT(*) / 2
) AS sq;
・結果
AVG(DISTINCT sales)|
-------------------|
410.0000|
例）分位数を求めるSQL
ー店舗売上デーブルにより、まず売上の順位(Ranking)を求め、それを四分割のグループに分ける。
SELECT
store_id, sales, ranking,
CASE
WHEN sa1.ranking <= 0.25 * ( SELECT COUNT(*) FROM store_sales ) THEN 1
WHEN sa1.ranking <= 0.5 * (SELECT COUNT(*) FROM store_sales ) THEN 2
WHEN sa1.ranking <= 0.75 * (SELECT COUNT(*) FROM store_sales ) THEN 3
ELSE 4
END AS 四分位
FROM
( SELECT
s1.store_id,
s1.sales,
(
SELECT
COUNT(s2.sales)
FROM
store_sales AS s2
WHERE
s2.sales >= s1.sales
) ranking
FROM
store_sales AS s1
) sa1;
・結果
store_id|sales|ranking|四分位|
--------|-----|-------|---|
A | 4350| 1| 1|
B | 550| 2| 1|
C | 400| 8| 4|
D | 420| 5| 2|
E | 450| 3| 2|
F | 420| 5| 2|
G | 400| 8| 4|
H | 320| 10| 4|
I | 320| 10| 4|
J | 400| 8| 4|
■■■ログデータひとつでできるユーザー分析■■■
・テーブル
use_id |action_date|
-------|-----------|
UID0001| 2019-05-24|
UID0001| 2019-05-24|
UID0002| 2019-05-25|
UID0001| 2019-05-26|
UID0003| 2019-05-27|
UID0001| 2019-05-28|
UID0004| 2019-05-29|
UID0001| 2019-05-30|
UID0005| 2019-05-30|
UID0001| 2019-05-30|
UID0001| 2019-05-30|
UID0001| 2019-06-01|
UID0001| 2019-06-02|
UID0002| 2019-06-03|
UID0002| 2019-06-03|
UID0001| 2019-06-04|
UID0003| 2019-06-04|
UID0001| 2019-06-05|
UID0003| 2019-06-06|
UID0004| 2019-06-07|
例）上記テーブルから、日別でユーザーアクション数を求める
・SQL
SELECT
action_date AS 日付,
COUNT(use_id) AS アクション数
FROM
simple_log
GROUP BY
action_date;
・結果
日付 |アクション数|
----------|------|
2019-05-24| 2|
2019-05-25| 1|
2019-05-26| 1|
2019-05-27| 1|
2019-05-28| 1|
2019-05-29| 1|
2019-05-30| 4|
2019-06-01| 1|
2019-06-02| 1|
2019-06-03| 2|
2019-06-04| 2|
2019-06-05| 1|
2019-06-06| 1|
2019-06-07| 1|
例）日別でユニークユーザー数を求める
・SQL
SELECT
action_date AS 日付,
COUNT(DISTINCT(use_id)) AS ユニークユーザー数
FROM
simple_log
GROUP BY
action_date
ORDER BY
action_date;
・結果
日付 |ユニークユーザー数|
----------|---------|
2019-05-24| 1|
2019-05-25| 1|
2019-05-26| 1|
2019-05-27| 1|
2019-05-28| 1|
2019-05-29| 1|
2019-05-30| 2|
2019-06-01| 1|
2019-06-02| 1|
2019-06-03| 1|
2019-06-04| 2|
2019-06-05| 1|
2019-06-06| 1|
2019-06-07| 1|
例）日別で新規ユーザー数を求める
・SQL
SELECT
first_action_date AS 新規登録日,
COUNT(use_id) AS 新規ユーザー数
FROM
(
SELECT
use_id,
MIN(action_date) AS first_action_date
FROM
simple_log
GROUP BY
use_id
) AS t
GROUP BY
first_action_date
ORDER BY
first_action_date;
・結果
新規登録日 |新規ユーザー数|
----------|-------|
2019-05-24| 1|
2019-05-25| 1|
2019-05-27| 1|
2019-05-29| 1|
2019-05-30| 1|
例）サービス利用継続期間毎のユーザー数を求める
・SQL
SELECT
action_term AS 継続期間,
COUNT(use_id) AS ユニークユーザー数
FROM
(
SELECT
use_id,
DATEDIFF(MAX(action_date), MIN(action_date)) AS action_term
FROM
simple_log
GROUP BY
use_id
) AS t
GROUP BY
action_term
ORDER BY
action_term;
・結果
継続期間|ユニークユーザー数|
----|---------|
0| 1|
9| 2|
10| 1|
12| 1|
例）アクション数が３回以上のユーザーリストを求める
・SQL
SELECT
use_id,
action_count AS アクション数
FROM
(
SELECT
use_id,
COUNT(use_id) AS action_count
FROM
simple_log
GROUP BY
use_id
) AS t
WHERE
action_count >= 3;
・結果
use_id |アクション数|
-------|------|
UID0001| 11|
UID0002| 3|
UID0003| 3|
例）直近７日間でアクション数が３回以上のユーザーリスト（直近日：2019-06-08）
・SQL
SELECT
use_id,
action_count AS アクション数
FROM
(
SELECT
use_id,
COUNT(use_id) AS action_count
FROM
simple_log
WHERE
DATEDIFF('2019-06-08', action_date) <= 7
GROUP BY
use_id
) AS t
WHERE
action_count >= 3;
・結果
use_id |アクション数|
-------|------|
UID0001| 4|
0コメント