「データサイエンス 100 本ノック（構造化データ加工編）」をやる
研究もバイトもやる気出ね～～～って時の逃亡先
逃亡してる暇があるのかと言われたら…
ソース
The-Japan-DataScientist-Society/100knocks-preprocess: データサイエンス 100 本ノック（構造化データ加工編）
知らなかったもの
クオーテーション
:star: 文字列はシングルクオーテーションで囲むのが無難。
| SQL | single | double |
|---|---|---|
| MySQL | 文字列定数 | 文字列定数 |
| PostgreSQL | 文字列定数 | カラム名 |
| 標準 SQL | 文字列定数 | カラム名 |
BETWEEN
カラム名 + BETWEEN + 下限 + AND + 上限 の順。
SELECT * FROM receipt WHERE amount BETWEEN 1000 AND 2000;
NOT
NOT column_name = ?VALUE の順。
!= も使える
-- 例 WHERE customer_id='CS018205000001' AND NOT product_cd = 'P071401019';
LIKE
便利。
store_cd が "S14" から始まるものは以下のクエリより求まる
select * from store where store_cd like 'S14%' LIMIT 10;
:star: 「～を含む」という検索も、LIKE '%～%' とすれば良い。
COLUMNS
テーブル情報の確認に使う MySQL と PostgreSQL で違う
# MySQL SHOW COLUMNS FROM customer; # PostgreSQL SELECT * FROM information_schema.columns WHERE table_name = 'customer' ORDER BY ordinal_position;
正規表現
MySQL は regexp 演算子を使う
PostgreSQL は ~ 演算子を使う
# MySQL select * from customer where status_cd regexp '^[A-F]' LIMIT 10; # PostgreSQL select * from customer where status_cd ~ '^[A-F]' LIMIT 10;
ORDER BY の逆順
「逆順」って言われると一瞬分からなくなるよね
デフォルトが 昇順 ASC、逆が 降順 DESC
ランク機能
SQL により使える関数が異なる。
MySQL は FIND_IN_SET() と GROUP_CONCAT() を組み合わせる
PostgresQL は RANK() OVER() 関数を用いる
# MySQL select *, FIND_IN_SET(hoge, (select GROUP_CONCAT(hoge order by hoge desc) from receipt)) as rank from receipt; # PostgesQL select *, RANK() OVER (order by hoge desc) as rank from receipt; -- ~ごとのランクが知りたい場合、`PARTITION` 関数が使える SELECT *. RANK() OVER (PARTITION BY hoge ORDER BY huga) as ranking from receipt;
コラム 1: OVER 句
ウィンドウ関数呼び出しの際、常に含まれる。OVER 句は、ウィンドウ関数による処理の問い合わせの行がどのように分解されるかを厳密に決定する。
参考: ウィンドウ関数
ウィンドウ関数は現在の行に何らかとも関係するテーブル行の集合に渡って計算を行います
連番をふる
ROW_NUMBER() 関数を用いる
OVER 句と組み合わせることで、rank っぽく使える
重複削除
DISTINCT 関数を用いる。
DISTINCT column_name(, column_name)
で用いるのが一般的？
集約関数
複数の入力行から 1 つの結果を計算する。
参考: 2.7. 集約関数
中央値
残念ながら PostgresQL には MEDIAN 関数のようなものはない 代わりに 50 パーセンタイルを求める
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;
分散
WITH 問い合わせ
１つの問い合わせのために存在する一時テーブルを定義
WHERE句のサブクエリ
WHERE句に集計関数を記述することは出来ない。(WHERE AVG(amount) >= 10 は ×)
このように WHERE 句で判定する条件をレコードの問い合わせ結果で使う場合、サブクエリを利用する
select * from products where price > (select AVG(price) from products)
COALESCE 句
一生読み方覚えられない (コウアレスみたいな)
COALESCE(expr1, expr2) = CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END
直積
CROSS JOIN のこと。
