100knocks

概要

データサイエンス100本ノック（構造化データ加工編）はデータ解析を各種言語(SQL, R, Python)で練習するためのリポジトリ。環境構築、データのセットがDockerで簡単に準備できる。標準はJupyter Notebookを使うようになっているが、使いにくいのでorg-modeを使用してSQLを書く。

環境構築

Emacs / org-modeで実行できるようにする。

Repository: データサイエンス100本ノック
READMEの通りに docker-compose を実行する。
標準のJupyter Notebookで問題なくアクセスできることを確認する。
init.elに追加。babelでSQLをロードする。これをしないと実行できない。

(org-babel-do-load-languages
 'org-babel-load-languages
 '((sql . t)))

実行したいorgファイル見出しにDB接続設定を追加。100本ノックのPostgreSQLのデフォルト設定になっている(READMEに記載されてる)。

:PROPERTIES:
  :header-args+: :results table
  :header-args+: :engine postgresql
  :header-args+: :dbhost localhost
  :header-args+: :dbuser postgres
  :header-args+: :dbpassword postgres12345
  :header-args+: :database dsdojo_db
:END:

コードブロックで C-c C-c で実行できる。

SELECT COUNT(1) FROM store CROSS JOIN product;

count
531590

Memo

:header-args+: :results table :header-args+: :engine postgresql :header-args+: :dbhost localhost :header-args+: :dbuser postgres :header-args+: :dbpassword postgres12345 :header-args+: :database dsdojo_db

等しい場合は同一順位: RANK

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

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

customer_id	amount	ranking
CS011415000006	10925	1
ZZ000000000000	6800	2
CS028605000002	5780	3
ZZ000000000000	5480	4
ZZ000000000000	5480	4
CS015515000034	5480	4
CS021515000089	5440	7
ZZ000000000000	5440	7
CS020515000102	5280	9
CS021515000089	5280	9

等しい場合でも別順位: ROW_NUMBER

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

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

customer_id	amount	ranking
CS011415000006	10925	1
ZZ000000000000	6800	2
CS028605000002	5780	3
CS015515000034	5480	4
ZZ000000000000	5480	5
ZZ000000000000	5480	6
ZZ000000000000	5440	7
CS021515000089	5440	8
ZZ000000000000	5280	9
CS009415000038	5280	10

ユニーク件数

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

SELECT count(distinct customer_id) FROM receipt

count
8307

GROUP BY, 日付でのMAX

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

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

customer_id	max
CS001311000059	20180211
CS004614000122	20181228
CS003512000043	20180106
CS011615000061	20190503
CS029212000033	20180621
CS007515000119	20190511
CS034515000123	20190708
CS004315000058	20170517
CS026414000014	20190720
CS001615000099	20170729

集計結果に対する条件: HAVING

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

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

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

中央値: PERCENTILE_CONT

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

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

store_cd	amount_50per
S13052	190
S14010	188
S14050	185
S13003	180
S13018	180

サブクエリ: WITH

S-029: レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに商品コード（product_cd）の最頻値を求めよ。

WITH product_mode AS (
    SELECT store_cd,product_cd, COUNT(1) as mode_cnt,
        RANK() OVER(PARTITION BY store_cd ORDER BY COUNT(1) DESC) AS rnk
    FROM receipt
    GROUP BY store_cd,product_cd
)
SELECT store_cd,product_cd, mode_cnt
FROM product_mode
WHERE rnk = 1
ORDER BY store_cd,product_cd;

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
S13008	P060303001	77
S13009	P060303001	64
S13015	P071401001	34
S13016	P071102001	32
S13017	P060101002	31
S13018	P071401001	47
S13019	P071401001	70
S13020	P071401001	79
S13031	P060303001	115
S13032	P060303001	85
S13035	P040503001	39
S13037	P060303001	88
S13038	P060303001	41
S13039	P071401001	36
S13041	P071401001	70
S13043	P060303001	56
S13044	P060303001	96
S13051	P050102001	5
S13051	P071003001	5
S13051	P080804001	5
S13052	P050101001	4
S14006	P060303001	70
S14010	P060303001	68
S14011	P060101001	51
S14012	P060303001	142
S14021	P060101001	30
S14022	P060303001	71
S14023	P071401001	70
S14024	P060303001	96
S14025	P060303001	46
S14026	P071401001	40
S14027	P060303001	152
S14028	P060303001	140
S14033	P071401001	68
S14034	P060303001	71
S14036	P040503001	19
S14036	P060101001	19
S14040	P060303001	80
S14042	P050101001	34
S14045	P060303001	33
S14046	P060303001	71
S14047	P060303001	36
S14048	P050101001	17
S14049	P060303001	55
S14050	P060303001	9

標本分散: VAR_SAMP

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

SELECT store_cd, var_samp(amount) as vars_amount
FROM receipt
GROUP BY store_cd
ORDER BY vars_amount desc
LIMIT 5

store_cd	vars_amount
S13052	441863.252526233968
S14011	306442.242431568709
S14034	297068.392740060738
S13001	295558.842617712478
S13015	295427.197085853584

標本標準偏差: STDDEV_SAMP

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

SELECT store_cd, stddev_samp(amount) as stds_amount
FROM receipt
GROUP BY store_cd
ORDER BY stds_amount desc
LIMIT 5

store_cd	stds_amount
S13052	664.727953772244
S14011	553.572255836190
S14034	545.039808399406
S13001	543.653237475610
S13015	543.532149082144

%刻み: PERCENTILE_CONT

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

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

amount_25per	amount_50per	amount_75per	amount_100per
102	170	288	10925

サブクエリ: WITH

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

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

avg
2547.7422345292559595

WHEREの条件に集約関数を使う

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

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

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件表示させよ。

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

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	南藤沢店

値がないときのデフォルト値: COALESCE

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

WITH customer_amount AS (
    SELECT customer_id, SUM(amount) AS sum_amount
    FROM receipt
    GROUP BY customer_id
)
SELECT c.customer_id, COALESCE(a.sum_amount,0)
FROM customer c
LEFT JOIN customer_amount a
ON c.customer_id = a.customer_id
WHERE c.gender_cd = '1'
      and c.customer_id not like 'Z%'
LIMIT 10

customer_id	coalesce
CS021313000114	0
CS031415000172	5088
CS028811000001	0
CS001215000145	875
CS015414000103	3122
CS033513000180	868
CS035614000014	0
CS011215000048	3444
CS009413000079	0
CS040412000191	210

複数のWITH

S-039: レシート明細テーブル（receipt）から売上日数の多い顧客の上位20件と、売上金額合計の多い顧客の上位20件を抽出し、完全外部結合せよ。ただし、非会員（顧客IDが’Z’から始まるもの）は除外すること。

WITH customer_days AS (
    select customer_id, count(distinct sales_ymd) come_days
    FROM receipt
    WHERE customer_id NOT LIKE 'Z%'
    GROUP BY customer_id
    ORDER BY come_days DESC LIMIT 20
),
customer_amount AS (
    SELECT customer_id, sum(amount) buy_amount
    FROM receipt
    WHERE customer_id NOT LIKE 'Z%'
    GROUP BY customer_id
    ORDER BY buy_amount DESC LIMIT 20
)
SELECT COALESCE(d.customer_id, a.customer_id), d.come_days, a.buy_amount
FROM customer_days d
FULL JOIN customer_amount a
ON d.customer_id = a.customer_id;

coalesce	come_days	buy_amount
CS040214000008	23
CS015415000185	22	20153
CS010214000010	22	18585
CS028415000007	21	19127
CS010214000002	21
CS017415000097	20	23086
CS016415000141	20	18372
CS021514000045	19
CS022515000226	19
CS031414000051	19	19202
CS039414000052	19
CS014214000023	19
CS021515000172	19
CS031414000073	18
CS007515000107	18
CS014415000077	18
CS021515000056	18
CS032415000209	18
CS021515000211	18
CS022515000028	18
CS011415000006		16094
CS016415000101		16348
CS030415000034		15468
CS021515000089		17580
CS034415000047		16083
CS006515000023		18372
CS038415000104		17847
CS015515000034		15300
CS032414000072		16563
CS011414000106		18338
CS001605000009		18925
CS009414000059		15492
CS035414000024		17615
CS007514000094		15735

集計と、表示を分離するイメージ。with句で集計をやり、その結果をメインの句で好きに加工できる。

直積: CROSS JOIN

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

SELECT COUNT(1) FROM store CROSS JOIN product;

count
531590

差分: LAG

S-041: レシート明細テーブル（receipt）の売上金額（amount）を日付（sales_ymd）ごとに集計し、前日からの売上金額増減を計算せよ。なお、計算結果は10件表示すればよい。

WITH sales_amount_by_date AS (
    SELECT sales_ymd, SUM(amount) as amount FROM receipt
    GROUP BY sales_ymd
    ORDER BY sales_ymd
)
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,
    amount - LAG(amount, 1) OVER(ORDER BY sales_ymd) as diff_amount
FROM sales_amount_by_date
LIMIT 10;

sales_ymd	lag_ymd	amount	lag_amount	diff_amount
20170101		33723
20170102	20170101	24165	33723	-9558
20170103	20170102	27503	24165	3338
20170104	20170103	36165	27503	8662
20170105	20170104	37830	36165	1665
20170106	20170105	32387	37830	-5443
20170107	20170106	23415	32387	-8972
20170108	20170107	24737	23415	1322
20170109	20170108	26718	24737	1981
20170110	20170109	20143	26718	-6575

複数のJOIN条件

S-042: レシート明細テーブル（receipt）の売上金額（amount）を日付（sales_ymd）ごとに集計し、各日付のデータに対し、１日前、２日前、３日前のデータを結合せよ。結果は10件表示すればよい。

WITH sales_amount_by_date AS (
    SELECT sales_ymd, SUM(amount) as amount FROM receipt
    GROUP BY sales_ymd
    ORDER BY sales_ymd
),
sales_amount_lag_date AS (
    SELECT sales_ymd,
        COALESCE(LAG(sales_ymd, 3) OVER (ORDER BY sales_ymd),
        MIN(sales_ymd) OVER (PARTITION BY NULL)) as lag_date_3,
        amount
    FROM sales_amount_by_date
)
SELECT a.sales_ymd, b.sales_ymd as lag_ymd,
    a.amount as amount, b.amount as lag_amount
FROM sales_amount_lag_date a
JOIN sales_amount_lag_date b
ON b.sales_ymd >= a.lag_date_3
    and b.sales_ymd < a.sales_ymd
ORDER BY sales_ymd, lag_ymd
LIMIT 10;

sales_ymd	lag_ymd	amount	lag_amount
20170102	20170101	24165	33723
20170103	20170101	27503	33723
20170103	20170102	27503	24165
20170104	20170101	36165	33723
20170104	20170102	36165	24165
20170104	20170103	36165	27503
20170105	20170102	37830	24165
20170105	20170103	37830	27503
20170105	20170104	37830	36165
20170106	20170103	32387	27503

日付の変換

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

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

customer_id	to_char
CS021313000114	19810429
CS037613000071	19520401
CS031415000172	19761004
CS028811000001	19330327
CS001215000145	19950329
CS020401000016	19740915
CS015414000103	19770809
CS029403000008	19730817
CS015804000004	19310502
CS033513000180	19620711

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

20211002090535-100knocks.org

20211002090535-100knocks.org

100knocks

概要

環境構築

Memo

等しい場合は同一順位: RANK

等しい場合でも別順位: ROW_NUMBER

ユニーク件数

GROUP BY, 日付でのMAX

集計結果に対する条件: HAVING

中央値: PERCENTILE_CONT

サブクエリ: WITH

標本分散: VAR_SAMP

標本標準偏差: STDDEV_SAMP

%刻み: PERCENTILE_CONT

サブクエリ: WITH

WHEREの条件に集約関数を使う

結合のスマートな書き方

値がないときのデフォルト値: COALESCE

複数のWITH

直積: CROSS JOIN

差分: LAG

複数のJOIN条件

日付の変換

練習用

Tasks

1~50問

Reference

文芸的プログラミング - Wikipedia

Archives

Files

20211002090535-100knocks.org

Latest commit

History

20211002090535-100knocks.org

File metadata and controls

100knocks

概要

環境構築

Memo

等しい場合は同一順位: RANK

等しい場合でも別順位: ROW_NUMBER

ユニーク件数

GROUP BY, 日付でのMAX

集計結果に対する条件: HAVING

中央値: PERCENTILE_CONT

サブクエリ: WITH

標本分散: VAR_SAMP

標本標準偏差: STDDEV_SAMP

%刻み: PERCENTILE_CONT

サブクエリ: WITH

WHEREの条件に集約関数を使う

結合のスマートな書き方

値がないときのデフォルト値: COALESCE

複数のWITH

直積: CROSS JOIN

差分: LAG

複数のJOIN条件

日付の変換

練習用

Tasks

1~50問

Reference

文芸的プログラミング - Wikipedia

Archives