データサイエンス100本ノック（構造化データ加工編）- SQL Part 4 (Q61 to Q80)の解説です。

参照(Reference) : 「データサイエンティスト協会スキル定義委員」の「データサイエンス100本ノック（構造化データ加工編）」

The Data Scientist Society Github :

GitHub - The-Japan-DataScientist-Society/100knocks-preprocess: データサイエンス100本ノック（構造化データ加工編）

データサイエンス100本ノック（構造化データ加工編）. Contribute to The-Japan-DataScientist-Society/100knocks-preprocess development by creating a...

Data Science 100 Knocks (Structured Data Processing) URL :

100knocks-preprocess/docker/work/answer/ans_preprocess_knock_Python.ipynb at master · The-Japan-DataScientist-Society/100knocks-preprocess

データサイエンス100本ノック（構造化データ加工編）. Contribute to The-Japan-DataScientist-Society/100knocks-preprocess development by creating a...

はじめに
使い方
データ加工100本ノック

はじめに

データベースは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

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)

# MagicコマンドでSQLを書くための設定
%sql $dsl

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

解説:

これは、データベースからデータを取得するSQLクエリです。ステップバイステップで分解してみましょう。

クエリは%%sqlマジックコマンドで始まりますが、これはJupyterノートブックで次のコードがSQLコードであることを指定するために使用されます。

メインクエリでは、customer_id、sum_amount、log_amountという3つのカラムを選択します。これらのカラムは、括弧内のサブクエリから派生することになります。

括弧内のサブクエリでは、まず、customer_id が文字 "Z" で始まるレコードをすべてフィルタリングして除外します。これは、NOT LIKE 'Z%'という条件によって行われます。

フィルタリングされたレコードは、GROUP BY句を使用してcustomer_idでグループ化されます。

SUM関数は、各顧客IDの合計金額を計算するために使用されます。

AS sum_amount句は、計算されたカラムをsum_amountに名前を変更します。

サブクエリはsum_amount_tblとしてエイリアスされます。

最後に、外側クエリはcustomer_id、sum_amount、sum_amountに0.5を加えた対数（ベースe）を選択します。これはLOG関数を使って計算され、log_amountという別名が付けられています。

結果はLIMIT句で10行に制限されています。

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

解説:

これは、データベースからデータを取得するSQLクエリです。ステップバイステップで分解してみましょう。

クエリは%%sqlマジックコマンドで始まりますが、これはJupyterノートブックで次のコードがSQLコードであることを指定するために使用されます。

メインクエリでは、customer_id、sum_amount、log_amountという3つのカラムを選択します。これらのカラムは、括弧内のサブクエリから派生することになります。

括弧内のサブクエリでは、まず、customer_id が文字 "Z" で始まるレコードをすべてフィルタリングして除外します。これは、NOT LIKE 'Z%'という条件によって行われます。

フィルタリングされたレコードは、GROUP BY句を使用してcustomer_idでグループ化されます。

SUM関数は、各顧客IDの合計金額を計算するために使用されます。

AS sum_amount句は、計算されたカラムをsum_amountに名前を変更します。

サブクエリはsum_amount_tblとしてエイリアスされます。

最後に、外側クエリはcustomer_id、sum_amount、sum_amountの自然対数（底e）プラス0.5を選択する。これはLN関数を使って計算され、log_amountという別名が付けられています。

結果は、LIMIT句で10行に制限されます。

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

解説:

これは、データベースからデータを取得するSQLクエリです。ステップバイステップで分解してみましょう。

クエリは%%sqlマジックコマンドで始まり、これはJupyterノートブックで次のコードがSQLコードであることを指定するために使用されます。

メインクエリでは、product_cd、unit_price、unit_cost、unit_profitの4つのカラムを選択します。これらのカラムはproductテーブルから派生します。

FROM句は、製品テーブルを指定します。

LIMIT句は、クエリによって返される行の数を10に制限します。

このクエリでは、単価から単価を引くことでユニット・プロフィットを計算します。これは - 演算子を使用して行われます。

クエリの結果には、productテーブルの最初の10行のproduct_cd、unit_price、unit_cost、unit_profitが含まれています。

全体として、このクエリは、選択した製品の価格、コスト、および利益に関する情報を取得します。

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

解説:

このコードはSQLを使って、「product」というデータベーステーブルの上位10商品の平均単価利益率を照会しています。以下、コードの各部の解説をします。

"%%sql "はJupyter notebookのマジックコマンドで、以下のコードをSQLとして解釈するようにノートブックに指示します。

"SELECT "は、データベースからデータを取得することを示すSQLキーワードです。

"AVG "は、与えられた値の集合の平均を計算するSQL関数です。

"((unit_price * 1.0 - unit_cost) / unit_price)" は、各商品の単位利益率を計算する計算式です。単価から単価を差し引き、その結果を単価で割るというものです。1.0の乗算は、除算が整数値ではなく、浮動小数点値を返すようにするために行われます。

"AS unit_profit_rate" は、計算された値を "unit_profit_rate" というカラムに代入します。

"FROM product "は、"product "テーブルからデータを取得することを指定する。

「LIMIT 10」は、返される行数を10行に制限します。つまり、何らかの基準（コードスニペットでは指定されていません）で上位10個の製品の結果のみを表示することを意味します。

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

解説:

このコードでは、SQLを使用して、"product "というデータベーステーブルの修正版を照会しています。修正内容は、各商品の単価から新しい価格を算出し、この新しい価格を使って新しい利益率を算出しています。以下、コードの各部を説明します。

"%%sql "はJupyter notebookのマジックコマンドで、次のコードをSQLとして解釈するようノートブックに指示します。

"WITH new_price_tbl AS (...)" は "new_price_tbl" という Common Table Expression (CTE) をセットアップしています。これにより、後続のクエリで使用できるサブクエリを定義することができます。

"( SELECT product_cd, unit_price, unit_cost, TRUNC(unit_cost / 0.7) AS new_price FROM product ) "は、「product」テーブルからデータを選択し、単価に基づいて新しい価格を計算し、「new_price」という新しい列に割り当てるサブクエリである。TRUNC」関数を使用して、計算された新価格を最も近い整数に切り捨てています。これは、会社が製品の価格を整数値で決めたいという仮定に基づいています。

"SELECT *, (new_price - unit_cost) / new_price AS new_profit_rate FROM new_price_tbl LIMIT 10 ; "は、"new_price_tbl "からすべての列を選択するメインクエリーです。CTEで、各商品の新しい利益率を計算します。新しい利益率は、新しい価格から単価を引き、その結果を新しい価格で割って、「new_profit_rate」という新しいカラムに代入することで計算されます。LIMIT 10」句は、返される行数を10行に制限します。つまり、何らかの基準（コード・スニペットでは指定されていません）によって上位10商品の結果のみを表示したいのです。

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

解説:

このコードはSQLで書かれており、「new_price_tbl」という名前の共通テーブル式（CTE）を使用して「product」という名前のテーブルからデータを選択するために使用されます。このコードでは、各商品の単価を0.7で割って新しい価格を算出し、新しい価格から単価を引き、その結果を新しい価格で割って新しい利益率を算出しています。

以下、コードのステップ・バイ・ステップの内訳を示します。

WITH new_price_tbl AS (...): これは、括弧で囲まれたSELECT文のデータを一時的に保存するために使用される「new_price_tbl」という名前の共通テーブル式（CTE）を定義しています。

SELECT product_cd, unit_price, unit_cost, ROUND(unit_cost / 0.7) AS new_price FROM product: これは、商品コード、単価、単価を選択し、各商品の単価を0.7で割り、その結果を丸めることで新しい価格を計算します。

SELECT *, (new_price - unit_cost) / new_price AS new_profit_rate FROM new_price_tbl LIMIT 10: これは 'new_price_tbl' CTEからすべての列を選択し、新価格から単価を引き、その結果を新価格で割ることによって新利益率を計算する。最後に、出力を最初の10行に制限しています。

まとめると、このコードは、CTEを使って必要なデータを一時的に保存し、そのデータに対して算術演算を行うことで、各商品の新価格と新利益率を算出しています。

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

解説:

このコードは前のコードと似ていますが、新しい価格を四捨五入する代わりに、CEIL関数を使用して最も近い整数に切り上げられます。

以下、コードのステップ・バイ・ステップの内訳を示します。

WITH new_price_tbl AS (...): これは、括弧で囲まれたSELECT文のデータを一時的に保存するために使用される「new_price_tbl」という名前の共通テーブル式（CTE）を定義しています。

SELECT product_cd, unit_price, unit_cost, CEIL(unit_cost / 0.7) AS new_price FROM product: これは、商品コード、単価、単価を選択し、各商品の単価を0.7で割り、CEIL関数で小数点以下を切り上げて、新しい価格を計算します。

SELECT *, (new_price - unit_cost) / new_price AS new_profit_rate FROM new_price_tbl LIMIT 10: これは 'new_price_tbl' CTEからすべての列を選択し、新価格から単価を引き、結果を新価格で割ることによって新利益率を計算する。最後に、出力を最初の10行に制限しています。

まとめると、このコードは、CTEを使って必要なデータを一時的に保存し、そのデータに対して算術演算を行うことで、各商品の新価格と新利益率を算出しています。ただし、新しい価格は、前のコードのように丸めるのではなく、CEIL関数を使用して最も近い整数に切り上げられます。

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

解説:

このコードはSQLで書かれており、'product'という名前のテーブルからデータを選択するために使用されます。このコードでは、商品コード、単価を選択し、各商品の税込価格を計算します。単価に1.1を掛け、結果を切り捨てて小数点以下を削除しています。

以下は、コードのステップバイステップの内訳です。

SELECT product_cd, unit_price, TRUNC(unit_price * 1.1) AS tax_price FROM product: 商品コード、単価を選択し、各商品の税込価格を計算します。単価に1.1を掛け、TRUNC関数で小数点以下を切り捨てています。

FROM product: データを選択する「商品」テーブルを指定します。

LIMIT 10：出力を最初の10行に制限します。

要約すると、このコードでは、各商品の単価に1.1を掛けて税込価格を計算し、TRUNC関数で小数点以下を切り捨てています。

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

解説:

このコードはSQLで書かれており、「amount_all」と「amount_07」という2つの共通テーブル式（CTE）を使用して、「recipate」と「product」という2つのテーブルからデータを選択するために使用されています。このコードでは、各顧客が使用した金額の合計と、メジャー・カテゴリー・コードが「07」である商品に使用した金額の合計を計算します。その後、2つのCTEを結合し、各顧客のカテゴリ「07」商品の販売率を計算します。

以下は、コードのステップ・バイ・ステップです。

WITH amount_all AS(...)。これは、括弧で囲まれたSELECT文のデータを一時的に保存するために使用される「amount_all」という名前の共通テーブル式（CTE）を定義しています。

SELECT customer_id, SUM(amount) AS sum_all FROM receipt GROUP BY customer_id: これは、顧客IDと各顧客が使用した金額の合計を「reciption」テーブルから選択し、その結果を顧客IDでグループ化します。

また、amount_07 AS(...)を使用します。これは、括弧で囲まれたSELECT文のデータを一時的に保存するために使用される「amount_07」という名前の別のCTEを定義しています。

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: これは、顧客IDとメジャー・カテゴリー・コードが'07'の製品に費やされた金額の合計を'reciport'と'product'テーブルから選択し、製品コードで2つのテーブルを結合し、メジャー・カテゴリー・コード'07'で製品をフィルタし、顧客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: これは、顧客ID、各顧客が使ったすべての金額の合計、各顧客がカテゴリー「07」製品に使った金額の合計、カテゴリー「07」製品に使った金額の合計を顧客が使ったすべての金額の合計で割ることによって各顧客のカテゴリー「07」製品の販売率を選択する。最後に、顧客 ID で 2 つの CTE を結合し、出力を最初の 10 行に制限しています。

要約すると、このコードは、まず各顧客の全消費金額の合計と各顧客のカテゴリー「07」製品への消費金額の合計を計算し、後者を前者で割ることによって、各顧客のカテゴリー「07」製品の販売率を計算する。これは、2つのCTEを使って必要なデータを一時的に保存し、そのデータに対して算術演算と結合を行うことで実現する。

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

解説:

このコードはSQLで書かれており、「receipt_distinct」という名前の共通テーブル式（CTE）を使用して、「receipt」と「customer」という名前の2つのテーブルからデータを選択するために使用されています。このコードでは、顧客の申込日と、その顧客が行った別個の販売日の間に経過した日数を計算しています。

以下はコードのステップ・バイ・ステップの内訳です。

WITH receipt_distinct AS(...)とします。これは、括弧で囲まれたSELECT文のデータを一時的に保存するために使用される「receipt_distinct」という名前の共通テーブル式（CTE）を定義しています。

SELECT distinct customer_id, sales_ymd FROM receipt: これは、'receipt'テーブルからdistinct customer IDとsales year-month-dayを選択するものである。

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: これは、顧客ID、売上年月日、申込日、顧客の申込日からその顧客が販売した明細の日付までの経過日数を選択する。経過日数の計算は、売上年月日と申込日をタイムスタンプに変換し、その差を日数で計算する。最後に、「receipt_distinct」CTEと「customer」テーブルを顧客IDで結合し、出力を最初の10行に制限しています。

要約すると、このコードでは、まず 'receipt' テーブルから顧客 ID と売上年・月・日を選択し、それを 'customer' テーブルと結合して顧客の申込日を取得することにより、顧客の申込日とその顧客が行った個別の売上日の間に経過した日数を計算しています。次に、売上年月日と申込日をタイムスタンプに変換し、その差を日数で計算する計算が行われる。これは、CTEを使用して必要なデータを一時的に保存し、そのデータに対して算術演算と結合を行うことで実現する。

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

解説:

このコードはSQLで書かれており、「recipate_distinct」と「time_age_tbl」という2つの共通テーブル式（CTE）を使用して、「recipate」と「customer」という2つのテーブルからデータを選択するために使用されています。このコードでは、顧客の申込日と、その顧客が行った別個の販売日の間の経過月数を計算しています。

以下は、コードのステップバイステップです。

WITH receipt_distinct AS (...): これは、括弧で囲まれたSELECT文のデータを一時的に保存するために使用される「receipt_distinct」という名前の共通テーブル式（CTE）を定義しています。

SELECT DISTINCT customer_id, sales_ymd FROM receipt: これは、'receipt'テーブルからdistinct customer IDとsales year-month-dayを選択するものである。

WITH time_age_tbl AS (...): これは、括弧で囲まれたSELECT文のデータを一時的に保存するために使用される「time_age_tbl」という名前の別のCTEを定義するものである。

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, 'YYYYMMMDD'))) AS time_age FROM receipt_distinct r JOIN customer c ON r.customer_id = c.customer_id: これは、顧客ID、売上年月日、申込日、および顧客の申込日からその顧客が行った個別の売上日までの経過時間を選択するものです。経過時間の計算では、売上年月日と申込日をタイムスタンプに変換し、AGE関数を用いてその差を計算する。最後に、「receipt_distinct」CTEと「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: これは顧客ID、売上年月日、申し込み日、顧客の申し込み日からその顧客が行った個別の売上日までの経過月数を選択するもの。経過月数の計算では、経過時間の年成分と月成分を抽出し、月数に変換しています。最後に、出力を最初の10行に限定しています。

要約すると、このコードでは、まず「recipate」テーブルから顧客IDと売上年月を選択し、「customer」テーブルと結合して顧客の申込日を取得することで、顧客の申込日からその顧客が行った個別の売上日までの経過月数を計算します。次に、CTEとAGE関数を使って、売上年月日と申込日の間の経過時間を計算し、その結果を月単位に変換する。これは、2つのCTEを使用して必要なデータを一時的に保存し、そのデータに対して算術演算と結合を実行することによって行われます。

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

解説:

このSQLコードは、購入した顧客の顧客ID、販売日、申込日を取得し、申込日と販売日の間の経過年数を計算する。

このコードでは、まず、receipt_distinct という共通のテーブル式を作成し、receipt テーブルから顧客 ID と販売日の個別の組み合わせを選択します。これにより、各顧客が複数回購入した場合でも、1回しかカウントされないことが保証されます。

メインクエリでは、receipt_distinctテーブルから顧客ID、販売日、申込日を選択し、EXTRACT関数を使用して申込日と販売日の間の経過年数を計算する。AGE関数で2つの日付の時間差を計算し、EXTRACT関数で結果の間隔から年成分を抽出する。

結果は、各顧客の顧客ID、販売日、申込日、経過年数を示す表となる。LIMIT句は、結果セットの最初の10行に出力を制限するために使用される。

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

解説:

このSQLコードは、購入した顧客の顧客ID、販売日、申込日を取得し、申込日と販売日の間の経過秒数を計算する。

このコードでは、まず、receipt_distinct という共通のテーブル式を作成し、receipt テーブルから顧客 ID と販売日の個別の組み合わせを選択します。これにより、各顧客が複数回購入した場合でも、1回しかカウントされないことが保証されます。

次にメインクエリでは、receipt_distinctテーブルから顧客ID、売上日、申込日を選択し、EXTRACT関数とTO_TIMESTAMP関数を使って、申込日と売上日の間の経過秒数を計算します。EXTRACT(EPOCH FROM <interval>)関数を使用して、結果の間隔から秒数を抽出しています。

結果は、各顧客の顧客ID、販売日、申込日、経過秒数を示す表である。LIMIT句は、結果セットの最初の10行に出力を制限するために使用されます。

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

解説:

このSQLコードは、elapsed_days_tblという名前の共通テーブル式（CTE）を定義することから始まります。このCTEは、レシート・テーブルの各 sales_ymd 日付の曜日（0から6までの数字で、0は日曜日）を抽出します。

メインクエリでは、elapsed_days_tbl CTEからsales_ymdとelapsed_daysが選択されています。次に、このコードでは、sales_ymdからelapsed_daysを引くことで新しい日付を計算します。これは、elapsed_days を整数としてキャストし、sales_ymd から減算することで行われます。結果の日付は、sales_ymdの日付に対応する週の月曜日となります。

コードの最後にあるLIMIT 10句は、出力を最初の10行に制限しています。

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

解説:

このSQLコードは、customerテーブルから10人の顧客のランダムなサブセットを選択するものです。RANDOM()関数は、テーブルの各行に対して0から1の間のランダムな値を生成し、WHERE句は、生成された値が0.01以下（選択される確率1%）であるかどうかに基づいて行をフィルタします。

これは、テーブルの他の条件や順序に関係なく、各行が選択される確率が等しいことを意味します。LIMIT句は、条件を満たす最初の10行に出力を制限するために使用されます。

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

解説:

このコードは、SQLプログラミング言語を使用して、"customer "テーブルから顧客のランダムなサブセットを選択するものです。

このコードでは、まず「customer」テーブルからすべての列を選択し、ROW_NUMBER関数とCOUNT関数を使用して2つの列を追加して「customer_tmp」という一時テーブルを作成します。

ROW_NUMBER関数は、ORDER BY RANDOM()句の結果に基づいて、テーブルの各行に対して一意の整数を割り当てます。これにより、テーブルの行の順序が効果的にランダム化されます。

OVER()句を持つCOUNT関数は、テーブル内の行の総数を数え、各行のこの数を含む "cnt "という新しい列を作成します。

次にメインのSELECT文は、ROW_NUMBER関数で作成された「row」列でフィルタリングして、一時テーブルから顧客のサブセットを選択する。具体的には、COUNT関数で作成された "cnt "カラムによって決定される、最初の1％の行を選択します。

最後の句「LIMIT 10」は、結果をサブセットの最初の10行に制限し、元の「customer」テーブルから10人の顧客のランダムサンプルを効果的に返します。

全体として、このコードは、テストや探索的分析の目的で、大きなテーブルから顧客のランダムなサンプルを選択する簡単な方法です。サンプリングの割合（この場合は1%）を調整することで、より大きなサンプルや小さなサンプルを作成することができます。

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

解説:

このSQLコードは、"customer "というテーブルから顧客のランダムなサンプルを選択し、サンプル内の各性別に該当する顧客の数を算出しています。

以下は、コードの各パートが行っていることの内訳です。

WITH句。WITH句：この句は、後続のSELECT文で使用する2つの一時テーブル、「customer_random」と「customer_rownum」を作成します。

customer_randomテーブル。このテーブルは、「customer」テーブルから顧客のランダムなサンプルを選択し、各顧客に性別に基づく「customer_r」値を割り当てる。cnt」列は、各性別グループの顧客の総数をカウントする。

customer_rownumテーブル。このテーブルは、「customer_random」テーブルの各顧客に、性別で仕切られた行番号（"rn"）を追加する。

SELECTステートメント。この文は、各性別グループにおいて、行番号がその性別グループにおける顧客の総数の10％以下である顧客の数を数える（すなわち、行番号による顧客の上位10％の顧客である）。結果は、「gender_cd」と「customer_num」の2つの列を持つ表で、「gender_cd」は性別コード、「customer_num」はその性別グループの中で行番号で上位10％に入る顧客の数である。

全体として、このコードは、より大きなデータセットから顧客のランダムなサンプルを選択し、その性別分布を分析するのに便利です。

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

解説:

このコードはSQLプログラミング言語を使用して、「customer」テーブルから顧客のサブセットをランダムに選択し、性別でグループ化し、各性別グループの顧客数を返すものです。

このコードでは、まず「customer」テーブルからすべての列を選択し、ROW_NUMBER関数とCOUNT関数を使用して2つの列を追加して「cusotmer_random」という一時テーブルを作成します。

ROW_NUMBER関数は、ORDER BY RANDOM()句の結果に基づいて、テーブルの各行に対して一意の整数を割り当てます。これは、テーブルの行の順序を効果的にランダム化します。

ROW_NUMBER関数のPARTITION BY gender_cd句は、ランダムな順序が各性別グループに対して別々に行われることを保証します。

OVER(PARTITION BY gender_cd)句を持つCOUNT関数は、各性別グループの行の総数をカウントし、各行のこのカウントを含む「cnt」と呼ばれる新しい列を作成する。

次にメインのSELECT文は、ROW_NUMBER関数で作成された「rn」列でフィルタリングすることにより、一時テーブルから顧客のサブセットを選択する。具体的には、COUNT関数で作成された "cnt "カラムによって決定されるように、各性別グループの行の最初の10％を選択する。

WHERE句は、「cusotmer_random」テーブルの行をフィルタリングし、「rn」値がそれぞれの性別グループの「cnt」値の10％以下であるものだけを含める。

最後に、メインのSELECT文は、gender_cdによって行をグループ化し、各グループの行のカウントを返し、効果的にランダムサンプルの各性別グループの顧客の数を提供します。

全体として、このコードは、サンプルが元のテーブルと同様に各性別グループの割合を含むことを保証しながら、大きなテーブルから顧客のサブセットをランダムに選択する方法です。サンプリングの割合（この場合、10％）は、より大きいまたは小さいサンプルを作成するために調整することができます。

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

解説:

このコードは、SQLプログラミング言語を使用して、対数変換された購入金額に基づいて、購入金額の合計が標準から大きく外れている顧客を識別しています。

このコードでは、まず、「レシート」テーブルからcustomer_idと購入金額の合計を選択し、customer_idでグループ化して「sales_amount」という一時テーブルを作成します。また、購入金額の合計はLN関数を使って対数変換され、ゼロの対数を取らないように0.5が加算されます。

次にメインのSELECT文は、一時テーブルからcustomer_id、sum_amount、log_sum_amountカラムを選択する。また、CROSS JOINを使用して、"stats_amount "という別の一時テーブルに参加します。"stats_amount "は、"sales_amount "テーブルからlog_sum_amount列の平均と母標準偏差を選択して作成します。

WHERE句は、"sales_amount "テーブルの行をフィルタリングして、log_sum_amountと平均log_sum_amountの差の絶対値をlog_sum_amountの標準偏差で割って、3より大きいものだけを含めます。これは、購入金額が平均値から3標準偏差以上離れている顧客を特定する方法であり、統計分析において外れ値を特定するための一般的な閾値である。

最後に、メインのSELECT文は、出力を10行に制限しています。

全体として、このコードは、購入金額を対数スケールに変換し、統計的外れ値検出基準を適用することにより、データセットの平均購入金額に対して、著しく大きな購入または小さな購入を行った顧客を識別する方法である。

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

解説:

このコードは、データベースに問い合わせるためにSQLプログラミング言語を使用しています。具体的には、共通テーブル式（CTE）を使用して、「sales_amount」という一時テーブルを作成し、「recipate」テーブルの各顧客の売上合計額を算出しています。

CTEのWHERE句は、IDが "Z "で始まる顧客をフィルタリングして除外する。GROUP BY句は、顧客IDによって売上をグループ化します。

メインクエリでは、「sales_amount」テーブルと、PERCENTILE_CONT関数を使用して売上金額の25パーセンタイルと75パーセンタイルを計算するサブクエリとの間でCROSS JOINが実行されます。これらのパーセンタイルは、"stats_amount "という一時テーブルに格納されます。

最後に、メインクエリのWHERE句で、25パーセンタイルや75パーセンタイルから四分位範囲（IQR）の1.5倍以上離れている売上金額をフィルタリングして除外します。これは、データセットの外れ値を特定するための一般的な方法です。結果は、上位10行に限定されます。

全体として、このコードはデータベースを照会して、同業他社と比較して売上金額が異常に高い、または低い顧客を、四分位範囲内の位置に基づいて特定するものです。

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

解説:

このコードは、SQLプログラミング言語を使用してデータベースに問い合わせをしています。具体的には、"product "テーブルを照会し、SUM文とCASE文を組み合わせて、テーブルの各列にある欠損値（NULL）の数を数えています。

このクエリでは、6つのカラムを選択し、テーブルのカラム名と一致するエイリアスを付与しています。「product_cd」、「category_major_cd」、「category_medium_cd」、「category_small_cd」、「unit_price」、「unit_cost」。

各カラムについて、CASE文は値がNULLかどうかをチェックし、NULLの場合は1を、そうでない場合は0を返します。そして、SUM関数で1と0を合計して、各列のNULL値の総数を求めます。

LIMIT 10句は、結果をテーブルの最初の10行に制限しますが、この場合、要約統計にしか興味がないので、特に意味はないでしょう。

全体として、このコードは「product」テーブルの欠損データをチェックし、各列の欠損値の数の簡単な要約を得るための簡単な方法である。

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

解説:

このコードは、SQLプログラミング言語を使用して、「product」テーブルから「unit_price」列と「unit_cost」列の両方がNULLでない行だけを選択して、「product_1」という新しいテーブルを作成しています。

1行目の「DROP TABLE IF EXISTS product_1;」は、「product_1」テーブルがすでに存在するかどうかをチェックし、存在する場合は削除して、新しく作成するテーブルとの競合を避ける安全策である。

2行目の「CREATE TABLE product_1 AS (...); 」は、「product_1」という新しいテーブルを作成し、その内容はSELECT文の結果に基づいていることを指定します。

SELECT文はproductテーブルからすべての列を選択しますが、WHERE句が含まれており、unit_priceまたはunit_cost列がNULLである行をフィルタリングします。

全体として、このコードは、元の "product "テーブルから、"unit_price "と "unit_cost "の両方のカラムに有効なデータを持つ行だけを含む新しいテーブルを作成します。これは、データをクリーンアップし、分析やモデリングに備えるための一般的な方法です。データの欠落は、いくつかの分析手法で問題を引き起こすことがあるからです。