データサイエンス100本ノック（SQL）91~100
追記
最近になってGCPのBigQueryでも回答を作ってみたので合わせてそのコードも記載する
基本的にPostgreSQLの下にGCPのコードを記していく
--S-091は解答を見ても良く分からなかったので割愛する
S-091: 顧客テーブル（customer）の各顧客に対し、売上実績のある顧客数と売上実績のない顧客数が1:1となるようにアンダーサンプリングで抽出せよ。
S-092:顧客テーブル（customer）では、
性別に関する情報が非正規化の状態で保持されている。これを第三正規化せよ。
PostgreSQL
--genderを除いたテーブルを作成
drop table if exists customer_nor_3;
create table if not exists customer_nor_3 as
select customer_id,
customer_name,
gender_cd,
birth_day,
age,
postal_cd,
address,
application_store_cd,
application_date,
status_cd
from customer;
drop table if exists gender_nor_3;
create table if not exists gender_nor_3 as
select distinct gender_cd,
gender
from customer
order by gender_cd asc;
BigQuery
drop table if exists [データセット名].customer_nor_3;
create table if not exists [データセット名].customer_nor_3 as
select customer_id,
customer_name,
gender_cd,
birth_day,
age,
postal_cd,
address,
application_store_cd,
application_date,
status_cd
from [データセット名].customer;
drop table if exists [データセット名].gender_nor_3;
create table if not exists [データセット名].gender_nor_3 as
select distinct gender_cd,
gender
from [データセット名].customer
order by gender_cd asc;
参考（第3正規化）
https://tech.012grp.co.jp/entry/db_normalization
customerの主キー（Primary Key）は顧客ID（customer_id）である
{性別コード（gender_cd）} → {性別（gender）} という従属関係が見える。
{顧客ID（customer_id）} → {性別コード（gender_cd）} という従属関係も明らか。
つまりは、{顧客ID（customer_id）} → {性別コード（gender_cd）} → {性別（gender）}
このように段階的な従属関係を、「推移的関数従属」と言う。
第3正規形の定義：「第2正規形のテーブルから、推移的関数従属している列が切り出されたもの」
customer（顧客テーブル）をcustomer_nor_3（顧客情報テーブル）とgender_nor_3（性別情報テーブル）に分割する。
S-093:商品テーブル（product）では各カテゴリのコード値だけを保有し、
カテゴリ名は保有していない。
カテゴリテーブル（category）と組み合わせて非正規化し、
カテゴリ名を保有した新たな商品テーブルを作成せよ。
PostgreSQL
drop table if exists p_c;
create table if not exists p_c as
select pr.product_cd,
pr.category_major_cd,
ca.category_major_name,
pr.category_medium_cd,
ca.category_medium_name,
pr.category_small_cd,
ca.category_small_name,
pr.unit_price,
pr.unit_cost
from product as pr
left outer join category as ca
on pr.category_small_cd = ca.category_small_cd;
BigQuery
drop table if exists [データセット名].p_c;
create table if not exists [データセット名].p_c as
select pr.product_cd,
pr.category_major_cd,
ca.category_major_name,
pr.category_medium_cd,
ca.category_medium_name,
pr.category_small_cd,
ca.category_small_name,
pr.unit_price,
pr.unit_cost
from [データセット名].product as pr
left outer join [データセット名].category as ca
on pr.category_small_cd = ca.category_small_cd
order by pr.product_cd asc;
S-094:先に作成したカテゴリ名付き商品データを以下の仕様でファイル出力せよ。
出力先のパスは'/tmp/data'を指定することでJupyterの/'work/data'と共有されるようになっている。
なお、COPYコマンドの権限は付与済みである。
ファイル形式はCSV（カンマ区切り）
ヘッダ有り
文字コードはUTF-8
PostgreSQL
copy product to 'product_utf-8.csv' with csv delimiter ',' encoding 'utf-8' header;
gcpのBigQueryではGUIで行う事ができる
ただし、料金がかかるかもしれないのでこれ以降は行わない（ストレージ保存に料金がかかるらしい？）
S-095:先に作成したカテゴリ名付き商品データを以下の仕様でファイル出力せよ。
出力先のパスは'/tmp/data'を指定することでJupyterの/'work/data'と共有されるようになっている。
なお、COPYコマンドの権限は付与済みである。
ファイル形式はCSV（カンマ区切り）
ヘッダ有り
文字コードはSJIS */
PostgreSQL
copy product to 'product_sjis.csv' with csv delimiter ',' encoding 'sjis' header;
S-096: 先に作成したカテゴリ名付き商品データを以下の仕様でファイル出力せよ。
出力先のパスは'/tmp/data'を指定することでJupyterの/'work/data'と共有されるようになっている。
なお、COPYコマンドの権限は付与済みである。
ファイル形式はCSV（カンマ区切り）
ヘッダ無し
文字コードはUTF-8
PostgreSQL
copy product to 'product_utf-8_no_header.csv' with csv delimiter ',' encoding 'utf-8';
S-097: 先に作成した以下形式のファイルを読み込み、テーブルを作成せよ。
また、先頭3件を表示させ、正しくとりまれていることを確認せよ。
ファイル形式はCSV（カンマ区切り）
ヘッダ有り
文字コードはUTF-8
PostgreSQL
drop table if exists product_utf_8;
create table product_utf_8 (product_cd text,
category_major_cd text,
category_major_name text,
category_medium_cd text,
category_medium_name text,
category_small_cd text,
category_small_name text,
unit_price int,
unit_cost int);
copy product_utf_8 from 'product_full_utf-8.csv' with csv header;
select * from product_utf_8 limit 3;
S-098:先に作成した以下形式のファイルを読み込み、テーブルを作成せよ。
また、先頭3件を表示させ、正しくとりまれていることを確認せよ。
ファイル形式はCSV（カンマ区切り）
ヘッダ無し
文字コードはUTF-8
PostgreSQL
drop table if exists product_utf_8_no_header;
create table product_utf_8_no_header (product_cd text,
category_major_cd text,
category_major_name text,
category_medium_cd text,
category_medium_name text,
category_small_cd text,
category_small_name text,
unit_price int,
unit_cost int);
copy product_utf_8_no_header from 'product_full_utf-8_no_header.csv' with csv;
select * from product_utf_8_no_header limit 3;
S-099:先に作成したカテゴリ名付き商品データを以下の仕様でファイル出力せよ。
出力先のパスは'/tmp/data'を指定することでJupyterの/'work/data'と共有されるようになっている。
なお、COPYコマンドの権限は付与済みである。
ファイル形式はTSV（タブ区切り）
ヘッダ有り
文字コードはUTF-8 */
PostgreSQL
copy product to 'product_utf-8.tsv' with csv delimiter e'\t' encoding 'utf-8' header;
S-100:先に作成した以下形式のファイルを読み込み、テーブルを作成せよ。
また、先頭10件を表示させ、正しくとりまれていることを確認せよ。
ファイル形式はTSV（タブ区切り）
ヘッダ有り
文字コードはUTF-8
PostgreSQL
drop table if exists product_utf_8_tsv;
create table product_utf_8_tsv (product_cd text,
category_major_cd text,
category_major_name text,
category_medium_cd text,
category_medium_name text,
category_small_cd text,
category_small_name text,
unit_price int,
unit_cost int);
copy product_full_utf_8_tsv from 'product_utf-8.tsv' with csv delimiter e'\t' header;
select * from product_utf_8_tsv limit 10;
Discussion