Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@mbs1sw1a7(@2828 ガジュマル)

データサイエンス100本ノック回答メモ（SQL編）【41-60】

Last updated at Posted at 2022-05-17

はじめに

データサイエンティスト100本ノックの記事

本編

41

  • LAG関数：前の行を持ってくる関数
  • LEAD関数：後ろの行を持ってくる関数
  • OVERは並べ替えの関数

42

  • 3日前のデータが無い＝当日、1日前、2日前、3日前のすべての日付データのあるものしか表示しない設定。
  • 頑張ればできそうだが、WITHの使い方にまだ慣れていない
  • OVER(ORDER BY sales_ymd)で並び順を並べたうえでの1つ前、2つ前、3つ前のデータをそれぞれもってきている。

43

問題文

S-043: レシート明細テーブル（receipt）と顧客テーブル（customer）を結合し、性別（gender）と年代（ageから計算）ごとに売上金額（amount）を合計した売上サマリテーブル（sales_summary）を作成せよ。性別は0が男性、1が女性、9が不明を表すものとする。

ただし、項目構成は年代、女性の売上金額、男性の売上金額、性別不明の売上金額の4項目とすること（縦に年代、横に性別のクロス集計）。また、年代は10歳ごとの階級とすること。

回答

DROP TABLE IF EXISTS sales_summary;
CREATE TABLE sales_summary AS
    WITH gender_era_amount AS (
        SELECT c.gender_cd,
        TRUNC(age/ 10) * 10 AS era,
        SUM(r.amount) AS amount
        FROM customer c
        JOIN receipt r
        ON c.customer_id = r.customer_id
        GROUP BY c.gender_cd, era
    )
    select era,
        MAX(CASE gender_cd WHEN '0' THEN amount ELSE 0 END) AS male ,
        MAX(CASE gender_cd WHEN '1' THEN amount ELSE 0 END) AS female,
        MAX(CASE gender_cd WHEN '9' THEN amount ELSE 0 END) AS unknown
    FROM gender_era_amount
    GROUP BY era
    ORDER BY era
;

解説

  • 問題文は”テーブル”の作成が最終ゴール。
  • 最初の以下の2文はテーブル作成用の構文で、無くてもSQL構文としては成立する。
DROP TABLE IF EXISTS sales_summary;
CREATE TABLE sales_summary AS
  • 構文記述時の思考
    • 最終的にテーブルを作成するので、”CREATE TABLE sales_summary AS”を冒頭にもってくる。
    • 二回目以降起動時に重複エラーとなるので、”DROP TABLE IF EXISTS sales_summary;”を最初に入れる。
    • sales_summaryテーブルの元となるテーブルをWITH句で作成し、そのテーブルを性別毎にフィルタリングして表示する形式にする
    • なぜMAX関数？

44

問題文

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

回答

%%sql
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

解説

  • UNION[ALL]複数のテーブルの和集合を取る。
  • 前の問題でsales_summaryテーブルを作成しており、それを活用した問題。

45

問題文

S-045: 顧客テーブル（customer）の生年月日（birth_day）は日付型でデータを保有している。これをYYYYMMDD形式の文字列に変換し、顧客ID（customer_id）とともに抽出せよ。データは10件を抽出すれば良い。

回答

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

解説

  • TO_CHAR(値,'書式')：指定された書式の文字データに変換
  • 今回は、日付型のカラムを文字データのYYYYMMDD形式に変換している
  • （参考URL）TO_CHAR 日付や数値を文字列へ変換するOracle SQL関数 https://segakuin.com/oracle/function/to_char.html

46

問題文

S-046: 顧客テーブル（customer）の申し込み日（application_date）はYYYYMMDD形式の文字列型でデータを保有している。これを日付型に変換し、顧客ID（customer_id）とともに抽出せよ。データは10件を抽出すれば良い。

回答

%%sql
SELECT customer_id, TO_DATE(application_date, 'YYYYMMDD')
FROM customer
LIMIT 10
;

解説

  • TO_DATE(値, 書式)：指定された値を指定された形式の日付型に変換
  • 今回は、文字データのカラムをYYYYMMDD形式の日付型に変換

47

問題文

S-047: レシート明細テーブル（receipt）の売上日（sales_ymd）はYYYYMMDD形式の数値型でデータを保有している。これを日付型に変換し、レシート番号(receipt_no)、レシートサブ番号（receipt_sub_no）とともに抽出せよ。データは10件を抽出すれば良い。

回答

%%sql
SELECT TO_DATE(CAST(sales_ymd AS VARCHAR), 'YYYYMMDD'), receipt_no, receipt_sub_no
FROM receipt
limit 10
;

解説

  • CAST(値 AS 書式)：指定された値を指定された型に変換
  • VARCHAR：可変調文字列型
  • 今回は、まず、YYYYMMDD形式の数値型を文字型にCASTで直してから、日付型にDATEで変換する

48

問題文

S-048: レシート明細テーブル（receipt）の売上エポック秒（sales_epoch）は数値型のUNIX秒でデータを保有している。これを日付型に変換し、レシート番号(receipt_no)、レシートサブ番号（receipt_sub_no）とともに抽出せよ。データは10件を抽出すれば良い。

回答

%%sql
SELECT TO_TIMESTAMP(sales_epoch) AS sales_date, receipt_no, receipt_sub_no
FROM receipt
LIMIT 10
;

解説

  • UNIX時間とは、コンピュータにおける日時の表現規則の一つで、主にUNIX系OSで標準的に用いられている形式。時刻を協定世界時（UTC）1970年1月1日午前0時ちょうどからの経過秒数で表す。
  • TO_TIMESTAMP(値)；TIMESTAMP型に変換

49

問題文

S-049: レシート明細テーブル（receipt）の販売エポック秒（sales_epoch）を日付型に変換し、「年」だけ取り出してレシート番号(receipt_no)、レシートサブ番号（receipt_sub_no）とともに抽出せよ。データは10件を抽出すれば良い。

回答

%%sql
SELECT TO_CHAR(EXTRACT(YEAR FROM TO_TIMESTAMP(sales_epoch)),'FM9999') AS sales_year, receipt_no, receipt_sub_no
FROM receipt
LIMIT 10
;

解説

  • EXTRACT(日付要素 FROM 元の日付要素)：指定した要素を取り出す
  • 'FM'：書式の幅になるまで先頭を0又は末尾を空白で埋める。
  • '9'：数値。正の場合は先頭に空白が付き、負の場合はマイナス記号(-)が付く。
  • 詳細はこちら(http://itref.fc2web.com/oracle/function/to_char.html)

50

問題文

S-050: レシート明細テーブル（receipt）の売上エポック秒（sales_epoch）を日付型に変換し、「月」だけ取り出してレシート番号(receipt_no)、レシートサブ番号（receipt_sub_no）とともに抽出せよ。なお、「月」は0埋め2桁で取り出すこと。データは10件を抽出すれば良い。

回答

%%sql
SELECT
    TO_CHAR(EXTRACT(MONTH FROM TO_TIMESTAMP(sales_epoch)),'FM00') AS sales_month, receipt_no, receipt_sub_no
FROM receipt
LIMIT 10
;

解説

  • '0'：数値。正の場合は先頭に空白が付き、負の場合はマイナス記号(-)が付く。桁数に足りない分は0で埋められる。
  • #49の月バージョンの問題

51

問題文

S-051: レシート明細テーブル（receipt）の売上エポック秒を日付型に変換し、「日」だけ取り出してレシート番号(receipt_no)、レシートサブ番号（receipt_sub_no）とともに抽出せよ。なお、「日」は0埋め2桁で取り出すこと。データは10件を抽出すれば良い。

回答

%%sql
SELECT TO_CHAR(EXTRACT(DAY FROM TO_TIMESTAMP(sales_epoch)),'FM00') AS sales_day, receipt_no, receipt_sub_no
FROM receipt
LIMIT 10
;

解説

  • 今度は日を抽出する問題。DAYを使う。

52

問題文

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

回答

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

解説

  • CASE WHEN 条件式1 THEN 返り値1 [WHEN 条件式2 THEN 返り値2] [ELSE 返り値] END：場合分けをする
  • それ以外の構文はいつも通り。階段に分けて構文は書かれているのが特徴的なぐらい。

53

問題文

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

回答

%%sql
-- 東京に1,それ以外を0とするpostal_flgというカラムを持つcustを作る
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, SUM(amount)
    FROM receipt
    GROUP BY customer_id
)
-- 2値化されたそれぞれの行についてカウント
SELECT c.postal_flg, COUNT(1)
FROM rect r
JOIN cust c
ON r.customer_id = c.customer_id
GROUP BY c.postal_flg
;

解説

  • SUBSTR(文字列, 開始位置, 切り取り文字数)：文字列を切り取る（開始位置は1から始める）
  • WITH句を使い、customerテーブルとreceiptテーブルの2つをまずは加工している

54

問題文

S-054: 顧客テーブル（customer）の住所（address）は、埼玉県、千葉県、東京都、神奈川県のいずれかとなっている。都道府県毎にコード値を作成し、顧客ID、住所とともに抽出せよ。値は埼玉県を11、千葉県を12、東京都を13、神奈川県を14とすること。結果は10件表示させれば良い。

回答

%%sql
SELECT
    customer_id,
    address,
    CASE SUBSTR(address,1,3)
        WHEN '埼玉県' THEN '11'
        WHEN '千葉県' THEN '12'
        WHEN '東京県' THEN '13'
        WHEN '神奈川' THEN '14'
    END AS prefecture_cd
FROM
    customer
LIMIT 10
;

解説

  • ここでも、SUBSTR(文字列, 開始位置, 切り取り文字数)：文字列を切り取る（開始位置は1から始める）

55

問題文

S-055: レシート明細テーブル（receipt）の売上金額（amount）を顧客ID（customer_id）ごとに合計し、その合計金額の四分位点を求めよ。その上で、顧客ごとの売上金額合計に対して以下の基準でカテゴリ値を作成し、顧客ID、売上金額合計とともに表示せよ。カテゴリ値は上から順に1〜4とする。結果は10件表示させれば良い。
最小値以上第一四分位未満
第一四分位以上第二四分位未満
第二四分位以上第三四分位未満
第三四分位以上

回答

%%sql
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_flg
FROM sales_amount a
CROSS JOIN sales_pct p
LIMIT 10
;

解説

  • 問題文がそもそも難しい。上位25%、50%等を算出する問題
  • 上位25%の値をpct25,50%をpct50・・・・としてその値より大きいか小さいかで、四分位点を求めている

56

問題文

S-056: 顧客テーブル（customer）の年齢（age）をもとに10歳刻みで年代を算出し、顧客ID（customer_id）、生年月日（birth_day）とともに抽出せよ。ただし、60歳以上は全て60歳代とすること。年代を表すカテゴリ名は任意とする。先頭10件を表示させればよい。

回答

%%sql
SELECT
    customer_id,
    birth_day,
    -- 年齢の10の位を取り出し、INTEGER型に変換。そして、年の最小値を取る。
    LEAST(CAST(TRUNC(age / 10) * 10 AS INTEGER), 60) AS era
FROM
    customer
GROUP BY
    customer_id,
    birth_day
    -- 確認用の条件
    -- HAVING LEAST(CAST(TRUNC(age / 10) * 10 AS INTEGER), 60) >= 60
LIMIT 10
;

解説

  • LEAST(値1, 値2,...)：引数の最小値を取る

57

問題文

S-057: 前問題の抽出結果と性別（gender）を組み合わせ、新たに性別×年代の組み合わせを表すカテゴリデータを作成せよ。組み合わせを表すカテゴリの値は任意とする。先頭10件を表示させればよい。

回答

%%sql
SELECT
    customer_id,
    birth_day,
    -- 例えば、160なら女性で、60歳以上
    gender_cd || LEAST(CAST(TRUNC(age / 10) * 10 AS INTEGER), 60) AS era
FROM
    customer
GROUP BY
    customer_id,
    birth_day
HAVING LEAST(CAST(TRUNC(age / 10) * 10 AS INTEGER), 60) >= 60
LIMIT 10
;

解説

  • '||'： 文字列データに任意の文字列を連結したり、複数の文字列データを連結する
  • gender_cd 1=女性、0=男性,9=その他
  • gender_cdと年齢を結合してカテゴリにしている。

58

問題文

S-058: 顧客テーブル（customer）の性別コード（gender_cd）をダミー変数化し、顧客ID（customer_id）とともに抽出せよ。結果は10件表示させれば良い。

回答

%%sql
-- SQL向きではない。カテゴリが多いととても長いSQL構文になるので。
SELECT
    customer_id,
    gender_cd,
    -- 個別にそれぞれ番号を割り振る
    CASE WHEN gender_cd = '0' THEN '1' ELSE '0' END AS gender_male,
    CASE WHEN gender_cd = '1' THEN '1' ELSE '0' END AS gender_female,
    CASE WHEN gender_cd = '9' THEN '1' ELSE '0' END AS gender_unknown
FROM
    customer
LIMIT 10
;

解説

  • gender_cd 1=女性、0=男性,9=その他でそれぞれ、対応する新規追加した列にフラグ'1'をつける構文

59 ここから数値化の問題。標準とかでてきて難しい。

問題文

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

回答

%%sql
-- (1)：売上金額の合計を計算＆'Z'で始まる顧客を除外
WITH sales_amount AS(
    SELECT
        customer_id,
        SUM(amount) as sum_amount
    FROM
        receipt
    WHERE
        customer_id NOT LIKE 'Z%'
    GROUP BY
        customer_id
),
-- (2)：平均0、分散1に正規化
stats_amount AS (
    SELECT
        AVG(sum_amount) AS avg_amount,
        stddev_samp(sum_amount) AS std_amount
    FROM
        sales_amount
)
SELECT
    customer_id,
    sum_amount,
    (sum_amount - avg_amount) / std_amount AS normal_amount
FROM sales_amount
CROSS JOIN stats_amount
LIMIT 10;

解説

  • 正直何をやっているのかわからない。
  • (1)はまだわかる。
  • stddev_sampは標準偏差を計算する関数
  • 正規化とはなにか？

60正規化問題

問題文

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

回答

%%sql
-- 売上金額の合計を計算&'Z'で始まる顧客の除外
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,
    -- Min-Max-normalization
    (sum_amount- min_amount) * 1.0 / (max_amount - min_amount) * 1.0 AS scale_amount
FROM sales_amount
CROSS JOIN stats_amount
LIMIT 10
;

解説

  • 常用対数化とはなにか？
  • 数学の勉強
  • 常用対数とは、10 を底とする対数 log10N のことです。・・・・？
    • つまり、10を何乗したらNになるか？を表す数
    • この問題でのNは売上合計金額をさす。
    • これは何に役立つ・・？なんのために算出している？
    • 数値の規模感を把握するのに使われる・・？
    • むずすぎる

61

問題文

(Sample)

回答

(Sample)

解説

  • (Sample)

参考記事

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?