SQLコードをひたすらに書きまくる部~データ加工100本ノックで遊ぼう~

SQLコードをひたすらに書きまくる部

SQLコード書きまくる部です。経験を積みたい。
今回は、データ加工100本ノックというのがあったので、こちらをやっていきます。

大本はこちらになります。

vscodeで実行したい場合は、
docker compose up -d --build --wait
によってコンテナを起動し、docker\work\preprocess_knock_SQL.ipynbを開き、カーネルを起動してるjupyter serverにする(http://localhost:8888 を入力して色々する)
これによってローカルで実行できるようになる。

ちなみに、環境整備をさぼりたい場合は、以下のリンク先にある、colabでできるというこちらを採用するといいかと思います。

基本事項をまとめるの会

前回参照

演習

今回100問ありますが、全部拾ってたらきりがない(というかQiitaのエディターが重くなりすぎる)ので、つまった問題に絞って載せていきます。

正規表現関連

参考資料
https://kino-code.com/sql19/

正規表現-いずれかに一致-

正規表現	説明	書き方	マッチ例
[...]	カッコ内のいずれか一文字(IN)	[abc]	a,b,c
[...]	カッコ内の文字以外(NOT IN)	[^abc]	a,b,c以外
｜	｜の左右いずれか(or)	a｜bc	a or bc
()	()中の文字をグループ化	a(b｜bc)	ab or abc

正規表現-文字列の先頭・末尾-

正規表現	説明	書き方	マッチ例
^	直後の文字が文字列の先頭	^ab	ab, abc
$	直前の文字が文字列の末尾	bc$	bc, abc

正規表現-文字列の繰り返し-

正規表現	説明	書き方	マッチ例
.	任意の1文字	.	任意の1文字
*	直前の文字を0回以上繰り返す	.*	0文字以上の任意の文字列
+	直前の文字を1回以上繰り返す	.+	1文字以上の任意の文字列
?	直前の文字が0回か1回	a?	bc, abc

正規表現-文字列の繰り返し(n回)-

正規表現	説明	書き方	マッチ例
{n}	直前の文字をn回繰り返す	a{3}	aaa
{n,}	直前の文字をn回以上繰り返す	a{3,}	aaabc, aaaabc
{,m}	直前の文字をm回以下繰り返す	a{3,}	aabc, aaabc
{n,m}	直前の文字をn回以上m回以下繰り返す	a{3,4}	aaabc, aaaabc

S-013: 顧客データ（customer）から、ステータスコード（status_cd）の先頭がアルファベットのA〜Fで始まるデータを全項目抽出し、10件表示せよ。

select
    *
from
    customer
where
    status_cd ~ '^[A-F]'
-- ~で正規表現(Postgreの方言？)、^はその直後の文字から開始、[A-F]で、A-Fの範囲の文字
LIMIT 10
;

解答例

SELECT * FROM customer WHERE status_cd ~ '^[A-F]' LIMIT 10;

S-014: 顧客データ（customer）から、ステータスコード（status_cd）の末尾が数字の1〜9で終わるデータを全項目抽出し、10件表示せよ。

%%sql
SELECT
    *
FROM
    customer
WHERE
    status_cd ~ '[1-9]$'
    -- 末尾文字の指定なら$の直前
LIMIT 10
;

解答例

SELECT * FROM customer WHERE status_cd ~ '[1-9]$' LIMIT 10;

S-015: 顧客データ（customer）から、ステータスコード（status_cd）の先頭がアルファベットのA〜Fで始まり、末尾が数字の1〜9で終わるデータを全項目抽出し、10件表示せよ。

select
    *
from
    customer
where 
    status_cd ~ '^[A-F].*[1-9]$'
    -- .は任意文字、*は0回以上の繰り返しなので、.*で任意文字任意回数繰り返し
LIMIT 
    10;

解答例

SELECT * FROM customer WHERE status_cd ~ '^[A-F].*[1-9]$' LIMIT 10;

S-016: 店舗データ（store）から、電話番号（tel_no）が3桁-3桁-4桁のデータを全項目表示せよ。

select
    *
from
    store
where
    tel_no ~ '[0-9]{3}-[0-9]{3}-[0-9]{4}'
    -- 文字列の繰り返し回数は{n,m}で制御可能
LIMIT 10
;

解答例

SELECT * FROM store WHERE tel_no ~ '^[0-9]{3}-[0-9]{3}-[0-9]{4}$';
-- 先頭と末尾を明示して指定

中央値・最頻値

S-028: レシート明細データ（receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）の中央値を計算し、降順でTOP5を表示せよ。

解答例

select
    store_cd,
    PERCENTILE_CONT(0.5) within group (order by amount) as amount_50per
    -- percentile_cont(0.0~1.0)で、順位付けした中での値を持ってこれる
from
    receipt
group by store_cd
order by amount_50per desc
limit 5
;

S-029: レシート明細データ（receipt）に対し、店舗コード（store_cd）ごとに商品コード（product_cd）の最頻値を求め、10件表示させよ。

with product_count_table as(
    select
        store_cd,
        product_cd,
        count(product_cd) as product_count
    from
        receipt
    group by
        store_cd, product_cd
    order by
        product_count desc
),
product_rank_table as (
    select
        store_cd,
        product_cd,
        product_count,
        DENSE_rank() over (PARTITION BY store_cd order by product_count desc) as product_rank
    from
        product_count_table
)
select
    *
from
    product_rank_table
where
    product_rank = 1
limit
    20
;

解答例2つ

-- コード例1: window関数や分析関数で最頻値を集計する
WITH product_cnt AS (
    SELECT
        store_cd,
        product_cd,
        COUNT(1) AS mode_cnt
    FROM receipt
    GROUP BY
        store_cd,
        product_cd
),
product_mode AS (
    SELECT
        store_cd,
        product_cd,
        mode_cnt,
        RANK() OVER(PARTITION BY store_cd ORDER BY mode_cnt DESC) AS rnk
    FROM product_cnt
)
SELECT
    store_cd,
    product_cd,
    mode_cnt
FROM product_mode
WHERE
    rnk = 1
ORDER BY
    store_cd,
    product_cd
LIMIT 10
;

-- コード例2: MODE()を使う簡易ケース（早いが最頻値が複数の場合は一つだけ選ばれる）
SELECT
    store_cd,
    MODE() WITHIN GROUP(ORDER BY product_cd)
FROM receipt
GROUP BY store_cd
ORDER BY store_cd
LIMIT 10
;

感想
中央値はともかく、最頻値が独自関数実装されてないの、普通にめんどくさすぎる
with句二つ重ねマジ？

忘れてた集約関数

VAR_pop -- 分散の導出
stddev_pop --  標準偏差の導出
LOG10(sum(amount)) as total_10 -- 底10の対数
LOG(sum(amount)) as total_log -- 底10の対数
LN(sum(amount)) as total_ln -- 底eの対数
AVG(column) -- nullは無視される
trunc(column) -- 切り捨て(roundは丸め)
ceil(column) -- 切り上げ(column以上の最も小さい整数を変える)

https://mamori017.hatenablog.com/entry/2017/04/25/022546

https://www.ibm.com/docs/ja/informix-servers/12.10.0?topic=functions-trunc-function

https://docs.oracle.com/cd/F39414_01/sqlrf/CEIL.html

テーブルの結合

https://products.sint.co.jp/siob/blog/sql-bootcamp-07

基本的には、

from tableA as A 
-- 内部結合(共通部分のみ)
inner join table B as B
-- 外部結合(左側/右側はすべて or どちらか一方にあれば拾う(full))
left/right/full outer join table B as B
on A.key1 = B.key1

で結合できる。unionの場合は

select * from tableA
union / union all --(重複排除を実行するかしないか)
select * from table B;

になるらしい。

最後に、cross joinで、データの直積(それぞれのテーブルごとの要素の組み合わせの数)を求められる

https://sql55.com/t-sql/t-sql-join-6.php

https://www.uncovertruth.co.jp/dx-accelerator/blog/articles/sql/053/

S-036: レシート明細データ（receipt）と店舗データ（store）を内部結合し、レシート明細データの全項目と店舗データの店舗名（store_name）を10件表示せよ。

select
    r.store_cd,
    s.store_name,
    r.sales_ymd,
    r.sales_epoch,
    r.receipt_no,
    r.receipt_sub_no,
    r.customer_id,
    r.product_cd,
    r.quantity,
    r.amount
from
    receipt r
    inner join store s
    on r.store_cd = s.store_cd
limit
    10
;

解答例

SELECT
    r.*, -- 全指定の場合はこれでいいらしい。涙
    s.store_name
FROM receipt r
JOIN store s
ON
    r.store_cd = s.store_cd
LIMIT 10
;

S-037: 商品データ（product）とカテゴリデータ（category）を内部結合し、商品データの全項目とカテゴリデータのカテゴリ小区分名（category_small_name）を10件表示せよ。

select
    p.*,
    c.category_small_name
from
    product p
    inner join category c
    on p.category_small_cd = c.category_small_cd
limit
    10
;

解答例

SELECT
    p.*,
    c.category_small_name
FROM product p
JOIN category c
ON
    p.category_small_cd = c.category_small_cd
LIMIT 10
;

S-038: 顧客データ（customer）とレシート明細データ（receipt）から、顧客ごとの売上金額合計を求め、10件表示せよ。ただし、売上実績がない顧客については売上金額を0として表示させること。また、顧客は性別コード（gender_cd）が女性（1）であるものを対象とし、非会員（顧客IDが"Z"から始まるもの）は除外すること。

select
    c.customer_id,
    sum(r.quantity * r.amount) as total_amount
    -- これだと、売り上げ実績がない顧客はNULLになる
    -- COALESCE(column, 0)で、NULLを0に置き換えられる
from
    customer c
    left outer join receipt r
    on c.customer_id = r.customer_id
where
    c.gender_cd = '1'
    and c.customer_id ~ '^[^Z].*'
group by
    c.customer_id
limit
    10
;

解答例

-- まず初めに顧客ごとに注文合計を算出
WITH customer_amount AS (
    SELECT
        customer_id,
        SUM(amount) AS sum_amount
    FROM receipt
    GROUP BY
        customer_id
),
-- 次に、有効なcustomer_idのみを抽出
customer_data AS (
    SELECT
        customer_id
    FROM customer
    WHERE
        gender_cd = '1'
        AND customer_id NOT LIKE 'Z%'
)
-- 最後に、外部結合して、有効customer_idをすべて乗せて出力
SELECT
    c.customer_id,
    COALESCE(a.sum_amount, 0)
FROM customer_data c
LEFT OUTER JOIN customer_amount a
ON
    c.customer_id = a.customer_id
LIMIT 10
;

S-039: レシート明細データ（receipt）から、売上日数の多い顧客の上位20件を抽出したデータと、売上金額合計の多い顧客の上位20件を抽出したデータをそれぞれ作成し、さらにその2つを完全外部結合せよ。ただし、非会員（顧客IDが"Z"から始まるもの）は除外すること。

with customer_with_top20_days as (
    select
        count(sales_ymd) as days,
        -- 同一日の複数取引の情報を重複している
        -- count(distinct とすべき
        customer_id
    from receipt
    where customer_id ~ '^[^Z].*'
    group by customer_id
    order by days desc
    limit 20
),
customer_with_top20_amount as (
    select
        sum(quantity * amount) as sales,
        customer_id
    from
        receipt
    where customer_id ~ '^[^Z].*'
    group by
        customer_id
    order by sales desc
    limit 20
)
select
    *
    --　このまま結合すると、idとデータが2テーブル分で4列データになってしまう。
    -- ので解答例のように、
    -- COALESCE(d.customer_id, a.customer_id) customer_id
    --　とすべきだった
from 
    customer_with_top20_days D
    full outer join customer_with_top20_amount A
    on D.customer_id = A.customer_id
;

解答例

WITH customer_data AS (
    select
        customer_id,
        sales_ymd,
        amount
    FROM receipt
    WHERE
        customer_id NOT LIKE 'Z%'
),
customer_days AS (
    select
        customer_id,
        COUNT(DISTINCT sales_ymd) come_days
    FROM customer_data
    GROUP BY
        customer_id
    ORDER BY
        come_days DESC
    LIMIT 20
),
customer_amount AS (
    SELECT
        customer_id,
        SUM(amount) buy_amount
    FROM customer_data
    GROUP BY
        customer_id
    ORDER BY
        buy_amount DESC
    LIMIT 20
)
SELECT
    COALESCE(d.customer_id, a.customer_id) customer_id,
    d.come_days,
    a.buy_amount
FROM customer_days d
FULL OUTER JOIN customer_amount a
ON
    d.customer_id = a.customer_id
;

S-040: 全ての店舗と全ての商品を組み合わせたデータを作成したい。店舗データ（store）と商品データ（product）を直積し、件数を計算せよ。

解答例

SELECT
    COUNT(1)
FROM store
CROSS JOIN product
;

クロス集計　縦持ちと横持ち

S-043: レシート明細データ（receipt）と顧客データ（customer）を結合し、性別コード（gender_cd）と年代（ageから計算）ごとに売上金額（amount）を合計した売上サマリデータを作成せよ。性別コードは0が男性、1が女性、9が不明を表すものとする。
ただし、項目構成は年代、女性の売上金額、男性の売上金額、性別不明の売上金額の4項目とすること（縦に年代、横に性別のクロス集計）。また、年代は10歳ごとの階級とすること。

-- customer_idベースで性別と年齢取得
with process_customer as (
    select
        customer_id,
        case
            when gender_cd = '0' then 'M'
            when gender_cd = '1' then 'F'
            else 'U'
        end as customer_gender,
        age
    from
        customer
),
-- customer_idベースで合計金額取得
process_receipt as (
    select
        customer_id,
        sum(quantity*amount) as total_amount
    from
        receipt
    group by
        customer_id
),
-- customer_idベースで前までのを結合+年代への変換
all_data as (
    select
        case
            when c.age / 10 = 1 then '10s'
            when c.age / 10 = 2 then '20s'
            when c.age / 10 = 3 then '30s'
            when c.age / 10 = 4 then '40s'
            when c.age / 10 = 5 then '50s'
            when c.age / 10 = 6 then '60s'
            when c.age / 10 = 7 then '70s'
            when c.age / 10 = 8 then '80s'
            when c.age / 10 = 9 then '90s'
        end as "年代",
        c.customer_gender,
        r.total_amount
    from
        process_customer c
        inner join process_receipt r
        on c.customer_id = r.customer_id
),
-- 性別と年代ごとに合計
gender_with_amount as (
    select
        "年代" , customer_gender, sum(total_amount) as total
    from
        all_data
    group by
        "年代" ,customer_gender
    order by
        "年代" 
), 
-- 性別ごとに情報抽出
male_table as (
select
    "年代", total
from
    gender_with_amount
where
    customer_gender = 'M'
),
female_table as (
select
    "年代", total
from
    gender_with_amount
where
    customer_gender = 'F'
),
unknown_table as (
select
    "年代", total
from
    gender_with_amount
where
    customer_gender = 'U'
)
-- femaleにのみ90sがあるので、これを起点としてleft outer join
select
    f."年代",
    f.total as female_total,
    m.total as male_total,
    u.total as unknown_total
from
    female_table f
    left outer join male_table m
    on f."年代" = m."年代"
    left outer join unknown_table u
    on f."年代" = u."年代"
;

解答例

-- まさかの新規テーブル作成
DROP TABLE IF EXISTS sales_summary;

CREATE TABLE sales_summary AS
    WITH gender_era_amount AS (
        SELECT
            TRUNC(age / 10) * 10 AS era,
            -- truncで小数点以下切り捨て
            c.gender_cd,
            SUM(r.amount) AS amount
        FROM customer c
        JOIN receipt r
        ON
            c.customer_id = r.customer_id
        GROUP BY
            era,
            -- group byでselectで命名したものが通るのか・・・
            c.gender_cd
    )
    SELECT
        era,
        -- case文どこでも使える
        SUM(CASE WHEN gender_cd = '0' THEN amount END) AS male,
        SUM(CASE WHEN gender_cd = '1' THEN amount END) AS female,
        SUM(CASE WHEN gender_cd = '9' THEN amount END) AS unknown
    FROM gender_era_amount
    GROUP BY
        era
    ORDER BY
        era
;

SELECT
    *
FROM sales_summary
;

S-044: 043で作成した売上サマリデータ（sales_summary）は性別の売上を横持ちさせたものであった。このデータから性別を縦持ちさせ、年代、性別コード、売上金額の3項目に変換せよ。ただし、性別コードは男性を"00"、女性を"01"、不明を"99"とする。

with male_table as (
    select
        era, male, '00' as gender_cd
    from
        sales_summary
),
female_table as (
    select
        era, female, '01' as gender_cd
    from
        sales_summary
),
unknown_table as (
    select
        era, male, '99' as gender_cd
    from
        sales_summary
)
select * from male_table
union all
select * from female_table
union all
select * from unknown_table;

解答例

-- SQL向きではないため、やや強引に記載する（カテゴリ数が多いときはとても長いSQLとなってしまう点に注意）
-- union allはselectからなるクエリをそのまんま結合できる
SELECT era, '00' AS gender_cd , male AS amount FROM sales_summary
UNION ALL
SELECT era, '01' AS gender_cd, female AS amount FROM sales_summary
UNION ALL
SELECT era, '99' AS gender_cd, unknown AS amount FROM sales_summary
;

日付関連

-- 日付から文字列
TO_CHAR(column, 'YYYYMMDD')

-- 文字列から日付
TO_DATE(column, 'YYYYMMDD')

-- 数値から日付
TO_DATE(CAST(column AS VARCHAR), 'YYYYMMDD')

-- 数値型のUNIX秒から日付
cast(TO_timestamp(sales_epoch) as DATE) as sales_ymd

-- 数値型のUNIX秒から年
extract(year from TO_timestamp(sales_epoch))

-- 数値型のUNIX秒から0うめ月
TO_char(
        extract(month from TO_timestamp(sales_epoch)),
        'FM00'
        )

-- 数値型のUNIX秒から0うめ日付
TO_CHAR(
        extract(day from TO_timestamp(sales_epoch)),
        'FM00'
    )

四分位数関連

S-055: レシート明細（receipt）データの売上金額（amount）を顧客ID（customer_id）ごとに合計し、その合計金額の四分位点を求めよ。その上で、顧客ごとの売上金額合計に対して以下の基準でカテゴリ値を作成し、顧客ID、売上金額合計とともに10件表示せよ。カテゴリ値は順に1〜4とする。

• 最小値以上第1四分位未満 ・・・ 1を付与
• 第1四分位以上第2四分位未満 ・・・ 2を付与
• 第2四分位以上第3四分位未満 ・・・ 3を付与
• 第3四分位以上 ・・・ 4を付与

with customer_total as (
    select
        customer_id,
        sum(amount) as total_amount
    from
        receipt
    group by
        customer_id
),
percentiles as (
    select
        PERCENTILE_CONT(0.25) within group (order by total_amount) as amount_25,
        PERCENTILE_CONT(0.50) within group (order by total_amount) as amount_50,
        PERCENTILE_CONT(0.75) within group (order by total_amount) as amount_75
    from
        customer_total
)
select
    customer_id,
    total_amount,
    case
        when total_amount < (select amount_25 from percentiles) then '1'
        when total_amount < (select amount_50 from percentiles) then '2'
        when total_amount < (select amount_75 from percentiles) then '3'
        else '4'
    end as category
from
    customer_total
limit 10;

解答例

WITH sales_amount AS(
    SELECT
        customer_id,
        SUM(amount) AS sum_amount
    FROM
        receipt
    GROUP BY
        customer_id
),
sales_pct AS (
    SELECT
        PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY sum_amount) AS pct25,
        PERCENTILE_CONT(0.50) WITHIN GROUP(ORDER BY sum_amount) AS pct50,
        PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY sum_amount) AS pct75
    FROM
        sales_amount
)
SELECT
    a.customer_id,
    a.sum_amount,
    CASE
        WHEN a.sum_amount < pct25 THEN 1
        WHEN pct25 <= a.sum_amount AND a.sum_amount < pct50 THEN 2
        WHEN pct50 <= a.sum_amount AND a.sum_amount < pct75 THEN 3
        WHEN pct75 <= a.sum_amount THEN 4
    END AS pct_group
FROM sales_amount a
CROSS JOIN sales_pct p
-- 基準値だけのテーブルを作ってそっから情報を引っ張ってくる場合、cross joinで持ってこれる
-- ほかの結合様式だと色々とめんどくさい
LIMIT 10
;

標準化と正規化

平均0、標準偏差1の場合(標準化)
S-059: レシート明細データ（receipt）の売上金額（amount）を顧客ID（customer_id）ごとに合計し、売上金額合計を平均0、標準偏差1に標準化して顧客ID、売上金額合計とともに10件表示せよ。標準化に使用する標準偏差は、分散の平方根、もしくは不偏分散の平方根のどちらでも良いものとする。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。

with customer_total as (
    select
        customer_id,
        sum(amount) as total
    from
        receipt
    where
        customer_id ~ '^[^Z].*'
    group by
        customer_id
),
avg_and_stddev as (
    select
        avg(total) as ave,
        STDDEV_POP(total) as stddev
    from
        customer_total
)
select
    customer_id,
    (total - ave) / stddev as normalized_amount
from
    customer_total
cross join avg_and_stddev
limit
    10;

解答例

WITH sales_amount AS(
    SELECT
        customer_id,
        SUM(amount) AS sum_amount
    FROM
        receipt
    WHERE
        customer_id NOT LIKE 'Z%'
    GROUP BY
        customer_id
),
stats_amount AS (
    SELECT
        AVG(sum_amount) AS avg_amount,
        STDDEV_POP(sum_amount) AS stddev_amount
        -- 母集団標準偏差の推定値(ルートn-1割)
        STDDEV_SAMP(sum_amount) AS stddev_amount
        -- 標本標準偏差(ルートn割)
    FROM
        sales_amount
)
SELECT
    customer_id,
    sum_amount,
    (sum_amount - avg_amount) / stddev_amount AS std_amount
FROM sales_amount
CROSS JOIN stats_amount
LIMIT 10
;

01正規化の場合
S-060: レシート明細データ（receipt）の売上金額（amount）を顧客ID（customer_id）ごとに合計し、売上金額合計を最小値0、最大値1に正規化して顧客ID、売上金額合計とともに10件表示せよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。

with customer_total as (
    select
        customer_id,
        sum(amount) as total
    from
        receipt
    where
        customer_id ~ '^[^Z].*'
    group by
        customer_id
),
max_and_min as (
    select
        max(total) as max_amount,
        min(total) as min_amount
    from
        customer_total
)
select
    customer_id,
    total,
    cast((total - min_amount) as float) / (max_amount - min_amount) as normalized_amount
    -- 整数同士で割り算をさせると、自動で小数点以下切り捨てになる
    -- ので、無理やり小数に変換して計算している
from
    customer_total
cross join max_and_min
limit
    10;

解答例

WITH sales_amount AS(
    SELECT
        customer_id,
        SUM(amount) AS sum_amount
    FROM
        receipt
    WHERE
        customer_id NOT LIKE 'Z%'
    GROUP BY
        customer_id
),
stats_amount AS (
    SELECT
        MAX(sum_amount) AS max_amount,
        MIN(sum_amount) AS min_amount
    FROM
        sales_amount
)
SELECT
    customer_id,
    sum_amount,
    1.0 * (sum_amount - min_amount)
            / (max_amount -  min_amount) AS scale_amount
    -- 無理やり小数に変換している
FROM sales_amount
CROSS JOIN stats_amount
LIMIT 10
;

感想
整数同士の割り算のデフォルトが小数点以下切り捨てなのは普通に納得いかない　なぜ

データにおける重複排除

S-087: 顧客データ（customer）では、異なる店舗での申込みなどにより同一顧客が複数登録されている。名前（customer_name）と郵便番号（postal_cd）が同じ顧客は同一顧客とみなして1顧客1レコードとなるように名寄せした名寄顧客データを作成し、顧客データの件数、名寄顧客データの件数、重複数を算出せよ。ただし、同一顧客に対しては売上金額合計が最も高いものを残し、売上金額合計が同一もしくは売上実績がない顧客については顧客ID（customer_id）の番号が小さいものを残すこととする。

with simplify_receipt as (
    select
        customer_id,
        coalesce(sum(quantity*amount),0) as total_amount
        -- 売上実績が存在しない場合の補完
        -- ただ、receipt上にあるIDはすべて取引実績が存在するので
        -- テーブル結合後にすべきだった
    from
        receipt
    group by
        customer_id
),
customer_with_receipt as (
    select
        c.customer_id,
        c.customer_name,
        c.postal_cd,
        r.total_amount
    from
        customer c
        left outer join simplify_receipt r
        on c.customer_id = r.customer_id
)
select
    min(customer_id), -- idの小さいほうの取得
    customer_name,
    postal_cd,
    max(total_amount) -- 金額の大きい方の取得
from
    customer_with_receipt
group by customer_name, postal_cd
order by max 
limit 10;

解答例

DROP TABLE IF EXISTS customer_u;
CREATE TABLE customer_u AS (
    WITH sales_amount AS(
        SELECT
            c.customer_id,
            c.customer_name,
            c.postal_cd, 
            COALESCE(SUM(r.amount), 0) AS sum_amount
            -- 結合時に合計金額を計算
        FROM
            customer c

        LEFT JOIN
            receipt r        
        ON c.customer_id = r.customer_id
        GROUP by
            c.customer_id, c.customer_name, c.postal_cd
    ),
    sales_ranking AS(
        SELECT
            *,
            ROW_NUMBER() OVER(
                PARTITION BY customer_name, postal_cd 
                ORDER BY sum_amount desc, customer_id ) AS ranking
        FROM sales_amount
        -- 金額とIDについての順位付けを実施
    )
    SELECT c.*
    FROM
        customer c
    JOIN
        sales_ranking r
    ON
        c.customer_id = r.customer_id
        AND r.ranking = 1 -- 順位付けの1位のみ引っ張る
);

S-088: 087で作成したデータを元に、顧客データに統合名寄IDを付与したデータを作成せよ。ただし、統合名寄IDは以下の仕様で付与するものとする。

• 重複していない顧客：顧客ID（customer_id）を設定
• 重複している顧客：前設問で抽出したレコードの顧客IDを設定

顧客IDのユニーク件数と、統合名寄IDのユニーク件数の差も確認すること。

解答例

DROP TABLE IF EXISTS customer_n;
CREATE TABLE customer_n AS (
    SELECT 
        c.*, 
        u.customer_id AS integration_id
    FROM 
        customer c
    JOIN
        customer_u u
    ON c.customer_name = u.customer_name
        AND c.postal_cd = u.postal_cd
    -- 名前と郵便番号の一致をキーにして、重複排除した方をidとして持ってくることで実行している
);

データ操作(分割・サンプリング・正規化)

S-089: 売上実績がある顧客を、予測モデル構築のため学習用データとテスト用データに分割したい。それぞれ8:2の割合でランダムにデータを分割せよ。

drop table if exists data_for_training;
create table data_for_training as (
    select
        c.*,
        row_number() over (order by random()) as rn
        -- 分割用に、ランダムに数字を振る
    from customer c 
    join receipt r 
    on c.customer_id = r.customer_id
);

drop table if exists customer_train;
create table customer_train as (
    select
        *
    from
        data_for_training
    where rn <= (select count(*)*0.7 from data_for_training)
);
drop table if exists customer_test;
create table customer_test as (
    select
        *
    from
        data_for_training
    where rn > (select count(*)*0.7 from data_for_training)
);

解答例

CREATE TEMP TABLE IF NOT EXISTS sales_customer AS (
    SELECT
        customer_id ,
        ROW_NUMBER() OVER(ORDER BY RANDOM()) AS row
    FROM
        customer
    JOIN
        receipt
    USING(customer_id)
    GROUP BY customer_id
    HAVING SUM(AMOUNT) > 0
);

DROP TABLE IF EXISTS customer_train;
CREATE TABLE customer_train AS
    SELECT
        customer.*
    FROM
        sales_customer
    JOIN
        customer 
    USING(customer_id)
    WHERE
        sales_customer.row <= (SELECT
                                  COUNT(1) 
                              FROM sales_customer) * 0.8
;

DROP TABLE IF EXISTS customer_test;
CREATE TABLE customer_test AS
    SELECT
        customer.* 
    FROM
        sales_customer 
    JOIN
        customer
    USING(customer_id)
    EXCEPT
        SELECT * FROM customer_train
;

S-090: レシート明細データ（receipt）は2017年1月1日〜2019年10月31日までのデータを有している。売上金額（amount）を月次で集計し、学習用に12ヶ月、テスト用に6ヶ月の時系列モデル構築用データを3セット作成せよ。

drop table if exists training_table;
create table training_table as (
with amount_by_month as (select 
    cast(extract(year from TO_timestamp(cast(sales_ymd as varchar), 'yyyymmdd')) as varchar)
     || '-' ||
    to_char(extract(month from TO_timestamp(cast(sales_ymd as varchar), 'yyyymmdd')), 'FM00')
    as "year-month",
    quantity * amount as total
from receipt
)
select
    "year-month",
    sum(total)
from amount_by_month
group by "year-month"
order by 1)
;

select * from training_table
where "year-month" <= '2018-06';

解答例

-- SQL向きではないため、やや強引に記載する（分割数が多くなる場合はSQLが長くなるため現実的ではない）
-- また、秒単位のデータなど時系列が細かく、かつ長期間に渡る場合はデータが膨大となるため注意（そのようなケースではループ処理でモデル学習ができる言語が望ましい）
-- 学習データ(0)とテストデータ(1)を区別するフラグを付与する

-- 下準備として年月ごとに売上金額を集計し、連番を付与
CREATE TEMP TABLE IF NOT EXISTS ts_amount AS (
    SELECT 
        SUBSTR(CAST(sales_ymd AS VARCHAR), 1, 6) AS sales_ym, 
        SUM(amount) AS sum_amount,
        ROW_NUMBER() OVER(
            ORDER BY SUBSTR(CAST(sales_ymd AS VARCHAR), 1, 6)) AS rn
    FROM
        receipt
    GROUP BY sales_ym
);

-- SQLでは限界があるが、作成データセットの増加に伴いなるべく使いまわしができるものにする
-- WITH句内のLAG関数について、ラグ期間を変えれば使い回せるよう記述
DROP TABLE IF EXISTS series_data_1 ;
CREATE TABLE series_data_1 AS (
    WITH lag_amount AS (
        SELECT
            sales_ym,
            sum_amount,
            LAG(rn, 0) OVER (ORDER BY rn) AS rn
        FROM ts_amount
    )
    SELECT 
        sales_ym,
        sum_amount, 
        CASE WHEN rn <= 12 THEN 0 WHEN 12 < rn THEN 1 END as test_flg
    FROM lag_amount 
    WHERE rn BETWEEN 1 AND 18);


DROP TABLE IF EXISTS series_data_2 ;
CREATE TABLE series_data_2 AS (
    WITH lag_amount AS (
        SELECT 
            sales_ym, 
            sum_amount, 
            LAG(rn, 6) OVER (ORDER BY rn) AS rn
        FROM ts_amount
    )
    SELECT
        sales_ym, 
        sum_amount, 
        CASE WHEN rn <= 12 THEN 0 WHEN 12 < rn THEN 1 END as test_flg
    FROM lag_amount
    WHERE rn BETWEEN 1 AND 18);

DROP TABLE IF EXISTS series_data_3 ;
CREATE TABLE series_data_3 AS (
    WITH lag_amount AS (
        SELECT
            sales_ym,
            sum_amount,
            LAG(rn, 12) OVER (ORDER BY rn) AS rn
        FROM ts_amount
    )
    SELECT 
        sales_ym, 
        sum_amount, 
        CASE WHEN rn <= 12 THEN 0 WHEN 12 < rn THEN 1 END as test_flg
    FROM lag_amount
    WHERE rn BETWEEN 1 AND 18);

S-091: 顧客データ（customer）の各顧客に対し、売上実績がある顧客数と売上実績がない顧客数が1:1となるようにアンダーサンプリングで抽出せよ。

解答例

SELECT SETSEED(0.1); 

CREATE TEMP TABLE IF NOT EXISTS down_sampling AS (
    WITH pre_table_1 AS(
        SELECT
            c.*
            ,COALESCE(r.sum_amount,0) AS sum_amount
        FROM
            customer c
        LEFT JOIN (
            SELECT
                customer_id,
                SUM(amount) AS sum_amount
            FROM
                receipt
            GROUP BY
                customer_id
        ) r 
        ON
            c.customer_id=r.customer_id
    )
    ,pre_table_2 AS(
        SELECT
            *
            ,CASE WHEN sum_amount > 0 THEN 1 ELSE 0 END  AS is_buy_flag
            ,CASE WHEN sum_amount = 0 THEN 1 ELSE 0 END  AS is_not_buy_flag
        FROM
            pre_table_1
    )
    ,pre_table_3 AS(
        SELECT
            *
            ,ROW_NUMBER() OVER(PARTITION BY is_buy_flag ORDER BY RANDOM())
        FROM
            pre_table_2
        CROSS JOIN
            (SELECT SUM(is_buy_flag) AS buying FROM pre_table_2) AS t1
        CROSS JOIN
            (SELECT SUM(is_not_buy_flag) AS not_buying FROM pre_table_2) AS t2
    )
    SELECT
        *
    FROM
        pre_table_3
    WHERE
        row_number <= buying
        AND row_number <= not_buying
);

S-092: 顧客データ（customer）の性別について、第三正規形へと正規化せよ。

第三正規化：推移関数従属の排除
今回なら、gender_cdが決まれば、genderがかぶるので、それを別テーブルで表現する

解答例

DROP TABLE IF EXISTS customer_std;
CREATE TABLE customer_std AS (
    SELECT
        customer_id,
        customer_name,
        gender_cd,
        birth_day,
        age,
        postal_cd,
        application_store_cd,
        application_date,
        status_cd
    FROM
        customer
);

DROP TABLE IF EXISTS gender_std;
CREATE TABLE gender_std AS (
    SELECT distinct
        gender_cd, gender
    FROM
        customer
);

S-093: 商品データ（product）では各カテゴリのコード値だけを保有し、カテゴリ名は保有していない。カテゴリデータ（category）と組み合わせて非正規化し、カテゴリ名を保有した新たな商品データを作成せよ。

今回は、category_small_cdのみ共通しているので、これをキーとして結合

解答例

DROP TABLE IF EXISTS product_full;
CREATE TABLE product_full AS (
    SELECT
        p.product_cd,
        p.category_major_cd,
        c.category_major_name,
        p.category_medium_cd,
        c.category_medium_name,
        p.category_small_cd,
        c.category_small_name,
        p.unit_price,
        p.unit_cost
    FROM
        product p
    JOIN
        category c
    USING(category_small_cd)
);

データ出力と読み込み

S-094: 093で作成したカテゴリ名付き商品データを以下の仕様でファイル出力せよ。

ファイル形式	ヘッダ有無	文字エンコーディング
CSV（カンマ区切り）	有り	UTF-8

ファイル出力先のパスは以下のようにすること（COPYコマンドの権限は付与済み）。

出力先
/tmp/data

※"/tmp/data"を指定することでJupyterの"/work/data"と共有されるようになっている。

解答例

copy product_full to '/tmp/data/product_full_UTF-8_header.csv'
-- テーブルと出力先の指定
with csv header encoding 'UTF-8';
-- エンコーディング条件指定

S-095: 093で作成したカテゴリ名付き商品データを以下の仕様でファイル出力せよ。

ファイル形式	ヘッダ有無	文字エンコーディング
CSV（カンマ区切り）	有り	CP932

PostgreSQLではShift_JISを指定することでCP932相当となる。ファイル出力先のパスは以下のようにすること（COPYコマンドの権限は付与済み）。

出力先
/tmp/data

※"/tmp/data"を指定することでJupyterの"/work/data"と共有されるようになっている。

解答例

copy product_full to '/tmp/data/product_full_SJIS_header.csv'
with csv header encoding 'SJIS';

S-096: 093で作成したカテゴリ名付き商品データを以下の仕様でファイル出力せよ。

ファイル形式	ヘッダ有無	文字エンコーディング
CSV（カンマ区切り）	無し	UTF-8

ファイル出力先のパスは以下のようにすること（COPYコマンドの権限は付与済み）。

出力先
/tmp/data

※"/tmp/data"を指定することでJupyterの"/work/data"と共有されるようになっている。

解答例

copy product_full to '/tmp/data/S_product_full_UTF-8_noh.csv'
with csv encoding 'UTF-8';
--headerを入れないことでなくなる

S-097: 094で作成した以下形式のファイルを読み込み、データを3件を表示させて正しく取り込まれていることを確認せよ。

ファイル形式	ヘッダ有無	文字エンコーディング
CSV（カンマ区切り）	有り	UTF-8

解答例

DROP TABLE IF EXISTS product_full;
CREATE TABLE product_full (
        product_cd           VARCHAR(10),
        category_major_cd    VARCHAR(2),
        category_major_name  VARCHAR(20),
        category_medium_cd   VARCHAR(4),
        category_medium_name VARCHAR(20),
        category_small_cd    VARCHAR(6),
        category_small_name  VARCHAR(20),
        unit_price           INTEGER,
        unit_cost            INTEGER
);

--　初めに、データ型をすべて指定しておく

copy product_full from '/tmp/data/product_full_UTF-8_header.csv'
with csv header encoding 'UTF-8';
-- 指定したデータ型に沿った形のものならコピー可能

S-098: 096で作成した以下形式のファイルを読み込み、データを3件を表示させて正しく取り込まれていることを確認せよ。

ファイル形式	ヘッダ有無	文字エンコーディング
CSV（カンマ区切り）	ヘッダ無し	UTF-8

解答例

copy product_full from '/tmp/data/S_product_full_UTF-8_noh.csv'
with csv encoding 'UTF-8';

S-099: 093で作成したカテゴリ名付き商品データを以下の仕様でファイル出力せよ。

ファイル形式	ヘッダ有無	文字エンコーディング
TSV（タブ区切り）	有り	UTF-8

ファイル出力先のパスは以下のようにすること（COPYコマンドの権限は付与済み）。

出力先
/tmp/data

※"/tmp/data"を指定することでJupyterの"/work/data"と共有されるようになっている。

ベースはcsvなので、そこに区切り文字を設定する

解答例

copy product_full to '/tmp/data/product_UTF-8_head.csv'
with csv header delimiter E'\t' encoding 'UTF-8';

S-100: 099で作成した以下形式のファイルを読み込み、データを3件を表示させて正しく取り込まれていることを確認せよ。

ファイル形式	ヘッダ有無	文字エンコーディング
TSV（タブ区切り）	有り	UTF-8

解答例

copy product_full from '/tmp/data/product_UTF-8_head.csv'
with csv header delimiter E'\t' encoding 'UTF-8';
-- 読み出しはtoをfromに変更すればOK

まとめ

(一部さぼったりもしましたけど)一通りやり終えました。
マージで長いです。けどこれで大部分はOKだと思います。やったね！

ぜひとも皆様も100本ノックしてください。