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

はじめに¶

  • データベースはPostgreSQL13です
  • 初めに以下のセルを実行してください
  • セルに %%sql と記載することでSQLを発行することができます
  • jupyterからはdescribeコマンドによるテーブル構造の確認ができないため、テーブル構造を確認する場合はlimitを指定したSELECTなどで代用してください
  • 使い慣れたSQLクライアントを使っても問題ありません（接続情報は以下の通り）
    • IPアドレス：Docker Desktopの場合はlocalhost、Docker toolboxの場合は192.168.99.100
    • Port:5432
    • database名: dsdojo_db
    • ユーザ名：padawan
    • パスワード:padawan12345
  • 大量出力を行うとJupyterが固まることがあるため、出力件数は制限することを推奨します（設問にも出力件数を記載）
    • 結果確認のために表示させる量を適切にコントロールし、作業を軽快にすすめる技術もデータ加工には求められます
  • 大量結果が出力された場合は、ファイルが重くなり以降開けなくなることもあります
    • その場合、作業結果は消えますがファイルをGitHubから取り直してください
    • vimエディタなどで大量出力範囲を削除することもできます
  • 名前、住所等はダミーデータであり、実在するものではありません
In [1]:
%load_ext sql
import os
from sqlalchemy import create_engine

pgconfig = {
    'host': 'db',
    'port': os.environ['PG_PORT'],
    'database': os.environ['PG_DATABASE'],
    'user': os.environ['PG_USER'],
    'password': os.environ['PG_PASSWORD'],
}
dsl = 'postgresql://{user}:{password}@{host}:{port}/{database}'.format(**pgconfig)
conn = create_engine(dsl)

# MagicコマンドでSQLを書くための設定
%sql conn
Out[1]:
'Connected: padawan@dsdojo_db'

使い方¶

  • セルの先頭に%%sqlと記載し、２行目以降にSQLを記述することでJupyterからPostgreSQLに対しSQLを実行できます。
In [2]:
%%sql
SELECT 'このように実行できます' AS sample;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.
Out[2]:
sample
このように実行できます

データ加工100本ノック¶

S-001: レシート明細データ（receipt）から全項目の先頭10件を表示し、どのようなデータを保有しているか目視で確認せよ。

In [3]:
%%sql

SELECT
    *
FROM receipt
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[3]:
sales_ymd sales_epoch store_cd receipt_no receipt_sub_no customer_id product_cd quantity amount
20181103 1541203200 S14006 112 1 CS006214000001 P070305012 1 158
20181118 1542499200 S13008 1132 2 CS008415000097 P070701017 1 81
20170712 1499817600 S14028 1102 1 CS028414000014 P060101005 1 170
20190205 1549324800 S14042 1132 1 ZZ000000000000 P050301001 1 25
20180821 1534809600 S14025 1102 2 CS025415000050 P060102007 1 90
20190605 1559692800 S13003 1112 1 CS003515000195 P050102002 1 138
20181205 1543968000 S14024 1102 2 CS024514000042 P080101005 1 30
20190922 1569110400 S14040 1102 1 CS040415000178 P070501004 1 128
20170504 1493856000 S13020 1112 2 ZZ000000000000 P071302010 1 770
20191010 1570665600 S14027 1102 1 CS027514000015 P071101003 1 680

S-002: レシート明細データ（receipt）から売上年月日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、10件表示せよ。

In [4]:
%%sql

SELECT
    sales_ymd,
    customer_id,
    product_cd,
    amount
FROM receipt
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[4]:
sales_ymd customer_id product_cd amount
20181103 CS006214000001 P070305012 158
20181118 CS008415000097 P070701017 81
20170712 CS028414000014 P060101005 170
20190205 ZZ000000000000 P050301001 25
20180821 CS025415000050 P060102007 90
20190605 CS003515000195 P050102002 138
20181205 CS024514000042 P080101005 30
20190922 CS040415000178 P070501004 128
20170504 ZZ000000000000 P071302010 770
20191010 CS027514000015 P071101003 680

S-003: レシート明細データ（receipt）から売上年月日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、10件表示せよ。ただし、sales_ymdsales_dateに項目名を変更しながら抽出すること。

In [5]:
%%sql

SELECT
    sales_ymd AS sales_date,
    customer_id,
    product_cd,
    amount 
FROM receipt
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[5]:
sales_date customer_id product_cd amount
20181103 CS006214000001 P070305012 158
20181118 CS008415000097 P070701017 81
20170712 CS028414000014 P060101005 170
20190205 ZZ000000000000 P050301001 25
20180821 CS025415000050 P060102007 90
20190605 CS003515000195 P050102002 138
20181205 CS024514000042 P080101005 30
20190922 CS040415000178 P070501004 128
20170504 ZZ000000000000 P071302010 770
20191010 CS027514000015 P071101003 680

S-004: レシート明細データ（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の条件を満たすデータを抽出せよ。

  • 顧客ID（customer_id）が"CS018205000001"
In [6]:
%%sql

SELECT
    sales_ymd,
    customer_id,
    product_cd,
    amount
FROM
    receipt
WHERE
    customer_id = 'CS018205000001'
;

 * postgresql://padawan:***@db:5432/dsdojo_db
12 rows affected.
Out[6]:
sales_ymd customer_id product_cd amount
20180911 CS018205000001 P071401012 2200
20180414 CS018205000001 P060104007 600
20170614 CS018205000001 P050206001 990
20170614 CS018205000001 P060702015 108
20190216 CS018205000001 P071005024 102
20180414 CS018205000001 P071101002 278
20190226 CS018205000001 P070902035 168
20190924 CS018205000001 P060805001 495
20190226 CS018205000001 P071401020 2200
20180911 CS018205000001 P071401005 1100
20190216 CS018205000001 P040101002 218
20190924 CS018205000001 P091503001 280

S-005: レシート明細データ（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の全ての条件を満たすデータを抽出せよ。

  • 顧客ID（customer_id）が"CS018205000001"
  • 売上金額（amount）が1,000以上
In [7]:
%%sql

SELECT
    sales_ymd,
    customer_id,
    product_cd,
    amount
FROM
    receipt
WHERE
    customer_id = 'CS018205000001'
    AND amount >= 1000
;

 * postgresql://padawan:***@db:5432/dsdojo_db
3 rows affected.
Out[7]:
sales_ymd customer_id product_cd amount
20180911 CS018205000001 P071401012 2200
20190226 CS018205000001 P071401020 2200
20180911 CS018205000001 P071401005 1100

S-006: レシート明細データ（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上数量（quantity）、売上金額（amount）の順に列を指定し、以下の全ての条件を満たすデータを抽出せよ。

  • 顧客ID（customer_id）が"CS018205000001"
  • 売上金額（amount）が1,000以上または売上数量（quantity）が5以上
In [8]:
%%sql

SELECT
    sales_ymd,
    customer_id,
    product_cd,
    quantity,
    amount
FROM
    receipt
WHERE
    customer_id = 'CS018205000001'
    AND
    (
        amount >= 1000
        OR quantity >= 5
    )
;

 * postgresql://padawan:***@db:5432/dsdojo_db
5 rows affected.
Out[8]:
sales_ymd customer_id product_cd quantity amount
20180911 CS018205000001 P071401012 1 2200
20180414 CS018205000001 P060104007 6 600
20170614 CS018205000001 P050206001 5 990
20190226 CS018205000001 P071401020 1 2200
20180911 CS018205000001 P071401005 1 1100

S-007: レシート明細データ（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の全ての条件を満たすデータを抽出せよ。

  • 顧客ID（customer_id）が"CS018205000001"
  • 売上金額（amount）が1,000以上2,000以下
In [9]:
%%sql

SELECT
    sales_ymd,
    customer_id,
    product_cd,
    amount
FROM
    receipt
WHERE
    customer_id = 'CS018205000001'
    AND amount BETWEEN 1000 AND 2000
;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.
Out[9]:
sales_ymd customer_id product_cd amount
20180911 CS018205000001 P071401005 1100

S-008: レシート明細データ（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の全ての条件を満たすデータを抽出せよ。

  • 顧客ID（customer_id）が"CS018205000001"
  • 商品コード（product_cd）が"P071401019"以外
In [10]:
%%sql

SELECT
    sales_ymd,
    customer_id,
    product_cd, amount
FROM
    receipt
WHERE
    customer_id = 'CS018205000001'
    AND product_cd != 'P071401019'
;

 * postgresql://padawan:***@db:5432/dsdojo_db
12 rows affected.
Out[10]:
sales_ymd customer_id product_cd amount
20180911 CS018205000001 P071401012 2200
20180414 CS018205000001 P060104007 600
20170614 CS018205000001 P050206001 990
20170614 CS018205000001 P060702015 108
20190216 CS018205000001 P071005024 102
20180414 CS018205000001 P071101002 278
20190226 CS018205000001 P070902035 168
20190924 CS018205000001 P060805001 495
20190226 CS018205000001 P071401020 2200
20180911 CS018205000001 P071401005 1100
20190216 CS018205000001 P040101002 218
20190924 CS018205000001 P091503001 280

S-009: 以下の処理において、出力結果を変えずにORをANDに書き換えよ。

SELECT * FROM store WHERE NOT (prefecture_cd = '13' OR floor_area > 900)

In [11]:
%%sql

SELECT * FROM store WHERE prefecture_cd != '13' AND floor_area <= 900;

 * postgresql://padawan:***@db:5432/dsdojo_db
3 rows affected.
Out[11]:
store_cd store_name prefecture_cd prefecture address address_kana tel_no longitude latitude floor_area
S14046 北山田店 14 神奈川県 神奈川県横浜市都筑区北山田一丁目 カナガワケンヨコハマシツヅキクキタヤマタイッチョウメ 045-123-4049 139.5916 35.56189 831.0
S14011 日吉本町店 14 神奈川県 神奈川県横浜市港北区日吉本町四丁目 カナガワケンヨコハマシコウホククヒヨシホンチョウヨンチョウメ 045-123-4033 139.6316 35.54655 890.0
S12013 習志野店 12 千葉県 千葉県習志野市芝園一丁目 チバケンナラシノシシバゾノイッチョウメ 047-123-4002 140.022 35.66122 808.0

S-010: 店舗データ（store）から、店舗コード（store_cd）が"S14"で始まるものだけ全項目抽出し、10件表示せよ。

In [12]:
%%sql

SELECT
    *
FROM store
WHERE
    store_cd LIKE 'S14%'
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[12]:
store_cd store_name prefecture_cd prefecture address address_kana tel_no longitude latitude floor_area
S14010 菊名店 14 神奈川県 神奈川県横浜市港北区菊名一丁目 カナガワケンヨコハマシコウホククキクナイッチョウメ 045-123-4032 139.6326 35.50049 1732.0
S14033 阿久和店 14 神奈川県 神奈川県横浜市瀬谷区阿久和西一丁目 カナガワケンヨコハマシセヤクアクワニシイッチョウメ 045-123-4043 139.4961 35.45918 1495.0
S14036 相模原中央店 14 神奈川県 神奈川県相模原市中央二丁目 カナガワケンサガミハラシチュウオウニチョウメ 042-123-4045 139.3716 35.57327 1679.0
S14040 長津田店 14 神奈川県 神奈川県横浜市緑区長津田みなみ台五丁目 カナガワケンヨコハマシミドリクナガツタミナミダイゴチョウメ 045-123-4046 139.4994 35.52398 1548.0
S14050 阿久和西店 14 神奈川県 神奈川県横浜市瀬谷区阿久和西一丁目 カナガワケンヨコハマシセヤクアクワニシイッチョウメ 045-123-4053 139.4961 35.45918 1830.0
S14028 二ツ橋店 14 神奈川県 神奈川県横浜市瀬谷区二ツ橋町 カナガワケンヨコハマシセヤクフタツバシチョウ 045-123-4042 139.4963 35.46304 1574.0
S14012 本牧和田店 14 神奈川県 神奈川県横浜市中区本牧和田 カナガワケンヨコハマシナカクホンモクワダ 045-123-4034 139.6582 35.42156 1341.0
S14046 北山田店 14 神奈川県 神奈川県横浜市都筑区北山田一丁目 カナガワケンヨコハマシツヅキクキタヤマタイッチョウメ 045-123-4049 139.5916 35.56189 831.0
S14022 逗子店 14 神奈川県 神奈川県逗子市逗子一丁目 カナガワケンズシシズシイッチョウメ 046-123-4036 139.5789 35.29642 1838.0
S14011 日吉本町店 14 神奈川県 神奈川県横浜市港北区日吉本町四丁目 カナガワケンヨコハマシコウホククヒヨシホンチョウヨンチョウメ 045-123-4033 139.6316 35.54655 890.0

S-011: 顧客データ（customer）から顧客ID（customer_id）の末尾が1のものだけ全項目抽出し、10件表示せよ。

In [13]:
%%sql

SELECT * FROM customer WHERE customer_id LIKE '%1' LIMIT 10;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[13]:
customer_id customer_name gender_cd gender birth_day age postal_cd address application_store_cd application_date status_cd
CS037613000071 六角 雅彦 9 不明 1952-04-01 66 136-0076 東京都江東区南砂********** S13037 20150414 0-00000000-0
CS028811000001 堀井 かおり 1 女性 1933-03-27 86 245-0016 神奈川県横浜市泉区和泉町********** S14028 20160115 0-00000000-0
CS040412000191 川井 郁恵 1 女性 1977-01-05 42 226-0021 神奈川県横浜市緑区北八朔町********** S14040 20151101 1-20091025-4
CS028314000011 小菅 あおい 1 女性 1983-11-26 35 246-0038 神奈川県横浜市瀬谷区宮沢********** S14028 20151123 1-20080426-5
CS039212000051 藤島 恵梨香 1 女性 1997-02-03 22 166-0001 東京都杉並区阿佐谷北********** S13039 20171121 1-20100215-4
CS015412000111 松居 奈月 1 女性 1972-10-04 46 136-0071 東京都江東区亀戸********** S13015 20150629 0-00000000-0
CS004702000041 野島 洋 0 男性 1943-08-24 75 176-0022 東京都練馬区向山********** S13004 20170218 0-00000000-0
CS041515000001 栗田 千夏 1 女性 1967-01-02 52 206-0001 東京都多摩市和田********** S13041 20160422 E-20100803-F
CS029313000221 北条 ひかり 1 女性 1987-06-19 31 279-0011 千葉県浦安市美浜********** S12029 20180810 0-00000000-0
CS034312000071 望月 奈央 1 女性 1980-09-20 38 213-0026 神奈川県川崎市高津区久末********** S14034 20160106 0-00000000-0

S-012: 店舗データ（store）から、住所 (address) に"横浜市"が含まれるものだけ全項目表示せよ。

In [14]:
%%sql

SELECT * FROM store WHERE address LIKE '%横浜市%';

 * postgresql://padawan:***@db:5432/dsdojo_db
11 rows affected.
Out[14]:
store_cd store_name prefecture_cd prefecture address address_kana tel_no longitude latitude floor_area
S14010 菊名店 14 神奈川県 神奈川県横浜市港北区菊名一丁目 カナガワケンヨコハマシコウホククキクナイッチョウメ 045-123-4032 139.6326 35.50049 1732.0
S14033 阿久和店 14 神奈川県 神奈川県横浜市瀬谷区阿久和西一丁目 カナガワケンヨコハマシセヤクアクワニシイッチョウメ 045-123-4043 139.4961 35.45918 1495.0
S14040 長津田店 14 神奈川県 神奈川県横浜市緑区長津田みなみ台五丁目 カナガワケンヨコハマシミドリクナガツタミナミダイゴチョウメ 045-123-4046 139.4994 35.52398 1548.0
S14050 阿久和西店 14 神奈川県 神奈川県横浜市瀬谷区阿久和西一丁目 カナガワケンヨコハマシセヤクアクワニシイッチョウメ 045-123-4053 139.4961 35.45918 1830.0
S14028 二ツ橋店 14 神奈川県 神奈川県横浜市瀬谷区二ツ橋町 カナガワケンヨコハマシセヤクフタツバシチョウ 045-123-4042 139.4963 35.46304 1574.0
S14012 本牧和田店 14 神奈川県 神奈川県横浜市中区本牧和田 カナガワケンヨコハマシナカクホンモクワダ 045-123-4034 139.6582 35.42156 1341.0
S14046 北山田店 14 神奈川県 神奈川県横浜市都筑区北山田一丁目 カナガワケンヨコハマシツヅキクキタヤマタイッチョウメ 045-123-4049 139.5916 35.56189 831.0
S14011 日吉本町店 14 神奈川県 神奈川県横浜市港北区日吉本町四丁目 カナガワケンヨコハマシコウホククヒヨシホンチョウヨンチョウメ 045-123-4033 139.6316 35.54655 890.0
S14048 中川中央店 14 神奈川県 神奈川県横浜市都筑区中川中央二丁目 カナガワケンヨコハマシツヅキクナカガワチュウオウニチョウメ 045-123-4051 139.5758 35.54912 1657.0
S14042 新山下店 14 神奈川県 神奈川県横浜市中区新山下二丁目 カナガワケンヨコハマシナカクシンヤマシタニチョウメ 045-123-4047 139.6593 35.43894 1044.0
S14006 葛が谷店 14 神奈川県 神奈川県横浜市都筑区葛が谷 カナガワケンヨコハマシツヅキククズガヤ 045-123-4031 139.5633 35.53573 1886.0

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

In [15]:
%%sql

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

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[15]:
customer_id customer_name gender_cd gender birth_day age postal_cd address application_store_cd application_date status_cd
CS031415000172 宇多田 貴美子 1 女性 1976-10-04 42 151-0053 東京都渋谷区代々木********** S13031 20150529 D-20100325-C
CS015414000103 奥野 陽子 1 女性 1977-08-09 41 136-0073 東京都江東区北砂********** S13015 20150722 B-20100609-B
CS011215000048 芦田 沙耶 1 女性 1992-02-01 27 223-0062 神奈川県横浜市港北区日吉本町********** S14011 20150228 C-20100421-9
CS029415000023 梅田 里穂 1 女性 1976-01-17 43 279-0043 千葉県浦安市富士見********** S12029 20150610 D-20100918-E
CS035415000029 寺沢 真希 9 不明 1977-09-27 41 158-0096 東京都世田谷区玉川台********** S13035 20141220 F-20101029-F
CS031415000106 宇野 由美子 1 女性 1970-02-26 49 151-0053 東京都渋谷区代々木********** S13031 20150201 F-20100511-E
CS029215000025 石倉 美帆 1 女性 1993-09-28 25 279-0022 千葉県浦安市今川********** S12029 20150708 B-20100820-C
CS033605000005 猪股 雄太 0 男性 1955-12-05 63 246-0031 神奈川県横浜市瀬谷区瀬谷********** S14033 20150425 F-20100917-E
CS033415000229 板垣 菜々美 1 女性 1977-11-07 41 246-0021 神奈川県横浜市瀬谷区二ツ橋町********** S14033 20150712 F-20100326-E
CS008415000145 黒谷 麻緒 1 女性 1977-06-27 41 157-0067 東京都世田谷区喜多見********** S13008 20150829 F-20100622-F

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

In [16]:
%%sql

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

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[16]:
customer_id customer_name gender_cd gender birth_day age postal_cd address application_store_cd application_date status_cd
CS001215000145 田崎 美紀 1 女性 1995-03-29 24 144-0055 東京都大田区仲六郷********** S13001 20170605 6-20090929-2
CS033513000180 安斎 遥 1 女性 1962-07-11 56 241-0823 神奈川県横浜市旭区善部町********** S14033 20150728 6-20080506-5
CS011215000048 芦田 沙耶 1 女性 1992-02-01 27 223-0062 神奈川県横浜市港北区日吉本町********** S14011 20150228 C-20100421-9
CS040412000191 川井 郁恵 1 女性 1977-01-05 42 226-0021 神奈川県横浜市緑区北八朔町********** S14040 20151101 1-20091025-4
CS009315000023 皆川 文世 1 女性 1980-04-15 38 154-0012 東京都世田谷区駒沢********** S13009 20150319 5-20080322-1
CS015315000033 福士 璃奈子 1 女性 1983-03-17 36 135-0043 東京都江東区塩浜********** S13015 20141024 4-20080219-3
CS023513000066 神戸 そら 1 女性 1961-12-17 57 210-0005 神奈川県川崎市川崎区東田町********** S14023 20150915 5-20100524-9
CS035513000134 市川 美帆 1 女性 1960-03-27 59 156-0053 東京都世田谷区桜********** S13035 20150227 8-20100711-9
CS001515000263 高松 夏空 1 女性 1962-11-09 56 144-0051 東京都大田区西蒲田********** S13001 20160812 1-20100804-1
CS040314000027 鶴田 きみまろ 9 不明 1986-03-26 33 226-0027 神奈川県横浜市緑区長津田********** S14040 20150122 2-20080426-4

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

In [17]:
%%sql

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

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[17]:
customer_id customer_name gender_cd gender birth_day age postal_cd address application_store_cd application_date status_cd
CS011215000048 芦田 沙耶 1 女性 1992-02-01 27 223-0062 神奈川県横浜市港北区日吉本町********** S14011 20150228 C-20100421-9
CS022513000105 島村 貴美子 1 女性 1962-03-12 57 249-0002 神奈川県逗子市山の根********** S14022 20150320 A-20091115-7
CS001515000096 水野 陽子 9 不明 1960-11-29 58 144-0053 東京都大田区蒲田本町********** S13001 20150614 A-20100724-7
CS013615000053 西脇 季衣 1 女性 1953-10-18 65 261-0026 千葉県千葉市美浜区幕張西********** S12013 20150128 B-20100329-6
CS020412000161 小宮 薫 1 女性 1974-05-21 44 174-0042 東京都板橋区東坂下********** S13020 20150822 B-20081021-3
CS001215000097 竹中 あさみ 1 女性 1990-07-25 28 146-0095 東京都大田区多摩川********** S13001 20170315 A-20100211-2
CS035212000007 内村 恵梨香 1 女性 1990-12-04 28 152-0023 東京都目黒区八雲********** S13035 20151013 B-20101018-6
CS002515000386 野田 コウ 1 女性 1963-05-30 55 185-0013 東京都国分寺市西恋ケ窪********** S13002 20160410 C-20100127-8
CS001615000372 稲垣 寿々花 1 女性 1956-10-29 62 144-0035 東京都大田区南蒲田********** S13001 20170403 A-20100104-1
CS032512000121 松井 知世 1 女性 1962-09-04 56 210-0011 神奈川県川崎市川崎区富士見********** S13032 20150727 A-20100103-5

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

In [18]:
%%sql

SELECT * FROM store WHERE tel_no ~ '^[0-9]{3}-[0-9]{3}-[0-9]{4}$';

 * postgresql://padawan:***@db:5432/dsdojo_db
34 rows affected.
Out[18]:
store_cd store_name prefecture_cd prefecture address address_kana tel_no longitude latitude floor_area
S12014 千草台店 12 千葉県 千葉県千葉市稲毛区千草台一丁目 チバケンチバシイナゲクチグサダイイッチョウメ 043-123-4003 140.118 35.63559 1698.0
S13002 国分寺店 13 東京都 東京都国分寺市本多二丁目 トウキョウトコクブンジシホンダニチョウメ 042-123-4008 139.4802 35.70566 1735.0
S14010 菊名店 14 神奈川県 神奈川県横浜市港北区菊名一丁目 カナガワケンヨコハマシコウホククキクナイッチョウメ 045-123-4032 139.6326 35.50049 1732.0
S14033 阿久和店 14 神奈川県 神奈川県横浜市瀬谷区阿久和西一丁目 カナガワケンヨコハマシセヤクアクワニシイッチョウメ 045-123-4043 139.4961 35.45918 1495.0
S14036 相模原中央店 14 神奈川県 神奈川県相模原市中央二丁目 カナガワケンサガミハラシチュウオウニチョウメ 042-123-4045 139.3716 35.57327 1679.0
S14040 長津田店 14 神奈川県 神奈川県横浜市緑区長津田みなみ台五丁目 カナガワケンヨコハマシミドリクナガツタミナミダイゴチョウメ 045-123-4046 139.4994 35.52398 1548.0
S14050 阿久和西店 14 神奈川県 神奈川県横浜市瀬谷区阿久和西一丁目 カナガワケンヨコハマシセヤクアクワニシイッチョウメ 045-123-4053 139.4961 35.45918 1830.0
S13052 森野店 13 東京都 東京都町田市森野三丁目 トウキョウトマチダシモリノサンチョウメ 042-123-4030 139.4383 35.55293 1087.0
S14028 二ツ橋店 14 神奈川県 神奈川県横浜市瀬谷区二ツ橋町 カナガワケンヨコハマシセヤクフタツバシチョウ 045-123-4042 139.4963 35.46304 1574.0
S14012 本牧和田店 14 神奈川県 神奈川県横浜市中区本牧和田 カナガワケンヨコハマシナカクホンモクワダ 045-123-4034 139.6582 35.42156 1341.0
S14046 北山田店 14 神奈川県 神奈川県横浜市都筑区北山田一丁目 カナガワケンヨコハマシツヅキクキタヤマタイッチョウメ 045-123-4049 139.5916 35.56189 831.0
S14022 逗子店 14 神奈川県 神奈川県逗子市逗子一丁目 カナガワケンズシシズシイッチョウメ 046-123-4036 139.5789 35.29642 1838.0
S14011 日吉本町店 14 神奈川県 神奈川県横浜市港北区日吉本町四丁目 カナガワケンヨコハマシコウホククヒヨシホンチョウヨンチョウメ 045-123-4033 139.6316 35.54655 890.0
S13016 小金井店 13 東京都 東京都小金井市本町一丁目 トウキョウトコガネイシホンチョウイッチョウメ 042-123-4015 139.5094 35.70018 1399.0
S14034 川崎野川店 14 神奈川県 神奈川県川崎市宮前区野川 カナガワケンカワサキシミヤマエクノガワ 044-123-4044 139.5998 35.57693 1318.0
S14048 中川中央店 14 神奈川県 神奈川県横浜市都筑区中川中央二丁目 カナガワケンヨコハマシツヅキクナカガワチュウオウニチョウメ 045-123-4051 139.5758 35.54912 1657.0
S12007 佐倉店 12 千葉県 千葉県佐倉市上志津 チバケンサクラシカミシヅ 043-123-4001 140.1452 35.71872 1895.0
S14026 辻堂西海岸店 14 神奈川県 神奈川県藤沢市辻堂西海岸二丁目 カナガワケンフジサワシツジドウニシカイガンニチョウメ 046-123-4040 139.4466 35.32464 1732.0
S13041 八王子店 13 東京都 東京都八王子市大塚 トウキョウトハチオウジシオオツカ 042-123-4026 139.4235 35.63787 810.0
S14049 川崎大師店 14 神奈川県 神奈川県川崎市川崎区中瀬三丁目 カナガワケンカワサキシカワサキクナカゼサンチョウメ 044-123-4052 139.7327 35.53759 962.0
S14023 川崎店 14 神奈川県 神奈川県川崎市川崎区本町二丁目 カナガワケンカワサキシカワサキクホンチョウニチョウメ 044-123-4037 139.7028 35.53599 1804.0
S13018 清瀬店 13 東京都 東京都清瀬市松山一丁目 トウキョウトキヨセシマツヤマイッチョウメ 042-123-4017 139.5178 35.76885 1220.0
S14027 南藤沢店 14 神奈川県 神奈川県藤沢市南藤沢 カナガワケンフジサワシミナミフジサワ 046-123-4041 139.4896 35.33762 1521.0
S14021 伊勢原店 14 神奈川県 神奈川県伊勢原市伊勢原四丁目 カナガワケンイセハラシイセハラヨンチョウメ 046-123-4035 139.3129 35.40169 962.0
S14047 相模原店 14 神奈川県 神奈川県相模原市千代田六丁目 カナガワケンサガミハラシチヨダロクチョウメ 042-123-4050 139.3748 35.55959 1047.0
S12013 習志野店 12 千葉県 千葉県習志野市芝園一丁目 チバケンナラシノシシバゾノイッチョウメ 047-123-4002 140.022 35.66122 808.0
S14042 新山下店 14 神奈川県 神奈川県横浜市中区新山下二丁目 カナガワケンヨコハマシナカクシンヤマシタニチョウメ 045-123-4047 139.6593 35.43894 1044.0
S12030 八幡店 12 千葉県 千葉県市川市八幡三丁目 チバケンイチカワシヤワタサンチョウメ 047-123-4005 139.924 35.72318 1162.0
S14025 大和店 14 神奈川県 神奈川県大和市下和田 カナガワケンヤマトシシモワダ 046-123-4039 139.468 35.43414 1011.0
S14045 厚木店 14 神奈川県 神奈川県厚木市中町二丁目 カナガワケンアツギシナカチョウニチョウメ 046-123-4048 139.3651 35.44182 980.0
S12029 東野店 12 千葉県 千葉県浦安市東野一丁目 チバケンウラヤスシヒガシノイッチョウメ 047-123-4004 139.8968 35.65086 1101.0
S12053 高洲店 12 千葉県 千葉県浦安市高洲五丁目 チバケンウラヤスシタカスゴチョウメ 047-123-4006 139.9176 35.63755 1555.0
S14024 三田店 14 神奈川県 神奈川県川崎市多摩区三田四丁目 カナガワケンカワサキシタマクミタヨンチョウメ 044-123-4038 139.5424 35.6077 972.0
S14006 葛が谷店 14 神奈川県 神奈川県横浜市都筑区葛が谷 カナガワケンヨコハマシツヅキククズガヤ 045-123-4031 139.5633 35.53573 1886.0

S-017: 顧客データ（customer）を生年月日（birth_day）で高齢順にソートし、先頭から全項目を10件表示せよ。

In [19]:
%%sql

SELECT * FROM customer ORDER BY birth_day LIMIT 10;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[19]:
customer_id customer_name gender_cd gender birth_day age postal_cd address application_store_cd application_date status_cd
CS003813000014 村山 菜々美 1 女性 1928-11-26 90 182-0007 東京都調布市菊野台********** S13003 20160214 0-00000000-0
CS026813000004 吉村 朝陽 1 女性 1928-12-14 90 251-0043 神奈川県藤沢市辻堂元町********** S14026 20150723 0-00000000-0
CS018811000003 熊沢 美里 1 女性 1929-01-07 90 204-0004 東京都清瀬市野塩********** S13018 20150403 0-00000000-0
CS027803000004 内村 拓郎 0 男性 1929-01-12 90 251-0031 神奈川県藤沢市鵠沼藤が谷********** S14027 20151227 0-00000000-0
CS013801000003 天野 拓郎 0 男性 1929-01-15 90 274-0824 千葉県船橋市前原東********** S12013 20160120 0-00000000-0
CS001814000022 鶴田 里穂 1 女性 1929-01-28 90 144-0045 東京都大田区南六郷********** S13001 20161012 A-20090415-7
CS016815000002 山元 美紀 1 女性 1929-02-22 90 184-0005 東京都小金井市桜町********** S13016 20150629 C-20090923-C
CS009815000003 中田 里穂 1 女性 1929-04-08 89 154-0014 東京都世田谷区新町********** S13009 20150421 D-20091021-E
CS012813000013 宇野 南朋 1 女性 1929-04-09 89 231-0806 神奈川県横浜市中区本牧町********** S14012 20150712 0-00000000-0
CS005813000015 金谷 恵梨香 1 女性 1929-04-09 89 165-0032 東京都中野区鷺宮********** S13005 20150506 0-00000000-0

S-018: 顧客データ（customer）を生年月日（birth_day）で若い順にソートし、先頭から全項目を10件表示せよ。

In [20]:
%%sql

SELECT * FROM customer ORDER BY birth_day DESC LIMIT 10;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[20]:
customer_id customer_name gender_cd gender birth_day age postal_cd address application_store_cd application_date status_cd
CS035114000004 大村 美里 1 女性 2007-11-25 11 156-0053 東京都世田谷区桜********** S13035 20150619 6-20091205-6
CS022103000002 福山 はじめ 9 不明 2007-10-02 11 249-0006 神奈川県逗子市逗子********** S14022 20160909 0-00000000-0
CS002113000009 柴田 真悠子 1 女性 2007-09-17 11 184-0014 東京都小金井市貫井南町********** S13002 20160304 0-00000000-0
CS004115000014 松井 京子 1 女性 2007-08-09 11 165-0031 東京都中野区上鷺宮********** S13004 20161120 1-20081231-1
CS002114000010 山内 遥 1 女性 2007-06-03 11 184-0015 東京都小金井市貫井北町********** S13002 20160920 6-20100510-1
CS025115000002 小柳 夏希 1 女性 2007-04-18 11 245-0018 神奈川県横浜市泉区上飯田町********** S14025 20160116 D-20100913-D
CS002113000025 広末 まなみ 1 女性 2007-03-30 12 184-0015 東京都小金井市貫井北町********** S13002 20171030 0-00000000-0
CS033112000003 長野 美紀 1 女性 2007-03-22 12 245-0051 神奈川県横浜市戸塚区名瀬町********** S14033 20150606 0-00000000-0
CS007115000006 福岡 瞬 1 女性 2007-03-10 12 285-0845 千葉県佐倉市西志津********** S12007 20151118 F-20101016-F
CS014113000008 矢口 莉緒 1 女性 2007-03-05 12 260-0041 千葉県千葉市中央区東千葉********** S12014 20150622 3-20091108-6

S-019: レシート明細データ（receipt）に対し、1件あたりの売上金額（amount）が高い順にランクを付与し、先頭から10件表示せよ。項目は顧客ID（customer_id）、売上金額（amount）、付与したランクを表示させること。なお、売上金額（amount）が等しい場合は同一順位を付与するものとする。

In [21]:
%%sql

SELECT
    customer_id,
    amount,
    RANK() OVER(ORDER BY amount DESC) AS ranking 
FROM receipt
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[21]:
customer_id amount ranking
CS011415000006 10925 1
ZZ000000000000 6800 2
CS028605000002 5780 3
ZZ000000000000 5480 4
ZZ000000000000 5480 4
CS015515000034 5480 4
ZZ000000000000 5440 7
CS021515000089 5440 7
CS020414000081 5280 9
ZZ000000000000 5280 9

S-020: レシート明細データ（receipt）に対し、1件あたりの売上金額（amount）が高い順にランクを付与し、先頭から10件表示せよ。項目は顧客ID（customer_id）、売上金額（amount）、付与したランクを表示させること。なお、売上金額（amount）が等しい場合でも別順位を付与すること。

In [22]:
%%sql

SELECT
    customer_id,
    amount,
    ROW_NUMBER() OVER(ORDER BY amount DESC) AS ranking 
FROM receipt
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[22]:
customer_id amount ranking
CS011415000006 10925 1
ZZ000000000000 6800 2
CS028605000002 5780 3
ZZ000000000000 5480 4
CS015515000034 5480 5
ZZ000000000000 5480 6
ZZ000000000000 5440 7
CS021515000089 5440 8
CS001412000160 5280 9
CS020515000102 5280 10

S-021: レシート明細データ（receipt）に対し、件数をカウントせよ。

In [23]:
%%sql
-- コード例1
SELECT COUNT(1) FROM receipt;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.
Out[23]:
count
104681
In [24]:
%%sql
-- コード例2（*でもOK）
SELECT COUNT(*) FROM receipt;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.
Out[24]:
count
104681

S-022: レシート明細データ（receipt）の顧客ID（customer_id）に対し、ユニーク件数をカウントせよ。

In [25]:
%%sql

SELECT
    COUNT(DISTINCT customer_id)
FROM receipt
;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.
Out[25]:
count
8307

S-023: レシート明細データ（receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）と売上数量（quantity）を合計せよ。

In [26]:
%%sql

SELECT store_cd
    , SUM(amount) AS amount
    , SUM(quantity) AS quantity
FROM receipt
group by store_cd
;

 * postgresql://padawan:***@db:5432/dsdojo_db
52 rows affected.
Out[26]:
store_cd amount quantity
S12007 638761 2099
S13017 748221 2376
S13043 587895 1881
S13052 100314 250
S13016 793773 2432
S14027 714550 2303
S13009 808870 2486
S14022 651328 2047
S13019 827833 2541
S13039 611888 1981
S14046 412646 1354
S13003 764294 2197
S14028 786145 2458
S14045 458484 1398
S13002 727821 2340
S14042 534689 1935
S13004 779373 2390
S13038 708884 2337
S12014 725167 2358
S14021 699511 2231
S14006 712839 2284
S13001 811936 2347
S14023 727630 2258
S14025 755581 2394
S13035 715869 2219
S14048 234276 769
S14012 720600 2412
S14024 736323 2417
S13041 728266 2233
S14026 824537 2503
S14010 790361 2290
S13015 780873 2248
S13008 809288 2491
S14049 230808 788
S14050 167090 580
S13031 705968 2336
S12013 787513 2425
S13044 520764 1729
S14033 725318 2282
S12029 794741 2555
S13037 693087 2344
S12030 684402 2403
S13032 790501 2491
S14034 653681 2024
S14040 701858 2233
S13005 629876 2004
S14047 338329 1041
S14011 805724 2434
S13018 790535 2562
S13020 796383 2383
S13051 107452 354
S14036 203694 635

S-024: レシート明細データ（receipt）に対し、顧客ID（customer_id）ごとに最も新しい売上年月日（sales_ymd）を求め、10件表示せよ。

In [27]:
%%sql

SELECT
    customer_id,
    MAX(sales_ymd)
FROM receipt
GROUP BY customer_id
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[27]:
customer_id max
CS001311000059 20180211
CS004614000122 20181228
CS003512000043 20180106
CS011615000061 20190503
CS029212000033 20180621
CS007515000119 20190511
CS034515000123 20190708
CS004315000058 20170517
CS026414000014 20190720
CS001615000099 20170729

S-025: レシート明細データ（receipt）に対し、顧客ID（customer_id）ごとに最も古い売上年月日（sales_ymd）を求め、10件表示せよ。

In [28]:
%%sql

SELECT
    customer_id,
    MIN(sales_ymd)
FROM receipt
GROUP BY customer_id
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[28]:
customer_id min
CS001311000059 20180211
CS004614000122 20181228
CS003512000043 20180106
CS011615000061 20190503
CS029212000033 20170318
CS007515000119 20170201
CS034515000123 20170527
CS004315000058 20170517
CS026414000014 20170718
CS001615000099 20170729

S-026: レシート明細データ（receipt）に対し、顧客ID（customer_id）ごとに最も新しい売上年月日（sales_ymd）と古い売上年月日を求め、両者が異なるデータを10件表示せよ。

In [29]:
%%sql

SELECT
    customer_id,
    MAX(sales_ymd),
    MIN(sales_ymd)
FROM receipt
GROUP BY customer_id
HAVING MAX(sales_ymd) != MIN(sales_ymd)
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[29]:
customer_id max min
CS029212000033 20180621 20170318
CS007515000119 20190511 20170201
CS034515000123 20190708 20170527
CS026414000014 20190720 20170718
CS010515000082 20181204 20180518
CS019315000045 20170920 20170423
CS008513000099 20190308 20170722
CS007615000070 20191025 20170929
CS025415000155 20191026 20170314
CS016414000063 20190617 20170109

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

In [30]:
%%sql

SELECT
    store_cd,
    AVG(amount) AS avg_amount
FROM receipt
GROUP BY store_cd
ORDER BY avg_amount DESC
LIMIT 5
;

 * postgresql://padawan:***@db:5432/dsdojo_db
5 rows affected.
Out[30]:
store_cd avg_amount
S13052 402.8674698795180723
S13015 351.1119604316546763
S13003 350.9155188246097337
S14010 348.7912621359223301
S13001 348.4703862660944206

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

In [31]:
%%sql

SELECT 
    store_cd, 
    PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY amount) AS amount_50per
FROM receipt
GROUP BY store_cd
ORDER BY amount_50per DESC
LIMIT 5
;

 * postgresql://padawan:***@db:5432/dsdojo_db
5 rows affected.
Out[31]:
store_cd amount_50per
S13052 190.0
S14010 188.0
S14050 185.0
S13003 180.0
S13018 180.0

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

In [32]:
%%sql

-- コード例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
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[32]:
store_cd product_cd mode_cnt
S12007 P060303001 72
S12013 P060303001 107
S12014 P060303001 65
S12029 P060303001 92
S12030 P060303001 115
S13001 P060303001 67
S13002 P060303001 78
S13003 P071401001 65
S13004 P060303001 88
S13005 P040503001 36
In [33]:
%%sql

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

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[33]:
store_cd mode
S12007 P060303001
S12013 P060303001
S12014 P060303001
S12029 P060303001
S12030 P060303001
S13001 P060303001
S13002 P060303001
S13003 P071401001
S13004 P060303001
S13005 P040503001

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

In [34]:
%%sql

SELECT
    store_cd,
    VAR_POP(amount) AS vars_amount
FROM receipt
GROUP BY store_cd
ORDER BY vars_amount DESC 
LIMIT 5
;

 * postgresql://padawan:***@db:5432/dsdojo_db
5 rows affected.
Out[34]:
store_cd vars_amount
S13052 440088.701311269173
S14011 306314.558163888889
S14034 296920.081011283873
S13001 295431.993329035348
S13015 295294.361115940880

S-031: レシート明細データ（receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）の標準偏差を計算し、降順で5件表示せよ。

In [35]:
%%sql

SELECT
    store_cd,
    STDDEV_POP(amount) as stds_amount
FROM receipt
GROUP BY store_cd
ORDER BY stds_amount DESC
LIMIT 5
;

 * postgresql://padawan:***@db:5432/dsdojo_db
5 rows affected.
Out[35]:
store_cd stds_amount
S13052 663.391815830787
S14011 553.456916267101
S14034 544.903735545357
S13001 543.536561170484
S13015 543.409938366921

S-032: レシート明細データ（receipt）の売上金額（amount）について、25％刻みでパーセンタイル値を求めよ。

In [36]:
%%sql

SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY amount) AS amount_25per,
    PERCENTILE_CONT(0.50) WITHIN GROUP(ORDER BY amount) AS amount_50per,
    PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY amount) AS amount_75per,
    PERCENTILE_CONT(1.0) WITHIN GROUP(ORDER BY amount) AS amount_100per
FROM receipt
;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.
Out[36]:
amount_25per amount_50per amount_75per amount_100per
102.0 170.0 288.0 10925.0

S-033: レシート明細データ（receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）の平均を計算し、330以上のものを抽出せよ。

In [37]:
%%sql

SELECT
    store_cd,
    AVG(amount) AS avg_amount
FROM receipt
GROUP BY store_cd
HAVING
    AVG(amount) >= 330
;

 * postgresql://padawan:***@db:5432/dsdojo_db
13 rows affected.
Out[37]:
store_cd avg_amount
S13052 402.8674698795180723
S13019 330.2086158755484643
S13003 350.9155188246097337
S14045 330.0820734341252700
S13004 330.9439490445859873
S13001 348.4703862660944206
S14026 332.3405884723901653
S14010 348.7912621359223301
S13015 351.1119604316546763
S12013 330.1941299790356394
S14047 330.0770731707317073
S14011 335.7183333333333333
S13020 337.8799321170980059

S-034: レシート明細データ（receipt）に対し、顧客ID（customer_id）ごとに売上金額（amount）を合計して全顧客の平均を求めよ。ただし、顧客IDが"Z"から始まるものは非会員を表すため、除外して計算すること。

In [38]:
%%sql

WITH customer_amount AS (
    SELECT
        customer_id,
        SUM(amount) AS sum_amount
    FROM receipt
    WHERE
        customer_id NOT LIKE 'Z%'
    GROUP BY customer_id
)
SELECT
    AVG(sum_amount)
FROM customer_amount
;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.
Out[38]:
avg
2547.7422345292559595

S-035: レシート明細データ（receipt）に対し、顧客ID（customer_id）ごとに売上金額（amount）を合計して全顧客の平均を求め、平均以上に買い物をしている顧客を抽出し、10件表示せよ。ただし、顧客IDが"Z"から始まるものは非会員を表すため、除外して計算すること。

In [39]:
%%sql

WITH customer_amount AS (
    SELECT
        customer_id,
        SUM(amount) AS sum_amount
    FROM receipt
    WHERE
        customer_id NOT LIKE 'Z%'
    GROUP BY customer_id
)
SELECT
    customer_id,
    sum_amount
FROM customer_amount
WHERE
    sum_amount >= (
        SELECT
            AVG(sum_amount)
        FROM customer_amount
    )
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[39]:
customer_id sum_amount
CS029212000033 3604
CS007515000119 7157
CS034515000123 3699
CS026414000014 6671
CS007615000070 2975
CS016414000063 6207
CS012514000018 2562
CS029515000142 3420
CS015215000021 3090
CS039814000011 8031

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

In [40]:
%%sql

SELECT
    r.*,
    s.store_name
FROM receipt r
JOIN store s
ON
    r.store_cd = s.store_cd
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[40]:
sales_ymd sales_epoch store_cd receipt_no receipt_sub_no customer_id product_cd quantity amount store_name
20181103 1541203200 S14006 112 1 CS006214000001 P070305012 1 158 葛が谷店
20181118 1542499200 S13008 1132 2 CS008415000097 P070701017 1 81 成城店
20170712 1499817600 S14028 1102 1 CS028414000014 P060101005 1 170 二ツ橋店
20190205 1549324800 S14042 1132 1 ZZ000000000000 P050301001 1 25 新山下店
20180821 1534809600 S14025 1102 2 CS025415000050 P060102007 1 90 大和店
20190605 1559692800 S13003 1112 1 CS003515000195 P050102002 1 138 狛江店
20181205 1543968000 S14024 1102 2 CS024514000042 P080101005 1 30 三田店
20190922 1569110400 S14040 1102 1 CS040415000178 P070501004 1 128 長津田店
20170504 1493856000 S13020 1112 2 ZZ000000000000 P071302010 1 770 十条仲原店
20191010 1570665600 S14027 1102 1 CS027514000015 P071101003 1 680 南藤沢店

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

In [41]:
%%sql

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

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[41]:
product_cd category_major_cd category_medium_cd category_small_cd unit_price unit_cost category_small_name
P040101001 04 0401 040101 198 149 弁当類
P040101002 04 0401 040101 218 164 弁当類
P040101003 04 0401 040101 230 173 弁当類
P040101004 04 0401 040101 248 186 弁当類
P040101005 04 0401 040101 268 201 弁当類
P040101006 04 0401 040101 298 224 弁当類
P040101007 04 0401 040101 338 254 弁当類
P040101008 04 0401 040101 420 315 弁当類
P040101009 04 0401 040101 498 374 弁当類
P040101010 04 0401 040101 580 435 弁当類

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

In [42]:
%%sql

WITH customer_amount AS (
    SELECT
        customer_id,
        SUM(amount) AS sum_amount
    FROM receipt
    GROUP BY
        customer_id
),
customer_data AS (
    SELECT
        customer_id
    FROM customer
    WHERE
        gender_cd = '1'
        AND customer_id NOT LIKE 'Z%'
)
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
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[42]:
customer_id coalesce
CS021313000114 0
CS031415000172 5088
CS028811000001 0
CS001215000145 875
CS015414000103 3122
CS033513000180 868
CS035614000014 0
CS011215000048 3444
CS009413000079 0
CS040412000191 210

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

In [43]:
%%sql

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
;

 * postgresql://padawan:***@db:5432/dsdojo_db
34 rows affected.
Out[43]:
customer_id come_days buy_amount
CS040214000008 23 None
CS015415000185 22 20153
CS010214000010 22 18585
CS028415000007 21 19127
CS010214000002 21 None
CS017415000097 20 23086
CS016415000141 20 18372
CS021514000045 19 None
CS022515000226 19 None
CS031414000051 19 19202
CS039414000052 19 None
CS014214000023 19 None
CS021515000172 19 None
CS031414000073 18 None
CS007515000107 18 None
CS014415000077 18 None
CS021515000056 18 None
CS032415000209 18 None
CS021515000211 18 None
CS022515000028 18 None
CS011415000006 None 16094
CS016415000101 None 16348
CS030415000034 None 15468
CS021515000089 None 17580
CS034415000047 None 16083
CS006515000023 None 18372
CS038415000104 None 17847
CS015515000034 None 15300
CS032414000072 None 16563
CS011414000106 None 18338
CS001605000009 None 18925
CS009414000059 None 15492
CS035414000024 None 17615
CS007514000094 None 15735

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

In [44]:
%%sql

SELECT
    COUNT(1)
FROM store
CROSS JOIN product
;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.
Out[44]:
count
531590

S-041: レシート明細データ（receipt）の売上金額（amount）を日付（sales_ymd）ごとに集計し、前回売上があった日からの売上金額増減を計算せよ。そして結果を10件表示せよ。

In [45]:
%%sql

WITH sales_amount_by_date AS (
    SELECT
        sales_ymd,
        SUM(amount) AS amount
    FROM receipt
    GROUP BY
        sales_ymd
),
sales_amount_by_date_with_lag as (
    SELECT
        sales_ymd,
        LAG(sales_ymd, 1) OVER(ORDER BY sales_ymd) lag_ymd,
        amount,
        LAG(amount, 1) OVER(ORDER BY sales_ymd) AS lag_amount
    FROM sales_amount_by_date
)
SELECT
    sales_ymd,
    amount,
    lag_ymd,
    lag_amount,
    amount - lag_amount AS diff_amount
FROM sales_amount_by_date_with_lag
ORDER BY
    sales_ymd
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[45]:
sales_ymd amount lag_ymd lag_amount diff_amount
20170101 33723 None None None
20170102 24165 20170101 33723 -9558
20170103 27503 20170102 24165 3338
20170104 36165 20170103 27503 8662
20170105 37830 20170104 36165 1665
20170106 32387 20170105 37830 -5443
20170107 23415 20170106 32387 -8972
20170108 24737 20170107 23415 1322
20170109 26718 20170108 24737 1981
20170110 20143 20170109 26718 -6575

S-042: レシート明細データ（receipt）の売上金額（amount）を日付（sales_ymd）ごとに集計し、各日付のデータに対し、前回、前々回、3回前に売上があった日のデータを結合せよ。そして結果を10件表示せよ。

In [46]:
%%sql

-- コード例1:縦持ちケース
WITH sales_amount_by_date AS (
    SELECT
        sales_ymd,
        SUM(amount) AS amount
    FROM receipt
    GROUP BY
        sales_ymd
),
sales_amount_lag_date AS (
    SELECT
        sales_ymd,
        LAG(sales_ymd, 3) OVER (ORDER BY sales_ymd) AS lag_date_3,
        amount
    FROM sales_amount_by_date
)
SELECT
    a.sales_ymd,
    a.amount,
    b.sales_ymd AS lag_ymd,
    b.amount AS lag_amount
FROM sales_amount_lag_date a
JOIN sales_amount_lag_date b
ON
    (
        a.lag_date_3 IS NULL
        OR a.lag_date_3 <= b.sales_ymd
    )
    AND b.sales_ymd < a.sales_ymd
ORDER BY
    sales_ymd,
    lag_ymd
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[46]:
sales_ymd amount lag_ymd lag_amount
20170102 24165 20170101 33723
20170103 27503 20170101 33723
20170103 27503 20170102 24165
20170104 36165 20170101 33723
20170104 36165 20170102 24165
20170104 36165 20170103 27503
20170105 37830 20170102 24165
20170105 37830 20170103 27503
20170105 37830 20170104 36165
20170106 32387 20170103 27503
In [47]:
%%sql

-- コード例2:横持ちケース
WITH sales_amount_by_date AS (
    SELECT
        sales_ymd,
        SUM(amount) AS amount
    FROM receipt
    GROUP BY
        sales_ymd
),
sales_amount_with_lag AS (
    SELECT
        sales_ymd,
        amount, 
        LAG(sales_ymd, 1) OVER (ORDER BY sales_ymd) AS lag_ymd_1,
        LAG(amount, 1) OVER (ORDER BY sales_ymd) AS lag_amount_1,
        LAG(sales_ymd, 2) OVER (ORDER BY sales_ymd) AS lag_ymd_2,
        LAG(amount, 2) OVER (ORDER BY sales_ymd) AS lag_amount_2,
        LAG(sales_ymd, 3) OVER (ORDER BY sales_ymd) AS lag_ymd_3,
        LAG(amount, 3) OVER (ORDER BY sales_ymd) AS lag_amount_3
    FROM sales_amount_by_date
)
SELECT
    sales_ymd,
    amount, 
    lag_ymd_1,
    lag_amount_1,
    lag_ymd_2,
    lag_amount_2,
    lag_ymd_3,
    lag_amount_3
FROM sales_amount_with_lag
WHERE
    lag_ymd_3 IS NOT NULL
ORDER BY
    sales_ymd
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[47]:
sales_ymd amount lag_ymd_1 lag_amount_1 lag_ymd_2 lag_amount_2 lag_ymd_3 lag_amount_3
20170104 36165 20170103 27503 20170102 24165 20170101 33723
20170105 37830 20170104 36165 20170103 27503 20170102 24165
20170106 32387 20170105 37830 20170104 36165 20170103 27503
20170107 23415 20170106 32387 20170105 37830 20170104 36165
20170108 24737 20170107 23415 20170106 32387 20170105 37830
20170109 26718 20170108 24737 20170107 23415 20170106 32387
20170110 20143 20170109 26718 20170108 24737 20170107 23415
20170111 24287 20170110 20143 20170109 26718 20170108 24737
20170112 23526 20170111 24287 20170110 20143 20170109 26718
20170113 28004 20170112 23526 20170111 24287 20170110 20143

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

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

In [48]:
%%sql

DROP TABLE IF EXISTS sales_summary;

CREATE TABLE sales_summary AS
    WITH gender_era_amount AS (
        SELECT
            TRUNC(age / 10) * 10 AS era,
            c.gender_cd,
            SUM(r.amount) AS amount
        FROM customer c
        JOIN receipt r
        ON
            c.customer_id = r.customer_id
        GROUP BY
            era,
            c.gender_cd
    )
    SELECT
        era,
        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
;

 * postgresql://padawan:***@db:5432/dsdojo_db
Done.
9 rows affected.
9 rows affected.
Out[48]:
era male female unknown
10.0 1591 149836 4317
20.0 72940 1363724 44328
30.0 177322 693047 50441
40.0 19355 9320791 483512
50.0 54320 6685192 342923
60.0 272469 987741 71418
70.0 13435 29764 2427
80.0 46360 262923 5111
90.0 None 6260 None

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

In [49]:
%%sql

-- SQL向きではないため、やや強引に記載する（カテゴリ数が多いときはとても長い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
;

 * postgresql://padawan:***@db:5432/dsdojo_db
27 rows affected.
Out[49]:
era gender_cd amount
10.0 00 1591
20.0 00 72940
30.0 00 177322
40.0 00 19355
50.0 00 54320
60.0 00 272469
70.0 00 13435
80.0 00 46360
90.0 00 None
10.0 01 149836
20.0 01 1363724
30.0 01 693047
40.0 01 9320791
50.0 01 6685192
60.0 01 987741
70.0 01 29764
80.0 01 262923
90.0 01 6260
10.0 99 4317
20.0 99 44328
30.0 99 50441
40.0 99 483512
50.0 99 342923
60.0 99 71418
70.0 99 2427
80.0 99 5111
90.0 99 None

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

In [50]:
%%sql

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

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[50]:
customer_id birth_day
CS021313000114 19810429
CS037613000071 19520401
CS031415000172 19761004
CS028811000001 19330327
CS001215000145 19950329
CS020401000016 19740915
CS015414000103 19770809
CS029403000008 19730817
CS015804000004 19310502
CS033513000180 19620711

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

In [51]:
%%sql

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

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[51]:
customer_id application_date
CS021313000114 2015-09-05
CS037613000071 2015-04-14
CS031415000172 2015-05-29
CS028811000001 2016-01-15
CS001215000145 2017-06-05
CS020401000016 2015-02-25
CS015414000103 2015-07-22
CS029403000008 2015-05-15
CS015804000004 2015-06-07
CS033513000180 2015-07-28

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

In [52]:
%%sql

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

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[52]:
receipt_no receipt_sub_no sales_ymd
112 1 2018-11-03
1132 2 2018-11-18
1102 1 2017-07-12
1132 1 2019-02-05
1102 2 2018-08-21
1112 1 2019-06-05
1102 2 2018-12-05
1102 1 2019-09-22
1112 2 2017-05-04
1102 1 2019-10-10

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

In [53]:
%%sql

SELECT 
    receipt_no,
    receipt_sub_no,
    CAST(TO_TIMESTAMP(sales_epoch) AS DATE) AS sales_ymd
FROM receipt 
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[53]:
receipt_no receipt_sub_no sales_ymd
112 1 2018-11-03
1132 2 2018-11-18
1102 1 2017-07-12
1132 1 2019-02-05
1102 2 2018-08-21
1112 1 2019-06-05
1102 2 2018-12-05
1102 1 2019-09-22
1112 2 2017-05-04
1102 1 2019-10-10

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

In [54]:
%%sql

SELECT 
    receipt_no, 
    receipt_sub_no,
    EXTRACT(YEAR FROM TO_TIMESTAMP(sales_epoch)) AS sales_year
FROM receipt 
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[54]:
receipt_no receipt_sub_no sales_year
112 1 2018
1132 2 2018
1102 1 2017
1132 1 2019
1102 2 2018
1112 1 2019
1102 2 2018
1102 1 2019
1112 2 2017
1102 1 2019

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

In [55]:
%%sql

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

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[55]:
receipt_no receipt_sub_no sales_month
112 1 11
1132 2 11
1102 1 07
1132 1 02
1102 2 08
1112 1 06
1102 2 12
1102 1 09
1112 2 05
1102 1 10

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

In [56]:
%%sql

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

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[56]:
receipt_no receipt_sub_no sales_day
112 1 03
1132 2 18
1102 1 12
1132 1 05
1102 2 21
1112 1 05
1102 2 05
1102 1 22
1112 2 04
1102 1 10

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

In [57]:
%%sql

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
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[57]:
customer_id sum_amount sales_flg
CS001311000059 2302 1
CS004614000122 248 0
CS003512000043 298 0
CS011615000061 246 0
CS029212000033 3604 1
CS007515000119 7157 1
CS034515000123 3699 1
CS004315000058 490 0
CS026414000014 6671 1
CS001615000099 768 0

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

In [58]:
%%sql

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 DISTINCT
        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
;

 * postgresql://padawan:***@db:5432/dsdojo_db
2 rows affected.
Out[58]:
postal_flg customer_cnt
0 3906
1 4400

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

In [59]:
%%sql

-- SQL向きではないため、やや強引に記載する（カテゴリ数が多いときはとても長いSQLとなってしまう点に注意）
-- コード例1（固定で切り出す）
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
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[59]:
customer_id address prefecture_cd
CS021313000114 神奈川県伊勢原市粟窪********** 14
CS037613000071 東京都江東区南砂********** 13
CS031415000172 東京都渋谷区代々木********** 13
CS028811000001 神奈川県横浜市泉区和泉町********** 14
CS001215000145 東京都大田区仲六郷********** 13
CS020401000016 東京都板橋区若木********** 13
CS015414000103 東京都江東区北砂********** 13
CS029403000008 千葉県浦安市海楽********** 12
CS015804000004 東京都江東区北砂********** 13
CS033513000180 神奈川県横浜市旭区善部町********** 14
In [60]:
%%sql

-- コード例2（正規表現を使う）
SELECT
    customer_id,
    address,
    CASE SUBSTRING(address, '^.*?[都道府県]')
        WHEN '埼玉県' THEN '11'
        WHEN '千葉県' THEN '12'
        WHEN '東京都' THEN '13'
        WHEN '神奈川県' THEN '14'
    END AS prefecture_cd
FROM
    customer
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[60]:
customer_id address prefecture_cd
CS021313000114 神奈川県伊勢原市粟窪********** 14
CS037613000071 東京都江東区南砂********** 13
CS031415000172 東京都渋谷区代々木********** 13
CS028811000001 神奈川県横浜市泉区和泉町********** 14
CS001215000145 東京都大田区仲六郷********** 13
CS020401000016 東京都板橋区若木********** 13
CS015414000103 東京都江東区北砂********** 13
CS029403000008 千葉県浦安市海楽********** 12
CS015804000004 東京都江東区北砂********** 13
CS033513000180 神奈川県横浜市旭区善部町********** 14
In [61]:
%%sql

-- コード例3（LIKEを使う）
SELECT
    customer_id,
    address,
    CASE
        WHEN address LIKE '埼玉県%' THEN '11'
        WHEN address LIKE '千葉県%' THEN '12'
        WHEN address LIKE '東京都%' THEN '13'
        WHEN address LIKE '神奈川県%' THEN '14'
    END AS prefecture_cd
FROM
    customer
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[61]:
customer_id address prefecture_cd
CS021313000114 神奈川県伊勢原市粟窪********** 14
CS037613000071 東京都江東区南砂********** 13
CS031415000172 東京都渋谷区代々木********** 13
CS028811000001 神奈川県横浜市泉区和泉町********** 14
CS001215000145 東京都大田区仲六郷********** 13
CS020401000016 東京都板橋区若木********** 13
CS015414000103 東京都江東区北砂********** 13
CS029403000008 千葉県浦安市海楽********** 12
CS015804000004 東京都江東区北砂********** 13
CS033513000180 神奈川県横浜市旭区善部町********** 14

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

  • 最小値以上第1四分位未満 ・・・ 1を付与
  • 第1四分位以上第2四分位未満 ・・・ 2を付与
  • 第2四分位以上第3四分位未満 ・・・ 3を付与
  • 第3四分位以上 ・・・ 4を付与
In [62]:
%%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_group
FROM sales_amount a
CROSS JOIN sales_pct p
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[62]:
customer_id sum_amount pct_group
CS001311000059 2302 3
CS004614000122 248 1
CS003512000043 298 1
CS011615000061 246 1
CS029212000033 3604 3
CS007515000119 7157 4
CS034515000123 3699 4
CS004315000058 490 1
CS026414000014 6671 4
CS001615000099 768 2

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

In [63]:
%%sql

SELECT
    customer_id,
    birth_day,
    -- 確認用の項目
    -- age,
    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
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[63]:
customer_id birth_day era
CS001105000001 2000-01-14 10
CS001112000009 2006-08-24 10
CS001112000019 2001-01-31 10
CS001112000021 2001-12-15 10
CS001112000023 2004-01-26 10
CS001112000024 2001-01-16 10
CS001112000029 2005-01-24 10
CS001112000030 2003-03-02 10
CS001113000004 2003-02-22 10
CS001113000010 2005-05-09 10

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

In [64]:
%%sql

-- 性別コード１桁と年代コード２桁を連結した性年代コードを生成する
SELECT
    customer_id,
    birth_day,
    gender_cd || TO_CHAR(LEAST(CAST(TRUNC(age / 10) * 10 AS INTEGER), 60), 'FM00') AS gender_era
FROM
    customer
GROUP BY
    customer_id,
    birth_day
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[64]:
customer_id birth_day gender_era
CS001105000001 2000-01-14 010
CS001112000009 2006-08-24 110
CS001112000019 2001-01-31 110
CS001112000021 2001-12-15 110
CS001112000023 2004-01-26 110
CS001112000024 2001-01-16 110
CS001112000029 2005-01-24 110
CS001112000030 2003-03-02 110
CS001113000004 2003-02-22 110
CS001113000010 2005-05-09 110

S-058: 顧客データ（customer）の性別コード（gender_cd）をダミー変数化し、顧客ID（customer_id）とともに10件表示せよ。

In [65]:
%%sql

-- カテゴリ数が多いときはとても長いSQLとなってしまう点に注意
-- カテゴリを一つ減らしたい場合はCASE文をどれか一つ削ればOK
SELECT
    customer_id,
    CASE WHEN gender_cd = '0' THEN '1' ELSE '0' END AS gender_cd_0,
    CASE WHEN gender_cd = '1' THEN '1' ELSE '0' END AS gender_cd_1,
    CASE WHEN gender_cd = '9' THEN '1' ELSE '0' END AS gender_cd_9
FROM
    customer
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[65]:
customer_id gender_cd_0 gender_cd_1 gender_cd_9
CS021313000114 0 1 0
CS037613000071 0 0 1
CS031415000172 0 1 0
CS028811000001 0 1 0
CS001215000145 0 1 0
CS020401000016 1 0 0
CS015414000103 0 1 0
CS029403000008 1 0 0
CS015804000004 1 0 0
CS033513000180 0 1 0

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

In [66]:
%%sql

-- コード例1（STDDEV_POPで標準化）
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
    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
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[66]:
customer_id sum_amount std_amount
CS001311000059 2302 -0.09032946448390523062
CS004614000122 248 -0.84533488878695858131
CS003512000043 298 -0.82695598361891930792
CS011615000061 246 -0.84607004499368015224
CS029212000033 3604 0.38825722609183744835
CS007515000119 7157 1.6942622273327082
CS034515000123 3699 0.42317714591111206778
CS004315000058 490 -0.75638098777364849812
CS026414000014 6671 1.5156192690993665
CS001615000099 768 -0.65419427503935013810
In [67]:
%%sql

-- コード例2（STDDEV_SAMPで標準化、コード例2と若干値が変わる）
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_SAMP(sum_amount) AS stddev_amount
    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
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[67]:
customer_id sum_amount std_amount
CS001311000059 2302 -0.09032402671702291270
CS004614000122 248 -0.84528400025253654164
CS003512000043 298 -0.82690620148098070841
CS011615000061 246 -0.84601911220339877497
CS029212000033 3604 0.38823385329429098451
CS007515000119 7157 1.6941602340010485
CS034515000123 3699 0.42315167096024706764
CS004315000058 490 -0.75633545419820630882
CS026414000014 6671 1.5155280299415258
CS001615000099 768 -0.65415489302835587608

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

In [68]:
%%sql

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
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[68]:
customer_id sum_amount scale_amount
CS001311000059 2302 0.09697601668404588113
CS004614000122 248 0.00773375043448036149
CS003512000043 298 0.00990615224191866528
CS011615000061 246 0.00764685436218282934
CS029212000033 3604 0.15354535974973931178
CS007515000119 7157 0.30791623218630517901
CS034515000123 3699 0.15767292318387208898
CS004315000058 490 0.01824817518248175182
CS026414000014 6671 0.28680048661800486618
CS001615000099 768 0.03032672923183872089

S-061: レシート明細データ（receipt）の売上金額（amount）を顧客ID（customer_id）ごとに合計し、売上金額合計を常用対数化（底10）して顧客ID、売上金額合計とともに10件表示せよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。

In [69]:
%%sql

SELECT
    customer_id,
    sum_amount,
    LOG(sum_amount + 0.5) AS log_amount
FROM
(
    SELECT
        customer_id,
        SUM(amount) AS sum_amount
    FROM
        receipt
    WHERE
        customer_id NOT LIKE 'Z%'
    GROUP BY
        customer_id
) AS sum_amount_tbl
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[69]:
customer_id sum_amount log_amount
CS001311000059 2302 3.3621996388688865
CS004614000122 248 2.3953263930693509
CS003512000043 298 2.4749443354653879
CS011615000061 246 2.3918169236132488
CS029212000033 3604 3.5568450298595813
CS007515000119 7157 3.8547613566936362
CS034515000123 3699 3.5681430316577019
CS004315000058 490 2.6906390117159673
CS026414000014 6671 3.8242234903608168
CS001615000099 768 2.8856438718357639

S-062: レシート明細データ（receipt）の売上金額（amount）を顧客ID（customer_id）ごとに合計し、売上金額合計を自然対数化（底e）して顧客ID、売上金額合計とともに10件表示せよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。

In [70]:
%%sql

SELECT
    customer_id,
    sum_amount,
    LN(sum_amount + 0.5) AS log_amount
FROM
(
    SELECT
        customer_id,
        SUM(amount) AS sum_amount
    FROM
        receipt
    WHERE
        customer_id NOT LIKE 'Z%'
    GROUP BY
        customer_id
) AS sum_amount_tbl
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[70]:
customer_id sum_amount log_amount
CS001311000059 2302 7.7417507681294619
CS004614000122 248 5.5154428455366834
CS003512000043 298 5.6987699328326568
CS011615000061 246 5.5073619934827448
CS029212000033 3604 8.1899383438446333
CS007515000119 7157 8.8759160369722701
CS034515000123 3699 8.2159529543656056
CS004315000058 490 6.1954252790054178
CS026414000014 6671 8.8056000011824754
CS001615000099 768 6.6444405629786506

S-063: 商品データ（product）の単価（unit_price）と原価（unit_cost）から各商品の利益額を算出し、結果を10件表示せよ。

In [71]:
%%sql

SELECT
    product_cd, 
    unit_price, 
    unit_cost,
    unit_price - unit_cost AS unit_profit
FROM
    product
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[71]:
product_cd unit_price unit_cost unit_profit
P040101001 198 149 49
P040101002 218 164 54
P040101003 230 173 57
P040101004 248 186 62
P040101005 268 201 67
P040101006 298 224 74
P040101007 338 254 84
P040101008 420 315 105
P040101009 498 374 124
P040101010 580 435 145

S-064: 商品データ（product）の単価（unit_price）と原価（unit_cost）から、各商品の利益率の全体平均を算出せよ。ただし、単価と原価には欠損が生じていることに注意せよ。

In [72]:
%%sql

SELECT
    AVG((unit_price * 1.0 - unit_cost) / unit_price) AS unit_profit_rate
FROM
    product
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.
Out[72]:
unit_profit_rate
0.24911389885177001279

S-065: 商品データ（product）の各商品について、利益率が30%となる新たな単価を求めよ。ただし、1円未満は切り捨てること。そして結果を10件表示させ、利益率がおよそ30％付近であることを確認せよ。ただし、単価（unit_price）と原価（unit_cost）には欠損が生じていることに注意せよ。

In [73]:
%%sql

WITH new_price_tbl AS (
    SELECT
        product_cd, 
        unit_price, 
        unit_cost,
        TRUNC(unit_cost / 0.7) AS new_price
    FROM
        product
) 
SELECT
    *,
    (new_price - unit_cost) / new_price AS new_profit_rate
FROM
    new_price_tbl
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[73]:
product_cd unit_price unit_cost new_price new_profit_rate
P040101001 198 149 212 0.29716981132075471698
P040101002 218 164 234 0.29914529914529914530
P040101003 230 173 247 0.29959514170040485830
P040101004 248 186 265 0.29811320754716981132
P040101005 268 201 287 0.29965156794425087108
P040101006 298 224 320 0.30000000000000000000
P040101007 338 254 362 0.29834254143646408840
P040101008 420 315 450 0.30000000000000000000
P040101009 498 374 534 0.29962546816479400749
P040101010 580 435 621 0.29951690821256038647

S-066: 商品データ（product）の各商品について、利益率が30%となる新たな単価を求めよ。今回は、1円未満を丸めること（四捨五入または偶数への丸めで良い）。そして結果を10件表示させ、利益率がおよそ30％付近であることを確認せよ。ただし、単価（unit_price）と原価（unit_cost）には欠損が生じていることに注意せよ。

In [74]:
%%sql

WITH new_price_tbl AS (
    SELECT
        product_cd, 
        unit_price, 
        unit_cost,
        ROUND(unit_cost / 0.7) AS new_price
    FROM
        product
) 
SELECT
    *,
    (new_price - unit_cost) / new_price AS new_profit_rate
FROM
    new_price_tbl
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[74]:
product_cd unit_price unit_cost new_price new_profit_rate
P040101001 198 149 213 0.30046948356807511737
P040101002 218 164 234 0.29914529914529914530
P040101003 230 173 247 0.29959514170040485830
P040101004 248 186 266 0.30075187969924812030
P040101005 268 201 287 0.29965156794425087108
P040101006 298 224 320 0.30000000000000000000
P040101007 338 254 363 0.30027548209366391185
P040101008 420 315 450 0.30000000000000000000
P040101009 498 374 534 0.29962546816479400749
P040101010 580 435 621 0.29951690821256038647

S-067: 商品データ（product）の各商品について、利益率が30%となる新たな単価を求めよ。今回は、1円未満を切り上げること。そして結果を10件表示させ、利益率がおよそ30％付近であることを確認せよ。ただし、単価（unit_price）と原価（unit_cost）には欠損が生じていることに注意せよ。

In [75]:
%%sql

WITH new_price_tbl AS (
    SELECT
        product_cd, 
        unit_price, 
        unit_cost,
        CEIL(unit_cost / 0.7) AS new_price
    FROM
        product
) 
SELECT
    *,
    (new_price - unit_cost) / new_price AS new_profit_rate
FROM
    new_price_tbl
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[75]:
product_cd unit_price unit_cost new_price new_profit_rate
P040101001 198 149 213 0.30046948356807511737
P040101002 218 164 235 0.30212765957446808511
P040101003 230 173 248 0.30241935483870967742
P040101004 248 186 266 0.30075187969924812030
P040101005 268 201 288 0.30208333333333333333
P040101006 298 224 320 0.30000000000000000000
P040101007 338 254 363 0.30027548209366391185
P040101008 420 315 450 0.30000000000000000000
P040101009 498 374 535 0.30093457943925233645
P040101010 580 435 622 0.30064308681672025723

S-068: 商品データ（product）の各商品について、消費税率10％の税込み金額を求めよ。1円未満の端数は切り捨てとし、結果を10件表示せよ。ただし、単価（unit_price）には欠損が生じていることに注意せよ。

In [76]:
%%sql

SELECT
    product_cd,
    unit_price,
    TRUNC(unit_price * 1.1) AS tax_price
FROM
    product
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[76]:
product_cd unit_price tax_price
P040101001 198 217
P040101002 218 239
P040101003 230 253
P040101004 248 272
P040101005 268 294
P040101006 298 327
P040101007 338 371
P040101008 420 462
P040101009 498 547
P040101010 580 638

S-069: レシート明細データ（receipt）と商品データ（product）を結合し、顧客毎に全商品の売上金額合計と、カテゴリ大区分コード（category_major_cd）が"07"（瓶詰缶詰）の売上金額合計を計算の上、両者の比率を求めよ。抽出対象はカテゴリ大区分コード"07"（瓶詰缶詰）の売上実績がある顧客のみとし、結果を10件表示せよ。

In [77]:
%%sql

WITH amount_all AS(
    SELECT
        customer_id,
        SUM(amount) AS sum_all
    FROM
        receipt
    GROUP BY
        customer_id
),
amount_07 AS (
    SELECT
        r.customer_id,
        SUM(r.amount) AS sum_07
    FROM
        receipt r
    JOIN
        product p
    ON
        r.product_cd = p.product_cd
    WHERE
        p.category_major_cd = '07'
    GROUP BY
        customer_id
)
SELECT
    amount_all.customer_id,
    sum_all,
    sum_07,
    sum_07 * 1.0 / sum_all AS sales_rate
FROM
    amount_all
JOIN
    amount_07
ON
    amount_all.customer_id = amount_07.customer_id
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[77]:
customer_id sum_all sum_07 sales_rate
CS001311000059 2302 102 0.04430929626411815812
CS011615000061 246 98 0.39837398373983739837
CS029212000033 3604 3604 1.00000000000000000000
CS007515000119 7157 2832 0.39569652088864049183
CS034515000123 3699 1202 0.32495268991619356583
CS026414000014 6671 3142 0.47099385399490331285
CS001615000099 768 318 0.41406250000000000000
CS010515000082 1482 553 0.37314439946018893387
CS019315000045 813 380 0.46740467404674046740
CS008513000099 1322 210 0.15885022692889561271

S-070: レシート明細データ（receipt）の売上日（sales_ymd）に対し、顧客データ（customer）の会員申込日（application_date）からの経過日数を計算し、顧客ID（customer_id）、売上日、会員申込日とともに10件表示せよ（sales_ymdは数値、application_dateは文字列でデータを保持している点に注意）。

In [78]:
%%sql

WITH receipt_distinct AS (
    SELECT distinct
        customer_id,
        sales_ymd
    FROM
        receipt
)
SELECT
    c.customer_id,
    r.sales_ymd,
    c.application_date,
    EXTRACT(DAY FROM (TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD') 
                - TO_TIMESTAMP(c.application_date, 'YYYYMMDD'))) AS elapsed_days
FROM
    receipt_distinct r
JOIN
    customer c
ON
    r.customer_id = c.customer_id
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[78]:
customer_id sales_ymd application_date elapsed_days
CS017515000010 20171117 20150803 837
CS019515000097 20190630 20141124 1679
CS008515000005 20170714 20150216 879
CS026414000097 20170809 20150430 832
CS034514000008 20181012 20150807 1162
CS029415000089 20180409 20150723 991
CS019411000012 20190314 20141213 1552
CS015614000006 20190802 20150211 1633
CS007515000053 20170712 20150325 840
CS024615000041 20170729 20150918 680

S-071: レシート明細データ（receipt）の売上日（sales_ymd）に対し、顧客データ（customer）の会員申込日（application_date）からの経過月数を計算し、顧客ID（customer_id）、売上日、会員申込日とともに10件表示せよ（sales_ymdは数値、application_dateは文字列でデータを保持している点に注意）。1ヶ月未満は切り捨てること。

In [79]:
%%sql

WITH receipt_distinct AS (
    SELECT DISTINCT
        customer_id,
        sales_ymd
    FROM
        receipt
),
time_age_tbl AS(
    SELECT
        c.customer_id,
        r.sales_ymd,
        c.application_date,
        AGE(TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD'),
                    TO_TIMESTAMP(c.application_date, 'YYYYMMDD')) AS time_age
    FROM
        receipt_distinct r
    JOIN
        customer c
    ON
        r.customer_id = c.customer_id
)
SELECT
    customer_id, 
    sales_ymd, 
    application_date,
    EXTRACT(YEAR FROM time_age) * 12 
        + EXTRACT(MONTH FROM time_age) AS elapsed_months
FROM
    time_age_tbl
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[79]:
customer_id sales_ymd application_date elapsed_months
CS017515000010 20171117 20150803 27
CS019515000097 20190630 20141124 55
CS008515000005 20170714 20150216 28
CS026414000097 20170809 20150430 27
CS034514000008 20181012 20150807 38
CS029415000089 20180409 20150723 32
CS019411000012 20190314 20141213 51
CS015614000006 20190802 20150211 53
CS007515000053 20170712 20150325 27
CS024615000041 20170729 20150918 22

S-072: レシート明細データ（receipt）の売上日（sales_ymd）に対し、顧客データ（customer）の会員申込日（application_date）からの経過年数を計算し、顧客ID（customer_id）、売上日、会員申込日とともに10件表示せよ（sales_ymdは数値、application_dateは文字列でデータを保持している点に注意）。1年未満は切り捨てること。

In [80]:
%%sql

WITH receipt_distinct AS (
    SELECT distinct
        customer_id,
        sales_ymd
    FROM
        receipt
)
SELECT
    c.customer_id,
    r.sales_ymd,
    c.application_date,
    EXTRACT(YEAR FROM AGE(
        TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD'), 
        TO_TIMESTAMP(c.application_date, 'YYYYMMDD'))) AS elapsed_years
FROM
    receipt_distinct r
JOIN
    customer c
ON
    r.customer_id = c.customer_id
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[80]:
customer_id sales_ymd application_date elapsed_years
CS017515000010 20171117 20150803 2
CS019515000097 20190630 20141124 4
CS008515000005 20170714 20150216 2
CS026414000097 20170809 20150430 2
CS034514000008 20181012 20150807 3
CS029415000089 20180409 20150723 2
CS019411000012 20190314 20141213 4
CS015614000006 20190802 20150211 4
CS007515000053 20170712 20150325 2
CS024615000041 20170729 20150918 1

S-073: レシート明細データ（receipt）の売上日（sales_ymd）に対し、顧客データ（customer）の会員申込日（application_date）からのエポック秒による経過時間を計算し、顧客ID（customer_id）、売上日、会員申込日とともに10件表示せよ（なお、sales_ymdは数値、application_dateは文字列でデータを保持している点に注意）。なお、時間情報は保有していないため各日付は0時0分0秒を表すものとする。

In [81]:
%%sql

WITH receipt_distinct AS (
    SELECT distinct
        customer_id,
        sales_ymd
    FROM
        receipt
)
SELECT
    c.customer_id,
    r.sales_ymd,
    c.application_date,
    EXTRACT(EPOCH FROM 
            TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD') - 
            TO_TIMESTAMP(c.application_date, 'YYYYMMDD')
    ) AS elapsed_epoch
FROM
    receipt_distinct r
JOIN
    customer c
ON
    r.customer_id = c.customer_id
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[81]:
customer_id sales_ymd application_date elapsed_epoch
CS017515000010 20171117 20150803 72316800.000000
CS019515000097 20190630 20141124 145065600.000000
CS008515000005 20170714 20150216 75945600.000000
CS026414000097 20170809 20150430 71884800.000000
CS034514000008 20181012 20150807 100396800.000000
CS029415000089 20180409 20150723 85622400.000000
CS019411000012 20190314 20141213 134092800.000000
CS015614000006 20190802 20150211 141091200.000000
CS007515000053 20170712 20150325 72576000.000000
CS024615000041 20170729 20150918 58752000.000000

S-074: レシート明細データ（receipt）の売上日（sales_ymd）に対し、当該週の月曜日からの経過日数を計算し、売上日、直前の月曜日付とともに10件表示せよ（sales_ymdは数値でデータを保持している点に注意）。

In [82]:
%%sql

WITH elapsed_days_tbl AS (
    SELECT
        TO_DATE(CAST(sales_ymd AS VARCHAR), 'YYYYMMDD') AS sales_ymd,
        EXTRACT(DOW FROM (
            TO_DATE(CAST(sales_ymd AS VARCHAR), 'YYYYMMDD') - 1)) AS elapsed_days
    FROM
        receipt
)
SELECT
    sales_ymd,
    elapsed_days,
    sales_ymd - CAST(elapsed_days AS INTEGER) AS monday
FROM 
    elapsed_days_tbl
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[82]:
sales_ymd elapsed_days monday
2018-11-03 5 2018-10-29
2018-11-18 6 2018-11-12
2017-07-12 2 2017-07-10
2019-02-05 1 2019-02-04
2018-08-21 1 2018-08-20
2019-06-05 2 2019-06-03
2018-12-05 2 2018-12-03
2019-09-22 6 2019-09-16
2017-05-04 3 2017-05-01
2019-10-10 3 2019-10-07

S-075: 顧客データ（customer）からランダムに1%のデータを抽出し、先頭から10件表示せよ。

In [83]:
%%sql

-- コード例１（シンプルにやるなら。ただし1.0%前後で件数変動）
SELECT * FROM customer WHERE RANDOM() <= 0.01
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[83]:
customer_id customer_name gender_cd gender birth_day age postal_cd address application_store_cd application_date status_cd
CS019415000117 宮脇 芽以 1 女性 1974-07-10 44 173-0036 東京都板橋区向原********** S13019 20141114 C-20100720-D
CS040513000111 寺西 奈央 1 女性 1966-06-03 52 226-0027 神奈川県横浜市緑区長津田********** S14040 20150728 6-20090622-6
CS029402000041 浅利 俊二 0 男性 1975-08-15 43 134-0013 東京都江戸川区江戸川********** S12029 20150220 0-00000000-0
CS019712000008 梅本 ヒカル 1 女性 1945-04-14 73 173-0037 東京都板橋区小茂根********** S13019 20150613 0-00000000-0
CS015713000077 長沢 結衣 1 女性 1947-10-09 71 136-0075 東京都江東区新砂********** S13015 20150308 0-00000000-0
CS008515000014 野沢 あさみ 1 女性 1959-06-09 59 157-0067 東京都世田谷区喜多見********** S13008 20150219 9-20091212-B
CS031514000047 原口 礼子 1 女性 1963-07-26 55 151-0064 東京都渋谷区上原********** S13031 20150927 8-20090607-6
CS004313000412 春日 瞳 1 女性 1984-07-26 34 176-0024 東京都練馬区中村********** S13004 20170525 0-00000000-0
CS035513000155 板倉 昌代 1 女性 1960-06-12 58 157-0075 東京都世田谷区砧公園********** S13035 20150625 0-00000000-0
CS003512000587 大山 沙知絵 1 女性 1959-04-01 59 214-0014 神奈川県川崎市多摩区登戸********** S13003 20170306 0-00000000-0
In [84]:
%%sql

-- コード例２（丁寧にやるなら。カウントを作って出力件数を固定）
WITH customer_tmp AS(
    SELECT
        *
        ,ROW_NUMBER() OVER(ORDER BY RANDOM()) AS row
        ,COUNT(*) OVER() AS cnt
    FROM customer
)
SELECT 
    customer_id
    ,customer_name
    ,gender_cd
    ,gender
    ,birth_day
    ,age
    ,postal_cd
    ,address
    ,application_store_cd
    ,application_date
    ,status_cd
FROM customer_tmp
WHERE row <= cnt * 0.01
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[84]:
customer_id customer_name gender_cd gender birth_day age postal_cd address application_store_cd application_date status_cd
CS027514000069 土屋 さやか 9 不明 1967-02-10 52 251-0033 神奈川県藤沢市片瀬山********** S14027 20141213 A-20101018-B
CS031504000012 向井 剛基 0 男性 1966-09-19 52 151-0062 東京都渋谷区元代々木町********** S13031 20150310 0-00000000-0
CS028403000022 新垣 三郎 0 男性 1973-09-12 45 246-0012 神奈川県横浜市瀬谷区東野********** S14028 20150905 0-00000000-0
CS001415000103 水谷 千夏 1 女性 1976-12-08 42 144-0051 東京都大田区西蒲田********** S13001 20150509 A-20100925-C
CS019313000145 早美 由美子 1 女性 1985-02-20 34 173-0033 東京都板橋区大山西町********** S13019 20141226 0-00000000-0
CS002412000346 荒川 美嘉 1 女性 1973-03-22 46 187-0045 東京都小平市学園西町********** S13002 20160808 0-00000000-0
CS003415000271 稲垣 遥 1 女性 1975-12-14 43 201-0001 東京都狛江市西野川********** S13003 20160630 A-20090325-8
CS029502000052 岡崎 優一 0 男性 1963-03-28 56 134-0085 東京都江戸川区南葛西********** S12029 20150803 0-00000000-0
CS002515000290 大山 みあ 1 女性 1959-06-05 59 185-0023 東京都国分寺市西元町********** S13002 20160627 C-20100730-B
CS009314000030 西川 奈々 1 女性 1983-05-15 35 158-0091 東京都世田谷区中町********** S13009 20150519 E-20100910-D

S-076: 顧客データ（customer）から性別コード（gender_cd）の割合に基づきランダムに10%のデータを層化抽出し、性別コードごとに件数を集計せよ。

In [85]:
%%sql

-- コード例1
WITH cusotmer_random AS (
    SELECT
        customer_id,
        gender_cd,
        cnt
    FROM (
        SELECT
            gender_cd,
            ARRAY_AGG(customer_id ORDER BY RANDOM()) AS customer_r,
            COUNT(1) AS cnt
        FROM
            customer
        GROUP BY gender_cd
    )sample, UNNEST(customer_r) AS customer_id
),
cusotmer_rownum AS(
    SELECT
        * ,
        ROW_NUMBER() OVER(PARTITION BY gender_cd) AS rn
    FROM
        cusotmer_random
)
SELECT
    gender_cd,
    COUNT(1) AS customer_num
FROM
    cusotmer_rownum
WHERE
    rn <= cnt * 0.1
GROUP BY
    gender_cd
;

 * postgresql://padawan:***@db:5432/dsdojo_db
3 rows affected.
Out[85]:
gender_cd customer_num
0 298
1 1791
9 107
In [86]:
%%sql

-- コード例2
WITH cusotmer_random AS (
SELECT 
    * , 
    ROW_NUMBER() OVER(PARTITION BY gender_cd ORDER BY RANDOM()) AS rn,
    COUNT(1) OVER(PARTITION BY gender_cd) cnt
FROM 
    customer
)
SELECT
    gender_cd,
    COUNT(1) AS customer_num
FROM
    cusotmer_random
WHERE 
    rn <= cnt * 0.1
GROUP BY
    gender_cd
;

 * postgresql://padawan:***@db:5432/dsdojo_db
3 rows affected.
Out[86]:
gender_cd customer_num
9 107
0 298
1 1791

S-077: レシート明細データ（receipt）の売上金額を顧客単位に合計し、合計した売上金額の外れ値を抽出せよ。なお、外れ値は売上金額合計を対数化したうえで平均と標準偏差を計算し、その平均から3σを超えて離れたものとする（自然対数と常用対数のどちらでも可）。結果は10件表示せよ。

In [87]:
%%sql

WITH sales_amount AS(
    SELECT
        customer_id,
        SUM(amount) AS sum_amount,
        LN(SUM(amount) + 0.5) AS log_sum_amount
    FROM
        receipt
    GROUP BY 
        customer_id
)
SELECT 
    customer_id,
    sum_amount,
    log_sum_amount
FROM
    sales_amount
CROSS JOIN (
    SELECT
        AVG(log_sum_amount) AS avg_amount,
        STDDEV_POP(log_sum_amount) AS std_amount
    FROM sales_amount   
) stats_amount
WHERE
    ABS(log_sum_amount - avg_amount) / std_amount > 3
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.
Out[87]:
customer_id sum_amount log_sum_amount
ZZ000000000000 12395003 16.332804005823312

S-078: レシート明細データ（receipt）の売上金額（amount）を顧客単位に合計し、合計した売上金額の外れ値を抽出せよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。なお、ここでは外れ値を第1四分位と第3四分位の差であるIQRを用いて、「第1四分位数-1.5×IQR」を下回るもの、または「第3四分位数+1.5×IQR」を超えるものとする。結果は10件表示せよ。

In [88]:
%%sql

WITH sales_amount AS(
    SELECT 
        customer_id,
        SUM(amount) AS sum_amount
    FROM
        receipt
    WHERE
        customer_id NOT LIKE 'Z%'
    GROUP BY 
        customer_id
)
SELECT
    customer_id,
    sum_amount
FROM
    sales_amount
CROSS JOIN (
    SELECT
        PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY sum_amount) AS amount_25per,
        PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY sum_amount) AS amount_75per
    FROM sales_amount   
) stats_amount
WHERE
    sum_amount < amount_25per - (amount_75per - amount_25per) * 1.5 
    OR amount_75per + (amount_75per - amount_25per) * 1.5 < sum_amount
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[88]:
customer_id sum_amount
CS013415000226 8362
CS011415000041 9454
CS014514000004 8872
CS021514000008 12839
CS014515000007 9763
CS040415000220 10158
CS028415000161 8465
CS034515000173 10074
CS022515000065 12903
CS007514000094 15735

S-079: 商品データ（product）の各項目に対し、欠損数を確認せよ。

In [89]:
%%sql

SELECT 
    SUM(
        CASE WHEN product_cd IS NULL THEN 1 ELSE 0 END
    ) AS product_cd,
    SUM(
        CASE WHEN category_major_cd IS NULL THEN 1 ELSE 0 END
    ) AS category_major_cd,
    SUM(
        CASE WHEN category_medium_cd IS NULL THEN 1 ELSE 0 END
    ) AS category_medium_cd,
    SUM(
        CASE WHEN category_small_cd IS NULL THEN 1 ELSE 0 END
    ) AS category_small_cd,
    SUM(
        CASE WHEN unit_price IS NULL THEN 1 ELSE 0 END
    ) AS unit_price,
    SUM(
        CASE WHEN unit_cost IS NULL THEN 1 ELSE 0 END
    ) AS unit_cost
FROM product LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.
Out[89]:
product_cd category_major_cd category_medium_cd category_small_cd unit_price unit_cost
0 0 0 0 7 7

S-080: 商品データ（product）のいずれかの項目に欠損が発生しているレコードを全て削除した新たな商品データを作成せよ。なお、削除前後の件数を表示させ、079で確認した件数だけ減少していることも確認すること。

In [90]:
%%sql

DROP TABLE IF EXISTS product_1;
CREATE TABLE product_1 AS (
    SELECT * FROM product
    WHERE unit_price IS NOT NULL AND unit_cost IS NOT NULL
);

 * postgresql://padawan:***@db:5432/dsdojo_db
Done.
10023 rows affected.
Out[90]:
[]
In [91]:
%%sql

SELECT '削除前', COUNT(1) FROM product;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.
Out[91]:
?column? count
削除前 10030
In [92]:
%%sql

SELECT '削除後', COUNT(1) FROM product_1;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.
Out[92]:
?column? count
削除後 10023

S-081: 単価（unit_price）と原価（unit_cost）の欠損値について、それぞれの平均値で補完した新たな商品データを作成せよ。なお、平均値については1円未満を丸めること（四捨五入または偶数への丸めで良い）。補完実施後、各項目について欠損が生じていないことも確認すること。

In [93]:
%%sql

DROP TABLE IF EXISTS product_2;
CREATE TABLE product_2 AS (
    SELECT
        product_cd, 
        category_major_cd, 
        category_medium_cd, 
        category_small_cd, 
        COALESCE(unit_price, unit_avg) AS unit_price,
        COALESCE(unit_cost, cost_avg) AS unit_cost
    FROM
        product
    CROSS JOIN (
        SELECT
            ROUND(AVG(unit_price)) AS unit_avg,
            ROUND(AVG(unit_cost)) AS cost_avg
        FROM
            product
    ) stats_product
);

 * postgresql://padawan:***@db:5432/dsdojo_db
Done.
10030 rows affected.
Out[93]:
[]
In [94]:
%%sql

SELECT 
    SUM(CASE WHEN unit_price IS NULL THEN 1 ELSE 0 END) AS unit_price,
    SUM(CASE WHEN unit_cost IS NULL THEN 1 ELSE 0 END) AS unit_cost
FROM product_2
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.
Out[94]:
unit_price unit_cost
0 0

S-082: 単価（unit_price）と原価（unit_cost）の欠損値について、それぞれの中央値で補完した新たな商品データを作成せよ。なお、中央値については1円未満を丸めること（四捨五入または偶数への丸めで良い）。補完実施後、各項目について欠損が生じていないことも確認すること。

In [95]:
%%sql

DROP TABLE IF EXISTS product_3;
CREATE TABLE product_3 AS (
    SELECT
        product_cd, 
        category_major_cd, 
        category_medium_cd, 
        category_small_cd, 
        COALESCE(unit_price, unit_med) AS unit_price,
        COALESCE(unit_cost, cost_med) AS unit_cost
    FROM
        product
    CROSS JOIN (
        SELECT
            ROUND(
                PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY unit_price)
            ) AS unit_med,
            ROUND(
                PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY unit_cost)
            ) AS cost_med
        FROM
            product
    ) stats_product
);

 * postgresql://padawan:***@db:5432/dsdojo_db
Done.
10030 rows affected.
Out[95]:
[]
In [96]:
%%sql

SELECT 
    SUM(CASE WHEN unit_price IS NULL THEN 1 ELSE 0 END) AS unit_price,
    SUM(CASE WHEN unit_cost IS NULL THEN 1 ELSE 0 END) AS unit_cost
FROM product_3
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.
Out[96]:
unit_price unit_cost
0 0

S-083: 単価（unit_price）と原価（unit_cost）の欠損値について、各商品のカテゴリ小区分コード（category_small_cd）ごとに算出した中央値で補完した新たな商品データを作成せよ。なお、中央値については1円未満を丸めること（四捨五入または偶数への丸めで良い）。補完実施後、各項目について欠損が生じていないことも確認すること。

In [97]:
%%sql

DROP TABLE IF EXISTS product_4;
CREATE TABLE product_4 AS (
    WITH category_median AS(
        SELECT
            category_small_cd, 
            ROUND(
                PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY unit_price)
            ) AS unit_med,
            ROUND(
                PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY unit_cost)
            ) AS cost_med
        FROM product
        GROUP BY category_small_cd
    )
    SELECT
        product_cd, 
        category_major_cd, 
        category_medium_cd, 
        category_small_cd, 
        COALESCE(unit_price, unit_med) AS unit_price,
        COALESCE(unit_cost, cost_med) AS unit_cost
    FROM
        product
    JOIN
        category_median
    USING(category_small_cd)
);

 * postgresql://padawan:***@db:5432/dsdojo_db
Done.
10030 rows affected.
Out[97]:
[]
In [98]:
%%sql

SELECT 
    SUM(CASE WHEN unit_price IS NULL THEN 1 ELSE 0 END) AS unit_price,
    SUM(CASE WHEN unit_cost IS NULL THEN 1 ELSE 0 END) AS unit_cost
FROM product_4
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.
Out[98]:
unit_price unit_cost
0 0

S-084: 顧客データ（customer）の全顧客に対して全期間の売上金額に占める2019年売上金額の割合を計算し、新たなデータを作成せよ。ただし、売上実績がない場合は0として扱うこと。そして計算した割合が0超のものを抽出し、結果を10件表示せよ。また、作成したデータに欠損が存在しないことを確認せよ。

In [99]:
%%sql

DROP TABLE IF EXISTS sales_rate;

CREATE TABLE sales_rate AS(
    WITH sales_amount_2019 AS (
        SELECT
            customer_id,
            SUM(amount) AS sum_amount_2019
        FROM
            receipt
        WHERE
            sales_ymd BETWEEN 20190101 AND 20191231
        GROUP BY
            customer_id
    ),
    sales_amount_all AS (
        SELECT
            customer_id,
            SUM(amount) AS sum_amount_all
        FROM
            receipt
        GROUP BY
            customer_id
    )
    SELECT
        a.customer_id,
        COALESCE(b.sum_amount_2019, 0) AS sales_amount_2019,
        COALESCE(c.sum_amount_all, 0)  AS sales_amount_all,
        CASE COALESCE(c.sum_amount_all, 0)
            WHEN 0 THEN 0 
            ELSE COALESCE(b.sum_amount_2019, 0) * 1.0 / c.sum_amount_all
        END AS sales_rate
    FROM
        customer a
    LEFT JOIN
        sales_amount_2019 b
    ON a.customer_id = b.customer_id
    LEFT JOIN
        sales_amount_all c
    ON a.customer_id = c.customer_id);

 * postgresql://padawan:***@db:5432/dsdojo_db
Done.
21971 rows affected.
Out[99]:
[]
In [100]:
%%sql

SELECT * FROM sales_rate
WHERE sales_rate > 0
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[100]:
customer_id sales_amount_2019 sales_amount_all sales_rate
CS031415000172 2971 5088 0.58392295597484276730
CS015414000103 874 3122 0.27994875080076873799
CS011215000048 248 3444 0.07200929152148664344
CS029415000023 3767 5167 0.72904973872653377201
CS035415000029 5823 7504 0.77598614072494669510
CS023513000066 208 771 0.26977950713359273671
CS035513000134 463 1565 0.29584664536741214058
CS001515000263 216 216 1.00000000000000000000
CS006415000279 229 229 1.00000000000000000000
CS031415000106 215 7741 0.02777418938121689704
In [101]:
%%sql

SELECT
    SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) AS unit_price,
    SUM(CASE WHEN sales_amount_2019 IS NULL THEN 1 ELSE 0 END) AS unit_price,
    SUM(CASE WHEN sales_amount_all IS NULL THEN 1 ELSE 0 END) AS unit_cost,
    SUM(CASE WHEN sales_rate IS NULL THEN 1 ELSE 0 END) AS unit_cost
FROM sales_rate 
;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.
Out[101]:
unit_price unit_price_1 unit_cost unit_cost_1
0 0 0 0

S-085: 顧客データ（customer）の全顧客に対し、郵便番号（postal_cd）を用いてジオコードデータ（geocode）を紐付け、新たな顧客データを作成せよ。ただし、1つの郵便番号（postal_cd）に複数の経度（longitude）、緯度（latitude）情報が紐づく場合は、経度（longitude）、緯度（latitude）の平均値を算出して使用すること。また、作成結果を確認するために結果を10件表示せよ。

In [102]:
%%sql

DROP TABLE IF EXISTS customer_1;

CREATE TABLE customer_1 AS (
    WITH geocode_avg AS(
        SELECT
            postal_cd,
            AVG(longitude) AS m_longitude,
            AVG(latitude) AS m_latitude
        FROM
            geocode
        GROUP BY 
            postal_cd
    )
    SELECT 
        *
    FROM
        customer c
    JOIN
        geocode_avg g
    USING(postal_cd)
);

 * postgresql://padawan:***@db:5432/dsdojo_db
Done.
21971 rows affected.
Out[102]:
[]
In [103]:
%%sql

SELECT * FROM customer_1 LIMIT 10;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[103]:
postal_cd customer_id customer_name gender_cd gender birth_day age address application_store_cd application_date status_cd m_longitude m_latitude
136-0076 CS037613000071 六角 雅彦 9 不明 1952-04-01 66 東京都江東区南砂********** S13037 20150414 0-00000000-0 139.8350200000000000 35.6719300000000000
151-0053 CS031415000172 宇多田 貴美子 1 女性 1976-10-04 42 東京都渋谷区代々木********** S13031 20150529 D-20100325-C 139.6896500000000000 35.6737400000000000
245-0016 CS028811000001 堀井 かおり 1 女性 1933-03-27 86 神奈川県横浜市泉区和泉町********** S14028 20160115 0-00000000-0 139.4836000000000000 35.3912500000000000
144-0055 CS001215000145 田崎 美紀 1 女性 1995-03-29 24 東京都大田区仲六郷********** S13001 20170605 6-20090929-2 139.7077500000000000 35.5408400000000000
136-0073 CS015414000103 奥野 陽子 1 女性 1977-08-09 41 東京都江東区北砂********** S13015 20150722 B-20100609-B 139.8360100000000000 35.6781800000000000
136-0073 CS015804000004 松谷 米蔵 0 男性 1931-05-02 87 東京都江東区北砂********** S13015 20150607 0-00000000-0 139.8360100000000000 35.6781800000000000
276-0022 CS007403000016 依田 満 0 男性 1975-08-18 43 千葉県八千代市上高野********** S12007 20150914 0-00000000-0 140.1326000000000000 35.7326400000000000
154-0015 CS035614000014 板倉 菜々美 1 女性 1954-07-16 64 東京都世田谷区桜新町********** S13035 20150804 0-00000000-0 139.6429700000000000 35.6288900000000000
223-0062 CS011215000048 芦田 沙耶 1 女性 1992-02-01 27 神奈川県横浜市港北区日吉本町********** S14011 20150228 C-20100421-9 139.6294600000000000 35.5537000000000000
226-0021 CS040412000191 川井 郁恵 1 女性 1977-01-05 42 神奈川県横浜市緑区北八朔町********** S14040 20151101 1-20091025-4 139.5396400000000000 35.5276300000000000

S-086: 085で作成した緯度経度つき顧客データに対し、会員申込店舗コード（application_store_cd）をキーに店舗データ（store）と結合せよ。そして申込み店舗の緯度（latitude）・経度情報（longitude)と顧客住所（address）の緯度・経度を用いて申込み店舗と顧客住所の距離（単位：km）を求め、顧客ID（customer_id）、顧客住所（address）、店舗住所（address）とともに表示せよ。計算式は以下の簡易式で良いものとするが、その他精度の高い方式を利用したライブラリを利用してもかまわない。結果は10件表示せよ。

$$ \mbox{緯度（ラジアン）}：\phi \\ \mbox{経度（ラジアン）}：\lambda \\ \mbox{距離}L = 6371 * \arccos(\sin \phi_1 * \sin \phi_2 + \cos \phi_1 * \cos \phi_2 * \cos(\lambda_1 − \lambda_2)) $$
In [104]:
%%sql

SELECT
    c.customer_id,
    c.address AS customer_address,
    s.address AS store_address,
    6371 * ACOS( 
        SIN(RADIANS(c.m_latitude)) 
        * SIN(RADIANS(s.latitude))
        + COS(RADIANS(c.m_latitude)) 
        * COS(RADIANS(s.latitude)) 
        * COS(RADIANS(c.m_longitude) - RADIANS(s.longitude))
    ) AS distance FROM
    customer_1 c
JOIN
    store s
ON
    c.application_store_cd = s.store_cd
LIMIT 10
;

 * postgresql://padawan:***@db:5432/dsdojo_db
10 rows affected.
Out[104]:
customer_id customer_address store_address distance
CS037613000071 東京都江東区南砂********** 東京都江東区南砂一丁目 1.4511822099658445
CS031415000172 東京都渋谷区代々木********** 東京都渋谷区初台二丁目 0.4117334789298223
CS028811000001 神奈川県横浜市泉区和泉町********** 神奈川県横浜市瀬谷区二ツ橋町 8.065196026704987
CS001215000145 東京都大田区仲六郷********** 東京都大田区仲六郷二丁目 1.2684209720729687
CS015414000103 東京都江東区北砂********** 東京都江東区南砂二丁目 1.449673414532165
CS015804000004 東京都江東区北砂********** 東京都江東区南砂二丁目 1.449673414532165
CS007403000016 千葉県八千代市上高野********** 千葉県佐倉市上志津 1.9208032538419055
CS035614000014 東京都世田谷区桜新町********** 東京都世田谷区用賀四丁目 1.0214681484997588
CS011215000048 神奈川県横浜市港北区日吉本町********** 神奈川県横浜市港北区日吉本町四丁目 0.8182767808775093
CS040412000191 神奈川県横浜市緑区北八朔町********** 神奈川県横浜市緑区長津田みなみ台五丁目 3.6641233580823287

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

In [105]:
%%sql

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
    )
    SELECT c.*
    FROM
        customer c
    JOIN
        sales_ranking r
    ON
        c.customer_id = r.customer_id
        AND r.ranking = 1
);

 * postgresql://padawan:***@db:5432/dsdojo_db
Done.
21941 rows affected.
Out[105]:
[]
In [106]:
%%sql

SELECT 
    customer_cnt, 
    customer_u_cnt, 
    customer_cnt - customer_u_cnt AS diff 
FROM
    (SELECT COUNT(1) AS customer_cnt FROM customer) customer
CROSS JOIN (SELECT COUNT(1) AS customer_u_cnt FROM customer_u) customer_u
;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.
Out[106]:
customer_cnt customer_u_cnt diff
21971 21941 30

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

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

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

In [107]:
%%sql

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
);

 * postgresql://padawan:***@db:5432/dsdojo_db
Done.
21971 rows affected.
Out[107]:
[]
In [108]:
%%sql

SELECT COUNT(1) AS ID数の差 FROM customer_n
WHERE customer_id != integration_id;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.
Out[108]:
id数の差
30

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

In [109]:
%%sql

SELECT SETSEED(0.1);

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
;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.
8306 rows affected.
Done.
6644 rows affected.
Done.
1662 rows affected.
Out[109]:
[]
In [110]:
%%sql

SELECT
    train_cnt * 1.0 / all_cnt as 学習データ割合,
    test_cnt * 1.0 / all_cnt as テストデータ割合
FROM
    (SELECT COUNT(1) AS all_cnt FROM sales_customer) all_data
CROSS JOIN
    (SELECT COUNT(1) AS train_cnt FROM customer_train) train_data
CROSS JOIN
    (SELECT COUNT(1) AS test_cnt FROM customer_test) test_data
;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.
Out[110]:
学習データ割合 テストデータ割合
0.79990368408379484710 0.20009631591620515290

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

In [111]:
%%sql

-- 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);

 * postgresql://padawan:***@db:5432/dsdojo_db
34 rows affected.
Done.
18 rows affected.
Done.
18 rows affected.
Done.
18 rows affected.
Out[111]:
[]
In [112]:
%%sql

-- series_data_2とseries_data_3の表示は割愛
SELECT * FROM series_data_1;

 * postgresql://padawan:***@db:5432/dsdojo_db
18 rows affected.
Out[112]:
sales_ym sum_amount test_flg
201701 902056 0
201702 764413 0
201703 962945 0
201704 847566 0
201705 884010 0
201706 894242 0
201707 959205 0
201708 954836 0
201709 902037 0
201710 905739 0
201711 932157 0
201712 939654 0
201801 944509 1
201802 864128 1
201803 946588 1
201804 937099 1
201805 1004438 1
201806 1012329 1

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

In [113]:
%%sql

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
);

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.
16612 rows affected.
Out[113]:
[]
In [114]:
%%sql

SELECT is_buy_flag, COUNT(1) FROM down_sampling GROUP BY is_buy_flag;

 * postgresql://padawan:***@db:5432/dsdojo_db
2 rows affected.
Out[114]:
is_buy_flag count
0 8306
1 8306

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

In [115]:
%%sql

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
);

 * postgresql://padawan:***@db:5432/dsdojo_db
Done.
21971 rows affected.
Done.
3 rows affected.
Out[115]:
[]
In [116]:
%%sql

-- データの内容確認
SELECT * FROM customer_std LIMIT 3;

 * postgresql://padawan:***@db:5432/dsdojo_db
3 rows affected.
Out[116]:
customer_id customer_name gender_cd birth_day age postal_cd application_store_cd application_date status_cd
CS021313000114 大野 あや子 1 1981-04-29 37 259-1113 S14021 20150905 0-00000000-0
CS037613000071 六角 雅彦 9 1952-04-01 66 136-0076 S13037 20150414 0-00000000-0
CS031415000172 宇多田 貴美子 1 1976-10-04 42 151-0053 S13031 20150529 D-20100325-C
In [117]:
%%sql

-- データの内容確認
SELECT * FROM gender_std LIMIT 3;

 * postgresql://padawan:***@db:5432/dsdojo_db
3 rows affected.
Out[117]:
gender_cd gender
0 男性
9 不明
1 女性

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

In [118]:
%%sql

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)
);

 * postgresql://padawan:***@db:5432/dsdojo_db
Done.
10030 rows affected.
Out[118]:
[]
In [119]:
%%sql
-- データの内容確認
SELECT * FROM product_full LIMIT 3;

 * postgresql://padawan:***@db:5432/dsdojo_db
3 rows affected.
Out[119]:
product_cd category_major_cd category_major_name category_medium_cd category_medium_name category_small_cd category_small_name unit_price unit_cost
P040101001 04 惣菜 0401 御飯類 040101 弁当類 198 149
P040101002 04 惣菜 0401 御飯類 040101 弁当類 218 164
P040101003 04 惣菜 0401 御飯類 040101 弁当類 230 173

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

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

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

|出力先| |:--:| |/tmp/data|

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

In [120]:
%%sql

COPY product_full TO '/tmp/data/S_product_full_UTF-8_header.csv' 
WITH CSV HEADER ENCODING 'UTF-8';

 * postgresql://padawan:***@db:5432/dsdojo_db
10030 rows affected.
Out[120]:
[]

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

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

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

|出力先| |:--:| |/tmp/data|

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

In [121]:
%%sql

COPY product_full TO '/tmp/data/S_product_full_SJIS_header.csv' 
WITH CSV HEADER ENCODING 'SJIS';

 * postgresql://padawan:***@db:5432/dsdojo_db
10030 rows affected.
Out[121]:
[]

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

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

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

|出力先| |:--:| |/tmp/data|

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

In [122]:
%%sql

COPY product_full TO '/tmp/data/S_product_full_UTF-8_noh.csv' 
WITH CSV ENCODING 'UTF-8';

 * postgresql://padawan:***@db:5432/dsdojo_db
10030 rows affected.
Out[122]:
[]

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

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

In [123]:
%%sql

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
);

 * postgresql://padawan:***@db:5432/dsdojo_db
Done.
Done.
Out[123]:
[]
In [124]:
%%sql

COPY product_full FROM '/tmp/data/S_product_full_UTF-8_header.csv' 
WITH CSV HEADER ENCODING 'UTF-8';

 * postgresql://padawan:***@db:5432/dsdojo_db
10030 rows affected.
Out[124]:
[]
In [125]:
%%sql

SELECT * FROM product_full LIMIT 3;

 * postgresql://padawan:***@db:5432/dsdojo_db
3 rows affected.
Out[125]:
product_cd category_major_cd category_major_name category_medium_cd category_medium_name category_small_cd category_small_name unit_price unit_cost
P040101001 04 惣菜 0401 御飯類 040101 弁当類 198 149
P040101002 04 惣菜 0401 御飯類 040101 弁当類 218 164
P040101003 04 惣菜 0401 御飯類 040101 弁当類 230 173

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

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

In [126]:
%%sql

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
);

 * postgresql://padawan:***@db:5432/dsdojo_db
Done.
Done.
Out[126]:
[]
In [127]:
%%sql

COPY product_full FROM '/tmp/data/S_product_full_UTF-8_noh.csv' 
WITH CSV ENCODING 'UTF-8';

 * postgresql://padawan:***@db:5432/dsdojo_db
10030 rows affected.
Out[127]:
[]
In [128]:
%%sql

SELECT * FROM product_full LIMIT 3;

 * postgresql://padawan:***@db:5432/dsdojo_db
3 rows affected.
Out[128]:
product_cd category_major_cd category_major_name category_medium_cd category_medium_name category_small_cd category_small_name unit_price unit_cost
P040101001 04 惣菜 0401 御飯類 040101 弁当類 198 149
P040101002 04 惣菜 0401 御飯類 040101 弁当類 218 164
P040101003 04 惣菜 0401 御飯類 040101 弁当類 230 173

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

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

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

|出力先| |:--:| |/tmp/data|

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

In [129]:
%%sql

COPY product_full TO '/tmp/data/S_product_full_UTF-8_header.tsv' 
WITH CSV HEADER DELIMITER E'\t' ENCODING 'UTF-8';

 * postgresql://padawan:***@db:5432/dsdojo_db
10030 rows affected.
Out[129]:
[]

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

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

In [130]:
%%sql

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
);

 * postgresql://padawan:***@db:5432/dsdojo_db
Done.
Done.
Out[130]:
[]
In [131]:
%%sql

COPY product_full FROM '/tmp/data/S_product_full_UTF-8_header.tsv' 
WITH CSV HEADER DELIMITER E'\t' ENCODING 'UTF-8';

 * postgresql://padawan:***@db:5432/dsdojo_db
10030 rows affected.
Out[131]:
[]
In [132]:
%%sql

SELECT * FROM product_full LIMIT 3;

 * postgresql://padawan:***@db:5432/dsdojo_db
3 rows affected.
Out[132]:
product_cd category_major_cd category_major_name category_medium_cd category_medium_name category_small_cd category_small_name unit_price unit_cost
P040101001 04 惣菜 0401 御飯類 040101 弁当類 198 149
P040101002 04 惣菜 0401 御飯類 040101 弁当類 218 164
P040101003 04 惣菜 0401 御飯類 040101 弁当類 230 173

これで１００本終わりです。おつかれさまでした！¶