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

参照(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-021: レシート明細データ（receipt）に対し、件数をカウントせよ。

In [23]:

%%sql
-- コード例1
SELECT COUNT(1) FROM receipt;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.

Out[23]:

count
104681

解説:

このコードは、"receipt "というテーブルから行数を選択するSQLクエリです。

SELECTキーワードは、データベースからデータを取得することを示すために使用されます。

COUNT関数は、「receipt」テーブルの行数をカウントするために使用されます。この場合、「COUNT(1)」文は、各行の最初の列の値がNULLでないテーブルの行数を数えるために使用されます。これは、どの列がNULLであるかに関係なくテーブルのすべての行をカウントする「COUNT(*)」の使用と同等である。

FROMキーワードは、データを取得したいテーブルを示すために使用され、この場合、「receipt」テーブルを示します。

つまり、要約すると、このクエリは「recipate」テーブルの行数を選択し、返すということです。

In [24]:

%%sql
-- コード例2（*でもOK）
SELECT COUNT(*) FROM receipt;

 * postgresql://padawan:***@db:5432/dsdojo_db
1 rows affected.

Out[24]:

count
104681

解説:

このコードは、"receipt "というテーブルから行数を選択するSQLクエリです。

SELECTキーワードは、データベースからデータを取得することを示すために使用されます。

COUNT関数は、「receipt」テーブルの行数をカウントするために使用されます。この場合、"COUNT(*) "ステートメントは、どの列がNULLであるかどうかにかかわらず、テーブルのすべての行を数えるために使用される。

FROMキーワードは、データを取得したいテーブル、この場合は「recipate」テーブルを示すために使用されます。

つまり、要約すると、このクエリは「recipate」テーブルのすべての行を選択し、そのカウントを返します。

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

解説:

このコードは、"receipt "というテーブルから、異なる顧客IDのカウントを選択するSQLクエリである。

%%sqlは、このコードがSQLコードであることを示すJupyter Notebookのセルマジックコマンドである。

SELECTキーワードは、データベースからデータを取得することを示すために使用されます。

COUNT関数は、「recipate」テーブル内の異なる顧客IDの数を数えるために使用されます。この場合、「COUNT(DISTINCT customer_id)」ステートメントは、テーブル内のユニークな顧客IDの数をカウントするために使用されます。

DISTINCTキーワードは、「customer_id」列のユニークな値のみをカウントすることを指定するために使用される。

FROMキーワードは、データを取得したいテーブルを示すために使用され、この場合、「receipt」テーブルを示します。

つまり、要約すると、このクエリは「recipate」テーブルのユニークな顧客IDのカウントを選択し、返します。

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

解説:

SQLコードが2回繰り返されています。

このコードは、"receipt "というテーブルから "store_cd"、"amount"、"quantity "という3つの列を選択する。このテーブルには、ある店舗の売上データが含まれています。

SUM関数を使用して、各店舗の「金額」列と「数量」列の合計を計算する。

GROUP BY句は、売上データを店舗ごとにグループ化するために使用されます。つまり、このクエリーは、「store_cd」カラムの一意の店舗コードごとに、「金額」と「数量」の合計を返すことになります。

最初に出現したコードは、2番目に出現したコードと全く同じであるため、重複や間違いかもしれません。それにもかかわらず、このクエリは、「receipt」テーブルの各店舗の販売商品の合計金額と数量を選択し、返します。

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

解説:

このコードは、"receipt "というテーブルから各顧客の最大売上日（sales_ymd）を選択するSQLクエリである。

%%sqlは、このコードがSQLコードであることを示すJupyter Notebookのセルマジックコマンドである。

SELECTキーワードは、データベースからデータを取得することを示すために使用されます。

customer_idと「MAX(sales_ymd)」は、「recipate」テーブルから選択したいカラムである。MAX」関数は、「customer_id」カラムの一意の顧客IDごとに「sales_ymd」カラムの最大値を選択するために使用される。

FROMキーワードは、データを取得するテーブルを示すために使用され、この場合、「recipate」テーブルである。

GROUP BY句は、顧客IDによって売上データをグループ化するために使用されます。つまり、このクエリは、「customer_id」列の一意の顧客IDごとに、最大売上日を返すことになる。

LIMITキーワードは、クエリによって返される結果の数を制限するために使用されます。この場合、「LIMIT 10」ステートメントは、結果を上位10行に制限します。

つまり、要約すると、このクエリは、「receipt」テーブルから各顧客の最大売上日を選択して返し、結果を上位10人の顧客に限定している。

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

解説:

このコードは、"receipt "というテーブルから各顧客の最小売上日（sales_ymd）を選択するSQLクエリである。

%%sqlは、このコードがSQLコードであることを示すJupyter Notebookのセルマジックコマンドである。

SELECTキーワードは、データベースからデータを取得することを示すために使用されます。

customer_idと「MIN(sales_ymd)」は、「recipate」テーブルから選択したいカラムである。MIN関数は、「customer_id」カラムの一意の顧客IDごとに「sales_ymd」カラムの最小値を選択するために使用される。

FROMキーワードは、データを取得するテーブルを示すために使用され、この場合、「recipate」テーブルからデータを取得する。

GROUP BY句は、顧客IDによって売上データをグループ化するために使用されます。つまり、このクエリは、「customer_id」列の一意の顧客IDごとに、売上日の最小値を返すことになります。

LIMITキーワードは、クエリによって返される結果の数を制限するために使用されます。この場合、「LIMIT 10」ステートメントは、結果を上位10行に制限します。

つまり、要約すると、このクエリは、「receipt」テーブルから各顧客の最小売上日を選択して返し、結果を上位10人の顧客に限定している。

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

解説:

このコードは、"receipt "というテーブルから顧客ID、最大売上日、最小売上日を選択するSQLクエリである。

%%sqlは、このコードがSQLコードであることを示すJupyter Notebookのセルマジックコマンドである。

SELECTキーワードは、データベースからデータを取得することを示すために使用されます。

customer_id、「MAX(sales_ymd)」、「MIN(sales_ymd)」は、「recipe」テーブルから選択したいカラムである。MAXは、「customer_id」列の一意の顧客IDごとに「sales_ymd」列の最大値を選択するために使用され、「MIN」は、「customer_id」列の一意の顧客IDごとに「sales_ymd」列の最小値を選択するために使用されている。

FROMキーワードは、データを取得したいテーブルを示すために使用され、この場合、「recipate」テーブルである。

GROUP BY句は、顧客IDによって売上データをグループ化するために使用されます。つまり、このクエリは、"customer_id "列のユニークな顧客IDごとに、売上日の最大値と最小値を返すことになります。

HAVING句は、ある条件に基づいて結果をフィルタリングするために使用されます。この場合、与えられた顧客IDについて、最大売上日と最小売上日が同じであってはならない、という条件があります。つまり、このクエリーは、異なる日付に購入した顧客のみを返すことになります。

LIMITキーワードは、クエリによって返される結果の数を制限するために使用されます。この場合、「LIMIT 10」ステートメントは、結果を上位10行に制限します。

つまり、このクエリは、異なる日付に購入した顧客の顧客ID、最大販売日、最小販売日を「レシート」テーブルから選択して返し、結果を上位10人の顧客に限定しています。

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

解説:

このコードは、"receipt "というテーブルから店舗コードとその平均売上金額を選択するSQLクエリである。

%%sqlは、このコードがSQLコードであることを示すJupyter Notebookのセルマジックコマンドである。

SELECTキーワードは、データベースからデータを取得することを示すために使用されます。

store_cd" と "AVG(amount) AS avg_amount" は "receipt" テーブルから選択したいカラムです。AVG関数は、「store_cd」列の一意の店舗コードごとに平均売上金額を計算するために使用されます。ASキーワードは、このカラムに「avg_amount」という別名をつけるために使用されます。この名前は、クエリ結果で表示される名前です。

FROMキーワードは、データを取得するテーブルを示すために使用され、この場合、「receipt」テーブルである。

GROUP BY句は、売上データを店舗コードでグループ化するために使用されます。つまり、このクエリでは、「store_cd」列の一意の店舗コードごとに平均売上金額を計算することになります。

ORDER BY句は、平均売上金額に基づいて結果を降順に並べるために使用されます。つまり、平均売上金額が最も高い店舗が結果の上位に表示されることになります。

LIMITキーワードは、クエリによって返される結果の数を制限するために使用されます。この場合、「LIMIT 5」文は、結果を上位5店舗に限定しています。

つまり、このクエリは、「レシート」テーブルから店舗コードとその平均売上額を選択し、平均売上額に基づいて降順に並べ、結果を上位5店舗に限定して返します。

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

解説:

これは、"receipt "という名前のテーブルからデータを取得するSQLクエリである。

このクエリは、テーブルから2つのカラムを選択します。"store_cd "と "amount_50per "です。store_cd」列は店舗コードを表し、「amount_50per」列は各店舗内のレシート1枚あたりの使用金額の中央値を計算する。

中央値の算出に使用する関数は、「PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY amount)」です。この関数は、「金額」列を昇順に並べ、50パーセンタイルの値を計算する。言い換えれば、順序付けられた金額のリストの真ん中の値を見つけるということです。

このクエリは、「GROUP BY」句で結果を店舗コード別にグループ化し、「ORDER BY」句で金額の中央値の降順で結果を並べます。そして、「LIMIT」句を使用して、出力を上位5件に制限しています。

つまり、このクエリは、レシート1枚あたりの使用金額の中央値が最も高い上位5店舗を返します。

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

解説:

これは、"receipt "というテーブルからデータを取得するSQLクエリです。

このクエリでは、"product_cnt "というCTE（Common Table Expression）を使用して、各商品が各店舗で販売された回数を計算しています。GROUP BY」句を使用して「store_cd」と「product_cd」でデータをグループ化し、「COUNT(1)」関数を使用して各グループの行数を数えます。結果は "mode_cnt "カラムに格納される。

次に、このクエリは「product_mode」という別のCTEを使用して、「RANK() OVER(PARTITION BY store_cd ORDER BY mode_cnt DESC)」関数で各店舗の商品の「mode_cnt」値をランク付けします。この関数は、各商品の "mode_cnt "の値に基づいて、各店舗内でのランクを割り当てる。モードカウントが最も高い商品には、ランク1が割り当てられます。

最後に、このクエリは、「WHERE rnk = 1」句で結果をフィルタリングして各店舗のトップセラー商品を選択し、「ORDER BY」句で店舗と商品コードごとに並べます。各店舗の売れ筋商品の店舗コード、商品コード、モード数を返し、「LIMIT」句で出力を最初の10行に限定する。

要約すると、このクエリは、販売回数に基づいて、各店舗で最も売れている商品を見つけ、上位10商品それぞれの店舗コード、商品コード、モードカウントを返します。

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

解説:

これは、"receipt "という名前のテーブルからデータを取得するSQLクエリである。

このクエリでは、2つのカラムを選択しています。"store_cd "と各店舗の "product_cd "列のモードです。

MODE() WITHIN GROUP(ORDER BY product_cd)」関数は、「store_cd」の各グループ内の「product_cd」値のモードを計算する。モードとは、データ集合の中で最も頻繁に出現する値のことである。ORDER BY」句は、モードを計算する前に「product_cd」値を昇順で並べることを指定する。

このクエリは、「GROUP BY」句を使用して「store_cd」によってデータをグループ化し、「ORDER BY」句を使用して「store_cd」によって結果を順序付ける。また、「LIMIT」句で出力を最初の10行に制限しています。

つまり、このクエリは、各店舗の「product_cd」値のモードを返し、これは各店舗で最もよく売られている商品を表している。結果は店舗コード順に並べられ、上位10店舗に限定されます。

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

解説:

これは、"receipt "という名前のテーブルからデータを取得するSQLクエリである。

このクエリでは、2つのカラムを選択しています。"store_cd "と各店舗の "amount "カラムの母分散です。

VAR_POP(amount)」関数は、「store_cd」の各グループ内の「amount」値の分散を計算する。分散とは、データセットの広がりや分散を表す指標です。VAR_POP "の "POP "は "population "を意味し、分散を計算するためにデータセット全体が使用されることを意味します。

このクエリは、「GROUP BY」句を使用して「store_cd」によってデータをグループ化し、「ORDER BY」句を使用して、各店舗の「amount」値の分散を含む「vars_amount」列によって結果を降順で並べる。LIMIT」句を使用して、出力を最初の5行に制限しています。

つまり、このクエリは、「金額」値の母集団分散が最も大きい上位5店舗を返しますが、これは、これらの店舗がレシート1枚あたりの使用金額の広がりや分散が最も大きいことを意味しています。

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

解説:

これは、"receipt "という名前のテーブルからデータを取得するSQLクエリである。

このクエリでは、2つのカラムを選択しています。"store_cd" と各店舗の "amount" カラムの母標準偏差です。

STDDEV_POP(amount)」関数は、「store_cd」の各グループ内の「amount」値の標準偏差を計算する。標準偏差は、データセットの広がりや分散を表す指標です。STDDEV_POP "の "POP "は "population "を意味し、標準偏差の算出にはデータセット全体が使われることを意味する。

このクエリは、"GROUP BY "句を使って "store_cd "でデータをグループ化し、"ORDER BY "句を使って降順で、各店舗の "金額 "の標準偏差を含む "stds_amount" 列で結果を並べる。LIMIT」句を使用して、出力を最初の5行に制限しています。

つまり、このクエリは、「金額」値の母標準偏差が最も高い上位5店舗を返すので、これらの店舗は、レシート1枚あたりの使用金額の広がりや分散が最も高いことを意味します。

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

解説:

これは、"recipate" テーブルの "amount" カラムのパーセンタイル値を計算するために使用する SQL コードです。このコードでは、PERCENTILE_CONT関数を使用して連続的なパーセンタイルを計算しています。これは、パーセンタイル値が、データ・ポイント間の補間値ではなく、データ・セットの実際の値に対応することを意味します。

このコードでは、25パーセンタイル（amount_25per）、50パーセンタイル（amount_50per）、75パーセンタイル（amount_75per）、100パーセンタイル（amount_100per）の4つのパーセンタイル値を指定して計算しています。これらのパーセンタイルは、パーセンタイルを計算する前に、WITHIN GROUP句を使用して「amount」列を昇順に並べます。

コードの最後にあるSELECT文は、計算されたパーセンタイル値をクエリから取得し、1行の出力として返します。このコードは、「recipate」テーブルの「amount」カラムの分布を分析し、データ内の異常値や異常なパターンを特定するのに便利です。

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

解説:

これは、"receipt "テーブルの各店舗の平均取引額を取得し、条件に基づいて結果をフィルタリングするために使用するSQLコードです。

このコードでは、まずSELECT文で、取得する2つの列（「store_cd」列と各店舗の「金額」列の平均値）を指定します。AVG関数を使用して各店舗の「amount」列の平均を計算し、ASキーワードを使用してこの計算された列に「avg_amount」という別名を割り当てています。

次のコード行では、GROUP BY句を使用して「store_cd」列でデータをグループ化し、平均取引額が各店舗で別々に計算されることを意味します。

次に、このコードはHAVING句を使用して、条件に基づいて結果をフィルタリングします。具体的には、各店舗の平均取引額が330以上であることを条件とします。この条件を満たさない店舗は、出力に含まれません。

結果として出力されるのは、2つのカラムを持つテーブルである。「store_cd」と「avg_amount」の2つのカラムを持つテーブルが出力されます。各行は、HAVING句で指定された条件を満たす「receipt」テーブルの店舗に対応し、「avg_amount」列にはその店舗の平均取引額が含まれます。このコードは、平均取引額に基づいて、異なる店舗のパフォーマンスを分析するのに便利です。

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

解説:

これは、IDが "Z "で始まる顧客を除いた、"レシート "テーブルの各顧客が使った合計金額の平均を計算するSQLコードです。

コードは、WITH句を使用して定義された共通テーブル式（CTE）で始まります。このCTEは「customer_amount」という名前で、「recipate」テーブルの各顧客の「customer_id」列と「amount」列の合計を取得するSELECT文が含まれています。WHERE句は、NOT LIKE演算子を用いて、IDが文字「Z」で始まる顧客をフィルタリングしています。結果は "customer_id "カラムでグループ化され、結果のテーブルには2つのカラムがあります。「customer_id」と「sum_amount」（各顧客が使った金額の合計）です。

次のコード行は、"customer_amount" CTE から "sum_amount" カラムの平均値を取得するものです。SELECT文はAVG関数を使用して、IDが "Z "で始まらない各顧客が使った合計金額の平均を表す "sum_amount "列の平均値を計算します。

結果として出力されるのは、"customer_amount" CTE の "sum_amount" 列の平均を表す 1 つの値です。このコードは、顧客の平均的な支出パターンを分析し、データ内の異常値や異常なパターンを特定するのに便利です。

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

解説:

これは、IDが "Z "で始まる顧客を除いて、"receipt "テーブルの顧客の平均合計金額より多く使った上位10人の顧客の "customer_id "と "sum_amount "列を検索するSQLコードです。

コードは、WITH句を使用して定義された共通テーブル式（CTE）で始まります。このCTEは「customer_amount」という名前で、「recipate」テーブルの各顧客の「customer_id」列と「amount」列の合計を取得するSELECT文が含まれています。WHERE句は、NOT LIKE演算子を用いて、IDが文字「Z」で始まる顧客をフィルタリングしています。結果は "customer_id "カラムでグループ化され、結果のテーブルには2つのカラムがあります。「customer_id」と「sum_amount」（各顧客が使った金額の合計）です。

次のコードでは、"customer_amount" CTE から "customer_id" と "sum_amount" カラムを取得しますが、"sum_amount" が CTE のすべての顧客について計算した平均 "sum_amount" 以上になる顧客に対してのみ取得します。これは、AVG関数を使用して平均「sum_amount」を計算し、WHERE句を使用して「customer_amount」CTEをフィルタリングするサブクエリを使用して実現されます。

このクエリの結果は、LIMIT句を使用して上位10行に制限されます。

結果として、2つのカラムを持つテーブルが出力されます。「customer_id」と「sum_amount」の2つの列を持つテーブルが出力されます。各行は、IDが "Z "で始まる顧客を除き、"recipate "テーブルの顧客の平均合計金額よりも多くの金額を使用した顧客に対応するものである。テーブルは "sum_amount "カラムに基づいて降順でソートされ、上位10行のみが出力に含まれる。このコードは、高額な買い物をする顧客を特定し、その消費パターンを分析するのに便利です。

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

解説:

これは、"receipt "テーブルと "store "テーブルのデータを含む結合テーブルから最初の10行を取得するSQLコードです。結果として得られるテーブルには、"recipate "テーブルのすべてのカラムと、"store "テーブルの追加カラム "store_name "が含まれることになる。

コードは、取得するカラムを指定するSELECTステートメントから始まります。この場合、SELECT文はワイルドカード（*）を使用して、「receipt」テーブルからすべてのカラムを取得する。また、SELECT文では、出力テーブルに含まれる「store」テーブルの「store_name」カラムを指定しています。

FROM句は「receipt」テーブルからデータを取得することを指定し、JOIN句は「store」テーブルを「receipt」テーブルに「store_cd」列を結合条件として結合することを指定する。これは、"store_cd "カラムの一致する値に基づいて、"recipate "テーブルと "store "テーブルの行が1つのテーブルに結合されることを意味します。

結果として出力されるのは、「receipt」テーブルのすべてのカラムと、「store」テーブルの追加カラム「store_name」を含むテーブルとなります。このテーブルは、LIMIT句を使用して最初の10行に制限されます。このコードは、関連する2つのテーブルのデータを分析し、データを1つのテーブルに結合して分析するのに便利です。

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	弁当類

解説:

このコードは、リレーショナル・データベースの管理と問い合わせに使用されるプログラミング言語であるSQLで書かれています。

このコードでは、2つのテーブルからデータを選択しています。「product "と "category "です。JOIN」キーワードを使用して、「category_small_cd」カラムの値が一致する両方のテーブルの行を結合しています。これにより、両テーブルのカラムを含む新しいテーブルが作成される。

SELECT」キーワードは、出力に含めるカラムを選択するために使用されます。この場合、「product」テーブルのすべてのカラム（「p.*」構文で指定）と「category」テーブルの「category_small_name」カラムが選択されます。

LIMIT」キーワードは、出力を最初の10行に制限する。これは、大規模なデータセットを扱う際に、クエリを素早くテストしたり、データのサンプルを取得したりするのに便利です。

つまり、このコードでは、「product」テーブルと「category」テーブルを結合し、「category_small_cd」の値が一致する最初の10行を返し、「product」テーブルのすべてのカラムと「category」テーブルの「category_small_name」列を含むことになる。

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

解説:

このコードもSQLで書かれており、前のコードより少し複雑です。このコードは、3つのテーブルからデータを選択する。"recipate"、"customer"、"customer_amount "の3つのテーブルからデータを選択します。

このコードでは、"WITH "句を使って2つのサブクエリを定義しています。「customer_amount」と「customer_data」です。

customer_amount」サブクエリは、「SUM」関数を使用して「customer_id」別に「recipate」テーブルの「amount」列を集約し、その結果に「sum_amount」という別名をつけます。このサブクエリは、2つのカラムを含む新しいテーブルを作成します。「customer_id "と "sum_amount "の2つのカラムを含む新しいテーブルが作成されます。

customer_data」サブクエリは、「customer」テーブルから、「gender_cd」が「1」に等しく、「customer_id」が文字「Z」で始まらない全ての行を選択する。このサブクエリは、1つのカラムを含む新しいテーブルを作成します。「customer_id "を含む新しいテーブルを作成します。

最後のクエリは、「LEFT OUTER JOIN」操作で「customer_data」テーブルと「customer_amount」テーブルを結合しています。これは、"customer_data "テーブルのすべての行と、"customer_amount "テーブルの一致する行があれば、それを返すものである。customer_amount」テーブルに一致する行がない場合、「COALESCE」関数を使用して「sum_amount」値を0に置き換える。

SELECT」句は、出力に含める2つのカラムを選択する。「customer_data」テーブルの「customer_id」と「customer_amount」テーブルの「COALESCE(a.sum_amount, 0) 」（「customer_amount」に一致する行がない場合は0）。LIMIT」句は、出力を最初の10行に制限する。

つまり、このコードは、「customer_id」が文字「Z」で始まらない男性顧客について、「customer_data」テーブルの最初の10行と、そのレシートの合計（またはレシートがない場合は0）を返すことになります。

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

解説:

このコードもSQLで書かれており、前のコードより少し複雑です。これは、"receipt "テーブルからデータを選択し、3つのサブクエリを作成します。「customer_data」、「customer_days」、「customer_amount」です。

customer_dataサブクエリは、「customer_id」が文字「Z」で始まらない「recipate」テーブルの行を選択し、3つのカラムに別名を付けます。"customer_id"、"sales_ymd"、"amount "の3つのカラムにエイリアスを与えます。このサブクエリは、3つのカラムを含む新しいテーブルを作成します。「customer_id」、「sales_ymd」、「amount」の3つのカラムを含む新しいテーブルが作成されます。

customer_daysサブクエリは、「customer_data」サブクエリの「sales_ymd」カラムを、「COUNT（DISTINCT sales_ymd）」関数を使って「customer_id」別に集約し、その結果に「come_days」という別名をつけます。そして、結果を "come_days "で降順に並べ、出力を上位20行に限定します。このサブクエリは、2つのカラムを含む新しいテーブルを作成します。「customer_id "と "come_days "の2つのカラムを含む新しいテーブルを作成します。

customer_amount」サブクエリは、「customer_data」サブクエリの「amount」カラムを、「SUM」関数を使用して「customer_id」ごとに集約し、その結果に「buy_amount」という別名をつけます。そして、結果を "buy_amount "で降順に並べ、出力を上位20行に限定します。このサブクエリは、2つのカラムを含む新しいテーブルを作成します。「customer_id」と「buy_amount」の2つのカラムを含む新しいテーブルが作成されます。

最後のクエリは、"FULL OUTER JOIN "オペレーションを使用して、"customer_days "テーブルと "customer_amount "テーブルを結合しています。これは、両方のテーブルからすべての行、両方のテーブルから一致する行、一致しない場合はNULL値を返します。COALESCE」関数を使用して、「customer_id」のNULL値を、もう一方のテーブルの対応する値に置き換える。

SELECT句は、出力に含める3つのカラムを選択する。"COALESCE(d.customer_id、a.customer_id) "でcustomer_idを、"customer_days "テーブルから "d.come_days"、"customer_amount "から "a.buy_amount "を取得するためです。

つまり、このコードでは、「come_days」（＝来店した日数）が最も多い上位20名の顧客と、「buy_amount」（＝お店で使った合計金額）が最も多い上位20名の顧客が、customer_idと一緒に返ってきます。顧客が両方のテーブルに現れる場合、出力には一度しか現れません。

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

解説:

このSQLコードは、"store "テーブルと "product "テーブルの間でデカルト積を実行することによって生成された結果セットの行数のカウントを取得します。

コードは、COUNT関数を指定したSELECT文から始まり、カウントする値は「1」、つまり行数をカウントすることを意味しています。

FROM句では、クエリで使用する2つのテーブル、"store "と "product "を指定しています。しかし、2つのテーブルを何らかのマッチング条件に基づいて結合するのではなく、CROSS JOINキーワードが使用されています。つまり、「store」テーブルの各行と「product」テーブルの各行が対になり、デカルト積となる。

結果として出力されるのは、デカルト積の行数のカウントを含む1つの列を持つ1つの行となります。このカウントは、"store "テーブルの行数と "product "テーブルの行数を掛け合わせたものとなる。このタイプのクエリは、一般に、2つ以上のテーブルから得られるデータの組み合わせの可能性の総数を計算するために使用されます。