[SQL] データサイエンス100本ノック（構造化データ加工編）

bokunaru·
公開:2024/7/10

2024/07/15

  • 「S-055」まで完了

    • S-050〜S-051

      to_char(

      EXTRACT(MONTH FROM TO_TIMESTAMP(sales_epoch)),

      'FM00'

      ) AS sales_month

      extract関数は、昨日のログに説明が書いてある

    • S-053

      • わからなかった。結果の想像がうまくできてなかった。receiptは売上実績しか入らないから、そこにあるcustomer_idはすべて売上を上げている。複数回買い物しているidもあるのでDistinctで重複排除

      • JOINで、ONの代わりに、usingも使えることを知った

    • S-054

    • S-055

      • わからなかった

      • このあたり、なんでその数値を出す意味から理解しないと一生覚えられないと思う

    • S-056

2024/07/14

  • ここまでやってきて、「work/answer」フォルダの中に、答えが掲載されていることに気づく。

  • 「S-049」まで完了

    • S-039

      • 完全外部結合＝full outer join

    • S-040

      • 集積＝CROSS JOIN

    • S-041

      lag(sum_amount, 1) over (order by sales_ymd) as lagamount

      sum_amount - lag(sum_amount, 1) over (order by sales_ymd) as diff_amount

      前後のレコードの値を取得するSQL – LAGとLEAD

    • S-043、S-045

      • どちらもやらなくても良いらしい？ちょっと飛ばす

    • S-045

      • TO_CHAR関数を使用して、日付型→文字列（YYYYMMDD）に変換

        to_char(birth_day, 'YYYYMMDD') as birth_day

    • S-046

      • TO_DATE関数を使用して、文字列→日付型（YYYY-MM-DD）に変換

        to_date(application_date, 'YYYYMMDD') as application_date

    • S-048

      • to_date,castの組み合わせ

      • YYYYMMDD形式の数値型を文字型に直してから、日付型に変換する

        to_date(cast(sales_ymd as varchar), 'YYYYMMDD') as sales_ymd

    • S-049

      • extract関数とは、日付や時刻から特定の部分（例：年、月、日、時間など）を抽出するための関数

        EXTRACT(YEAR FROM TO_TIMESTAMP(sales_epoch)) AS sales_year

2024/07/13

  • 「S-038」まで完了

    • 集約関数関連。count,sum,max,min、ここらへんはみっちりやっているので問題なし

    • S-028

    • S-029

    • S-030

    • S-031

    • S-032

      • パーセンタイル

        パーセンタイルは、統計分析やデータ分析で頻繁に使用されます。特に、テストの成績分布や収入分布など、データの位置を理解するために役立つ

        percentile_cont(0.25) within group (order by amount) as per_25

    • S-035

      • 間違えた。今度やり直しする

    • S-038

      • with句で、「顧客ごとの売上金額合計を求める」

        • 上記をwithにしないといけないのを思いついていなかった

      • NULLを0に変更するやり方をど忘れしていた

        coalesce(csa.sum_amount, '0')

2024/07/12

  • 「S-020」まで完了

    • S-016

    • S-019

      • 問いの「ランクを表示させる？」で、ああ…知らない関数使うのか他の人の答えみよってなった

      • window関数のRANKを使うんですね。あの、便利とうわさの…。RANKについては、このサイトがわかりやすかった。にしても便利な関数ですね。

        SELECT

        customer_id

        ,amount

        ,rank() over(order by amount desc) as rank_result

        FROM

        receipt

        LIMIT 10

    • S-020

      • window関数のROW_NUMBER()を使う

        • 同率の場合でも同じ順位にはならない

2024/07/11

  • コンテナ起動後に、SQLを叩いても「UsageError: Cell magic `%%sql` not found.」と出て焦ったけど、こちらの記事の対処で解決した。感謝

  • 「S-015」まで完了

    • S-013

      • 正規表現の方法を知らずに、LIKE句をORでつなげまくっていた。何かもっと楽な方法あるでしょっと思い他の人の答えを見たら正規表現を利用していた

        select * from customer where status_cd ~ '^[A-F]' limit 10;

        ・^：文字列の先頭を表す正規表現

        ・[]：集合を表す正規表現（[A-F]:A,B,C,D,E,F）

      • SQLの正規表現をさらに詳しく解説している記事

    • S-014

      • 後方からの書き方を解説記事からうまく導き出せず、答えを見てしまった。「$」をつけることわかっていたけど、後ろにつけるのか

        ~ '^[A-F]'

        ~ '[1-9]$'

        ^ ：前方一致

        $：後方一致

    • S-015

      • 正規表現をつなげる書き方があるんだろうなと思いつつわからなかったため、andで重ねた。下記が正解のようだが暗記するほうが早そうだ。

        ~ '^[A-F].*[1-9]$'

        ・「.」：任意の１文字を表す正規表現

        ・「*」：直前の項目の0回以上複数の繰り返しを表す正規表現

  • 正規表現をつかった部分一致は、ほとんどやってこなかったので何度か練習が必要とわかった

2024/07/10

SQL
@bokunaru
bokunaru