背景
ひょんなことから、データ分析のためのSQL知識量を問われる可能性が突然発生した。
そのため、とりあえず週末に3時間だけデータ分析につかえそうなSQLを叩き込んだ。
ただ、すぐ忘れるはずなので、直前に見返して脳内再インストールするためのメモをここに残すこととした。
PostgreSQLのインストール、ユーザ作成
私の場合、すでにインストール済、ユーザ作成済だったためここは割愛。
※PostgreSQL インストール 簡単とかでググれば多分いけるはず
PostgreSQLにログイン
ターミナル上から、
$ psql
データベースの作成
公式はcreate database DB名;
# 入力
create database color_ball;
# 出力
CREATE DATABASE
データベースの確認
# 入力
\c l
# 出力
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------+----------+-----------+---------+-------+-----------------------
color_ball | ubuntu | SQL_ASCII | C | C |
postgres | postgres | SQL_ASCII | C | C |
ubuntu | ubuntu | SQL_ASCII | C | C |
(3 rows)
# SQL入力モードへ離脱したいときは`q`
データベースへのコネクト
公式は\c DB名
# 入力
\c color_ball
# 出力
You are now connected to database "color_ball" as user "ubuntu".
テーブルの作成
公式はcreate table テーブル名(カラム名 データ型, カラム名 データ型, カラム名 データ型, ・・・);
# 入力
create table balls(radius float, weight float, color text);
# 出力
CREATE TABLE
# 入力
create table price(color text, price integer);
# 出力
CREATE TABLE
参考：データ型一覧（https://www.postgresql.jp/document/9.4/html/datatype.html）
テーブル一覧の確認
# 入力
\dt
# 出力
List of relations
Schema | Name | Type | Owner
--------+-------+-------+--------
public | balls | table | ubuntu
public | price | table | ubuntu
(2 rows)
テーブル詳細の確認
公式は\d テーブル名
# 入力
\d balls
# 出力
Table "public.balls"
Column | Type | Modifiers
---------+------------------+-----------
radius | double precision |
weight | double precision |
color | text |
# 入力
\d price
# 出力
Table "public.price"
Column | Type | Modifiers
--------+---------+-----------
color | text |
price | integer |
テストデータ作成
先ほど作成したテーブルのカラムに合わせ、1レコード1行カンマ区切りのCSVファイルを作成。
balls.csv
5.5,10,gold
3,6,red
4,8,green
3,6,reinbow
4,2,green
1,9,white
5,5,red
50,8,gold
# データは多いほうがイメージしやすいです
price.csv
reinbow,10000
gold,5000
red,1000
green,500
white,100
テストデータをテーブルへ投入
公式はcopy テーブル名 from 'パス' WITH CSV;
# 入力
copy balls from '/home/ubuntu/workspace/balls.csv' WITH CSV;
# 出力
COPY 8
# 入力
copy price from '/home/ubuntu/workspace/price.csv' WITH CSV;
# 出力
COPY 5
テーブルレコードの確認
公式はselect * from テーブル名;
# 入力
select * from balls;
# 出力
radius | weight | color
--------+---------+---------
5.5 | 10 | gold
3 | 6 | red
4 | 8 | green
3 | 6 | reinbow
4 | 2 | green
1 | 9 | white
5 | 5 | red
50 | 8 | gold
(8 rows)
# 入力
select * from price;
# 出力
color | price
---------+-------
reinbow | 10000
gold | 5000
red | 1000
green | 500
white | 100
(5 rows)
これでやっと勉強用データセット準備完了！！
諸条件によるデータ抽出方法
公式は
select 表示したいカラム名 from テーブル名
where 条件 and 条件 and ・・・ and not (外したい条件) and not (外したい条件) ・・・
order by 昇順表示したいカラム名 asc, 降順表示したいカラム名 desc, ・・・ # 並び替えは前にあるものが優先される
limit 表示させたい数
offset 省きたい上位データ数;
つまり、半径が4以上、重さが5以上から10以下、greenでないボールを重い順に2位から3位まで抽出し、全カラムを表示したいときは、
# 入力
select * from balls
where radius >=4 and weight between 5 and 10 and not(color = 'green')
order by weight desc
limit 2
offset 1;
# 出力
radius | weight | color
--------+---------+-------
50 | 8 | gold
5 | 5 | red
(2 rows)
データ集計
公式は、
select count(*) as "なずけたいカラム名称",
sum(合計したいカラム名) as "なずけたいカラム名称",
avg(平均したいカラム名) as "なずけたいカラム名称",
max(最大値をしりたいカラム名) as "なずけたいカラム名称",
min(最小値をしりたいカラム名) as "なずけたいカラム名称",
グルーピングしたいカラム名
from テーブル名
group by グルーピングしたいカラム名
order by 並び替え条件;
つまり、ボールの色ごとに、ボール数、重さの平均、半径の平均を表示させ、重さの平均順に表示させたい場合、
# 入力
select count(*) as "amount",
avg(radius) as "radius_avg",
avg(weight) as "weight_avg",
color
from balls
group by color
order by weight_avg desc;
# 出力
amount | radius_avg | weight_avg | color
--------+------------+-------------+---------
2 | 27.75 | 9 | gold
1 | 1 | 9 | white
1 | 3 | 6 | reinbow
2 | 4 | 5.5 | red
2 | 4 | 5 | green
(5 rows)
別テーブルの結合
公式は、
select 表示させたいカラム名
from 元テーブル名
join 別テーブル名 on 元テーブル名.接続キーとなるカラム名 = 別テーブル名.接続キーとなるカラム名;
where 諸条件
group by グルーピング
order by 並べ方;
つまり、ballテーブルに値段も表示させたい場合は、
# 入力
select * from balls
join price on balls.color = price.color;
# 出力
radius | weight | color | color | price
--------+---------+---------+---------+-------
5.5 | 10 | gold | gold | 5000
50 | 8 | gold | gold | 5000
4 | 8 | green | green | 500
4 | 2 | green | green | 500
5 | 5 | red | red | 1000
3 | 6 | red | red | 1000
3 | 6 | reinbow | reinbow | 10000
1 | 9 | white | white | 100
(8 rows)
ボールの色ごとに、ボール数、値段の合計を表示させ、値段の平均順に表示させたい場合、
# 入力
select count(*) as "amount",
sum(price) as "price_sum",
balls.color
from balls
join price on balls.color = price.color
group by balls.color
order by price_sum desc;
# 出力
amount | price_sum | color
--------+-----------+---------
2 | 10000 | gold
1 | 10000 | reinbow
2 | 2000 | red
2 | 1000 | green
1 | 100 | white
(5 rows)
注意するポイントとしては、接続キーとなるカラム名を使用する際には、テーブル名を指定しないとエラーが発生する。
（上記例だと、group by colorだとエラーになる）
あまり使いたくないSQL
データ解析の場合、集計や抽出は頻繁に行うが、
データ削除や更新はあまり行わないはず。
そのため、
- drop
- delete
- update
- alter
このあたりは、根拠と覚悟をもってタイピングする必要がある。