3時間でSQLによるデータいじくり手法を叩き込んで、その後睡眠をとり忘却し、直前に見返して脳内再インストールするためのメモ

## 背景

ひょんなことから、**データ分析のための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

このあたりは、根拠と覚悟をもってタイピングする必要がある。