<jupyter_start><jupyter_text>Day 2: データ選択・集約のための基礎的なSQL操作大規模なデータの管理およびそれに基づくデータ分析を行っている組織やプロジェクトでは，何かしらのデータベースマネジメントシステム（DBMS）をデータを処理している．今日，データベースは* [関係データベース（RDB）](https://ja.wikipedia.org/wiki/関係データベース)* [NoSQL](https://ja.wikipedia.org/wiki/NoSQL)（RDB以外のデータベース）に大別される．このうち，関係データベースは長い歴史があり，構造化されたデータを保存・管理・検索・集約するために現在でも広く使われている．Day 2からDay 5では，関係データベースの管理システム（DBMS）の中でも，簡単に環境が構築できる軽量なシステムである[SQLite](https://www.sqlite.org/)を用いる．SQLiteの関係データベース上に保存された架空の小売店（仮称：杏森堂）の購買データをターゲットに，Excelでは扱いきれないサイズのデータの前処理・分析を体験する．なお，Day 2からDay 5の演習で用いるデータセットは，[データサイエンス100本ノック（構造化データ加工編）](https://github.com/The-Japan-DataScientist-Society/100knocks-preprocess)で提供されているデータセットをSQLite形式に変換したものである．また，「ノック」は[データサイエンス100本ノック（構造化データ加工編）](https://github.com/The-Japan-DataScientist-Society/100knocks-preprocess)に収録されている演習課題を，本授業向けにアレンジしたものである．「課題」は完全に山本が作成したものである．---  SQL利用環境セットアップSQLiteはWindows/Mac/LinuxどのOSでもインストールして利用することができる．本演習は環境構築の手間をできるだけ減らすために，Google Colaboratory上でSQLiteを用いる．Googleにログインした状態で[本演習サイト](https://data-analytics2022.hontolab.org)の各回の「ドリル（模範解答なし）」のリンクをクリックすると，あなた用のGoogle Colaboratoyがブラウザ上で立ち上がり，各回のドリル・課題を行うためのページが表示される．ページが表示されたら，以下のコードが書かれた箇所（セル）を実行しよう．すると，Google Colaboratory上でSQLiteを実行するための環境が用意される．なお，Google Colaboratory上でコードを実行するには，以下の2つの方法がある：* 「再生ボタン」をクリックする* コードが書かれたセルをクリックし，Shift + Enterキーを押す<jupyter_code># Google Colaboratory上のSQLiteをアップグレードする
import sys
if 'google.colab' in sys.modules:
    !gdown --id 1BSHIKQ7rFw5BpTq5nw1UZfjPK_7Mpnbi -O /usr/lib/python3.7/lib-dynload/<jupyter_output><empty_output><jupyter_text>上記コードを実行したら，アップグレード版SQLiteを使うために，Google Colaboratoryを再起動させよう．再起動の方法は，画面上部の`Runtime`から`Restart Runtime`をクリックする．再起動が完了したら，コードを実行しよう．以下のコードはGoogle Colab（Jupyter）上でSQLを対話的に利用するためのおまじないである．<jupyter_code># ipython-sqlがインストールされていない場合
!pip install ipython-sql<jupyter_output><empty_output><jupyter_text>英文が数行表示された後，SQLiteがインストールされる． 続いて，演習で用いる購買データが収められたSQLiteデータベースをダウンロードしよう． 以下のコードを実行すると，Google Colaboratory上にデータベースがダウンロードされる．<jupyter_code># SQLiteデータベース（data-analytics-lecture.db）のファイルをダウンロード
import os
if not os.path.exists('data/data-analytics-lecture.db'):
    !wget -P data https://github.com/hontolab-courses/data-analytics-2022/raw/main/data/data-analytics-lecture.db<jupyter_output><empty_output><jupyter_text>データベースがダウンロードできたら，データをロードしておこう．以下のコードを実行すると，Google Colaboratory上でダウンロードしたSQLiteデータベースを操作できるようになる．<jupyter_code>%load_ext sql
%sql sqlite:///data/data-analytics-lecture.db<jupyter_output><empty_output><jupyter_text>なお，Google Colaboratoryはセッションを閉じると，環境が初期化され，以前に自前でインストールしたライブラリが削除される．Google Colaboratoryを使って演習に取り組む際には，上記コードを毎回忘れず実行しよう． ---  Google Colaboratory上でのSQLの実行方法 Google Colaboratoryでは，以下のようにセルの冒頭に`%%sql`と書き，2行目以降にSQL文を書くとSQLを実行することができる．試しに以下のSQLを実行してみよう．<jupyter_code>%%sql

SELECT
    name AS table_name
FROM
    sqlite_master
WHERE
    type = 'table';<jupyter_output><empty_output><jupyter_text>上記SQLは，データベースに格納されたテーブルの一覧を表示するものである．ダウンロードしたSQLiteデータベース，つまり今回扱う杏森堂のデータベースには，**6つ**のテーブルが格納されていることが分かる．なお，対象データベースの構造が記された実体関連図（ER図）は[コチラ](https://github.com/hontolab-courses/data-analytics-2022/raw/main/doc/ER-diagram.pdf)から確認することできる．課題とドリルに取り組む前に，あと1つ，別のSQLを実行してみよう．以下のSQLを実行すると，`receipt`テーブルの最初の10件を表示することができる（SQL文に`LIMIT`を付けないと，テーブル内の全データが表示されてしまうので注意）．<jupyter_code>%%sql

SELECT * FROM receipt LIMIT 10;<jupyter_output><empty_output><jupyter_text>--- 課題1データベースを整備した杏森堂から，再度データ分析の依頼を受けたとする．購買履歴が格納されたデータベースを用いて，これまでに何名の顧客が何回（何日）ショッピングを行ったのかを分析したい．レシート明細テーブル（`receipt`）を用いて顧客の購買頻度を分析し，以下の項目について分析結果を表示するためのSQLを書き，実行結果とともに示しなさい：* 購買頻度（これまでに店舗を利用した日数）* 購買頻度に対応する顧客の数* 該当する購買頻度以下の顧客数の累積値ただし，顧客ID（`customer_id`）が"Z"から始まるのものは非会員を表すため，除外して分析すること．※ 以下は，SQL実行結果のイメージである：| 購買頻度（日数） | 購買頻度に対応する顧客の数 | 顧客数累積値 || ---- | ---- | ---- || 1 | 2761 | 2761 || 2 | 1499 | 4260 || 3 | 881 | 5141 || ... | ... | ... |<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>--- ノック以下は，上記課題を解くための要素を学ぶことができるクイズである．課題を解くためのスキルを着実に習得したい人は，以下のノックを順次解いてみよう．ノックの解説は授業中に行う．  Knock 11: 選択レシート明細のテーブル（`receipt`）から全項目の先頭10件を表示し，どのようなレコードが保存されているか確認せよ．<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 12: 射影1レシート明細のテーブル（`receipt`）から売上日（`sales_ymd`），顧客ID（`customer_id`），商品コード（`product_cd`），売上金額（`amount`）の順に列を指定し，10件レコードを表示せよ．<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 13: 射影2レシート明細のテーブル（`receipt`）から売上日（`sales_ymd`），顧客ID（`customer_id`），商品コード（`product_cd`），売上金額（`amount`）の順に列を指定し，10件レコードを表示せよ．ただし，列`sales_ymd`は列名を`sales_date`に変更して表示すること．<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 14: 選択2レシート明細のテーブル（`receipt`）から売上日（`sales_ymd`），顧客ID（`customer_id`），商品コード（`product_cd`），売上金額（`amount`）の順に列を指定し，以下の条件を満たすレコードをすべて抽出せよ．* 顧客IDが"CS018205000001"<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 15: 選択3レシート明細のテーブル（`receipt`）から売上日（`sales_ymd`），顧客ID（`customer_id`），商品コード（`product_cd`），売上金額（`amount`）の順に列を指定し，以下の条件を満たすレコードをすべて抽出せよ．* 顧客IDが"CS018205000001"* 売上金額が1000以上<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 16: 選択4レシート明細のテーブル（`receipt`）から売上日（`sales_ymd`），顧客ID（`customer_id`），商品コード（`product_cd`），売上数量（`quantity`），売上金額（`amount`）の順に列を指定し，以下の条件を満たすレコードをすべて抽出せよ．* 顧客IDが"CS018205000001"* 売上金額が1000以上または売上数量が5以上<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 17: 選択5レシート明細のテーブル（`receipt`）から売上日（`sales_ymd`），顧客ID（`customer_id`），商品コード（`product_cd`），売上金額（`amount`）の順に列を指定し，以下の条件を満たすレコードをすべて抽出せよ．* 顧客IDが"CS018205000001"* 売上金額が1000以上2000以下<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 18: 選択6レシート明細のテーブル（`receipt`）から売上日（`sales_ymd`），顧客ID（`customer_id`），商品コード（`product_cd`），売上金額（`amount`）の順に列を指定し，以下の条件を満たすレコードをすべて抽出せよ．* 顧客IDが"CS018205000001"* 商品コードが"P071401019"以外<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 19: 選択7下記SQL文について，問い合わせ結果が変わらないよう`OR`を`AND`に書き換えよ．```SELECT     *FROM    storeWHERE    NOT (prefecture_cd == 13 OR floor_area > 900);```<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 20: LIKE句1店舗テーブル（`store`）から，店舗コード（`store_cd`）が"S14"で始まるものだけを全項目抽出し，10件だけ表示せよ．<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 21: LIKE句2店舗テーブル（`store`）から横浜市の店舗だけを全項目表示せよ．<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 22: 正規表現1顧客テーブル（`customer`）から，ステータスコード（`status_cd`）の先頭がアルファベットのA〜Fで始まるデータを全項目抽出し，10件だけ表示せよ．<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 23: 正規表現2顧客テーブル（`customer`）から，ステータスコード（`status_cd`）の末尾が数字の1〜9で終わるデータを全項目抽出し，10件だけ表示せよ．<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 24: 正規表現3顧客テーブル（`customer`）から，ステータスコード（`status_cd`）の先頭がアルファベットのA〜Fで始まり，末尾が数字の1〜9で終わるデータを全項目抽出し，10件だけ表示せよ．<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 25: ソート1顧客テーブル（`customer`）のレコードを高年齢順にソートし，先頭10レコードを前項目表示せよ．<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 26: ソート2顧客テーブル（`customer`）のレコードを若い順（同じ年齢の場合は誕生日が遅い順）にソートし，先頭10レコードを前項目表示せよ．<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 27: 集計1レシート明細テーブル（`receipt`）のレコード数を求めよ．<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 28: 集計2レシート明細テーブル（`receipt`）中の顧客ID（`customer_id`）の総数（重複を除く）を求めよ．<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 29: 集約演算1レシート明細テーブル（`receipt`）に対し，店舗コード（`store_cd`）ごとに売上金額の合計（`total_amount`）と売上数量の合計（`total_quantity`）を計算し表示せよ．なお，表示件数は先頭の10件でよい．<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 30: 集約演算2レシート明細テーブル（`receipt`）に対し，顧客ID（`customer_id`）ごとに最も新しい売上日と最も古い売り上げ日を求め，それらが異なる顧客IDを10件表示せよ．なお，表示項目は顧客IDと最も新しい売り上げ日（`latest_sales_ymd`），最も古い売り上げ日（`oldest_sales_ymd`）とする．<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 31: 集約演算3レシート明細テーブル（`receipt`）に対し，店舗コード（`store_cd`）ごとに売上金額（`amount`）の平均を計算し，平均売り上げ金額の上位5件の店舗を表示せよ．<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 32: 集約演算4レシート明細テーブル（`receipt`）に対し，店舗コード（`store_cd`）ごとに売上金額（`amount`）の総額を計算し，売り上げ総額が800,000以上の店舗を表示せよ．<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 33: Window関数1レシート明細テーブル（`receipt`）の各レコードに対して売上金額（`amount`）が高い順にランク（順位）を付与し，先頭10件を抽出せよ．その際，項目として顧客ID（`customer_id`），売上金額（`amount`），付与したランクを表示させること．なお，売上金額が等しい場合は，同一順位を付与するものとする．<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 34: Window関数2レシート明細テーブル（`receipt`）の各レコードに対して売上金額（`amount`）が高い順にランク（順位）を付与し，先頭10件を抽出せよ．その際，項目として顧客ID（`customer_id`），売上金額（`amount`），付与したランクを表示させること．なお，売上金額が等しい場合は抜け番にせず，異なるランクを付与するものとする．<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 35: Window関数3レシート明細テーブル（`receipt`）から顧客ID（`customer_id`）が`CS040214000008`のレコードのみを取り出し，購買日（`sales_ymd`）でソートした上で以下の項目を表示せよ：* 購買日（`sales_ymd`）* その日の売上総額（`amount`）* 全期間を通した当該顧客の売上総額（`total_amount`）なお，表示件数は先頭の10件のみでよい．<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 36: Window関数4レシート明細テーブル（`receipt`）から顧客ID（`customer_id`）が`CS040214000008`のレコードのみを取り出し，購買日（`sales_ymd`）でソートした上で以下の項目を表示せよ：* 購買日（`sales_ymd`）* その日の売上総額（`amount`）* その日までの売上の累積値（`cumulative_total_amount`）なお，表示件数は先頭の10件のみでよい．<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 37: 副問い合わせ1レシート明細テーブル（`receipt`）を利用して顧客ID（`customer_id`）ごとの売上総額を計算し，その平均（`avg_total_amount`）を求めよ．ただし，顧客IDが"Z"から始まるのものは非会員を表すため，除外して計算すること．<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 38: 副問い合わせ2レシート明細テーブル（`receipt`）を利用して全商品の中で売上総額が上位3位の商品コード（`product_cd`）を求め，各店舗（`store_cd`）における3つの商品の売上総額を求めよ．なお，結果は`store_cd`列でソートして表示すること．なお，このノックでは`WITH句`を用いよ．表示件数は30件でよい．<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 39: 副問い合わせ3（難）レシート明細テーブル（`receipt`）に対し，店舗コード（`store_cd`）ごとに商品コードごと（`product_cd`）の売上金額（`amount`）総額を計算し，各店舗で売上総額が最も大きかった商品コードとその売上総額を表示せよ．※ヒント：Window関数の`OVER`句の中で`PARTITION BY`句を使う<jupyter_code>%%sql<jupyter_output><empty_output><jupyter_text>Knock 40: 副問い合わせ4（難）レシート明細テーブル（`receipt`）に対して商品コードごと（`product_cd`）の売上金額（`amount`）総額を計算し，売上総額のパーセンタイル値が25%から75%の商品コードを表示せよ．なお，表示する件数は10でよい．※ヒント: Window関数の`PERCENT_RANK`を使う<jupyter_code>%%sql<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14005037783375315, 'fraction_numerical': 0.03314861460957179, 'mean_word_length': 25.25925925925926, 'pattern_counts': {'":': 0, '<': 149, '<?xml version=': 0, '>': 150, 'https://': 8, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '37', 'dir': 'jupyter-structured-clean-dedup', 'id': '129687', 'license': 'MIT', 'n_tokens_mistral': 7353, 'n_tokens_neox': 6984, 'n_words': 179, 'path': 'notebook/day-02.ipynb', 'repo_name': 'hontolab-courses/data-analytics-2022', 'size': '9925'}