はじめに
データサイエンス100本ノックをSQLで一通り行ったので後で思い出す時用のメモ
データサイエンス100本ノック
本ページでのテーブル名や列名は実際の100本ノックとは異なる。
またデータベース管理システムによってはやや書き方が異なるものもあるらしいが、100本ノックではPostgreSQLがデフォルトのよう。
PostgreSQLユーザーガイド
100本ノックメモ
- 基本事項
SQL文の最後には必ずセミコロン( ; )
大文字、小文字の区別はない
練習では取得件数多すぎると処理が重くなるので極力LIMITで取得件数制限
句の処理順番は表の通り
| 順番 | 句 | 内容 |
|---|---|---|
| 1 | FROM | データを取り出すテーブルを指定 |
| 2 | JOIN | テーブルの結合 |
| 3 | WHERE | 抽出条件付け |
| 4 | GROUP BY | グルーピング |
| 5 | HAVING | GROUP BY後に条件付け |
| 6 | SELECT | データ取得 |
| 6 | UNION | SELECT結果の結合 |
| 7 | ORDER BY | 並び替え |
| 8 | LIMIT | 取得件数を制限 |
- 基本構文
特定の列だけ取得する場合は列名をSELECT句で記述。
全列取得する場合はアスタリスク( * )を使用。
LIMITは取得する件数を制限する。
SQL
-- 基本構文例
SELECT
product_id,
product_name,
price
FROM product
LIMIT 10;
- 取得列に名前を付ける( AS )
日本語の場合はダブルクォーテーションで囲む。
"AS"は省略可
SQL
SELECT
product_id AS id,
price AS "値段"
FROM product;
- 抽出条件( WHERE )
SQL
-- priceが200以下だけ取得
SELECT *
FROM product
WHERE price <= 200;
- 複数条件
"!="はpython同様否定の演算子。
SQL
-- product_idがp001"かつ"「priceが200以下またはtaxが10でない」"を取得
SELECT *
FROM product
WHERE product_id = 'p001'
AND
(
price <= 200
OR tax != 10
);
- 条件を範囲で指定( BETWEEN )
SQL
-- priceが100以上、200以下
SELECT *
FROM product
WHERE price BETWEEN 100 AND 200;
- 文字条件
"p0"で終わる場合：WHERE product_id LIKE '%p0'
"p0"が含まれる場合：WHERE product_id LIKE '%p0%'
SQL
-- product_idが"p0"から始まるデータだけ取得
SELECT *
FROM product
WHERE product_id LIKE 'p0%';
- 文字条件(正規表現)
チルダ( ~ )が正規表現を使いますよ、の意味。
^ ： 直後の文字が文字列の先頭
SQL
-- product_idがアルファベットのA~Cで始まるデータだけ取得
SELECT *
FROM product
WHERE product_id ~ '^[A-C]';
$ ： 直前の文字が文字列の末尾
SQL
-- product_idが数字の1~5で終わるデータだけ取得
SELECT *
FROM product
WHERE product_id ~ '[1-5]$';
^と$の組み合わせ
SQL
-- product_idがアルファベットのA~Cで始まり数字の1~5で終わるデータだけ取得
SELECT *
FROM product
WHERE product_id ~ '^[A-C].*[1-5]$';
電話番号が3桁-4桁-4桁を正規表現：~ '^[0-9]{3}-[0-9]{3}-[0-9]{4}$'
- 並び替え( ORDER BY )
昇順の場合は下記例の"DESC"削除
SQL
-- priceの降順に並び変える
SELECT *
FROM product
ORDER BY price DESC;
- ランク付け( RANK )
順位を付ける関数、対象の数値が同じ場合は同一順位が付与される。
"OVER"は分析関数で、集約関数と異なり行ごとに集計結果を持たせることができる。
OVER内ではしばしば"PARTITION BY"が用いられ、これはグループ単位で行を取得する。(GROUP BYと違って同じグループでまとめてしまわれない。)
(参考：SQL のウィンドウ関数を使うサンプル: OVER, PARTITION BY, ORDER BY, ROW_NUMBER, RANK, DENSE_RANK)
SQL
-- priceの高い順に並び変えてランク付け
SELECT
product_id,
price,
RANK() OVER(ORDER BY price DESK) AS ranking
FROM product;
- ランク付け( ROW_NUMBER )
順位を付ける関数、対象の数値が同じ場合でも別順位を付与する。
SQL
-- priceの高い順に並び変えてランク付け
SELECT
product_id,
price,
ROW_NUMBER() OVER(ORDER BY price DESK) AS ranking
FROM product;
- 件数カウント( COUNT )
特定の列をカウントする場合はNULLはカウントされないが下記例のような場合はNULLもカウントされる。
SQL
-- 行数をカウント, 下記はCOUNT(1)でも同じ結果を返す
SELECT
COUNT(*)
FROM product;
- ユニーク件数のカウント( DISTINCT )
DISTICTは重複を除いてくれる。
SQL
-- product_nameに重複がある場合、それらを除いて件数カウント
SELECT
COUNT(DISTINCT product_name)
FROM product;
- 集約関数
上記で説明したCOUNTも集約関数の一つ
集約関数はグルーピングを行う"GROUP BY"句と用いられることが多い。
下記表は100本ノックで見かけた数例であり、他にもたくさんある。（参考：集約関数)
| 集約関数 | 内容 |
|---|---|
| SUM | 合計 |
| MIN | 最小 |
| MAX | 最大 |
| STDDEV_POP | 標本標準偏差 |
| STDDEV_SAMP, STDDEV | 標準偏差 |
| VAR_POP | 分散 |
| VAR_SAMP, VARIANCE | 標本分散 |
| COUNT | 件数カウント |
| AVG | 平均 |
SQL
-- product_category毎にグルーピングした上でpriceの合計を取得
SELECT
product_id,
product_category,
SUM(price) AS price
FROM product
GROUP BY product_category;
DATE型（日付）に対してMAXを使うと最新日付が取得できる。MINで最古日付。
- グルーピング後の条件付け( HAVING )
グルーピングしたあとに条件付ける場合はWHEREではなくHAVINGを用いる。
MAX(price)に名前を付けても、SELECTより前にHAVINGが処理されるため、MAX(price)のまま条件記述する。
SQL
-- product_category毎にグルーピングした上でpriceの最小の2倍よりもpriceの最大が大きいデータを取得
SELECT
product_id,
product_category,
MAX(price) AS price_max
MAX(price) AS price_min
FROM product
GROUP BY product_category
HAVING MIN(price)*2 < MAX(price);
- 中央値、四分位数( PERCENTILE_CONT )
PERCENTILE_CONT()のカッコ内を0.25, 0.75とすることで各四分位数を取得できる。
ORDER BYはSELECT処理の後なので、自分で付けた名前を使用できる。
SQL
-- product_category毎にグルーピングした上でpriceの中央値を取得して高い順に並び替え
SELECT
product_id,
product_category,
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY proce) AS price_50per
FROM product
GROUP BY product_category
ORDER BY price_50per DESC;
- 最頻値( MODE )
MODE()で最頻値を取得するのは楽だが、複数ある場合1つだけ取得される。
window関数や分析関数で記述すればやや長くなるが複数ある場合もそれぞれ取得できる。
SQL
-- product_categoryごとのpriceの最頻値を取得
SELECT
product_category,
MODE() WITHIN GROUP(ORDER BY price)
FROM receipt
GROUP BY product_category;
- テーブルの結合( JOIN )
| 表記 | 結合名 |
|---|---|
| JOIN ON | 内部結合 |
| LEFT OUTER JOIN ON | 左外部結合 |
| RIGHT OUTER JOIN ON | 右外部結合 |
| FULL OUTER JOIN ON | 完全外部結合 |
| CROSS JOIN | クロス結合 |
結合には大きく分けて内部結合と外部結合があり、結合の方法が変わる。
参考：【SQL】結合入門（クロス結合、内部結合、外部結合）
JOIN ONは内部結合を行い、構文は下記コードの通り。
両テーブルにある同じデータ列(列名は異なっててもよい)を連結キーとして結合。
各テーブルに名前を付けることでSELECTで取得する内容を短く書ける。
テーブルに名前を付ける際の"AS"は省略可(ここのASはよく省略されているイメージ)。
また"ON"の代わりに"USING"を使って"USING(store_id)"でも可だが、列名は同じである必要がある。また"USING"の場合、列数が減少する。("ON"の場合はどちらのstore_idも残る)
その結果"USING"を使用した場合SELECT句では下記例とは違い、結合キーについてはテーブル名を明示する必要が無くなる。
SQL
-- store_idをキーとしてproductとstoreを内部結合
SELECT
p.product_id,
p.store_id,
s.store_name
FROM product AS p
JOIN store AS s
ON p.store_id = s.store_id;
- NULLでない最初の値を返す( COALESCE )
SQL
-- priceがNULLのデータの場合、0が返される処理
SELECT
produce_id,
COALESCE(price, 0) AS price_nonnull
FROM product;
- n件前のデータを取得( LAG )
LAG関数はwindow関数の一つであり、n件前のデータを取得できる。
また下記コードではWITH句を使っている。WITH句ではサブクエリを作って名前を付けることができる。
サブクエリは一時的に作られる自分で定義したテーブルのようなもの。
WITH句を使わなくても、後半のメインのテーブル操作のFROM内でサブクエリを定義することもできるが、WITH句の方が可読性が高く、名前を付けることで使いまわしができるのでWITH句の方が好ましいかも。
SQL
-- 売り上げsalesについて一週間前との差をとる(売上日sales_ymd)
WITH sales_date_lag AS(
SELECT
sales_ymd,
LAG(sales_ymd, 7) OVER(ORDER BY sales_ymd) AS lag_ymd,
sales,
LAG(sales, 7) OVER(ORDER BY sales_ymd) AS lag_sales
FROM product
)
SELECT
sales_ymd,
sales,
lag_ymd,
lag_sales,
sales - lag_sales AS diff_sales
FROM sales_date_lag
ORDER BY sales_ymd;
- 複数の取得結果を縦方向に結合( UNION )
UNIONを各SQL文の間に挟むだけ。
UNIONでは重複行が削除されるが、UNION ALLでは重複行も全て表示される。
SQL
-- priceが100の結果と200の結果を縦に繋げる
SELECT
produce_id,
price_name
FROM product
WHERE price = 100
UNION
SELECT
produce_id,
price_name
FROM product
WHERE price = 200;
- 文字列型への変換( TO_CHAR )
下記例の逆、日付型への変換は"TO_DATE"
SQL
-- 日付型で格納されている列(sales_ymd)を'YYYYMMDD'形式の文字型へ変換
SELECT
product_id,
TO_CHAR(sales_ymd, 'YYYYMMDD') AS DAY_yyyymmdd
FROM product;
- データ型の変換( CAST )
CAST(<列名>, AS データ型)で変換。
SQL
-- 数値型の日付を日付型に変換、数値型を文字型にした後日付型にする。
SELECT
product_id,
TO_DATE(CAST(sales_ymd AS VARCHAR), 'YYYYMMDD') AS DAY_yyyymmdd
FROM product;
- 日付型の日付から年、月、日だけ取り出す( EXTRACT )
EXTRACT(MONTH FROM <日付>)で月だけ取り出せる。
年の場合はYEAR, 日の場合はDAY。
'FM00'で0埋めし2桁表示できる。
また"TO_TIMESTAMP"は数値型UNIX秒を日付型に変換する。
(UNIX秒は1970年1月1日午前0時0分0秒からの形式的な経過秒数)
SQL
-- UNIX秒を日付型に変換した後、月だけ取得し、文字列型にして頭を0埋め。
SELECT
product_id,
TO_CHAR(EXTRACT(MONTH FROM TO_TIMESTAMP(sales_epoch)), 'FM00') AS sales_month
FROM product;
- 場合分け( CASE式 )
CASE WHEN <条件> THEN <処理> ELSE <条件に当てはまらない場合の処理> END
で場合分け処理ができる。
SQL
-- priceが100より大きい場合1とし、100以下の場合0とする(NULLはないとする)。
SELECT
product_id,
CASE
WHEN 100 < price THEN 1
ELSE 0
END AS price_0_1
FROM product;
- 文字列の特定の位置を取得( SUBSTR )
SUBSTR(<列名>, <開始位置>, <文字長>)で特定の位置の文字列を抜き出せる。
また似た関数のSUBSTRINGはSUBSTAR<文字長>の部分が終了位置になる。正規表現を使って文字を抜き出すことも可能。
SQL
-- addressの3~7文字目を取得する
SELECT
store_id,
SUBSTR(address, 3, 5) AS address_3_7
FROM product;
-
数値の丸め関数
TRUNC：数値を切り捨て
ROUND：数値を四捨五入
CEIL：数値を切り上げ -
引数の最小値を返す( LEAST )
MINはテーブルのカラムや結果セット内の最小値を求めるが、LEASTは複数の値の中での最小値を求める。
SQL
-- priceを100代は100,200代は200とし300以上はすべて300とする。
SELECT
product_id,
LEAST(CAST(TRUNC(price/100)*100 AS INTEGER), 300) AS price
FROM product;
- 連結演算子( || )
連結演算子は文字列を連結できる。
SQL
-- product_idとproduct_nameを"_"で繋げる
SELECT
product_id || '_' || product_name
FROM product;
-
対数をとる( LOG, LN )
LOG()：常用対数（底が10）化
LN()：自然対数（底がe）化 -
日付の減算( AGE )
AGE(<日付>)で今日の日付からの減算、AGE(<日付1>, <日付2>)で日付1,2の減算。
参考：日付/時刻関数と演算子
100本ノックでは"DOW"も使用。DOWは日付に対し日曜日(0)～土曜日(6)までを数値で返す。 -
ランダムなデータ抽出( RANDOM )
RANDOM()は0~1の数値を生成するのに使用でき、その応用で下記コードのようにすると10%のデータが取得できる。
ただし、ランダムなため正確に10%というわけではない。
正確に10%抽出したい場合はカウントを作って出力件数を固定にする。
SQL
-- ランダムに10%の行を取得
SELECT *
FROM product
WHERE RANDOM() <= 0.1;
- 入力を連結して1行の配列化( ARRAY_AGG )
NULLを含めて入力値が結合された配列として返される。
使い方については下記リンク記事が分かりやすかった。
参考：目指せ！！SQLの配列マスター
また"UNNEST"を使えば配列をフラット化してくれる。(1×10の行列を10×1にしてくれる。)
SQL
-- 全product_nameを1行に連結
SELECT ARRAY_AGG(product_name)
FROM product;
- 一時テーブルの作成 ( CREATE TEMP TABLE )
現セッションにおいてのみ有効な一時的なテーブルを作る。
WITHとの違い
WITH句：使うたびに計算される。最初に構文チェックされるためデバッグには良い。
TEMP TABLE：一度作れば再計算されることなく使い回せる。作るタイミングまでミスがわからない。
SQL
CREATE TEMP TABLE IF NOT EXISTS temp_customer AS(
SELECT ...
FROM ...
JOIN ... ON ... USING ...
GROUP BY ...
HAVING ...
);
-- temp_customerテーブルを使用してSQL文を書ける。
SELECT ...
-- <省略>
- データのランダム抽出( RANDOM )
RANDOM()は0~1の数値を生成するのに使用でき、その応用で下記コードのようにすると10%のデータが取得できる。
ただし、ランダムなため正確に10%というわけではない。
正確に10%抽出したい場合はカウントを作って出力件数を固定にする。
SQL
-- ランダムに10%の行を取得
SELECT *
FROM product
WHERE RANDOM() <= 0.1;
- 入力を連結して1行の配列化( ARRAY_AGG )
NULLを含めて入力値が結合された配列として返される。
使い方については下記リンク記事が分かりやすかった。
参考：目指せ！！SQLの配列マスター
また"UNNEST"を使えば配列をフラット化してくれる。(110の行列を101にしてくれる。)
SQL
-- 全product_nameを1行に連結
SELECT ARRAY_AGG(product_name)
FROM product;
- 一時テーブルの作成 ( CREATE TEMP TABLE )
現セッションにおいてのみ有効な一時的なテーブルを作る。
WITHとの違い
WITH句：使うたびに計算される。最初に構文チェックされるためデバッグには良い。
TEMP TABLE：一度作れば再計算されることなく使い回せる。作るタイミングまでミスがわからない。
SQL
CREATE TEMP TABLE IF NOT EXISTS temp_customer AS(
SELECT ...
FROM ...
JOIN ... ON ... USING ...
GROUP BY ...
HAVING ...
);
-- temp_customerテーブルを使用してSQL文を書ける。
SELECT ...
-- <省略>
- csvファイルへの出力、読み込み( COPY TO )
この辺はOracle, MySQL, PostgreSQL, SQL Serverそれぞれ全く違うが下記コードは最初に述べたようにpostgreSQLの場合。
ヘッダ無の場合は下記コードの"HEADER"を削除。
エンコーディング形式を指定できる。
逆にcsvファイルを読み込む際はテーブルの定義をした後、下記コードの"TO"を"FROM"に変更。
SQL
-- customer_after_processテーブルをcsvファイルとして保存する
COPY customer_after_process TO '/<ディレクトリ>/customer_after_process.csv'
WITH CSV HEADER ENCORDING 'UTF-8';
- データ分析で使いそうなこと
欠損数の確認
SQL
SELECT
SUM(CASE WHEN price IS NULL THEN 1 ELSE 0 END) AS price_check_null
from product;
訓練データとテストデータに8:2で分ける時の考え方
カウント定義して訓練データをランダムに8割取得し、その訓練データの一時テーブルを作成
その後テストデータでは"EXCEPT"を使用して訓練データテーブルのデータを除いたものを取得
- その他
絶対値( ABS )
三角関数( SIN, COS, ACOS, ASIN)
度からラジアン( RADIANS )
ラジアンから度( DEGREES )
乱数を固定にする( SET SEED(0.5) )
第一正規形、第二正規形、第三正規形について
(参考：第1正規形から第3正規形まで簡単にまとめてみた)
おわりに
100本ノックではテーブルの作成やデータの挿入については触れられていないため、そこらへんは下記図書を参考にした。サブクエリの説明なども丁寧に書かれてあり、100本ノック前に一読しておいた方がテーブル操作の整理がしやすいと思った。
SQL 第2版 ゼロからはじめるデータベース操作
また100本ノックの解答例でも別名を付ける"AS"が省略されがちだったが、個人的には省略しない方が、読みやすいため、意識的に書くようにしていきたい。