データサイエンス100本ノック（構造化データ加工編）- SQL Part 5 (Q81 to Q100)の解説です。
参照(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...
github.com
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...
github.com
はじめに
- データベースは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-081: 単価（unit_price）と原価（unit_cost）の欠損値について、それぞれの平均値で補完した新たな商品データを作成せよ。なお、平均値については1円未満を丸めること（四捨五入または偶数への丸めで良い）。補完実施後、各項目について欠損が生じていないことも確認すること。
In [93]:
%%sql
DROP TABLE IF EXISTS product_2;
CREATE TABLE product_2 AS (
SELECT
product_cd,
category_major_cd,
category_medium_cd,
category_small_cd,
COALESCE(unit_price, unit_avg) AS unit_price,
COALESCE(unit_cost, cost_avg) AS unit_cost
FROM
product
CROSS JOIN (
SELECT
ROUND(AVG(unit_price)) AS unit_avg,
ROUND(AVG(unit_cost)) AS cost_avg
FROM
product
) stats_product
);
* postgresql://padawan:***@db:5432/dsdojo_db Done. 10030 rows affected.
Out[93]:
[]
解説:
このコードはSQL言語で書かれており、以下のステップを実行します。
product_2というテーブルがすでにデータベースに存在する場合、それを削除します。これは、何もない状態から始めることを保証するために行われます。
CREATE TABLE文を使用して、product_2という新しいテーブルが作成されます。
新しいテーブルのデータは、SELECT文を使用してproductテーブルから取得されます。
SELECT文では、product_cd、category_major_cd、category_medium_cd、category_small_cdという列がproductテーブルから選択されています。
SELECT文の中で、COALESCE関数を使用して2つの新しい列が作成されます。COALESCEは、式のリストで最初の非NULL値を返します。この場合、unit_priceがNULLの場合、代わりに全商品の平均単価（unit_avg）が使用されます。同様に、unit_costがNULLの場合は、全商品の平均単価（cost_avg）が代わりに使用される。
SELECT文のFROM句は、商品テーブルの全商品の平均単価と単価を計算するサブクエリとのCROSS JOINを実行する。ROUND関数は、平均値を最も近い整数に丸めるために使用されます。
SELECT文の結果は、CREATE TABLE文によって暗黙的に実行されるINSERT INTO文を使用して、product_2テーブルに入力するために使用されます。
このコードでは、product_2という新しいテーブルが作成され、productテーブルと同じ列が含まれますが、unit_priceとunit_cost列のnull値は、すべての商品で計算された平均単価と単価にそれぞれ置き換えられています。
In [94]:
%%sql
SELECT
SUM(CASE WHEN unit_price IS NULL THEN 1 ELSE 0 END) AS unit_price,
SUM(CASE WHEN unit_cost IS NULL THEN 1 ELSE 0 END) AS unit_cost
FROM product_2
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
Out[94]:
| unit_price | unit_cost |
|---|---|
| 0 | 0 |
解説:
このコードはSQL言語で書かれており、以下のステップを実行します。
SELECT文を実行し、product_2テーブルに問い合わせる。
SUM関数をCASE式と組み合わせて使用し、unit_price列とunit_cost列のNULL値の数を別々にカウントします。
各CASE式では、WHEN unit_price IS NULLまたはWHEN unit_cost IS NULLは、それぞれのカラムの値がNULLかどうかをチェックします。
カラムにNULL値がある場合、CASE式は1に評価され、そうでない場合は0に評価されます。
次にSUM関数が各行の1と0を合計して、unit_priceとunit_cost列のNULL値の総数をそれぞれ計算します。
ASキーワードは、結果セットの列を意味ある名前、つまりunit_priceとunit_costにするために使用されます。
最後に、LIMIT句は出力を最初の10行に制限するために使用されています。
要約すると、このコードはproduct_2テーブルのunit_priceとunit_cost列のNULL値の数を表示する結果セットを返します。この出力は、これらの列でデータが欠落している行が何行あるかを示しており、対処する必要があるデータ品質の問題を特定するのに便利です。
S-082: 単価（unit_price）と原価（unit_cost）の欠損値について、それぞれの中央値で補完した新たな商品データを作成せよ。なお、中央値については1円未満を丸めること（四捨五入または偶数への丸めで良い）。補完実施後、各項目について欠損が生じていないことも確認すること。
In [95]:
%%sql
DROP TABLE IF EXISTS product_3;
CREATE TABLE product_3 AS (
SELECT
product_cd,
category_major_cd,
category_medium_cd,
category_small_cd,
COALESCE(unit_price, unit_med) AS unit_price,
COALESCE(unit_cost, cost_med) AS unit_cost
FROM
product
CROSS JOIN (
SELECT
ROUND(
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY unit_price)
) AS unit_med,
ROUND(
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY unit_cost)
) AS cost_med
FROM
product
) stats_product
);
* postgresql://padawan:***@db:5432/dsdojo_db Done. 10030 rows affected.
Out[95]:
[]
解説:
このコードはSQL言語で書かれており、以下のステップを実行します。
product_3というテーブルがすでにデータベースに存在する場合、それを削除する。これは、何もない状態から始めることを保証するために行われます。
CREATE TABLEステートメントを使用して、product_3という新しいテーブルを作成します。
新しいテーブルのデータは、SELECT文を使用してproductテーブルから取得されます。
SELECT文では、product_cd、category_major_cd、category_medium_cd、category_small_cdという列がproductテーブルから選択されています。
SELECT文の中で、COALESCE関数を使用して2つの新しい列が作成されます。COALESCEは、式のリストで最初の非NULL値を返します。この場合、unit_priceがNULLの場合、代わりに全商品の単価の中央値（unit_med）が使用されます。同様に、unit_costがNULLの場合は、全商品の単価の中央値(cost_med)が代わりに使用されます。
SELECT文のFROM句は、商品テーブルの全商品の単価の中央値と単価を計算するサブクエリとのCROSS JOINを実行する。中央値の算出にはPERCENTILE_CONT関数が使用されます。WITHIN GROUP句は行の順序を指定し、中央値の算出に使用する値を関数が認識できるようにします。
ROUND関数は、中央値を最も近い整数に丸めるために使用されます。
SELECT文の結果は、CREATE TABLE文によって暗黙のうちに実行されるINSERT INTO文を使って、product_3テーブルに入力するために使用されます。
このコードでは、product_3という新しいテーブルが作成され、productテーブルと同じ列が含まれますが、unit_priceとunit_cost列のnull値は、すべての製品で計算されたそれぞれの単価と単価の中央値で置き換えられています。中央値は、行の順序を指定するWITHIN GROUP句を持つPERCENTILE_CONT関数を使用して計算されています。
In [96]:
%%sql
SELECT
SUM(CASE WHEN unit_price IS NULL THEN 1 ELSE 0 END) AS unit_price,
SUM(CASE WHEN unit_cost IS NULL THEN 1 ELSE 0 END) AS unit_cost
FROM product_3
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
Out[96]:
| unit_price | unit_cost |
|---|---|
| 0 | 0 |
解説:
このコードはSQL言語で書かれており、以下のステップを実行します。
SELECT文を実行し、product_3テーブルを照会します。
SUM関数をCASE式と組み合わせて使用し、unit_price列とunit_cost列のNULL値の数を別々にカウントします。
各CASE式では、WHEN unit_price IS NULLまたはWHEN unit_cost IS NULLは、それぞれの列の値がNULLであるかどうかをチェックします。
カラムにNULL値がある場合、CASE式は1に評価され、そうでない場合は0に評価されます。
次にSUM関数が各行の1と0を合計して、unit_priceとunit_cost列のNULL値の総数をそれぞれ計算します。
ASキーワードは、結果セットの列を意味ある名前、つまりunit_priceとunit_costにするために使用されます。
最後に、LIMIT句は出力を最初の10行に制限するために使用されています。
要約すると、このコードはproduct_3テーブルのunit_priceとunit_cost列のNULL値の数を表示する結果セットを返します。この出力は、これらの列でデータが欠落している行が何行あるかを示すもので、対処すべきデータ品質の問題を特定するのに便利です。product_3テーブルは前のステップで作成され、unit_priceとunit_cost列のnull値は、全製品で計算されたそれぞれの単価と単価の中央値に置き換えられました。
S-083: 単価（unit_price）と原価（unit_cost）の欠損値について、各商品のカテゴリ小区分コード（category_small_cd）ごとに算出した中央値で補完した新たな商品データを作成せよ。なお、中央値については1円未満を丸めること（四捨五入または偶数への丸めで良い）。補完実施後、各項目について欠損が生じていないことも確認すること。
In [97]:
%%sql
DROP TABLE IF EXISTS product_4;
CREATE TABLE product_4 AS (
WITH category_median AS(
SELECT
category_small_cd,
ROUND(
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY unit_price)
) AS unit_med,
ROUND(
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY unit_cost)
) AS cost_med
FROM product
GROUP BY category_small_cd
)
SELECT
product_cd,
category_major_cd,
category_medium_cd,
category_small_cd,
COALESCE(unit_price, unit_med) AS unit_price,
COALESCE(unit_cost, cost_med) AS unit_cost
FROM
product
JOIN
category_median
USING(category_small_cd)
);
* postgresql://padawan:***@db:5432/dsdojo_db Done. 10030 rows affected.
Out[97]:
[]
解説:
このコードはSQL言語で書かれており、以下のステップを実行します。
DROP TABLE文を実行し、product_4テーブルがすでに存在する場合は削除します。
CREATE TABLE文を実行し、product_4テーブルを作成します。
category_medianという名前の共通テーブル式（CTE）が定義されます。このCTEは、ORDER BY句を持つSELECT文を使用して、商品テーブルの行を各カテゴリ内のunit_priceとunit_cost列でソートし、PERCENTILE_CONT関数を使用して各カテゴリのunit_priceとunit_costの中央値を計算する。GROUP BY句は、category_small_cd列によってデータをグループ化しています。
メインの SELECT 文は、product テーブルからデータを取得し、category_small_cd 列で category_median CTE と結合しています。COALESCE関数を使用して、unit_priceおよびunit_cost列のNULL値を対応するカテゴリの中央値で置き換えます。
最後に、CREATE TABLE AS構文を使用して、結果のデータがproduct_4テーブルに挿入されます。
要約すると、このコードでは、productテーブルとCTE category_medianを結合して、新しいテーブルproduct_4を作成しています。PERCENTILE_CONT関数を使用して、productテーブルの各カテゴリについてunit_priceとunit_costの中央値が計算されます。結果のテーブルproduct_4は、unit_priceとunit_cost列のNULL値を対応するカテゴリの中央値で置き換えた商品テーブルのデータを含んでいます。
In [98]:
%%sql
SELECT
SUM(CASE WHEN unit_price IS NULL THEN 1 ELSE 0 END) AS unit_price,
SUM(CASE WHEN unit_cost IS NULL THEN 1 ELSE 0 END) AS unit_cost
FROM product_4
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
Out[98]:
| unit_price | unit_cost |
|---|---|
| 0 | 0 |
解説:
このコードもSQLで書かれており、以下のステップを実行します。
SELECT文を実行し、product_4テーブルのunit_price列とunit_cost列のnull値の合計を取得する。
SUM関数のCASE文は、unit_price列とunit_cost列の各値がnullかどうかをチェックする。このようにして、SUM関数は各列のNULL値の総数を計算する。
ASキーワードは、列の名前をそれぞれunit_priceとunit_costに変更するために使用されます。
LIMIT 句は、クエリによって返される行の数を 10 に制限するために使用されます。
要約すると、このコードは product_4 テーブルの unit_price と unit_cost 列の null 値の総数を計算します。これは、テーブルの欠損データを特定し、さらなるデータのクリーニングまたはインピュテーションが必要かどうかを判断するのに役立ちます。
S-084: 顧客データ（customer）の全顧客に対して全期間の売上金額に占める2019年売上金額の割合を計算し、新たなデータを作成せよ。ただし、売上実績がない場合は0として扱うこと。そして計算した割合が0超のものを抽出し、結果を10件表示せよ。また、作成したデータに欠損が存在しないことを確認せよ。
In [99]:
%%sql
DROP TABLE IF EXISTS sales_rate;
CREATE TABLE sales_rate AS(
WITH sales_amount_2019 AS (
SELECT
customer_id,
SUM(amount) AS sum_amount_2019
FROM
receipt
WHERE
sales_ymd BETWEEN 20190101 AND 20191231
GROUP BY
customer_id
),
sales_amount_all AS (
SELECT
customer_id,
SUM(amount) AS sum_amount_all
FROM
receipt
GROUP BY
customer_id
)
SELECT
a.customer_id,
COALESCE(b.sum_amount_2019, 0) AS sales_amount_2019,
COALESCE(c.sum_amount_all, 0) AS sales_amount_all,
CASE COALESCE(c.sum_amount_all, 0)
WHEN 0 THEN 0
ELSE COALESCE(b.sum_amount_2019, 0) * 1.0 / c.sum_amount_all
END AS sales_rate
FROM
customer a
LEFT JOIN
sales_amount_2019 b
ON a.customer_id = b.customer_id
LEFT JOIN
sales_amount_all c
ON a.customer_id = c.customer_id);
* postgresql://padawan:***@db:5432/dsdojo_db Done. 21971 rows affected.
Out[99]:
[]
解説:
このコードもSQLで書かれており、以下のステップを実行します。
SELECT文が実行され、領収書テーブルと顧客テーブルからデータを取得します。
2つのサブクエリが作成され、2019年およびすべての年の各顧客の総売上金額が計算されます。
SELECT文は、LEFT JOIN演算子を使用して、顧客テーブルと2つのサブクエリを結合する。これにより、顧客テーブルのすべての顧客が、領収書テーブルに売上データがない場合でも、出力に含まれるようになります。
COALESCE関数は、顧客が領収書テーブルに売上データを持たない場合に使用されます。NULL値を0に置き換えてくれます。
CASE文は、各顧客の売上率を計算します。全年度の売上金額の合計が0かどうかをチェックし、その場合は0を返します。そうでない場合は、2019年の売上高を全年の売上高合計で割る。
結果は、sales_rateという新しいテーブルに挿入されます。
要約すると、このコードは、レシート・テーブルの売上データに基づいて、各顧客の売上率を計算します。売上率は、全年度の売上金額の合計に対する2019年度の売上金額の比率です。結果は、sales_rateという新しいテーブルに保存されます。
In [100]:
%%sql
SELECT * FROM sales_rate
WHERE sales_rate > 0
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
Out[100]:
| customer_id | sales_amount_2019 | sales_amount_all | sales_rate |
|---|---|---|---|
| CS031415000172 | 2971 | 5088 | 0.58392295597484276730 |
| CS015414000103 | 874 | 3122 | 0.27994875080076873799 |
| CS011215000048 | 248 | 3444 | 0.07200929152148664344 |
| CS029415000023 | 3767 | 5167 | 0.72904973872653377201 |
| CS035415000029 | 5823 | 7504 | 0.77598614072494669510 |
| CS023513000066 | 208 | 771 | 0.26977950713359273671 |
| CS035513000134 | 463 | 1565 | 0.29584664536741214058 |
| CS001515000263 | 216 | 216 | 1.00000000000000000000 |
| CS006415000279 | 229 | 229 | 1.00000000000000000000 |
| CS031415000106 | 215 | 7741 | 0.02777418938121689704 |
In [101]:
%%sql
SELECT
SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) AS unit_price,
SUM(CASE WHEN sales_amount_2019 IS NULL THEN 1 ELSE 0 END) AS unit_price,
SUM(CASE WHEN sales_amount_all IS NULL THEN 1 ELSE 0 END) AS unit_cost,
SUM(CASE WHEN sales_rate IS NULL THEN 1 ELSE 0 END) AS unit_cost
FROM sales_rate
;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
Out[101]:
| unit_price | unit_price_1 | unit_cost | unit_cost_1 |
|---|---|---|---|
| 0 | 0 | 0 | 0 |
解説:
このコードもSQLで書かれており、以下のステップを実行します。
SELECT文を実行し、sales_rateテーブルからsales_rateの値が0より大きいすべての行を取得します。
LIMIT句は、出力を最初の10行に制限しています。
結果が出力として返されます。
要約すると、このコードは、sales_rateテーブルから、sales rateが0より大きい、つまり、顧客が2019年に少なくとも1回の販売を行ったことを意味するすべての行を選択する。LIMIT句は、出力を最初の10行に制限しています。
S-085: 顧客データ（customer）の全顧客に対し、郵便番号（postal_cd）を用いてジオコードデータ（geocode）を紐付け、新たな顧客データを作成せよ。ただし、1つの郵便番号（postal_cd）に複数の経度（longitude）、緯度（latitude）情報が紐づく場合は、経度（longitude）、緯度（latitude）の平均値を算出して使用すること。また、作成結果を確認するために結果を10件表示せよ。
In [102]:
%%sql
DROP TABLE IF EXISTS customer_1;
CREATE TABLE customer_1 AS (
WITH geocode_avg AS(
SELECT
postal_cd,
AVG(longitude) AS m_longitude,
AVG(latitude) AS m_latitude
FROM
geocode
GROUP BY
postal_cd
)
SELECT
*
FROM
customer c
JOIN
geocode_avg g
USING(postal_cd)
);
* postgresql://padawan:***@db:5432/dsdojo_db Done. 21971 rows affected.
Out[102]:
[]
解説:
このコードはSQLで書かれており、以下のステップを実行します。
WITH句を使用して、geocodeテーブルを使用して各郵便番号の平均経度および緯度を計算するサブクエリを定義します。
SELECT文は、postal_cd列を使用して、customerテーブルとWITH句で定義されたサブクエリを結合する。
結合操作の結果は、CREATE TABLE文を使用して、customer_1という新しいテーブルに挿入されます。同じ名前のテーブルがすでに存在する場合は、まずDROP TABLE IF EXISTS文を使用して削除されます。
要約すると、このコードでは、postal_cd列を使用してcustomerテーブルとgeocodeテーブルを結合し、各郵便番号の平均経度と緯度をcustomerテーブルに追加して、customer_1という新しいテーブルを作成します。出来上がったテーブルには、customerテーブルのすべての列と、平均経度と緯度のための2つの列が追加されています。
In [103]:
%%sql
SELECT * FROM customer_1 LIMIT 10;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
Out[103]:
| postal_cd | customer_id | customer_name | gender_cd | gender | birth_day | age | address | application_store_cd | application_date | status_cd | m_longitude | m_latitude |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 136-0076 | CS037613000071 | 六角 雅彦 | 9 | 不明 | 1952-04-01 | 66 | 東京都江東区南砂********** | S13037 | 20150414 | 0-00000000-0 | 139.8350200000000000 | 35.6719300000000000 |
| 151-0053 | CS031415000172 | 宇多田 貴美子 | 1 | 女性 | 1976-10-04 | 42 | 東京都渋谷区代々木********** | S13031 | 20150529 | D-20100325-C | 139.6896500000000000 | 35.6737400000000000 |
| 245-0016 | CS028811000001 | 堀井 かおり | 1 | 女性 | 1933-03-27 | 86 | 神奈川県横浜市泉区和泉町********** | S14028 | 20160115 | 0-00000000-0 | 139.4836000000000000 | 35.3912500000000000 |
| 144-0055 | CS001215000145 | 田崎 美紀 | 1 | 女性 | 1995-03-29 | 24 | 東京都大田区仲六郷********** | S13001 | 20170605 | 6-20090929-2 | 139.7077500000000000 | 35.5408400000000000 |
| 136-0073 | CS015414000103 | 奥野 陽子 | 1 | 女性 | 1977-08-09 | 41 | 東京都江東区北砂********** | S13015 | 20150722 | B-20100609-B | 139.8360100000000000 | 35.6781800000000000 |
| 136-0073 | CS015804000004 | 松谷 米蔵 | 0 | 男性 | 1931-05-02 | 87 | 東京都江東区北砂********** | S13015 | 20150607 | 0-00000000-0 | 139.8360100000000000 | 35.6781800000000000 |
| 276-0022 | CS007403000016 | 依田 満 | 0 | 男性 | 1975-08-18 | 43 | 千葉県八千代市上高野********** | S12007 | 20150914 | 0-00000000-0 | 140.1326000000000000 | 35.7326400000000000 |
| 154-0015 | CS035614000014 | 板倉 菜々美 | 1 | 女性 | 1954-07-16 | 64 | 東京都世田谷区桜新町********** | S13035 | 20150804 | 0-00000000-0 | 139.6429700000000000 | 35.6288900000000000 |
| 223-0062 | CS011215000048 | 芦田 沙耶 | 1 | 女性 | 1992-02-01 | 27 | 神奈川県横浜市港北区日吉本町********** | S14011 | 20150228 | C-20100421-9 | 139.6294600000000000 | 35.5537000000000000 |
| 226-0021 | CS040412000191 | 川井 郁恵 | 1 | 女性 | 1977-01-05 | 42 | 神奈川県横浜市緑区北八朔町********** | S14040 | 20151101 | 1-20091025-4 | 139.5396400000000000 | 35.5276300000000000 |
解説:
このコードはSQLで書かれており、customer_1テーブルに対して単純なSELECT文を実行します。
SELECT文は、ワイルドカード文字*を使用してcustomer_1テーブルからすべての列と行を取得します。
LIMIT 句は、クエリによって返される行の数を最初の 10 行に制限しています。
このテーブルには、customerテーブルのすべての列と、各顧客の郵便番号の平均経度および緯度の2つの列が追加されています。
S-086: 085で作成した緯度経度つき顧客データに対し、会員申込店舗コード（application_store_cd）をキーに店舗データ（store）と結合せよ。そして申込み店舗の緯度（latitude）・経度情報（longitude)と顧客住所（address）の緯度・経度を用いて申込み店舗と顧客住所の距離（単位：km）を求め、顧客ID（customer_id）、顧客住所（address）、店舗住所（address）とともに表示せよ。計算式は以下の簡易式で良いものとするが、その他精度の高い方式を利用したライブラリを利用してもかまわない。結果は10件表示せよ。
緯度（ラジアン）：φ緯度（ラジアン）：λ:距離L=6371∗arccos(sinφ1∗sinφ2+cosφ1∗cosφ2∗cos(λ1−λ2))
In [104]:
%%sql
SELECT
c.customer_id,
c.address AS customer_address,
s.address AS store_address,
6371 * ACOS(
SIN(RADIANS(c.m_latitude))
* SIN(RADIANS(s.latitude))
+ COS(RADIANS(c.m_latitude))
* COS(RADIANS(s.latitude))
* COS(RADIANS(c.m_longitude) - RADIANS(s.longitude))
) AS distance FROM
customer_1 c
JOIN
store s
ON
c.application_store_cd = s.store_cd
LIMIT 10
;
* postgresql://padawan:***@db:5432/dsdojo_db 10 rows affected.
Out[104]:
| customer_id | customer_address | store_address | distance |
|---|---|---|---|
| CS037613000071 | 東京都江東区南砂********** | 東京都江東区南砂一丁目 | 1.4511822099658445 |
| CS031415000172 | 東京都渋谷区代々木********** | 東京都渋谷区初台二丁目 | 0.4117334789298223 |
| CS028811000001 | 神奈川県横浜市泉区和泉町********** | 神奈川県横浜市瀬谷区二ツ橋町 | 8.065196026704987 |
| CS001215000145 | 東京都大田区仲六郷********** | 東京都大田区仲六郷二丁目 | 1.2684209720729687 |
| CS015414000103 | 東京都江東区北砂********** | 東京都江東区南砂二丁目 | 1.449673414532165 |
| CS015804000004 | 東京都江東区北砂********** | 東京都江東区南砂二丁目 | 1.449673414532165 |
| CS007403000016 | 千葉県八千代市上高野********** | 千葉県佐倉市上志津 | 1.9208032538419055 |
| CS035614000014 | 東京都世田谷区桜新町********** | 東京都世田谷区用賀四丁目 | 1.0214681484997588 |
| CS011215000048 | 神奈川県横浜市港北区日吉本町********** | 神奈川県横浜市港北区日吉本町四丁目 | 0.8182767808775093 |
| CS040412000191 | 神奈川県横浜市緑区北八朔町********** | 神奈川県横浜市緑区長津田みなみ台五丁目 | 3.6641233580823287 |
解説:
このSQLコードは、customer_1とstoreの2つのテーブルからデータを取得し、各顧客の住所と対応する店舗の住所の間の距離を計算します。
SELECT文は、以下のカラムを取得します。
customer_1 テーブルから customer_id を取得する。
customer_1テーブルからcustomer_idを取り出し、customer_addressにリネームする。
storeテーブルのaddress（store_addressにリネームされる）。
顧客と店舗間の距離の計算値で、Haversineの公式を使用。ACOS、SIN、COS関数を使用して、両方のアドレスの緯度と経度の正弦と余弦のアークコサインをそれぞれ計算します。そして、その結果に地球の半径（6371km）をかけて、キロメートル単位の距離を求めます。
JOIN文は、application_store_cd列とstore_cd列の一致をもとに、customer_1テーブルとstoreテーブルの行を結合する。LIMIT句は、返される行の数を10に制限する。
S-087: 顧客データ（customer）では、異なる店舗での申込みなどにより同一顧客が複数登録されている。名前（customer_name）と郵便番号（postal_cd）が同じ顧客は同一顧客とみなして1顧客1レコードとなるように名寄せした名寄顧客データを作成し、顧客データの件数、名寄顧客データの件数、重複数を算出せよ。ただし、同一顧客に対しては売上金額合計が最も高いものを残し、売上金額合計が同一もしくは売上実績がない顧客については顧客ID（customer_id）の番号が小さいものを残すこととする。
In [105]:
%%sql
DROP TABLE IF EXISTS customer_u;
CREATE TABLE customer_u AS (
WITH sales_amount AS(
SELECT
c.customer_id,
c.customer_name,
c.postal_cd,
COALESCE(SUM(r.amount), 0) AS sum_amount
FROM
customer c
LEFT JOIN
receipt r
ON c.customer_id = r.customer_id
GROUP by
c.customer_id, c.customer_name, c.postal_cd
),
sales_ranking AS(
SELECT
*,
ROW_NUMBER() OVER(
PARTITION BY customer_name, postal_cd
ORDER BY sum_amount desc, customer_id ) AS ranking
FROM sales_amount
)
SELECT c.*
FROM
customer c
JOIN
sales_ranking r
ON
c.customer_id = r.customer_id
AND r.ranking = 1
);
* postgresql://padawan:***@db:5432/dsdojo_db Done. 21941 rows affected.
Out[105]:
[]
解説:
このSQLコードは、2つのテーブル "customer "と "recipate "のデータを結合して、"customer_u "という新しいテーブルを作成します。まず、"sales_amount "という名前の共通テーブル式（CTE）を定義し、左結合を使用して "customer "テーブルと "recipate "テーブルを結合して、各顧客の合計売上額を計算します。このCTEは、COALESCE関数を使用して、売上がない顧客には0を返します。
次に、"sales_ranking "という名前の別のCTEが定義され、customer_nameとpostal_cdの各グループ内で、総売上金額に基づいて各顧客の売上ランキングを計算します。ROW_NUMBER()関数を使用して、sum_amountに基づいて降順で各顧客にランクを割り当てています。
最後に、SELECT文は「customer」テーブルと「sales_ranking」CTEを結合し、順位が1である行だけをフィルタリングします。これにより、customer_nameとpostal_cdグループごとに上位の顧客だけが効果的に選択されます。出来上がったデータは、"customer_u "という名前の新しいテーブルに挿入されます。
In [106]:
%%sql
SELECT
customer_cnt,
customer_u_cnt,
customer_cnt - customer_u_cnt AS diff
FROM
(SELECT COUNT(1) AS customer_cnt FROM customer) customer
CROSS JOIN (SELECT COUNT(1) AS customer_u_cnt FROM customer_u) customer_u
;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
Out[106]:
| customer_cnt | customer_u_cnt | diff |
|---|---|---|
| 21971 | 21941 | 30 |
解説:
このSQLコードは、2つの異なるテーブルの顧客数を照会し、その差を計算するものです。
コードの最初の部分は、"customer "テーブルから顧客の総カウントを選択し、"customer_cnt "としてエイリアスを付けるサブクエリです。
コードの2番目の部分は、「customer_u」テーブルから顧客の総カウントを選択し、「customer_u_cnt」という名前で別名化する別のサブクエリです。
最後に、外側クエリでは、2つのサブクエリから「customer_cnt」と「customer_u_cnt」を選択し、両者の差を「diff」として計算します。これは、"customer_cnt "から "customer_u_cnt "を差し引くことで行われます。
結果は、"customer_cnt"、"customer_u_cnt"、"diff "の3つのカラムを持つ1行になります。customer_cnt」列は「customer」テーブルの顧客の総数を、「customer_u_cnt」列は「customer_u」テーブルの顧客の総数を、「diff」列はこれら二つのカウントの差を表示する。
S-088: 087で作成したデータを元に、顧客データに統合名寄IDを付与したデータを作成せよ。ただし、統合名寄IDは以下の仕様で付与するものとする。
- 重複していない顧客：顧客ID（customer_id）を設定
- 重複している顧客：前設問で抽出したレコードの顧客IDを設定
顧客IDのユニーク件数と、統合名寄IDのユニーク件数の差も確認すること。
In [107]:
%%sql
DROP TABLE IF EXISTS customer_n;
CREATE TABLE customer_n AS (
SELECT
c.*,
u.customer_id AS integration_id
FROM
customer c
JOIN
customer_u u
ON c.customer_name = u.customer_name
AND c.postal_cd = u.postal_cd
);
* postgresql://padawan:***@db:5432/dsdojo_db Done. 21971 rows affected.
Out[107]:
[]
解説 :
このSQLコードは、既存のcustomerテーブルとcustomer_uテーブルを結合して、customer_nという新しいテーブルを作成します。
JOIN条件は、customer_nameとpostal_cdカラムに同じ値を持つcustomer_uテーブル間の行をマッチさせます。これらの一致した行に対して、結果のテーブルcustomer_nのintegration_id列はcustomer_uテーブルのcustomer_idに設定されます。
したがって、customer_nテーブルには、customerテーブルのすべての行と、customer_uテーブルのcustomer_idの値を含むintegration_idカラムが追加されます。customer_uテーブルのcustomer_nameとpostal_cdの値が一致しない行は、integration_idカラムにNULL値を持つことになります。
In [108]:
%%sql
SELECT COUNT(1) AS ID数の差 FROM customer_n
WHERE customer_id != integration_id;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
Out[108]:
| id数の差 |
|---|
| 30 |
解説:
このSQLコードは、"customer_n "テーブルの "customer_id "カラムと "integration_id "カラムが等しくない行数を照会する。
customer_nテーブルは、前のコードブロックで「customer」テーブルと「customer_u」テーブルを、顧客の名前と郵便番号を結合条件として結合して作成されました。これは、"customer "テーブルに新しい "integration_id "カラムを追加し、"customer_u "テーブルから対応する "customer_id "値を含むことによって、2つのテーブルを効果的に統合した。
現在のクエリは、「customer_n」テーブルの「customer_id」値が「integration_id」値と等しくない行数をカウントしています。これは、「customer」テーブルの中に、「customer_u」テーブルに対応するレコードがない、または「customer_u」テーブルの「customer_id」値が異なるレコードがあることを意味します。これは、データ統合の問題を特定し、不一致をさらに調査するために使用することができます。
S-089: 売上実績がある顧客を、予測モデル構築のため学習用データとテスト用データに分割したい。それぞれ8:2の割合でランダムにデータを分割せよ。
In [109]:
%%sql
SELECT SETSEED(0.1);
CREATE TEMP TABLE IF NOT EXISTS sales_customer AS (
SELECT
customer_id ,
ROW_NUMBER() OVER(ORDER BY RANDOM()) AS row
FROM
customer
JOIN
receipt
USING(customer_id)
GROUP BY customer_id
HAVING SUM(AMOUNT) > 0
);
DROP TABLE IF EXISTS customer_train;
CREATE TABLE customer_train AS
SELECT
customer.*
FROM
sales_customer
JOIN
customer
USING(customer_id)
WHERE
sales_customer.row <= (SELECT
COUNT(1)
FROM sales_customer) * 0.8
;
DROP TABLE IF EXISTS customer_test;
CREATE TABLE customer_test AS
SELECT
customer.*
FROM
sales_customer
JOIN
customer
USING(customer_id)
EXCEPT
SELECT * FROM customer_train
;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected. 8306 rows affected. Done. 6644 rows affected. Done. 1662 rows affected.
Out[109]:
[]
解説:
このコードは、SQLを使用して、顧客の販売データに基づく機械学習モデルのトレーニングデータセットとテストデータセットを作成しています。各行が何を行っているのか、その内訳は以下の通りです。
%%sql SELECT SETSEED(0.1);
これは、クエリで使用する乱数生成器の種を設定するものです。これは、クエリを実行するたびに同じ結果が得られるようにするためのものです。
CREATE TEMP TABLE IF NOT EXISTS sales_customer AS ( SELECT customer_id, ROW_NUMBER() OVER(ORDER BY RANDOM()) AS row FROM customer JOIN receipt USING(customer_id) GROUP BY customer_id HAVING SUM(AMOUNT) > 0 );
これは、各顧客のcustomer_idとランダムに割り当てられた行番号を含むsales_customerという一時テーブルを作成します。JOINを使用して、customerとrecipateという2つのテーブルからデータを選択します。customer_idでデータをグループ化し、購入した顧客（SUM(AMOUNT) > 0）だけを含むように結果をフィルタリングしています。
DROP TABLE IF EXISTS customer_train; CREATE TABLE customer_train AS SELECT customer.* FROM sales_customer JOIN customer USING(customer_id) WHERE sales_customer.row <= (SELECT COUNT(1) FROM sales_customer) * 0.8 ;
これは、sales_customerテーブルの顧客の80％を含むcustomer_trainというテーブルを作成します。これは、sales_customerテーブルの行数がsales_customerテーブルの総行数の80％以下である顧客について、顧客テーブルからすべての列を選択する。
DROP TABLE IF EXISTS customer_test; CREATE TABLE customer_test AS SELECT customer.* FROM sales_customer JOIN customer USING(customer_id) EXCEPT SELECT * FROM customer_train ;
これは、sales_customerテーブルから残りの20％の顧客を含むcustomer_testというテーブルを作成します。これは、customer_idがsales_customerテーブルにあり、customer_trainテーブルにはない顧客のcustomerテーブルからすべてのカラムを選択する。これにより、テストセットには、トレーニングセットにはない顧客が含まれるようになります。
In [110]:
%%sql
SELECT
train_cnt * 1.0 / all_cnt as 学習データ割合,
test_cnt * 1.0 / all_cnt as テストデータ割合
FROM
(SELECT COUNT(1) AS all_cnt FROM sales_customer) all_data
CROSS JOIN
(SELECT COUNT(1) AS train_cnt FROM customer_train) train_data
CROSS JOIN
(SELECT COUNT(1) AS test_cnt FROM customer_test) test_data
;
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected.
Out[110]:
| 学習データ割合 | テストデータ割合 |
|---|---|
| 0.79990368408379484710 | 0.20009631591620515290 |
解説:
このコードは、前のSQLコードで作成したテーブルをもとに、トレーニングセットとテストセットのデータの割合を計算しています。各行が何を行っているのか、その内訳を紹介します。
%%sql SELECT train_cnt * 1.0 / all_cnt as Percentage of training data, test_cnt * 1.0 / all_cnt as Test Data Percentage
これはメインクエリで、トレーニングセットとテストセットのデータのパーセンテージを選択するものです。train_cnt、test_cnt、all_cnt変数を使用し、これらは以下のサブクエリで計算されます。
FROM (SELECT COUNT(1) AS all_cnt FROM sales_customer) all_data CROSS JOIN (SELECT COUNT(1) AS train_cnt FROM customer_train) train_data CROSS JOIN (SELECT COUNT(1) AS test_cnt FROM customer_test) test_data ;
これらのサブクエリは、sales_customerテーブルの総行数（all_cnt）、customer_trainテーブルの行数（train_cnt）、customer_testテーブルの行数（test_cnt）を算出します。
これらのサブクエリを1つのテーブルにまとめるには、CROSS JOINオペレータを使用します。各サブクエリは1行しか返さないので、クロスジョインの結果は3つの列（all_cnt、train_cnt、test_cnt）を持つ1つの行になります。
次にメインクエリは、train_cntとtest_cntをそれぞれall_cntで割ることによって、トレーニングセットとテストセットのデータの割合を計算する。結果を浮動小数点数に変換するために * 1.0 式が使用され、浮動小数点精度で除算が行われることが保証されます。
S-090: レシート明細データ（receipt）は2017年1月1日〜2019年10月31日までのデータを有している。売上金額（amount）を月次で集計し、学習用に12ヶ月、テスト用に6ヶ月の時系列モデル構築用データを3セット作成せよ。
In [111]:
%%sql
-- SQL向きではないため、やや強引に記載する（分割数が多くなる場合はSQLが長くなるため現実的ではない）
-- また、秒単位のデータなど時系列が細かく、かつ長期間に渡る場合はデータが膨大となるため注意（そのようなケースではループ処理でモデル学習ができる言語が望ましい）
-- 学習データ(0)とテストデータ(1)を区別するフラグを付与する
-- 下準備として年月ごとに売上金額を集計し、連番を付与
CREATE TEMP TABLE IF NOT EXISTS ts_amount AS (
SELECT
SUBSTR(CAST(sales_ymd AS VARCHAR), 1, 6) AS sales_ym,
SUM(amount) AS sum_amount,
ROW_NUMBER() OVER(
ORDER BY SUBSTR(CAST(sales_ymd AS VARCHAR), 1, 6)) AS rn
FROM
receipt
GROUP BY sales_ym
);
-- SQLでは限界があるが、作成データセットの増加に伴いなるべく使いまわしができるものにする
-- WITH句内のLAG関数について、ラグ期間を変えれば使い回せるよう記述
DROP TABLE IF EXISTS series_data_1 ;
CREATE TABLE series_data_1 AS (
WITH lag_amount AS (
SELECT
sales_ym,
sum_amount,
LAG(rn, 0) OVER (ORDER BY rn) AS rn
FROM ts_amount
)
SELECT
sales_ym,
sum_amount,
CASE WHEN rn <= 12 THEN 0 WHEN 12 < rn THEN 1 END as test_flg
FROM lag_amount
WHERE rn BETWEEN 1 AND 18);
DROP TABLE IF EXISTS series_data_2 ;
CREATE TABLE series_data_2 AS (
WITH lag_amount AS (
SELECT
sales_ym,
sum_amount,
LAG(rn, 6) OVER (ORDER BY rn) AS rn
FROM ts_amount
)
SELECT
sales_ym,
sum_amount,
CASE WHEN rn <= 12 THEN 0 WHEN 12 < rn THEN 1 END as test_flg
FROM lag_amount
WHERE rn BETWEEN 1 AND 18);
DROP TABLE IF EXISTS series_data_3 ;
CREATE TABLE series_data_3 AS (
WITH lag_amount AS (
SELECT
sales_ym,
sum_amount,
LAG(rn, 12) OVER (ORDER BY rn) AS rn
FROM ts_amount
)
SELECT
sales_ym,
sum_amount,
CASE WHEN rn <= 12 THEN 0 WHEN 12 < rn THEN 1 END as test_flg
FROM lag_amount
WHERE rn BETWEEN 1 AND 18);
* postgresql://padawan:***@db:5432/dsdojo_db 34 rows affected. Done. 18 rows affected. Done. 18 rows affected. Done. 18 rows affected.
Out[111]:
[]
解説:
このコードでは、売上データの集計表（ts_amount）にウィンドウ関数を適用して、3つの新しいテーブル（series_data_1、series_data_2、series_data_3）を作成しています。以下は、各行が何をするのかの内訳です。
%%sql
CREATE TEMP TABLE IF NOT EXISTS ts_amount AS
(
SELECT
SUBSTR(CAST(sales_ymd AS VARCHAR), 1, 6) AS sales_ym,
SUM(amount) AS sum_amount,
ROW_NUMBER() OVER(ORDER BY SUBSTR(CAST(sales_ymd AS VARCHAR), 1, 6)) AS rn
FROM
receipt
GROUP BY
sales_name
);
このコードでは、月別に売上データをまとめ（SUBSTR関数を使ってsales_ymd列から年と月を抽出）、売上総額を計算し（SUM(amount)）、各行に行番号を割り当てる（ROW_NUMBER() OVER(...) AS rn）一時テーブル（ts_amount）を作成しています。行番号は、後続のクエリで、複数の期間にわたるデータを結合するために使用されます。
DROP TABLE IF EXISTS series_data_1 ;
CREATE TABLE series_data_1 AS (
WITH lag_amount AS
(
SELECT
sales_ymです。
sum_amountとなります。
LAG(rn, 0) OVER (ORDER BY rn) AS rn
FROM
ts_amount
)
SELECT
sales_ymです。
sum_amountとなります。
CASE WHEN rn <= 12 THEN 0 WHEN 12 < rn THEN 1 END as test_flg
FROM
lag_amount
WHERE
rn BETWEEN 1 AND 18)。
このコードは、現在の期間の売上データと前の期間の売上データを（窓関数とLAG関数を使って）結合して新しいテーブル（series_data_1）を作成し、各行がトレーニングセット（test_flg = 0）に属するかテストセット（test_flg = 1）に属するかによってバイナリフラグ（test_flg）を割り当てています。これは、行番号（rn）を閾値（12）と照合することで行われ、データを2つの部分に分割します：最初の12行はトレーニングに使用され、残りの6行はテストに使用されます。こうしてできた表は全部で18行になります。このコードでは、この処理を、期間を変えてもう2回繰り返しています。
DROP TABLE IF EXISTS series_data_2 ;
CREATE TABLE series_data_2 AS (
WITH lag_amount AS (
SELECT
sales_ymです。
sum_amountとする。
LAG(rn, 6) OVER (ORDER BY rn) AS rn
FROM ts_amount
)
SELECT
sales_ymです。
sum_amountとする。
CASE WHEN rn <= 12 THEN 0 WHEN 12 < rn THEN 1 END as test_flg
FROM lag_amount
WHERE rn BETWEEN 1 AND 18);
DROP TABLE IF EXISTS series_data_3 ;
CREATE TABLE series_data_3 AS (
WITH lag_amount AS (
SELECT
sales_ymです。
sum_amountとする。
LAG(rn, 12) OVER (ORDER BY rn) AS rn
FROM ts_amount
)
SELECT
sales_ymです。
sum_amountとする。
CASE WHEN rn <= 12 THEN 0 WHEN 12 < rn THEN 1 END as test_flg
FROM lag_amount
WHERE rn BETWEEN 1 AND 18）。
series_data_2 は半年前の売上データを、series_data_3 は1年前の売上データを参照期間としています。
In [112]:
%%sql
-- series_data_2とseries_data_3の表示は割愛
SELECT * FROM series_data_1;
* postgresql://padawan:***@db:5432/dsdojo_db 18 rows affected.
Out[112]:
| sales_ym | sum_amount | test_flg |
|---|---|---|
| 201701 | 902056 | 0 |
| 201702 | 764413 | 0 |
| 201703 | 962945 | 0 |
| 201704 | 847566 | 0 |
| 201705 | 884010 | 0 |
| 201706 | 894242 | 0 |
| 201707 | 959205 | 0 |
| 201708 | 954836 | 0 |
| 201709 | 902037 | 0 |
| 201710 | 905739 | 0 |
| 201711 | 932157 | 0 |
| 201712 | 939654 | 0 |
| 201801 | 944509 | 1 |
| 201802 | 864128 | 1 |
| 201803 | 946588 | 1 |
| 201804 | 937099 | 1 |
| 201805 | 1004438 | 1 |
| 201806 | 1012329 | 1 |
解説:
このコードは、テーブル series_data_1 からすべての列と行を選択する SQL クエリです。テーブルseries_data_1は、前のSQLコードブロックでCREATE TABLEステートメントを使って作成されています。
series_data_1の作成では、クエリは各月の金額の合計を計算する一時テーブルts_amountを作成し、月に基づいて行番号を割り当てています。行番号とLAG()ウィンドウ関数を使用して、クエリは新しいテーブルseries_data_1を作成し、各月の売上金額と、その行がトレーニングデータとテストデータのどちらに含まれるかを示すフラグを格納します。
最後に、このコードブロックでは、クエリが series_data_1 からすべての列と行を選択し、前の SQL 文の結果を表示できるようにします。
S-091: 顧客データ（customer）の各顧客に対し、売上実績がある顧客数と売上実績がない顧客数が1:1となるようにアンダーサンプリングで抽出せよ。
In [113]:
%%sql
SELECT SETSEED(0.1);
CREATE TEMP TABLE IF NOT EXISTS down_sampling AS (
WITH pre_table_1 AS(
SELECT
c.*
,COALESCE(r.sum_amount,0) AS sum_amount
FROM
customer c
LEFT JOIN (
SELECT
customer_id,
SUM(amount) AS sum_amount
FROM
receipt
GROUP BY
customer_id
) r
ON
c.customer_id=r.customer_id
)
,pre_table_2 AS(
SELECT
*
,CASE WHEN sum_amount > 0 THEN 1 ELSE 0 END AS is_buy_flag
,CASE WHEN sum_amount = 0 THEN 1 ELSE 0 END AS is_not_buy_flag
FROM
pre_table_1
)
,pre_table_3 AS(
SELECT
*
,ROW_NUMBER() OVER(PARTITION BY is_buy_flag ORDER BY RANDOM())
FROM
pre_table_2
CROSS JOIN
(SELECT SUM(is_buy_flag) AS buying FROM pre_table_2) AS t1
CROSS JOIN
(SELECT SUM(is_not_buy_flag) AS not_buying FROM pre_table_2) AS t2
)
SELECT
*
FROM
pre_table_3
WHERE
row_number <= buying
AND row_number <= not_buying
);
* postgresql://padawan:***@db:5432/dsdojo_db 1 rows affected. 16612 rows affected.
Out[113]:
[]
解説:
このコードは、顧客データのダウンサンプリングを実行するSQLクエリです。ダウンサンプリングとは、データセットからいくつかのデータポイントをランダムに削除して、クラスのバランスをとったり、データセットサイズを小さくしたりする処理です。
クエリでは、最初のステートメントでランダムシード値を設定しています。次のステートメントでは、顧客データと各顧客の金額の合計を含むdown_samplingという一時テーブルを作成しています。最初の CTE の LEFT JOIN 句は、顧客テーブルと領収書テーブルを結合して、各顧客の金額の合計を計算します。
2番目のCTEでは、CASE文を使用して2つの追加列が作成されています。これらの列は、顧客が購入を行ったかどうかを示すフラグである。次に、3番目のCTEで、ROW_NUMBER()ウィンドウ関数を使用して、is_buy_flag列に基づいて各顧客レコードに行番号を割り当てています。
最後に、クエリの最後のステートメントで、一時テーブルdown_samplingが選択されますが、行番号が購入顧客数と非購入顧客数以下である行のみが選択されます。これにより、結果のテーブルで各クラスの行数がバランスよく配置されるようになります。
In [114]:
%%sql
SELECT is_buy_flag, COUNT(1) FROM down_sampling GROUP BY is_buy_flag;
* postgresql://padawan:***@db:5432/dsdojo_db 2 rows affected.
Out[114]:
| is_buy_flag | count |
|---|---|
| 0 | 8306 |
| 1 | 8306 |
解説:
このコードは、down_sampling一時テーブルのレコードをis_buy_flag列でグループ化し、各グループ内のレコード数をカウントするSQLクエリを実行します。
down_samplingテーブルは、前のコードセルでcustomerテーブルとrecipateテーブルのamount列をcustomer_idで集約するサブクエリの間で左結合を実行して作成されました。結果として得られるテーブルには、顧客情報の列と、各顧客が購入した金額の総和があります。
pre_table_2サブクエリは、sum_amountカラムに基づいて2つの新しいカラムを作成します。is_buy_flagカラムは、少なくとも1回の購入を行った顧客には1が、購入を行わなかった顧客には0が設定されます。is_not_buy_flagカラムは、購入を1回もしていない顧客には1、少なくとも1回購入した顧客には0が設定される。
pre_table_3サブクエリは、is_buy_flagカラムとランダムな順序に基づいて、pre_table_2テーブルの各行に対して行番号を生成します。また、2つの別々のサブクエリによるクロスジョインを使用して、is_buy_flagが1である場合と0である場合のpre_table_2テーブルの行の合計数を計算する。
最後に、メインのSELECT文は、行番号がis_buy_flagが1である行と0である行の合計数以下であるpre_table_3からすべての行を選択します。これにより、is_buy_flagの各値に対して同じ数のレコードが存在することになります。
したがって、SELECT文の出力は、down_samplingテーブルのis_buy_flagが0であるレコードと1であるレコードのカウントを示します。
S-092: 顧客データ（customer）の性別について、第三正規形へと正規化せよ。
In [115]:
%%sql
DROP TABLE IF EXISTS customer_std;
CREATE TABLE customer_std AS (
SELECT
customer_id,
customer_name,
gender_cd,
birth_day,
age,
postal_cd,
application_store_cd,
application_date,
status_cd
FROM
customer
);
DROP TABLE IF EXISTS gender_std;
CREATE TABLE gender_std AS (
SELECT distinct
gender_cd, gender
FROM
customer
);
* postgresql://padawan:***@db:5432/dsdojo_db Done. 21971 rows affected. Done. 3 rows affected.
Out[115]:
[]
解説:
このSQLコードは、customerという名前の既存のテーブルから2つの新しいテーブルを作成します。
コードの最初の部分では、customer_stdというテーブルが存在する場合はそれを削除し、customer_stdという新しいテーブルを作成します。この新しいテーブルには、customerテーブルと同じ列があり、customer_id、customer_name、gender_cd、birth_day、age、postal_cd、application_store_cd、application_date、status_cdとなっています。この新しいテーブルを作成する目的は、提供されたコードからは明らかではありません。データを標準化したり、前処理をするために作成されるのかもしれません。
コードの2番目の部分は、gender_stdというテーブルが存在すればそれを削除し、gender_stdという新しいテーブルを作成します。新しいテーブルはgender_cdとgenderという2つのカラムを持っています。gender_cdカラムは、顧客テーブルで見つかったすべての性別コードの明確なリストであり、genderカラムは対応する性別の説明である。このテーブルは、性別コードをそれぞれの性別の説明にマッピングするために使用されるかもしれません。
In [116]:
%%sql
-- データの内容確認
SELECT * FROM customer_std LIMIT 3;
* postgresql://padawan:***@db:5432/dsdojo_db 3 rows affected.
Out[116]:
| customer_id | customer_name | gender_cd | birth_day | age | postal_cd | application_store_cd | application_date | status_cd |
|---|---|---|---|---|---|---|---|---|
| CS021313000114 | 大野 あや子 | 1 | 1981-04-29 | 37 | 259-1113 | S14021 | 20150905 | 0-00000000-0 |
| CS037613000071 | 六角 雅彦 | 9 | 1952-04-01 | 66 | 136-0076 | S13037 | 20150414 | 0-00000000-0 |
| CS031415000172 | 宇多田 貴美子 | 1 | 1976-10-04 | 42 | 151-0053 | S13031 | 20150529 | D-20100325-C |
解説:
このコードでは、先に作成したcustomer_stdテーブルから最初の3行を選択しています。customer_stdテーブルは、customerテーブルのコピーで、カラムのサブセットがあります。これは、データが新しいテーブルに正しくインポートされたことを確認するための基本的なチェックです。
In [117]:
%%sql
-- データの内容確認
SELECT * FROM gender_std LIMIT 3;
* postgresql://padawan:***@db:5432/dsdojo_db 3 rows affected.
Out[117]:
| gender_cd | gender |
|---|---|
| 0 | 男性 |
| 9 | 不明 |
| 1 | 女性 |
解説:
このコードは、前のコードブロックで作成されたgender_stdテーブルから最初の3行を選択するSQLクエリを実行します。
LIMIT句は、クエリによって返される行の数を3行に制限するために使用されます。gender_stdテーブルはSELECT DISTINCT文を使用して作成されたので、それはgender_cdのユニークな値と顧客テーブルから対応する性別の値のみを含むことになります。したがって、このクエリは、gender_stdテーブルから最初の3つのユニークな行を返します。
このクエリの出力は、gender_stdテーブルの最初の3行のgender_cdとgender値を表示します。
S-093: 商品データ（product）では各カテゴリのコード値だけを保有し、カテゴリ名は保有していない。カテゴリデータ（category）と組み合わせて非正規化し、カテゴリ名を保有した新たな商品データを作成せよ。
In [118]:
%%sql
DROP TABLE IF EXISTS product_full;
CREATE TABLE product_full AS (
SELECT
p.product_cd,
p.category_major_cd,
c.category_major_name,
p.category_medium_cd,
c.category_medium_name,
p.category_small_cd,
c.category_small_name,
p.unit_price,
p.unit_cost
FROM
product p
JOIN
category c
USING(category_small_cd)
);
* postgresql://padawan:***@db:5432/dsdojo_db Done. 10030 rows affected.
Out[118]:
[]
解説:
このコードは、2つのテーブル "product "と "category "を結合して、"product_full "という新しいテーブルを作成する。
productテーブルには、商品コード、カテゴリーコード、単価、単価など、各商品に関する情報が格納されています。
categoryテーブルには、各カテゴリーレベルのカテゴリーコードとカテゴリー名が格納されています。
JOIN句は、category_small_cdカラムに基づいて、2つのテーブルを接続する。USINGキーワードを使用することで、クエリは、category_small_cdの値が両方のテーブルで等しい行にマッチする。
新しいテーブル「product_full」には、「product」テーブルのすべてのカラムに加え、「category」テーブルのカテゴリコードを検索して得られるカテゴリ名のカラムが追加されています。
In [119]:
%%sql
-- データの内容確認
SELECT * FROM product_full LIMIT 3;
* postgresql://padawan:***@db:5432/dsdojo_db 3 rows affected.
Out[119]:
| product_cd | category_major_cd | category_major_name | category_medium_cd | category_medium_name | category_small_cd | category_small_name | unit_price | unit_cost |
|---|---|---|---|---|---|---|---|---|
| P040101001 | 04 | 惣菜 | 0401 | 御飯類 | 040101 | 弁当類 | 198 | 149 |
| P040101002 | 04 | 惣菜 | 0401 | 御飯類 | 040101 | 弁当類 | 218 | 164 |
| P040101003 | 04 | 惣菜 | 0401 | 御飯類 | 040101 | 弁当類 | 230 | 173 |
解説:
このコードは、productとcategoryという2つのテーブルからデータを選択し、product_fullという新しいテーブルを作成します。
JOIN句は、共通の列であるcategory_small_cdに基づいて、2つのテーブルを結合するために使用されます。これは、結果のテーブルが、category_small_cdの値が両方のテーブルで同じである行だけを持つことを意味します。
2つのテーブルから選択されたカラムは、product_cd、category_major_cd、category_major_name、category_medium_cd、category_medium_name、category_small_cd、category_small_name、unit_price、unit_costです。
結果のテーブルproduct_fullには、結合条件を満たすすべての行について、これらのカラムが含まれることになります。
LIMIT句は、出力に表示される行の数を3行だけに制限するために使用されます。
S-094: 093で作成したカテゴリ名付き商品データを以下の仕様でファイル出力せよ。
|ファイル形式|ヘッダ有無|文字エンコーディング| |:–:|:–:|:–:| |CSV（カンマ区切り）|有り|UTF-8|
ファイル出力先のパスは以下のようにすること（COPYコマンドの権限は付与済み）。
|出力先| |:–:| |/tmp/data|
※”/tmp/data”を指定することでJupyterの”/work/data”と共有されるようになっている。
In [120]:
%%sql
COPY product_full TO '/tmp/data/S_product_full_UTF-8_header.csv'
WITH CSV HEADER ENCODING 'UTF-8';
* postgresql://padawan:***@db:5432/dsdojo_db 10030 rows affected.
Out[120]:
[]
解説:
このコードは、product_fullテーブルの内容を、"/tmp/data "ディレクトリにある "S_product_full_UTF-8_header.csv "という名前のCSVファイルにエクスポートしています。COPYコマンドはPostgreSQLのコマンドで、ファイルやテーブル間でデータをコピーするために使用されます。この場合、TOキーワードでデータのコピー先となるファイルを指定します。CSVキーワードはファイル形式をカンマ区切り値で指定し、HEADERキーワードはファイルの最初の行に列ヘッダを含むことを指定します。最後に、ENCODINGキーワードは、出力ファイルの文字エンコーディングをUTF-8で指定するために使用されます。
S-095: 093で作成したカテゴリ名付き商品データを以下の仕様でファイル出力せよ。
|ファイル形式|ヘッダ有無|文字エンコーディング| |:–:|:–:|:–:| |CSV（カンマ区切り）|有り|CP932|
PostgreSQLではShift_JISを指定することでCP932相当となる。ファイル出力先のパスは以下のようにすること（COPYコマンドの権限は付与済み）。
|出力先| |:–:| |/tmp/data|
※”/tmp/data”を指定することでJupyterの”/work/data”と共有されるようになっている。
In [121]:
%%sql
COPY product_full TO '/tmp/data/S_product_full_SJIS_header.csv'
WITH CSV HEADER ENCODING 'SJIS';
* postgresql://padawan:***@db:5432/dsdojo_db 10030 rows affected.
Out[121]:
[]
解説:
このコードは、PostgreSQLのCOPYコマンドを使用して、product_fullテーブルの内容をS_product_full_SJIS_header.csvという名前のCSVファイルにエクスポートしています。WITH CSV HEADERオプションは、出力に列名を含むヘッダー行を含めることを指定し、ENCODING 'SJIS'オプションは、出力ファイルがShift-JIS文字エンコーディングを使用してエンコードされることを指定します。
Shift-JISは日本で一般的に使用されている文字コードで、CSVファイルに使用できるいくつかの文字コードのうちの1つである。Shift-JISエンコーディングのCSVファイルにデータをエクスポートすることで、この特定のエンコーディングを必要とする他のアプリケーションやデータベースにファイルを簡単にインポートすることができます。
S-096: 093で作成したカテゴリ名付き商品データを以下の仕様でファイル出力せよ。
|ファイル形式|ヘッダ有無|文字エンコーディング| |:–:|:–:|:–:| |CSV（カンマ区切り）|無し|UTF-8|
ファイル出力先のパスは以下のようにすること（COPYコマンドの権限は付与済み）。
|出力先| |:–:| |/tmp/data|
※”/tmp/data”を指定することでJupyterの”/work/data”と共有されるようになっている。
In [122]:
%%sql
COPY product_full TO '/tmp/data/S_product_full_UTF-8_noh.csv'
WITH CSV ENCODING 'UTF-8';
* postgresql://padawan:***@db:5432/dsdojo_db 10030 rows affected.
Out[122]:
[]
解説:
このコードは、product_fullテーブルの内容を、出力ファイルにヘッダー行を含めずに、/tmp/data/ディレクトリのS_product_full_UTF-8_noh.csvというCSVファイルにエクスポートしています。
Postgresでは、COPYコマンドを使用して、ファイルから/ファイルへデータをコピーします。この場合、product_fullテーブルがファイルにコピーされます。WITH CSVオプションは、ファイルがカンマ区切りの値（CSV）形式であることを指定します。ENCODINGオプションは、ファイルの文字エンコーディングを指定します。UTF-8は、異なる言語の文字を幅広くサポートするUTF-8エンコーディングを使用するファイルであることを示す。
HEADERオプションが指定されていないため、出力ファイルにはヘッダー行が含まれない。したがって、出力ファイルには、product_fullテーブルのデータ行のみがCSV形式で含まれることになる。
S-097: 094で作成した以下形式のファイルを読み込み、データを3件を表示させて正しく取り込まれていることを確認せよ。
|ファイル形式|ヘッダ有無|文字エンコーディング| |:–:|:–:|:–:| |CSV（カンマ区切り）|有り|UTF-8|
In [123]:
%%sql
DROP TABLE IF EXISTS product_full;
CREATE TABLE product_full (
product_cd VARCHAR(10),
category_major_cd VARCHAR(2),
category_major_name VARCHAR(20),
category_medium_cd VARCHAR(4),
category_medium_name VARCHAR(20),
category_small_cd VARCHAR(6),
category_small_name VARCHAR(20),
unit_price INTEGER,
unit_cost INTEGER
);
* postgresql://padawan:***@db:5432/dsdojo_db Done. Done.
Out[123]:
[]
解説:
このSQLコードは、product_fullテーブルが存在する場合はそれを削除し、同じ名前の新しいテーブルを作成します。新しいテーブルには、product_cd、category_major_cd、category_major_name、category_medium_cd、category_medium_name、category_small_cd、category_small_name、it_price、it_costの8列があります。最初の7列は長さの異なるVARCHAR型であり、最後の2列はINTEGER型である。
カラムのデータ型と長さを指定することで、コードはproduct_fullテーブルのスキーマを定義していることになります。これにより、各カラムが特定のデータ型と長さを持つようになり、このテーブルに対するクエリのパフォーマンスが向上し、間違った型や長さのデータが挿入された場合に発生する可能性のあるエラーを防ぐことができる。
In [124]:
%%sql
COPY product_full FROM '/tmp/data/S_product_full_UTF-8_header.csv'
WITH CSV HEADER ENCODING 'UTF-8';
* postgresql://padawan:***@db:5432/dsdojo_db 10030 rows affected.
Out[124]:
[]
解説:
このコードは、'/tmp/data/S_product_full_UTF-8_header.csv'にあるCSVファイルから、データベース内に既に作成されている'product_full'という新しいテーブルにデータを取り込みます。
PostgreSQLのCOPYコマンドは、ファイルからテーブルへ、またはその逆方向へデータをコピーすることができます。FROMキーワードはCSVファイルのパスを指定し、WITHキーワードはCOPYコマンドのオプションを指定するために使用されます。この場合、CSVはファイルがComma-Separated Value形式であることを、HEADERはファイルの最初の行が列名を含むことを、ENCODING「UTF-8」はファイルで使用される文字エンコーディングを指定することを、それぞれ指定する。
データベースにはすでにテーブル「product_full」が作成されているので、COPYコマンドはCSVファイルのデータを既存のテーブルに挿入する。
In [125]:
%%sql
SELECT * FROM product_full LIMIT 3;
* postgresql://padawan:***@db:5432/dsdojo_db 3 rows affected.
Out[125]:
| product_cd | category_major_cd | category_major_name | category_medium_cd | category_medium_name | category_small_cd | category_small_name | unit_price | unit_cost |
|---|---|---|---|---|---|---|---|---|
| P040101001 | 04 | 惣菜 | 0401 | 御飯類 | 040101 | 弁当類 | 198 | 149 |
| P040101002 | 04 | 惣菜 | 0401 | 御飯類 | 040101 | 弁当類 | 218 | 164 |
| P040101003 | 04 | 惣菜 | 0401 | 御飯類 | 040101 | 弁当類 | 230 | 173 |
解説:
このSQLコードは、product_fullテーブルから最初の3行のデータを取得するシンプルなSELECTステートメントです。データは以前、CSVファイルからCOPYコマンドを使用してテーブルにロードされました。このクエリはLIMIT句を使用して、返される行の数を3行に制限しています。
SELECT文は、product_fullテーブルからすべての列を取得します。このテーブルには、コード、カテゴリー、価格など、商品に関する情報が含まれています。データは、product_cd、category_major_cd、category_major_name、category_medium_cd、category_medium_name、category_small_cd、category_small_name、it_price、it_costなどの列に整理しています。
S-098: 096で作成した以下形式のファイルを読み込み、データを3件を表示させて正しく取り込まれていることを確認せよ。
|ファイル形式|ヘッダ有無|文字エンコーディング| |:–:|:–:|:–:| |CSV（カンマ区切り）|ヘッダ無し|UTF-8|
In [126]:
%%sql
DROP TABLE IF EXISTS product_full;
CREATE TABLE product_full (
product_cd VARCHAR(10),
category_major_cd VARCHAR(2),
category_major_name VARCHAR(20),
category_medium_cd VARCHAR(4),
category_medium_name VARCHAR(20),
category_small_cd VARCHAR(6),
category_small_name VARCHAR(20),
unit_price INTEGER,
unit_cost INTEGER
);
* postgresql://padawan:***@db:5432/dsdojo_db Done. Done.
Out[126]:
[]
解説:
このコードは、product_fullという名前のテーブルが存在する場合はそれを削除し、同じ名前とスキーマを持つ新しいテーブルを作成します。新しいテーブルには9つのカラムがあります。
product_cdカラムは、VARCHAR(10)型です。
category_major_cdカラムはVARCHAR(2)の型です。
category_major_name カラムは VARCHAR(20) 型である。
category_medium_cd カラムは VARCHAR(4) 型である。
category_medium_name カラムは VARCHAR(20) 型である。
category_small_cd カラムは VARCHAR(6) 型である。
category_small_name カラムは VARCHAR(20) 型である。
unit_price カラムは INTEGER 型である。
unit_cost カラムは INTEGER 型である。
VARCHARデータ型は、指定された最大長を持つ可変長の文字列を格納するために使用されます。INTEGERデータ型は、整数を格納するために使用されます。
このコードでは、指定されたスキーマで空のテーブルが作成され、データはテーブルに追加されない。
In [127]:
%%sql
COPY product_full FROM '/tmp/data/S_product_full_UTF-8_noh.csv'
WITH CSV ENCODING 'UTF-8';
* postgresql://padawan:***@db:5432/dsdojo_db 10030 rows affected.
Out[127]:
[]
解説:
このコードは、PostgreSQLのCOPYコマンドを使用して、「S_product_full_UTF-8_noh.csv」というCSVファイルから「product_full」というテーブルにデータをインポートしています。
WITH CSVオプションは、データがCSV形式であることを指定します。ENCODINGオプションはファイルの文字エンコーディングを指定し、この場合はUTF-8です。
ファイルにはヘッダー行がないので、列名と型は、「product_full」テーブルを作成する前のSQL文で明示的に指定します。
データがテーブルにインポートされると、SELECT文を使って'product_full'テーブルの最初の3行を取得し、データが正しくインポートされたことを確認します。
In [128]:
%%sql
SELECT * FROM product_full LIMIT 3;
* postgresql://padawan:***@db:5432/dsdojo_db 3 rows affected.
Out[128]:
| product_cd | category_major_cd | category_major_name | category_medium_cd | category_medium_name | category_small_cd | category_small_name | unit_price | unit_cost |
|---|---|---|---|---|---|---|---|---|
| P040101001 | 04 | 惣菜 | 0401 | 御飯類 | 040101 | 弁当類 | 198 | 149 |
| P040101002 | 04 | 惣菜 | 0401 | 御飯類 | 040101 | 弁当類 | 218 | 164 |
| P040101003 | 04 | 惣菜 | 0401 | 御飯類 | 040101 | 弁当類 | 230 | 173 |
解説:
このコードは、SQLのSELECT文を使用して、"product_full "テーブルから最初の3行を選択する。SELECT文は、"product_full "テーブルからすべての列（product_cd, category_major_cd, category_major_name, category_medium_cd, category_medium_name, category_small_cd, category_small_name, unit_price, unit_cost）を取り出し、 LIMITキーワードで出力を最初の3行に限定しています。結果は、"product_full "テーブルの最初の3行を表示します。
S-099: 093で作成したカテゴリ名付き商品データを以下の仕様でファイル出力せよ。
|ファイル形式|ヘッダ有無|文字エンコーディング| |:–:|:–:|:–:| |TSV（タブ区切り）|有り|UTF-8|
ファイル出力先のパスは以下のようにすること（COPYコマンドの権限は付与済み）。
|出力先| |:–:| |/tmp/data|
※”/tmp/data”を指定することでJupyterの”/work/data”と共有されるようになっている。
In [129]:
%%sql
COPY product_full TO '/tmp/data/S_product_full_UTF-8_header.tsv'
WITH CSV HEADER DELIMITER E'\t' ENCODING 'UTF-8';
* postgresql://padawan:***@db:5432/dsdojo_db 10030 rows affected.
Out[129]:
[]
解説:
product_fullテーブルの内容を、カンマの代わりにタブ(˶‾‾‾)を区切り文字として使用したCSV形式でエクスポートします。書き出されたファイルは、/tmp/dataディレクトリに保存され、S_product_full_UTF-8_header.tsvという名前になっています。HEADERオプションが含まれているので、ファイルの1行目にはカラムヘッダーが含まれます。
以下は、コードの個々のコンポーネントの内訳です。
COPY product_full: product_fullテーブルのデータがファイルにコピーされることを指定します。
TO '/tmp/data/S_product_full_UTF-8_header.tsv': エクスポートされるファイルのパスと名前を指定します。
WITH CSV：エクスポートされるファイルがCSV形式であることを指定します。
HEADER: ファイルの最初の行がカラムヘッダーを含むことを指定します。
DELIMITER E' \t': タブ文字を区切り文字として使用することを指定します。
ENCODING 'UTF-8': ファイルをエクスポートするときに使用する文字エンコーディングを指定します。
S-100: 099で作成した以下形式のファイルを読み込み、データを3件を表示させて正しく取り込まれていることを確認せよ。
|ファイル形式|ヘッダ有無|文字エンコーディング| |:–:|:–:|:–:| |TSV（タブ区切り）|有り|UTF-8|
In [130]:
%%sql
DROP TABLE IF EXISTS product_full;
CREATE TABLE product_full (
product_cd VARCHAR(10),
category_major_cd VARCHAR(2),
category_major_name VARCHAR(20),
category_medium_cd VARCHAR(4),
category_medium_name VARCHAR(20),
category_small_cd VARCHAR(6),
category_small_name VARCHAR(20),
unit_price INTEGER,
unit_cost INTEGER
);
* postgresql://padawan:***@db:5432/dsdojo_db Done. Done.
Out[130]:
[]
解説:
このSQLコードブロックは、まずproduct_fullというテーブルが存在する場合はそれを削除し、同じ名前とスキーマを持つ新しいテーブルを作成します。
product_fullテーブルのスキーマには、以下のデータ型を持つ9つのカラムがあります。
product_cd: VARCHAR(10) (すなわち、最大長10までの可変長文字列)
category_major_cd。VARCHAR(2)
category_major_name: VARCHAR(20)
category_medium_cd。VARCHAR(4)
category_medium_name: VARCHAR(20)
category_small_cdです。VARCHAR(6)
category_small_name: VARCHAR(20)
unit_priceです。INTEGER
unit_cost: INTEGER
このコードブロックの目的は、データを読み込む前にproduct_fullテーブルの構造を定義することです。
In [131]:
%%sql
COPY product_full FROM '/tmp/data/S_product_full_UTF-8_header.tsv'
WITH CSV HEADER DELIMITER E'\t' ENCODING 'UTF-8';
* postgresql://padawan:***@db:5432/dsdojo_db 10030 rows affected.
Out[131]:
[]
解説:
このコードは、'/tmp/data/S_product_full_UTF-8_header.tsv'にあるTSV（タブ区切り値）ファイルからデータをコピーして、現在のデータベースの'product_full'に挿入する。
COPYコマンドは、入力ファイルがCSVファイルで、ヘッダーがファイルの最初の行に含まれ、使用される区切り文字がタブ文字（'˶'）であることを指定する。ENCODINGパラメータは、ファイルがUTF-8でエンコードされていることを指定します。
このコマンドは、「product_full」テーブルがすでに存在し、入力ファイルのデータ（product_cd、category_major_cd、category_major_name、category_medium_cd、category_medium_name、category_small_cd、category_small_name、unit_price、unit_cost）と同じ構造を持っていると仮定します。このコマンドは、ファイルのデータをテーブルに挿入し、ファイルのデータの各行に対してテーブルに新しい行を作成します。
In [132]:
%%sql
SELECT * FROM product_full LIMIT 3;
* postgresql://padawan:***@db:5432/dsdojo_db 3 rows affected.
Out[132]:
| product_cd | category_major_cd | category_major_name | category_medium_cd | category_medium_name | category_small_cd | category_small_name | unit_price | unit_cost |
|---|---|---|---|---|---|---|---|---|
| P040101001 | 04 | 惣菜 | 0401 | 御飯類 | 040101 | 弁当類 | 198 | 149 |
| P040101002 | 04 | 惣菜 | 0401 | 御飯類 | 040101 | 弁当類 | 218 | 164 |
| P040101003 | 04 | 惣菜 | 0401 | 御飯類 | 040101 | 弁当類 | 230 | 173 |
解説:
このコードは、product_fullテーブルから最初の3行を選択するSQLクエリを実行します。SELECT文の後に*を付けていますが、これはテーブルからすべての列を選択することを意味します。LIMIT 3は、結果セットをテーブルの最初の3行に制限します。
結果は、テーブルで指定された列に従って、product_fullテーブルのデータを表示します。正確な出力は、テーブルに格納されているデータによって異なります。
Comment