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

参照(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-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

解説:

このコードはSQL言語で書かれており、「WITH」句を使用して「sales_amount_by_date」と「sales_amount_by_date_with_lag」という2つの一時テーブルを作成しています。そして、「sales_amount_by_date_with_lag」テーブルから特定の列を選択し、「LAG」関数を使用して連続する日付間の売上金額の差を計算しています。

具体的には、このコードは次のような処理を実行している。

最初の「WITH」句は、「sales_amount_by_date」という一時テーブルを作成し、「recipate」テーブルの各日付の売上金額の合計を計算する。

2番目の「WITH」句は、「sales_amount_by_date_with_lag」という別の一時テーブルを作成し、「LAG」関数を使用して前日の売上金額と連続する日付間の売上金額の差分を計算する。

最後の「SELECT」文では、「sales_amount_by_date_with_lag」テーブルから、売上日、売上金額、前日、前売上金額、連続する日付の売上金額の差などの列を選択する。

そして、結果は売上日の昇順でソートされ、最初の10行に限定されます。

要約すると、このコードは「LAG」関数を使用して「レシート」テーブルの連続する日付間の売上金額の差を計算し、最初の10行の結果を表示します。

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

解説:

このコードもSQL言語で書かれており、「WITH」句を使用して「sales_amount_by_date」と「sales_amount_lag_date」という2つの一時テーブルを作成しています。そして、「sales_amount_lag_date」テーブルに対して自己結合を行い、特定の条件に基づいて結果にフィルタをかけています。

具体的には、このコードは以下の処理を実行しています。

最初の「WITH」句は、「sales_amount_by_date」という一時テーブルを作成し、「recipate」テーブルの各日付における売上金額の合計を計算する。

2番目の「WITH」句は、「sales_amount_lag_date」という別の一時テーブルを作成し、「LAG」関数を使用して、「sales_amount_by_date」テーブルの3日前の売上日および各日の売上金額を計算する。

最後の「SELECT」文は、「sales_amount_lag_date」テーブルから特定の列を選択し、自身との自己結合を実行する。結合条件は、ある行の売上日が他の行の売上日よりも小さく、2行目の売上日が3日前の1行目の売上日以下である行のみを含むように結果をフィルタリングします。

そして、結果は、販売日と前販売日の昇順でソートされ、最初の10行に限定されます。

要約すると、このコードは、「receipt」テーブルの各日付の売上日と売上金額を含むテーブルに対して、自己結合を実行しています。結合条件は、ある行の売上日が他の行の売上日よりも小さく、かつ2行目の売上日が3日前の1行目の売上日以下である行のみを含むように結果をフィルタリングします。結果は、最初の10行の販売日、販売金額、前販売日、前販売金額が表示されます。

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

解説:

このコードもSQL言語で書かれており、「WITH」句を使用して「sales_amount_by_date」という名前の一時テーブルを作成しています。次に、「LAG」関数を使用して、「sales_amount_with_lag」という名前の別の一時テーブルに追加の列を作成しています。最後に、「sales_amount_with_lag」テーブルから特定のカラムを選択し、特定の条件に基づいて結果をフィルタリングしています。

具体的には、このコードでは以下の処理を実行しています。

最初の「WITH」句は、「sales_amount_by_date」という一時テーブルを作成し、「recipate」テーブルの各日付における売上金額の合計を計算する。

2番目の "WITH "句は、"sales_amount_with_lag "という別の一時テーブルを作成し、"sales_amount_by_date "テーブルの各日の売上日および前3日間の売上金額を計算するために、"LAG "関数を使用しています。

最後の「SELECT」文は、「sales_amount_with_lag」テーブルから特定の列を選択し、3つ前の日付がNULLでない行のみを含むように結果をフィルタリングする。つまり、テーブルの最初の2行は、3番目の前の日付を持っていないので除外されます。

次に、結果を販売日の昇順で並べ替え、最初の10行に限定します。

要約すると、このコードは「レシート」テーブルの各日付の売上金額の合計を計算し、「LAG」関数を使用して各日付の前3日間の売上日と売上金額を計算している。結果は、各日付の売上日と売上金額、前3日間の売上日と売上金額を表示します。結果は、最初の2行が3つ目の前の日付を持っていないため、除外するようにフィルタリングされます。結果の最初の10行は、売上日の昇順で表示されます。

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

解説:

このSQLコードでは、年齢層（時代）と性別ごとに売上金額の合計をまとめたsales_summaryという名前のテーブルを作成します。

まず、顧客テーブルと領収書テーブルの結合を使用して、gender_era_amount という名前の共通テーブル式（CTE）が作成されます。このCTEは、年齢グループ（時代）と性別に基づいてレコードをグループ化し、総売上金額を計算する。

次に、gender_era_amount CTE のデータを集計して sales_summary テーブルが作成されます。このテーブルには、時代、男性、女性、不明という4つのカラムがあります。SELECT句内のSUM(CASE...)文は、性別ごとの売上金額を別々の列にピボットするために使用されます。

最後に、SELECT ステートメントを使用して、新しく作成された sales_summary テーブルからすべての行を取得します。

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

解説:

このコードはSQLで書かれており、sales_summaryというテーブルからデータを取得する。このコードの目的は、sales_summaryテーブルのデータを、より簡単に分析できる形式に変換することです。

sales_summaryテーブルからデータを取得するためにSELECT文を使用し、複数のSELECT文の結果を1つの結果セットに結合するためにUNION ALL演算子を使用します。

最初のSELECT文は、sales_summaryテーブルからデータを取得し、eraとmaleのカラムを選択する。また、gender_cd という新しいカラムも含まれており、このカラムの値は 00 である。このカラムは、文字列リテラル '00' AS gender_cd を使って追加されています。ASキーワードは、列の名前をgender_cdに変更するために使用されます。このSELECT文の結果セットには、era、gender_cd、amountの3つのカラムが含まれています。

2番目のSELECT文は最初の文と似ていますが、eraとfemaleの列を選択し、gender_cdの列を01に設定しています。このSELECT文の結果セットにも、era、gender_cd、amountの3つのカラムが含まれています。

このSELECT文の結果セットにも、era、gender_cd、amountの3つのカラムが含まれています。

最後に、UNION ALL演算子を使用して、3つのSELECT文の結果セットを1つの結果セットにまとめます。結果のテーブルは、eraとgender_cdの組み合わせごとに1行を持ち、amount列はgender_cdの値に応じて、男性、女性、または不明のいずれかの値を含むことになる。

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

解説:

このコードはSQLで書かれており、customerというテーブルからデータを取得する。このコードの目的は、customerテーブルからcustomer_idとbirth_dayカラムを取得し、birth_dayカラムを特定の方法でフォーマットすることである。

SELECT文を使用してcustomerテーブルからデータを取得し、LIMIT句を使用して返される行の数を10行に制限しています。

TO_CHAR関数は、birth_dayカラムを指定された書式の文字列に変換するために使用されます。この場合、フォーマットは'YYYYMMDD'で、誕生日の年、月、日を表しています。結果の文字列の長さは8文字で、年は4文字、月は2文字、日は2文字で表されます。

ASキーワードは、結果のカラムの名前をbirth_dayに変更するために使用されます。このSELECT文の結果セットには、customer_idとbirth_dayという2つのカラムが含まれています。birth_day 列には、各顧客の誕生日が指定されたフォーマットで含まれています。

全体として、このコードは、customerテーブルからcustomer_idとbirth_dayカラムを取得し、birth_dayカラムを特定の方法でフォーマットしたい場合に便利です。このフォーマットされたデータは、分析またはレポート作成に便利です。

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

解説:

このコードはSQLで書かれており、customerというテーブルからデータを取得する。このコードの目的は、customerテーブルからcustomer_id列とapplication_date列を取得し、application_date列を日付形式に変換することである。

SELECT文を使用してcustomerテーブルからデータを取得し、LIMIT句を使用して返される行の数を10行に制限しています。

TO_DATE関数は、application_dateカラムを文字列から日付形式に変換するために使用されます。この場合、形式は'YYYYMMDD'で、申請日の年、月、日を表しています。結果として、日付の値は yyyy-mm-dd という形式になります。

ASキーワードは、結果のカラムをapplication_dateにリネームするために使用されます。このSELECT文の結果セットには、customer_idとapplication_dateという2つのカラムが含まれます。application_dateカラムには、各顧客の申込日が日付形式で含まれています。

全体として、このコードは、顧客テーブルから customer_id と application_date 列を取得し、application_date 列を日付形式に変換したい場合に便利です。このフォーマットされたデータは、特に日付の計算や比較が含まれる場合、分析またはレポート作成に便利です。

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

解説:

このコードはSQLで書かれており、receiptというテーブルからデータを取得する。このコードの目的は、reciportテーブルからreciport_no、reciport_sub_no、sales_ymd列を取得し、sales_ymd列を日付形式に変換することである。

SELECT文は、recipateテーブルからデータを取得するために使用され、LIMIT句は、返される行の数を10に制限するために使用されています。

CAST関数は、sales_ymdカラムを整数から文字列に変換するために使用されます。結果の文字列には、販売日の年、月、日が'YYYYMMDD'のフォーマットで含まれることになる。

TO_DATE関数は、sales_ymdの文字列を日付形式に変換するために使用されます。この場合、フォーマットは 'YYYYMMDD' で、これは販売日の年、月、日を表します。結果として得られる日付の値は、yyyy-mm-ddの形式となります。

AS キーワードは、結果のカラムを sales_ymd にリネームするために使用されます。このSELECT文の結果セットには、recipate_no、recipate_sub_no、およびsales_ymdの3つのカラムが含まれています。sales_ymdカラムには、各レシートの販売日が日付形式で含まれています。

全体として、このコードは、recipate_no、recipate_sub_no、および sales_ymd 列を receipt テーブルから取得し、sales_ymd 列を日付形式に変換したい場合に便利です。このフォーマット化されたデータは、特に日付の計算や比較が含まれる場合、分析またはレポート作成に便利です。

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

解説:

このコードはSQLで書かれており、receiportというテーブルからデータを取得する。このコードの目的は、recipateテーブルからrecipate_no、recipate_sub_no、sales_epoch列を取得し、sales_epoch列を日付形式に変換することである。

SELECT文を使用して領収書テーブルからデータを取得し、LIMIT句を使用して返される行の数を10行に制限しています。

TO_TIMESTAMP関数を使用して、sales_epochカラムを整数値からタイムスタンプ形式に変換しています。結果として得られるタイムスタンプは、1970年1月1日00:00:00 UTCからの秒数を表すことになります。

CAST関数は、タイムスタンプを日付形式に変換するために使用されます。この場合、結果の日付値はyyyy-mm-ddの形式となります。

AS キーワードは、結果のカラムの名前を sales_ymd に変更するために使用されます。このSELECT文の結果セットには、recipate_no、recipate_sub_no、およびsales_ymdの3つのカラムが含まれています。sales_ymdカラムには、各レシートの販売日が日付形式で含まれています。

全体として、このコードは receipt テーブルから receipt_no、recipate_sub_no、および sales_epoch 列を取得し、sales_epoch 列を日付形式に変換したいときに便利です。このフォーマットされたデータは、特に日付の計算や比較が含まれる場合、分析またはレポート作成に便利です。

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

解説:

このコードはSQLで書かれており、receiportというテーブルからデータを取得する。このコードの目的は、recipateテーブルからrecipate_no、recipate_sub_no、sales_epoch列を取得し、sales_epochのタイムスタンプ列から年を抽出することである。

領収書テーブルからデータを取得するためにSELECT文を使用し、LIMIT句を使用して返される行の数を10行に制限しています。

TO_TIMESTAMP関数を使用して、sales_epoch列を整数値からタイムスタンプ形式に変換します。結果として得られるタイムスタンプは、1970年1月1日00:00:00 UTCからの秒数を表すことになります。

EXTRACT関数は、sales_epochのタイムスタンプから年を抽出するために使用されます。結果として得られる値は、年を表す整数となります。

ASキーワードは、結果のカラムをsales_yearにリネームするために使用されています。このSELECT文の結果セットには、recipate_no、recipate_sub_no、およびsales_yearの3つのカラムが含まれています。sales_year列には、各レシートの販売日の年が含まれています。

全体として、このコードは receipt テーブルから receipt_no、recipate_sub_no、および sales_epoch 列を取得し、sales_epoch タイムスタンプ列から年を抽出したい場合に便利です。この抽出されたデータは、販売日の年号に基づいてデータをグループ化したりフィルタリングしたりするのに便利です。

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

解説:

このコードはSQLで書かれており、receiportというテーブルからデータを取得する。このコードの目的は、receiportテーブルからreciport_no、reciport_sub_no、sales_epoch列を取得し、sales_epochタイムスタンプ列から月を抽出し、結果の月を先頭ゼロの2桁の文字列にフォーマットすることである。

SELECT文は、レシートテーブルからデータを取得するために使用され、LIMIT句は、返される行の数を10に制限するために使用されます。

TO_TIMESTAMP関数を使用して、sales_epochカラムを整数値からタイムスタンプ形式に変換しています。結果として得られるタイムスタンプは、1970年1月1日00:00:00 UTCからの秒数を表すことになります。

EXTRACT関数は、sales_epochのタイムスタンプから月を抽出するために使用されます。結果の値は、月を表す整数となります。

TO_CHAR関数は、抽出された月を、必要に応じて先頭のゼロを含む文字列に変換するために使用される。FM00'フォーマット文字列は、結果の文字列が固定幅の2文字であることを指定し、先頭のゼロは削除される。

AS キーワードは、結果のカラムの名前を sales_month に変更するために使用されます。このSELECT文の結果セットには、recipate_no、recipate_sub_no、およびsales_monthの3つのカラムが含まれています。sales_monthカラムには、各レシートの販売日の月が、先頭の0を含む2桁の文字列として含まれています。

全体として、このコードは、reciportテーブルからreciport_no、reciport_sub_no、およびsales_epoch列を取得し、sales_epochタイムスタンプ列から月を抽出し、結果の月を、先頭がゼロの2桁の文字列としてフォーマットしたい場合に有用である。このフォーマットされたデータは、販売日の月に基づいてデータをグループ化またはフィルタリングするのに便利です。

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

解説:

このコードはSQLで書かれており、receiportというテーブルからデータを取得する。このコードの目的は、receiportテーブルからreciport_no、reciport_sub_no、sales_epoch列を取得し、sales_epochタイムスタンプ列から日を抽出し、結果の日を先頭ゼロの2桁の文字列にフォーマットすることである。

SELECT文は、レシートテーブルからデータを取得するために使用され、LIMIT句は、返される行の数を10に制限するために使用されます。

TO_TIMESTAMP関数を使用して、sales_epochカラムを整数値からタイムスタンプ形式に変換しています。結果として得られるタイムスタンプは、1970年1月1日00:00:00 UTCからの秒数を表すことになります。

EXTRACT関数は、sales_epochのタイムスタンプから日を抽出するために使用されます。結果の値は、日を表す整数になります。

TO_CHAR関数は、抽出された日を、必要に応じて先頭のゼロを含む文字列に変換するために使用されます。FM00'フォーマット文字列は、結果の文字列が固定幅の2文字であることを指定し、先頭のゼロは削除される。

AS キーワードは、結果のカラムの名前を sales_day に変更するために使用されます。このSELECT文の結果セットには、recipate_no、recipate_sub_no、sales_dayの3つのカラムが含まれています。sales_day列には、各レシートの販売日の日が、先頭の0を含む2桁の文字列として含まれています。

全体として、このコードは、reciportテーブルからreciport_no、reciport_sub_no、およびsales_epochカラムを取得し、sales_epochタイムスタンプカラムから日を抽出し、結果の日を、先頭のゼロを持つ2桁の文字列としてフォーマットしたい場合に有用である。このフォーマットされたデータは、販売日の日にちに基づいてデータをグループ化したりフィルタリングしたりするのに便利です。

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

解説:

このSQLコードは、recipateというテーブルからデータを選択しています。このコードの目的は、recipateテーブルの各顧客の売上金額の合計を計算することと、各顧客の売上金額の合計が2000より大きいか否かを示すフラグを作成することです。

SELECT文は、customer_id列、SUM関数で計算した各顧客の売上合計金額、売上合計金額が2000より大きい場合は1、それ以外は0を返すCASE式を取得する。フラグを含む結果のカラムは sales_flg と名付けられます。

WHERE句は、顧客IDが文字'Z'で始まるレコードをフィルタリングして除外します。これは、特定の顧客を計算やフラグ立てから除外するために使用することができます。

GROUP BY句は、顧客ごとに総売上金額とフラグが計算されるように、結果を顧客IDでグループ化します。

LIMIT句は、出力を最初の10個の結果に制限しています。

全体として、このコードは、レシート・テーブルの各顧客の売上合計額を計算し、売上合計額がある閾値（この場合、2000）より大きいかどうかを示すフラグを作成したい場合に便利です。これは、高額な買い物をする顧客を特定したり、顧客の売上金額を集計して分析する必要がある他のタイプの分析に役立つ可能性があります。

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

解説:

このSQLコードには、custという名前の共通テーブル式（CTE）が含まれており、customerテーブルからデータを選択して、postal_cdに基づいて各顧客のpostal_flgフラグを計算しています。

postal_flgフラグはCASE式を使用して計算され、postal_cdの最初の3文字が100から209（含む）であるかどうかをチェックし、真の場合は1、偽の場合は0という値を割り当てます。このフラグは、例えば郵便番号の範囲によって顧客をグループ化するために使用することができます。

rectという2番目のCTEは、recipateテーブルから個別のcustomer_idを選択します。

最後のSELECT文は、customer_id列でcustとrectのCTEを結合し、postal_flg列で結果をグループ分けしています。COUNT(DISTINCT c.customer_id)関数は、各 postal_flg値について異なる顧客の数を数えます。

全体として、このコードは、顧客テーブルと領収書テーブルに格納されているデータに基づいて、郵便番号の範囲によって顧客をグループ化し、各範囲の異なる顧客の数をカウントしたい場合に便利です。

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

解説:

このSQLコードは、customerテーブルから各顧客のcustomer_id、address、prefecture_cd codeを選択する。

このコードのCASE式は、address列の最初の3文字をチェックし、以下の条件に基づいて対応するprefecture_cdの値を割り当てます。

住所欄の最初の3文字が'埼玉県'であれば、prefecture_cd値は'11'に設定される。

住所欄の最初の3文字が'千葉県'の場合、prefecture_cd の値は'12'に設定される。

住所欄の最初の3文字が'東京都'の場合、prefecture_cdの値は'13'に設定される。

住所欄の最初の3文字が'神奈川'の場合、prefecture_cd の値は'14'に設定される。

このコードは、顧客テーブルに格納されている住所情報をもとに、顧客を都道府県別に分類したい場合に有効である。結果として得られるprefecture_cdカラムは、さらなる分析やグループ分けに利用できる。

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

解説:

このSQLコードは、customerテーブルから各顧客のcustomer_id、address、prefecture_cd codeを選択する。

このコードのCASE式は、正規表現パターン'^.*?[都道府県]'を使って住所列から都道府県名を抽出し、以下の条件に基づいて対応するprefecture_cd値を割り当てています。

抽出された都道府県名が'埼玉県'である場合、prefecture_cd 値は'11'に設定される。
抽出された都道府県名が '千葉県' の場合、prefecture_cd の値は '12' に設定される。
抽出された都道府県名が'東京都'の場合、prefecture_cd の値は'13'に設定される。
抽出された都道府県名が'神奈川県'の場合、prefecture_cdの値は'14'に設定される。

このコードは、前の例と似ていますが、SUBSTR関数の代わりにSUBSTRING関数を使って住所欄から都道府県名を抽出しています。正規表現パターン「^.*?[都道府県]」は、任意の文字(^.*?)で始まり、都、道、府、県のいずれかの文字で終わる文字列にマッチします。このコードは、顧客テーブルの住所情報が標準化されておらず、都道府県名の書式にばらつきがある場合に有効である。作成されたprefecture_cdカラムは、さらなる分析やグループ化に使用することができる。

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

解説:

このSQLコードは、customerという名前のテーブルに対してクエリを実行し、最初の10行のcustomer_id、address、prefecture_cdカラムを選択しています。prefecture_cd列は、LIKE演算子を使用したaddress列と一致するようにCASE文を使用して作成されています。

具体的には、CASE文は、日本の都道府県（埼玉県％、千葉県％、東京都％、神奈川県％）の後に任意の文字列が続く文字列パターンとLIKE演算子を使用して住所カラムをチェックする。住所カラムがいずれかのパターンに一致する場合、対応する都道府県コード（11、12、13、14）がprefecture_cdカラムに付与される。

このコードは、先に示した他の2つの例と比較して、住所欄から都道府県コードを抽出する代替的な方法である。

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

解説:

このSQLコードは、次のタスクを実行します。

SUM関数を使用して、顧客IDごとにレシートテーブルの金額列を合計し、顧客IDでグループ化することにより、各顧客の売上金額の合計を計算します。結果のテーブルは、sales_amountという名前のサブクエリとして保存されます。

窓関数であるPERCENTILE_CONT関数を使用して、sales_amountサブクエリからsum_amountカラムの四分位数（25位、50位、75位）を計算する。

CROSS JOIN操作でsales_amountサブクエリとsales_pctサブクエリを結合し、sales_amountテーブルの各行について四分位値を取得します。

最後に、新しい列pct_groupを計算し、売上総額に基づいて各顧客にパーセンタイル・グループを以下のように割り当てます。

1:sum_amountが25パーセンタイルより小さい場合（pct25)
2:sum_amountが25パーセンタイル以上50パーセンタイル未満の場合（pct50）。
3:sum_amountが50パーセンタイル以上75パーセンタイル未満の場合（pct75）。
sum_amount が 75 パーセンタイル以上であれば 4。

最終結果は、最初の10行に限定されます。

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

解説:

このコードは、customerテーブルからcustomer_id、birth_day、era（計算フィールド）を選択しています。eraフィールドは、顧客の年齢（明示的に選択されていないが、計算に使用される）を決定し、10年未満に切り捨てられた年齢と60との間の低い値を取ることによって計算される。

結果はcustomer_idとbirth_dayでグループ化されます。次に、HAVING句を使用して、年齢が60未満の顧客をフィルタリングしています。

要するに、このコードは、顧客テーブルから顧客の情報を選択し、年齢と時代を計算し、customer_idとbirth_dayによって結果をグループ化し、時代が60未満の顧客をフィルタリングしています。

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

解説:

このSQLコードは、customer_id、birth_day、そしてgender_cdとeraからなる連結された文字列を選択する。eraは顧客の年齢を10年単位で切り捨てたもので、最大eraは60歳です。gender_eraの文字列は、gender_cdとeraを連結することで生成されます。

SQLコードは、customer_idとbirth_dayで結果をグループ化し、結果の最初の10行のみを返します。

全体として、このコードは年齢と性別を含む顧客データの要約を作成し、さらに時代のディメンションを追加します。作成されたテーブルは、年齢と性別に基づく顧客のさらなる分析やセグメンテーションに使用できる可能性があります。

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

解説:

このSQLコードは、customer_idを選択し、3つの新しい列、gender_cd_0、gender_cd_1、gender_cd_9を作成します。これらの新しいカラムは、CASE文を使用して作成されています。

CASE文は条件文であり、一連の条件を評価し、対応する結果を返します。この場合、各顧客について、コードはgender_cdカラムの値をチェックする。gender_cd が '0' に等しい場合、コードは gender_cd_0 列に '1' を返し、他の列には '0' を返します。gender_cd が '1' の場合、コードは gender_cd_1 カラムに '1' を返し、他のカラムに '0' を返す。gender_cd が '9' に等しい場合、コードは gender_cd_9 カラムに '1' を、その他のカラムに '0' を返します。

結果の出力は customer_id、gender_cd_0、gender_cd_1、gender_cd_9 列を持ち、各顧客について、新しい列の1つだけが顧客の性別を示す '1' という値を持つことになる。

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

解説:

このコードはSQL言語で書かれており、SQLをサポートするデータベース管理システムを使っています。

このコードは、recipateというテーブルに対してデータ分析タスクを実行しています。このテーブルには、customer_id や金額などの列を持つトランザクション・データが含まれています。

このコードは、共通テーブル式（CTE）を使用して、2つの一時テーブルsales_amountとstats_amountを作成しています。

sales_amountという名前の最初のCTEは、文字'Z'で始まらないcustomer_idを持つ各顧客が使った金額の合計を計算しています。取引データをcustomer_idでグループ化し、各customer_idの金額の合計を計算しています。

stats_amountという名前の2番目のCTEは、各顧客が使用した合計金額の平均と標準偏差を計算しています。

最後のSELECT文は、sales_amountテーブルからカラムを選択し、クロスジョインを使用してstats_amountテーブルと結合します。また、合計金額から平均金額を引き、それを標準偏差で割ることで、各顧客が使用した標準化された金額を計算しています。

最後のLIMIT 10は、出力を最初の10行に制限しています。

全体として、このコードは取引データに対して統計分析を行い、文字 'Z' で始まる customer_id を持たない各顧客が使用した標準化された金額を算出しています。

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

解説:

このコードは前のコードと非常によく似ていますが、stats_amount CTEに重要な違いが1つあります。

標準偏差の計算にSTDDEV_POP関数を使用する代わりに、STDDEV_SAMP関数を使用しています。

STDDEV_SAMPは、データのサンプルに基づく母集団の標準偏差の推定値であるサンプル標準偏差を計算します。

これに対してSTDDEV_POPは、母集団全体が分析可能であると仮定して、正確な母集団標準偏差を計算する。

つまり、このコードの出力は前のものと似ていますが、標準偏差の計算は母集団全体ではなく、データのサンプルに基づくことになります。

一般に、データセットが非常に大きい場合、サンプルと母集団の標準偏差の差は小さくなります。しかし、データセットが小さければ、その差は大きくなる可能性があります。

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

解説:

このコードもレシート・テーブルを解析していますが、標準化された金額を計算する代わりに、各顧客が使用した秤量された金額を計算しています。

このコードでは、sales_amountとstats_amountという2つのCTEを使用していますが、これは前のコードと同様です。

最初のCTEは、文字「Z」で始まらないcustomer_idを持つ各顧客が使った合計金額を計算し、2番目のCTEは、任意の顧客が使った合計金額の最大値と最小値を計算しています。

最後のSELECT文は、sales_amountテーブルからカラムを選択し、クロスジョインを使用してstats_amountテーブルと結合しています。

また、各顧客が使用したスケーリングされた金額を計算します。これは、使用した合計金額から使用した最小金額を引き、使用した合計金額の範囲で割ったもので、最大金額と最小金額の差になります。

式の前の1.0 *は、結果を浮動小数点数に変換するために使用されます。これは、式で使用される値のいずれかが浮動小数点数である場合に必要です。

最後のLIMIT 10は、出力を最初の10行に制限しています。

全体として、このコードは、文字「Z」で始まるcustomer_idを持たない各顧客が費やした金額（scaled amount）を求めるというデータ分析タスクを実行しています。スケーリングされた金額とは、0から1の間の値で、すべての顧客が使った総額の範囲に対する、各顧客が使った総額の割合を表しています。