SQLコードをひたすらに書きまくる部
SQLコード書きまくる部です。経験を積みたい。
今回は、SQL道場というサイトでコードを書いていきます。
基本事項をまとめるの会
知ってる基本事項をまとめます コピペ最高！
処理順序
SQL文の処理順序は以下の通りです。今回は、
| step | 文 | 内容 |
|---|---|---|
| 1 | from | itemsテーブルから |
| 2 | where | item_priceが300以上の行を |
| 3 | group by | item_shopでグルーピングして |
| 4 | having | 平均価格が300以上の行を表示する |
| 5 | select | item_shopと店舗ごとの平均価格を選び |
| 6 | order by | 平均価格を降順で並び変えて |
| 7 | limit | 表示件数を20に制限する |
という順序で処理をしています。(適当に作ったサンプル)コードは以下の通りです。
SELECT -- 表示する列の選択
item_shop, -- 前カンマ、後ろカンマどちらでもOK
AVG(item_price) AS "avg_price"
-- group byで分けたグループごとに集計関数で集計している
-- また、平均価格の列を"avg_price"と命名
FROM
items
-- データを引っ張るテーブルを指定
WHERE
item_price > 300
-- テーブル全体から行単位で条件付け
GROUP BY
item_shop
-- グループ化するカラムを選択
-- ちなみに、group byに入ったカラム名以外は、select上では集計関数を走らせないとエラー
HAVING
AVG(item_price) > 300
-- where文で処理した後にさらに条件付け
ORDER BY
avg_price DESC
-- 降順で並び替え(昇順ならascか無記載)
LIMIT
20
-- 表示件数の制限
; -- コードの終了宣言？(入れないとエラー)
以下、注意事項(兼備忘録です)
select句の処理順序
ここで注意したいのは、select句の処理順序は、記述順序とは異なりだいぶ後ろの方だということです。 なので、例えば今回平均価格を"avg_price"と命名していますが、この名前を指定して機能するのはHAVING句以降になります。テーブルのスキャンコスト低減
よく言われる話として、クエリ(今回のような、テーブルからデータを取り出すSQL文)の実行コストを下げるというのがあるかと思いますが、その際にLIMITを使うのはあまり有効ではないようです。 というのも、LIMITはあくまで表示される件数を制限しているだけであり、テーブルのスキャン範囲を制限してはいません。 テーブルをスキャンするコストを下げたいのであれば、WHERE句で条件を付けるといった対応が必要となります。 snowflakeの場合は、DBから直接テーブルを選んだ後、テーブルプレビューをすることで上位100件を直接確認できます。https://dev.classmethod.jp/articles/20200129-snowflake-performance-tuning-7tips/
またそれ以外にも、テーブル情報を確認する方法(describe)や、件数制限をする方法(top n)があります
describe table <table name>;
-- 各カラムとその変数タイプの一覧表示
select top n <column name> from <table name>;
-- 上位n件のみ取り出し
sqlの三値論理について
sqlは、True/Falseの二値論理ではなく、Unknownを含めた三値論理で動いています。例えば、and演算を考えた際には以下のような真理値表になります。| And | True | Unknown | False |
|---|---|---|---|
| True | True | Unknown | False |
| Unknown | Unknown | Unknown | False |
| False | False | False | False |
or演算の場合には、以下のようになります。
| OR | True | Unknown | False |
|---|---|---|---|
| True | True | True | True |
| Unknown | True | Unknown | Unknown |
| False | True | Unknown | False |
判別方法としては、
- unknownをT/Fに入れ替えても結果が同じならその結果
- unknownをT/Fに入れ替えて結果が変わるならunknown
となるそうです。
これを注意しなければならない理由としては、where句での例えばINでデータを制限する際、Trueのものしか引っかからないようになっており、Falseとunknownはヒットしません。
なので、nullが入っている場合は、IS NULLなどを使ってそこらへんをうまくやる必要があります。
条件式でwhere column!=NULLとかやっても、論理式結果がunknownになる関係上、IS NULLを使うしかありません。
SQL道場での修行
修行場所はこちらです。
このサイトでは、実行環境を個人で用意する必要がなく、アクセスしたら即座にエディターが開くので、非常に楽です。
ひたすらにクエリを書きまくりましょう。
しょきゅーへん
書きます。ただひたすらに。
初級問題1
問題文
daily_retail_dataテーブルから全てのデータを取得し、日付順に並べ替えて表示してください。
条件
テーブル：daily_retail_data
カラム：＊
回答
select *
from daily_retail_data
order by transaction_date asc;
-- 昇順を明示
解答例
select
*
from
daily_retail_data
order by
transaction_date;
初級問題2
問題文
daily_retail_dataテーブルから業種カテゴリ（business_category）と売上（daily_revenue）のみを取得し、売上の高い順に並べ替えて表示してください。
条件
テーブル：daily_retail_data
カラム：business_category, daily_revenue
回答
select
business_category,
daily_revenue
from
daily_retail_data
order by daily_revenue desc;
解答例
select
business_category,
daily_revenue
from
daily_retail_data
order by
daily_revenue desc;
初級問題3
問題文
daily_retail_dataテーブルに記録されている取引の総件数を計算してください。
条件
テーブル：daily_retail_data
カラム：すべて
回答
select count(*) as total_records
-- count(*)なら全レコード数を数える データにnullが入っていても数える
-- count(列指定)ならnullの入ったレコードは数えない
from daily_retail_data;
解答例
select
count(*) as total_records
from
daily_retail_data;
初級問題4
問題文
daily_retail_dataテーブルから、売上（daily_revenue）が50000以上の取引データを取得してください。
条件
テーブル：daily_retail_data
カラム：daily_revenue
回答
select *
from daily_retail_data
where daily_revenue >= 50000 ;
-- where文での絞り込み
解答例
select
*
from
daily_retail_data
where
daily_revenue >= 50000;
初級問題5
問題文
daily_retail_dataテーブルから業種カテゴリ（business_category）と売上（daily_revenue）を取得し、業種カテゴリをアルファベット順に並べ替えて表示してください。
条件
テーブル：daily_retail_data
カラム：business_category, daily_revenue
回答
select business_category, daily_revenue
from daily_retail_data
order by business_category;
解答例
select
business_category,
daily_revenue
from
daily_retail_data
order by
business_category;
初級問題6
問題文
daily_retail_dataテーブルから、重複のない業種カテゴリ（business_category）の一覧を取得してください。
条件
テーブル：daily_retail_data
カラム：business_category
回答
select distinct business_category
from daily_retail_data;
解答例
select distinct
business_category
from
daily_retail_data
order by
business_category;
-- 問題文で言われてなかったじゃん！！！！
初級問題7
問題文
daily_retail_dataテーブルから、business_categoryに「shop」を含む店舗の取引データを取得し、以下の順で表示してください：
- 取引日（transaction_date）の昇順
- 業種カテゴリ（business_category）の昇順
条件
テーブル：daily_retail_data
カラム：transaction_date, business_category, daily_revenue
回答
select transaction_date, business_category, daily_revenue
from daily_retail_data
where business_category like '%shop%';
-- '文字列'で検索 '%文字列%'で、文字列を含むものを検索
-- '文字列%'なら、文字列から始まるものを検索
解答例
select
*
from
daily_retail_data
where
business_category like '%shop%'
order by
transaction_date,
business_category;
初級問題8
問題文
daily_retail_dataテーブルから、daily_revenueがNULLの取引データを全て取得してください。
条件
テーブル：daily_retail_data
カラム：*
回答
select *
from daily_retail_data
where daily_revenue IS NULL;
解答例
select
*
from
daily_retail_data
where
daily_revenue is null;
初級問題9
問題文
daily_retail_dataテーブルの売上（daily_revenue）の最大値と最小値を取得してください。
条件
テーブル：daily_retail_data
カラム：daily_revenue
回答
select MAX(daily_revenue) as 'max_revenue', MIN(daily_revenue) as 'min_revenue'
from daily_retail_data;
解答例
select
max(daily_revenue) as max_revenue,
min(daily_revenue) as min_revenue
from
daily_retail_data;
初級問題10
問題文
daily_retail_dataテーブルから、売上（daily_revenue）が30000以上の取引件数を計算してください。
条件
テーブル：daily_retail_data
カラム：*
回答
select count(*) as high_revenue_count
from daily_retail_data
where daily_revenue >= 30000;
解答例
select
count(*) as high_revenue_count
from
daily_retail_data
where
daily_revenue >= 30000;
初級問題11
問題文
daily_retail_dataテーブルから、売上（daily_revenue）が30000以上50000以下の取引データを取得してください。結果は売上の昇順で表示してください。
条件
テーブル：daily_retail_data
カラム：*
回答
select *
from daily_retail_data
where daily_revenue between 30000 and 50000
order by daily_revenue asc;
解答例
select
*
from
daily_retail_data
where
daily_revenue between 30000 and 50000
order by
daily_revenue;
初級問題12
問題文
daily_retail_dataテーブルから、2024年1月1日の取引データのみを取得してください。
条件
テーブル：daily_retail_data
カラム：*
回答
select *
from daily_retail_data
where transaction_date = '2024-01-01';
解答例
select
*
from
daily_retail_data
where
transaction_date = '2024-01-01';
初級問題13
問題文
daily_retail_dataテーブルから、業種コード（industry_code）が'RT001'の取引データを取得してください。結果は日付の昇順で表示してください。
条件
テーブル：daily_retail_data
カラム：*
回答
select *
from daily_retail_data
where industry_code = 'RT001'
order by transaction_date;
解答例
select
*
from
daily_retail_data
where
industry_code = 'RT001'
order by
transaction_date;
初級問題14
問題文
条件
テーブル：daily_retail_data
カラム：daily_revenue
回答
select AVG(daily_revenue) as "avg_revenue"
from daily_retail_data;
解答例
select
avg(daily_revenue) as avg_revenue
from
daily_retail_data;
初級問題15
問題文
daily_retail_dataテーブルに含まれる業種カテゴリ（business_category）の種類数を計算してください。
条件
テーブル：daily_retail_data
カラム：business_category
回答
select count(distinct business_category) as category_count
from daily_retail_data;
解答例
select
count(distinct business_category) as category_count
from
daily_retail_data;
初級問題16
問題文
daily_retail_dataテーブルから、売上（daily_revenue）が0より大きい取引のみを取得してください。結果は売上の降順で表示してください。
条件
テーブル：daily_retail_data
カラム：*
回答
select *
from daily_retail_data
where daily_revenue > 0
order by daily_revenue desc;
解答例
select
*
from
daily_retail_data
where
daily_revenue > 0
order by
daily_revenue desc;
初級問題17
問題文
daily_retail_dataテーブルから、業種カテゴリ（business_category）に'Coffee'を含む取引データを取得してください。
条件
テーブル：daily_retail_data
カラム：*
回答
select *
from daily_retail_data
where business_category like '%Coffee%';
解答例
select
*
from
daily_retail_data
where
business_category like '%Coffee%';
初級問題18
問題文
daily_retail_dataテーブルの売上（daily_revenue）の合計値を計算してください。
条件
テーブル：daily_retail_data
カラム：daily_revenue
回答
select sum(daily_revenue) as "total_revenue"
from daily_retail_data;
解答例
select
sum(daily_revenue) as total_revenue
from
daily_retail_data;
初級問題19
問題文
daily_retail_dataテーブルから、以下の条件を満たすデータを取得してください：
- 2024年1月1日以降のデータ
- 売上（daily_revenue）が40000以上
条件
テーブル：daily_retail_data
カラム：*
回答
select *
from daily_retail_data
where transaction_date >= '2024-01-01' and daily_revenue >= 40000;
解答例
select
*
from
daily_retail_data
where
transaction_date >= '2024-01-01'
and daily_revenue >= 40000;
初級問題20
問題文
daily_retail_dataテーブルから、取引日（transaction_date）と売上（daily_revenue）のみを取得し、取引日の降順で表示してください。
条件
テーブル：daily_retail_data
カラム：transaction_date, daily_revenue
回答
select transaction_date, daily_revenue
from daily_retail_data
order by transaction_date desc;
解答例
select
transaction_date,
daily_revenue
from
daily_retail_data
order by
transaction_date desc;
初級編は以上です。長いですね。
ちゅーきゅーへん
書きます。ただひたすらに。
中級問題1
問題文
daily_retail_dataテーブルを使用して、各業種（business_category）の以下の指標を計算してください：
- 総売上
- 平均売上
- 最大売上
- 最小売上
結果は総売上の降順で表示してください。
条件
テーブル：daily_retail_data
カラム：business_category, daily_revenue
回答
select
business_category,
SUM(daily_revenue) AS total_revenue,
AVG(daily_revenue) AS avg_revenue,
MAX(daily_revenue) AS max_revenue,
MIN(daily_revenue) AS min_revenue
-- 集計関数をかませている
-- 集計関数を使う場合、group byとセット
-- select上で集計関数が使われないカラムはgroup byしないとダメ
from
daily_retail_data
group by
business_category
-- グループ化するカラムの指定
order by
total_revenue desc
;
解答例
select
business_category,
sum(daily_revenue) as total_revenue,
round(avg(daily_revenue)) as avg_revenue,
max(daily_revenue) as max_revenue,
min(daily_revenue) as min_revenue
from
daily_retail_data
group by
business_category
order by
total_revenue desc;
中級問題2
問題文
daily_retail_dataテーブルから、2024年の月次売上トレンドを分析してください。以下の指標を計算してください：
- 月間総売上
- 1日あたりの平均売上
- 前月比増減率（%）
結果は月順に表示してください。
条件
テーブル：daily_retail_data
カラム：transaction_date, daily_revenue
回答
select
strftime('%Y-%m', transaction_date) as "year_month",
-- これで'2024-01-01'から'2024-01'を取り出せるらしい
-- EXTRACT(year/month from transaction_date)でも抽出は可能だが、月の桁数が2桁に安定的にできなかった
-- 例 1月が'2024-1となり、次が'2024-10'となってダメ
SUM(daily_revenue) as "total_revenue",
AVG(daily_revenue) as "avg_daily_revenue",
"total_revenue" / LAG("total_revenue") over (order by "year_month") as growth_rate
-- window関数は、集計関数とは微妙に異なる。
-- 今回は、ひとつ前の行をとってくるLAGを使用
from
daily_retail_data
group by
"year_month"
order by
"year_month"
;
解答例
with -- with句を使って、可読性の向上と事前テーブル読み出し
monthly_sales as (
select
strftime ('%Y-%m', transaction_date) as year_month,
sum(daily_revenue) as total_revenue,
avg(daily_revenue) as avg_daily_revenue
from
daily_retail_data
group by
year_month
)
select
year_month,
total_revenue,
round(avg_daily_revenue) as avg_daily_revenue,
-- 丸めている
round(
(
total_revenue - lag(total_revenue) over (
order by
year_month
)
) * 100.0 / lag(total_revenue) over (
order by
year_month
),
1 -- 小数点以下は一桁にしている
) as growth_rate
from
monthly_sales
order by
year_month;
感想
普通にwith句を使う発想が抜けてた。
可読性の観点からwith句を使うべき
というか難易度が突然上がりすぎている
中級問題3
問題文
daily_retail_dataテーブルを使用して、各業種の売上構成比を計算してください。以下の指標を含めてください：
- 総売上
- 売上構成比（%）
- 累計構成比（%）
結果は売上構成比の降順で表示してください。
条件
テーブル：daily_retail_data
カラム：business_category, daily_revenue
回答
-- with句で売り上げの合計値の算出
with all_total_revenue as (
select SUM(daily_revenue)
from daily_retail_data
), -- with並列はできないので、二つ目以降はwithなし
-- カテゴリーごとの売り上げと割合
sum_revenue_on_category as (
select
business_category,
sum(daily_revenue) as "total_revenue",
sum(daily_revenue) / (
select *
from all_total_revenue
) * 100 as "revenue_share"
from
daily_retail_data
group by
business_category
order by
"total_revenue" desc
)
-- 累積割合の計算を、window関数で獲得
select
business_category,
total_revenue,
ROUND("revenue_share",2),
ROUND(sum(revenue_share) OVER (
order by revenue_share desc
ROWS UNBOUNDED PRECEDING -- カンマつなげをするとエラーになる
),2) as cumulative_share
from
sum_revenue_on_category
;
解答例
-- with句でカテゴリごとの売り上げの合計と売り上げ合計
with
sales_by_category as (
select
business_category,
sum(daily_revenue) as total_revenue
from
daily_retail_data
group by
business_category
),
total_sales as (
select
sum(total_revenue) as grand_total
from
sales_by_category
)
select
business_category,
total_revenue,
round(
total_revenue * 100.0 / (
select
grand_total
from
total_sales
),
1
) as revenue_share,
round(
sum(total_revenue) over (
order by
total_revenue desc
) * 100.0 / (
select
grand_total
from
total_sales
),-- window関数を使用 使う場合はoverを並べる
1
) as cumulative_share
from
sales_by_category
order by
revenue_share desc;
中級問題4
問題文
daily_retail_dataテーブルから、各業種の月次売上推移を分析してください。以下の指標を計算してください：
- 月間売上
- 売上構成比（%）
- 前月からの増加額
結果は業種と年月の順で表示してください。
条件
テーブル：daily_retail_data
カラム：transaction_date, daily_revenue
回答
-- with句で月の売り上げを、カテゴリごとのものとその合計とで作成
with revenue_on_monthly_and_category as (select
business_category,
strftime('%Y-%m', transaction_date) as year_month,
sum(daily_revenue) as monthly_revenue
from daily_retail_data
group by business_category, year_month
order by business_category, year_month
),
revenue_on_monthly as(
select
year_month,
sum(monthly_revenue) as all_category_monthly_revenue
from
revenue_on_monthly_and_category
group by
year_month
)
-- year_monthをキーにしてinner joinすることで、次ごとの売り上げ合計を紐づけ
select
mc.business_category,
mc.year_month,
mc.monthly_revenue,
mc.monthly_revenue / m.all_category_monthly_revenue as revenue_share,
mc.monthly_revenue - LAG(mc.monthly_revenue, 1) OVER (PARTITION BY mc.business_category
order by mc.business_category, mc.year_month) as revenue_increase
from
revenue_on_monthly_and_category mc
inner join revenue_on_monthly m on mc.year_month = m.year_month
order by
mc.business_category, mc.year_month
;
解答例
with
monthly_sales as (
select
business_category,
strftime ('%Y-%m', transaction_date) as year_month,
sum(daily_revenue) as monthly_revenue
from
daily_retail_data
group by
business_category,
year_month
),
monthly_total as (
select
year_month,
sum(monthly_revenue) as total_monthly_revenue
from
monthly_sales
group by
year_month
)
select
m.business_category,
m.year_month,
m.monthly_revenue,
round(
m.monthly_revenue * 100.0 / t.total_monthly_revenue,
1
) as revenue_share,
m.monthly_revenue - lag(m.monthly_revenue) over (
partition by
m.business_category
order by
m.year_month
) as revenue_increase
from
monthly_sales m
join monthly_total t on m.year_month = t.year_month
order by
m.business_category,
m.year_month;
中級問題5
問題文
daily_retail_dataテーブルから、曜日ごとの売上パターンを分析してください。以下の指標を計算してください：
- 平均売上
- 売上構成比（%）
- 取引件数
結果は平均売上の降順で表示してください。
条件
テーブル：daily_retail_data
カラム：transaction_date, daily_revenue
回答
with weekly_revenue as (
SELECT
case
when DAYNAME(transaction_date) = 'Monday' then '月曜日'
when DAYNAME(transaction_date) = 'Tuesday' then '火曜日'
when DAYNAME(transaction_date) = 'Wednesday' then '水曜日'
when DAYNAME(transaction_date) = 'Thursday' then '木曜日'
when DAYNAME(transaction_date) = 'Friday' then '金曜日'
when DAYNAME(transaction_date) = 'Saturday' then '土曜日'
when DAYNAME(transaction_date) = 'Sunday' then '日曜日'
end as day_of_week,
AVG(daily_revenue) as "avg_revenue",
count(*) as transaction_count
FROM
daily_retail_data
group by
day_of_week
),
sum_revenue as (
select
sum("avg_revenue")
FROM
weekly_revenue
)
select
day_of_week,
ROUND(avg_revenue, 2),
ROUND(avg_revenue / (select * from sum_revenue) * 100, 2) as "revenue_share",
transaction_count
FROM
weekly_revenue
order by
avg_revenue desc
;
解答例
with
daily_sales as (
select
case strftime ('%w', transaction_date)
when '0' then '日曜日'
when '1' then '月曜日'
when '2' then '火曜日'
when '3' then '水曜日'
when '4' then '木曜日'
when '5' then '金曜日'
when '6' then '土曜日'
end as day_of_week,
daily_revenue
from
daily_retail_data
)
select
day_of_week,
round(avg(daily_revenue)) as avg_revenue,
round(
sum(daily_revenue) * 100.0 / (
select
sum(daily_revenue)
from
daily_sales
),
1
) as revenue_share,
count(*) as transaction_count
from
daily_sales
group by
day_of_week
order by
avg_revenue desc;
中級問題6
問題文
daily_retail_dataテーブルの売上を以下の区分で分類し、分析してください：
- 10万円以上：高額
- 5万円以上10万円未満：中額
- 5万円未満：少額
各区分について以下の指標を計算してください：
- 件数
- 総売上
- 平均売上
結果は区分の降順で表示してください。
条件
テーブル：daily_retail_data
カラム：daily_revenue
回答
select
case
when daily_revenue >= 100000 then '高額'
when daily_revenue >= 50000 then '中額'
when daily_revenue <= 50000 then '少額'
-- else '少額'にしたらNULLなくなった なぜ
end as revenue_category,
count(daily_revenue) as transaction_count,
sum(daily_revenue) as total_revenue,
AVG(daily_revenue) as avg_revenue
from
daily_retail_data
group by
revenue_category
order by
avg_revenue desc
;
-- 実行結果
-- 高額 ~~~
-- 中額 ~~~
-- 少額 ~~~
-- NULL ~~
-- なぜかNULL列が出た 謎
-- NULLデータ2つあった
解答例
with
categorized_sales as (
select
case
when daily_revenue >= 100000 then '高額'
when daily_revenue >= 50000 then '中額'
else '少額'
end as revenue_category,
daily_revenue
from
daily_retail_data
)
select
revenue_category,
count(*) as transaction_count,
sum(daily_revenue) as total_revenue,
round(avg(daily_revenue)) as avg_revenue
from
categorized_sales
group by
revenue_category
order by -- case文で直接順序指定ができる
case revenue_category
when '高額' then 1
when '中額' then 2
else 3
end;
感想
データ中のNULLを許すな
中級問題7
問題文
条件
テーブル：daily_retail_data
カラム：daily_revenue
回答
select
strftime('%Y-%m', transaction_date) as year_month,
sum(daily_revenue) as monthly_revenue,
sum(daily_revenue) / 200000 as achievement_rate
from
daily_retail_data
group by
year_month
order by
year_month
;
解答例
with
monthly_sales as (
select
strftime ('%Y-%m', transaction_date) as year_month,
sum(daily_revenue) as monthly_revenue
from
daily_retail_data
group by
year_month
)
select
year_month,
monthly_revenue,
round(monthly_revenue * 100.0 / 2000000000, 1) as achievement_rate
from
monthly_sales
order by
year_month;
中級問題8
問題文
daily_retail_dataテーブルから、業種ごとの週次平均売上を分析してください。以下の指標を計算してください：
- 週次平均売上
- 週次最大売上
- 週次最小売上
結果は業種と週の順で表示してください。
条件
テーブル：daily_retail_data
カラム：transaction_date, daily_revenue
回答
select
business_category,
strftime ('%U', transaction_date) as week_number,
-- %Uで週数を00~53で取得可能
AVG(daily_revenue) as avg_weekly_revenue,
MAX(daily_revenue) as max_weekly_revenue,
MIN(daily_revenue) as min_weekly_revenue
from daily_retail_data
group by business_category, week_number
order by business_category, week_number;
解答例
select
business_category,
strftime ('%W', transaction_date) as week_number,
round(avg(daily_revenue)) as avg_weekly_revenue,
max(daily_revenue) as max_weekly_revenue,
min(daily_revenue) as min_weekly_revenue
from
daily_retail_data
group by
business_category,
week_number
order by
business_category,
cast(week_number as integer);
-- cast (column as 変数タイプ) で変換できるっぽい
感想
週数って取得できるようになってるんだ・・・
中級問題9
問題文
daily_retail_dataテーブルを使用して、業種ごとの7日間移動平均売上を計算してください。以下の指標を含めてください：
- 当日売上
- 7日間移動平均
- 移動平均からの乖離率（%）
結果は日付順に表示してください。
条件
テーブル：daily_retail_data
カラム：business_category, transaction_date, daily_revenue
回答
with daily_revenue_on_category as (
select
business_category,
transaction_date,
sum(daily_revenue) as sum_daily_revenue,
ROUND(AVG(sum_daily_revenue) over (partition by business_category
order by transaction_date rows between 3 PRECEDING and 3 FOLLOWING),2)
as moving_avg_7d
from
daily_retail_data
group by
business_category,
transaction_date
order by
business_category,
transaction_date
)
select
business_category,
transaction_date,
sum_daily_revenue as "daily_revenue",
moving_avg_7d,
ROUND(("daily_revenue" - moving_avg_7d) / moving_avg_7d * 100, 2) as "deviation_rate"
from
daily_revenue_on_category
order by
business_category, transaction_date
;
解答例
select
business_category,
transaction_date,
daily_revenue,
round(
avg(daily_revenue) over (
partition by
business_category
order by
transaction_date rows between 6 preceding
and current ROW
)
) as moving_avg_7d,
round(
(
daily_revenue - avg(daily_revenue) over (
partition by
business_category
order by
transaction_date rows between 6 preceding
and current ROW
)
) * 100.0 / avg(daily_revenue) over (
partition by
business_category
order by
transaction_date rows between 6 preceding
and current ROW
),
1
) as deviation_rate
from
daily_retail_data
order by
business_category,
transaction_date;
感想
普通にこれ解答例にある移動平均を求める部分はwith句でやった方がいいはず
可読性がない
中級問題10
問題文
daily_retail_dataテーブルを使用して、各業種の売上変動を分析してください。以下の指標を計算してください：
- 平均売上
- 売上の標準偏差
- 最大売上と最小売上の差（売上範囲）
- 変動係数（標準偏差/平均）
結果は変動係数の降順で表示してください。
条件
テーブル：daily_retail_data
カラム：business_category, daily_revenue
回答
select
business_category,
AVG(daily_revenue) as avg_revenue,
stddev(daily_revenue) as std_dev,
MAX(daily_revenue) - MIN(daily_revenue) as revenue_range,
ROUND(stddev(daily_revenue) / AVG(daily_revenue), 2) as cv
from
daily_retail_data
group by
business_category
order by
cv desc
;
解答例
select
business_category,
round(avg(daily_revenue)) as avg_revenue,
round(stddev(daily_revenue)) as std_dev,
max(daily_revenue) - min(daily_revenue) as revenue_range,
round(stddev(daily_revenue) / avg(daily_revenue), 2) as cv
from
daily_retail_data
group by
business_category
order by
cv desc;
GPTに追加で問題を作らせよう！
追加で作らせました。
問題作成プロンプト
sqlの演習を行っています。 あなたには、次のテーブルデータから、問題を作成してもらいたいです。 その際、whereやgroup byを使うだけで終わる単純な問題ではなく、テーブル結合やウィンドウ関数の使用が必要となる、複雑な問題を作成してください。使用するテーブルは以下の通りです。
テーブル名：daily_retail_data
カラム1：transaction_date 取引日（YYYY-MM-DD形式）
カラム2：industry_code 業界コード（例：RT001）
カラム3：business_category ビジネスカテゴリ名（例：Supermarket chains, Fashion outlets）
カラム４：daily_revenue 日次売上高
GPT問題1
【問題文】
あなたは小売業界のデータアナリストです。業界コード（industry_code）ごとの売上推移を分析するために、以下の条件を満たすレポートを作成してください。
【要件】
各業界（industry_code）について、月ごとの合計売上（monthly_revenue）を算出しなさい。
その月の売上が、同業界における過去3か月間の売上平均より高いかどうかを判定しなさい。
各行には以下の列を含めなさい：
- industry_code
- month（YYYY-MM形式）
- monthly_revenue（月間売上）
- avg_revenue_last_3_months（過去3か月の平均売上）
- is_above_average（monthly_revenue > avg_revenue_last_3_monthsならTRUE、そうでなければFALSE）
条件
テーブル：daily_retail_data
カラム：industry_code, daily_revenue, transaction_date
回答
with monthly_revenue as (select
industry_code,
strftime('%Y-%m', transaction_date) as "month",
sum(daily_revenue) as monthly_revenue
from
daily_retail_data
group by
industry_code, "month"
)
select
industry_code,
"month",
monthly_revenue,
ROUND(AVG(monthly_revenue) over (
PARTITION BY industry_code
order by "month"
ROWS between 3 preceding
and 1 preceding), 2)
as avg_revenue_last_3_months,
case
when monthly_revenue > avg_revenue_last_3_months then TRUE
else FALSE
end as "is_above_average"
from
monthly_revenue
order by
industry_code, "month"
;
解答例(GPT4o作成)
WITH monthly_revenue AS (
SELECT
industry_code,
strftime('%Y-%m', transaction_date) AS month,
SUM(daily_revenue) AS monthly_revenue
FROM
daily_retail_data
GROUP BY
industry_code, month
),
with_avg AS (
SELECT
industry_code,
month,
monthly_revenue,
ROUND(AVG(monthly_revenue) OVER (
PARTITION BY industry_code
ORDER BY month
ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING
), 2) AS avg_revenue_last_3_months
FROM
monthly_revenue
)
SELECT
industry_code,
month,
monthly_revenue,
avg_revenue_last_3_months,
CASE
WHEN monthly_revenue > avg_revenue_last_3_months THEN TRUE
ELSE FALSE
END AS is_above_average
FROM
with_avg
ORDER BY
industry_code, month;
GPT問題2
【問題文】
あなたは小売業界のデータ分析担当者です。業界ごとの売上傾向の変化を把握するため、以下の分析を行ってください。
【要件】
各業界（industry_code）ごとに、直近2か月間（最新月とその前の月）の売上高を比較し、「売上が急増した業界（増加率20%以上）」を抽出してください。
比較対象の2か月は、データの中で最新の月と、その1つ前の月とします。
出力する列は以下の通り：
- industry_code
- prev_month（前の月：YYYY-MM）
- prev_month_revenue
- latest_month（最新の月：YYYY-MM）
- latest_month_revenue
- growth_rate（前月比増加率、％）
- is_significant_growth（増加率が20%以上ならTRUE）
条件
テーブル：daily_retail_data
カラム：industry_code, transaction_date, daily_revenue
回答
with monthly_revenue as (
select
industry_code,
strftime('%Y-%m', transaction_date) as "month",
sum(daily_revenue) as month_revenue
from
daily_retail_data
group by
industry_code, "month"
order by
industry_code, "month"
)
select
industry_code,
LAG("month",1) over (
PARTITION BY industry_code
ORDER BY "month"
) as "prev_month",
LAG("month_revenue",1) over (
PARTITION BY industry_code
ORDER BY "month"
) as "prev_month_revenue",
"month" as latest_month,
month_revenue as latest_month_revenue,
(month_revenue - LAG(month_revenue,1) over (
PARTITION BY industry_code
ORDER BY "month"
)) / LAG(month_revenue,1) over (
PARTITION BY industry_code
ORDER BY "month"
) * 100 as growth_rate,
case
when growth_rate >= 20 then TRUE
else FALSE
end as "is_significant_growth"
from
monthly_revenue
order by
industry_code, "month"
;
解答例
WITH monthly_revenue AS (
SELECT
industry_code,
strftime('%Y-%m', transaction_date) AS month,
SUM(daily_revenue) AS month_revenue
FROM
daily_retail_data
GROUP BY
industry_code, month
),
with_growth AS (
SELECT
industry_code,
month AS latest_month,
month_revenue AS latest_month_revenue,
LAG(month) OVER (PARTITION BY industry_code ORDER BY month) AS prev_month,
LAG(month_revenue) OVER (PARTITION BY industry_code ORDER BY month) AS prev_month_revenue,
ROUND(
(month_revenue - LAG(month_revenue) OVER (PARTITION BY industry_code ORDER BY month)) * 100.0
/ LAG(month_revenue) OVER (PARTITION BY industry_code ORDER BY month), 2
) AS growth_rate
FROM
monthly_revenue
),
latest_2_months AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY industry_code ORDER BY latest_month DESC) AS rn
FROM
with_growth
)
SELECT
industry_code,
prev_month,
prev_month_revenue,
latest_month,
latest_month_revenue,
growth_rate,
CASE
WHEN growth_rate >= 20 THEN TRUE
ELSE FALSE
END AS is_significant_growth
FROM
latest_2_months
WHERE
rn = 1 -- 最新月のみ出力
ORDER BY
industry_code;
まとめ
ひとまず、実装されている問題+αを一通り片付けきりました。
ウィンドウ関数とテーブルの結合の部分をあまりできなかったので、そこは別途演習をしていきたい所存です。
次は、追加問題snowflakeを使った演習の方をやっていきたいと思います。
参考