SQL修行vol.8
『達人に学ぶSQL徹底指南書』ー7.ウィンドウ関数で行間比較を行う
世間よりだいぶ遅れてゼルダの伝説BotWを昨年始めたわけだが、コログを320匹ほど集めたところで、絶対900匹全部集めきりたいという気持ちになってきた今日この頃。コログ収集に励みたい欲を抑えつつ、今日の学びのアウトプットを。(これ終わったらコログ集めよう)
活用ケース1:成長・後退・現状維持
例:前年に比べて年商が増えたのか、減ったのか、変わらなかったのかを調べる
STEP1_前年と年商が同じ年度を求める
--相関サブクエリの利用
SELECT
year,
sale,
FROM
Sales S1
WHERE sale = (SELECT
sale
FROM
Sales S2
WHERE
S2.year = S1.year - 1)
ORDER BY
year;
--ウィンドウ関数の利用
SELECT
year,
current_sale
FROM
SELECT
year,
sale AS current_sale
SUM(sale) OVER (ORDER BY year
ROWS BETWEEN 1 PRECEDING AND PRECEDING) AS pre_sale
FROM
Sales
WHERE
current_sale = pre_sale
ORDER BY
year;
STEP2_前年に比べて年商が増えたのか、減ったのか、変わらなかったのかを一度に求める
--相関サブクエリの利用
SELECT
year,
current_sale AS sale,
CASE WHEN current_sale < pre_sale THEN '↓'
WHEN current_sale = pre_sale THEN '→'
WHEN current_sale > pre_sale THEN '↑'
THEN '-'
END
FROM
(SELECT
year,
sale AS current_sale,
(SELECT
sale AS pre_sale
FROM
Sales S2
WHERE
S2.year = S1.year - 1)
FROM
Sales S10
ORDER BY
year;
--ウィンドウ関数の利用
SELECT
year,
current_sale AS sale
CASE WHEN current_sale < pre_sale THEN '↓'
WHEN current_sale = pre_sale THEN '→'
WHEN current_sale > pre_sale THEN '↑'
THEN '-'
END
FROM
SELECT(year,
sale AS current_sale,
SUM(sale) OVER(ORDER BY year
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS pre_sale
FROM
Sales
ORDER BY
year;
活用ケース2:時系列に歯抜けがある場合ー直近と比較
例:活用ケース1と同じことをしたいが、各年のデータが揃っていないときに「直近」の行を比較対象にして求める
※「直近」=1.自分より前の年であること、2.条件1を満たす中で最大であること
STEP1_直近の年と年商が同じ年度を求める
--相関サブクエリの利用
SELECT
year,
sale
FROM
Sales S1
WHERE
sale = (SELECT
sale
FROM
Sales S2
WHERE
S2.year = (SELECT
MAX(year)
FROM
Sales S3
WHERE
S2.year > S3.year
)
)
ORDER BY
year;
--ウィンドウ関数の利用
SELECT
year,
current_sale
FROM
SELECT
year,
sale AS current_sale
SUM(sale) OVER(ORDER BY year
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS pre_sale
FROM
Sales
WHERE
current_sale = pre_sale
ORDER BY
year;
活用ケース3:ウィンドウ関数 vs. 相関サブクエリ
〜おさらい〜
ウィンドウ関数:「行の順序」に基づいて操作を行う
相関サブクエリ:複数のテーブルを結合して操作を行う
例:各商品分類について平均単価より高い商品を選択する
--相関サブクエリの利用
SELECT
shohin_bunrui,
shohin_mei,
hanbai_tanka
FROM
Shohin S1
WHERE
hanbai_tanka > (SELECT
AVG(hanbai_tanka)
FROM
Shohin S2
WHERE
S2.shohin_bunrui = S1.shohin_bunrui
GROUP BY
shohin_bunrui
);
--ウィンドウ関数の利用
SELECT
shohin_bunrui,
shohin_mei,
hanbai_tanka
FROM
(SELECT
shohin_bunrui,
shohin_mei,
hanbai_tanka,
AVG(hanbai_tanka) OVER(PATITION BY shohin_bunri) AS avg_tanka
FROM
Shohin)
WHERE
hanbai_tanka > avg_tabka;
活用ケース4:オーバーラップする期間を調べる
例:部屋の予約状況から予約の重複を調べる
--相関サブクエリの利用
SELECT
reserver,
start_date,
end_date
FROM
Reservation R1
WHERE EXISTS (SELECT
*
FROM
Reservation R2
WHERE
R1.reserver <> R2.reserver
AND
(R1.start_date BETWEEN R2.star_date AND R2.edate
OR
R1.end_date BETWEEN R2.star_date AND R2.edate)
);
--ウィンドウ関数の利用
SELECT
reserver,
next_reserver
FROM
SELECT
reserver,
start_date,
end_date,
MAX(start_Date)
OVER(ORDER BY start_date
ROW BETWEEN 1 PRECEDING AND 1 PRECEDING) AS next_sdat,
MAX(reserver)
OVER(ORDER BY start_date
ROW BETWEEN 1 PRECEDING AND 1 PRECEDING) AS next_reserver
FROM
Reservations
WHERE
next_sdate BETWEEN start_date AND end_date;
この記事が気に入ったらサポートをしてみませんか?