SQL 100 本ノック 知識の整理(45-57)

個人的な整理、続き。

Q45:

顧客データ（customer）の生年月日（birth_day）は日付型でデータを保有している。これをYYYYMMDD形式の文字列に変換し、顧客ID（customer_id）とともに10件表示せよ。

SELECT
    customer_id,
    TO_CHAR(birth_day, 'YYYYMMDD')
FROM
    customer
LIMIT 10
;

日付型を文字列型に変換する時の基本。

Q46:

YYYYMMDD形式の文字列型を日付型に変換

TO_DATE(application_date, 'YYYYMMDD') AS application_date

文字列型を日付型に。

Q47:

YYYYMMDD形式の数値型を日付型に変換

TO_DATE(CAST(sales_ymd AS VARCHAR), 'YYYYMMDD') AS sales_ymd

CAST(expression AS data_type)でデータ型の変換を行う。TO_CHARよりも簡潔。この問題の場合、TO_が続くと読みにくくなる。TO_DATEで日付型に変換
数値型→文字列型→日付型 というように変換する。

Q48:

売上エポック秒（sales_epoch）は数値型のUNIX秒でデータを保有している。これを日付型に変換する。

CAST(TO_TIMESTAMP(sales_epoch) AS DATE) AS sales_ymd

エポック秒：UNIXエポック（1970年1月1日 00:00:00 UTC）からの経過秒数を表す数値型のデータ。UNIX秒とも呼ばれる。エポック秒は秒までのデータを保有しているので、日時型(TIMESTAMP型)で秒以下を切り落とした後、日付型に変換する。

Q49:

売上エポック秒（sales_epoch）を日付型に変換し,「年」だけ取り出す。

EXTRACT(YEAR FROM TO_TIMESTAMP(sales_epoch)) AS sales_year

エポック秒を変換するには日時型(TIMESTAMP型)を使うのが一般的。日時型からEXTRACT関数でYEARのみを抽出している。

Q50:

売上エポック秒（sales_epoch）を日付型に変換し、「月」だけ取り出す。「月」は0埋め2桁で取り出す。

TO_CHAR(EXTRACT (MONTH FROM TO_TIMESTAMP(sales_epoch)), 'FM00') AS sales_month

TO_CHAR(expression, format) という構文を持つので第二引数でformatを指定できる。今回は0埋め2桁なので、'FM00'でゼロを含む2桁の文字列に変換している。

Q52:

レシート明細データ（receipt）の売上金額（amount）を顧客ID（customer_id）ごとに合計の上、売上金額合計に対して2,000円以下を0、2,000円より大きい金額を1に二値化し、顧客ID、売上金額合計とともに10件表示せよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。

SELECT
    customer_id,
    SUM(amount) AS sum_amount,
    CASE
        WHEN SUM(amount) > 2000 THEN 1
        ELSE 0
    END AS sales_flg
FROM
    receipt
WHERE
    customer_id NOT LIKE 'Z%'
GROUP BY
    customer_id
LIMIT 10
;

CASE ~ WHEN ~ THEN ~ ELSE ~ ENDの一般的なCASE文の流れ。

Q53:

顧客データ（customer）の郵便番号（postal_cd）に対し、東京（先頭3桁が100〜209のもの）を1、それ以外のものを0に二値化せよ。さらにレシート明細データ（receipt）と結合し、全期間において売上実績のある顧客数を、作成した二値ごとにカウントせよ。

WITH cust AS(
    SELECT
        customer_id,
        postal_cd,
        CASE
            WHEN CAST (SUBSTR(postal_cd, 1, 3) AS INTEGER)                       BETWEEN 100 AND 209 THEN 1
            ELSE 0
            END AS postal_flg
    FROM
        customer
),
rect AS(
    SELECT
        customer_id
    FROM
        receipt
)
SELECT
    c.postal_flg,
    COUNT(DISTINCT c.customer_id) AS customer_cnt
FROM
    cust c
JOIN
    rect r
USING
    (customer_id)
GROUP BY
    c.postal_flg
;

完成形は、1,0のそれぞれの場合で売上実績のある顧客数が表示されているテーブルをイメージ。最初の一時テーブルで二値化、次の一時テーブルで顧客IDの抽出、最後に重複排除のカウントをして結合したら完成しそう。

最初の一時テーブルでは指定された範囲の郵便番号の先頭3桁を取り出すことがポイントとなる。まずは文字列型を数値型にして大小による範囲指定を可能にする。そのあと指定された桁数分を取り出すという作業を行う。SUBSTR関数は、指定された文字列の一部を取り出す関数で、この場合、postal_cdの先頭から3文字目までの部分文字列を取り出す。

SUBSTR(文字列, 開始位置, 抽出する文字数)

二番目の一時テーブルでは売上実績のある顧客IDを抽出。最後にCOUNT(DISTINCT)で重複を除いたIDのカウントを行う。

Q54:

顧客データ（customer）の住所（address）は、埼玉県、千葉県、東京都、神奈川県のいずれかとなっている。都道府県毎にコード値を作成し、顧客ID、住所とともに10件表示せよ。値は埼玉県を11、千葉県を12、東京都を13、神奈川県を14とすること。

SELECT
    customer_id,
    address,
    CASE
        WHEN SUBSTR(address,1,4) = '神奈川県' THEN '14'
    END AS prefecture_cd

    CASE
        WHEN address LIKE '神奈川%' THEN '14'
    END AS prefecture_cd

これまでの学習を振り返るとこんな感じでコード値を作成できそう。

Q55:

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

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

WITH
    sales_amount AS(
    SELECT
        customer_id,
        SUM(amount) AS sum_amount
    FROM
        receipt
    GROUP BY
        customer_id
),
    sales_per AS(
    SELECT
        PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY sum_amount) AS per25,
        PERCENTILE_CONT(0.50) WITHIN GROUP(ORDER BY sum_amount) AS per50,
        PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY sum_amount) AS per75
    FROM
        sales_amount
)
SELECT
    a.customer_id,
    a.sum_amount,
    CASE 
        WHEN a.sum_amount < per25 THEN 1
        WHEN per25 <= a.sum_amount AND a.sum_amount < per50 THEN 2
        WHEN per50 <= a.sum_amount AND a.sum_amount < per75 THEN 3
        WHEN a.sum_amount >= per75 THEN 4
    END AS sales_cd
FROM
    sales_amount a
CROSS JOIN
    sales_per p
LIMIT 10
;

完成形は顧客ID,売上金額,カテゴリ値が並んでいるイメージ。最初の一時テーブルで顧客ごとの売上合計金額を求める。次の一時テーブルで四分位数を求めて、それらを最後に結合する。

カラム毎に格納されている値をレコード毎に格納するためにCROSS JOINを用いる。いわゆる横→縦変換。二番目の一時テーブルは四分位点を求める3列のテーブルである。それを各売上金額に対応させるために縦方向に変換したい。そのためにCROSS JOINを用いる。

Q56:

顧客データ（customer）の年齢（age）をもとに10歳刻みで年代を算出し、顧客ID（customer_id）、生年月日（birth_day）とともに10件表示せよ。ただし、60歳以上は全て60歳代とすること。年代を表すカテゴリ名は任意とする。

LEAST(CAST(TRUNC(age/10) * 10 AS INTEGER), 60) AS era

ageを10で割って小数点を切り捨てる。その値に10をかけて数値型に変換する。LEAST関数では、数値型(INTERGER型)の値と60を比較して、より小さい方を返す。つまり、数値型の値が60を超えた場合は60が返される。

Q57:

056の抽出結果と性別コード（gender_cd）により、新たに性別×年代の組み合わせを表すカテゴリデータを作成し、10件表示せよ。組み合わせを表すカテゴリの値は任意とする。

gender_cd || TO_CHAR(LEAST(CAST(TRUNC(age/10) * 10 AS INTEGER), 60), 'FM00') AS gender_era

gender_cdが文字列なので、結合するためには年代の値も文字列に変換する必要がある。また、年齢が一桁の場合0になってしまい、他の年代と桁が揃わないので、'FM00'を指定して見やすく表示する。