「10年使えるデータ分析」読みながらメモ

練習用の仮想環境へのログインメモ（終わったらVM消す）

postgresユーザーのPWはposgre

VM名はsql-ubuntu

PostgreSQLログイン方法とログアウト方法

code:posgre

su - postgres

pw入力

psql -U postgres

postgres=# \q

何回やってもテーブルの参照を忘れる

/dtで全部のDBを取得

データベース > スキーマ > テーブル

この構成を頭に入れておく。

スキーマをまたいでSQLを実行することはできるが、データベースをまたいでの実行は不可

データベースを分けるときの例

本番環境とテスト環境

アプリケーションごとに分ける

pgAdmin=phpMyAdmin

レコードの取り出し

ランダムにレコードを取得する（全体の20％のデータをランダムに取り出す）

postgres=# select * from access_log_wide where random() < 0.2;

レコードのカウント

postgres=# select count (*) from access_log_wide;

四則演算

selectで取得したカラムのデータに対して四則演算が可能

ただし全てのselectしたデータに対して加算されるので注意（access_log_wideにはsearch_hitカラムのデータは1レコードしかなかったので下記の結果になっただけ）

code:add

postgres=# select search_hit from access_log_wide;

search_hit

------------

154

(5 rows)

postgres=#

postgres=# select search_hit + 1 from access_log_wide;

?column?

----------

155

(5 rows)

postgres=#

除算は整数除算になるので注意！！！

例えば3/5=0, 6/4=1 になる

SQLではこれが普通

整数除算をうまく使うと年代別の切り捨てなどに応用可能

code:年齢別のカテゴリ.sql

select

c.customer_id

, c.customer_name

, m.customer_category_name

from

customers as c

inner join customer_category_mapping as m

on (c.customer_age / 5 * 5) = m.customer_category_rank

;

# 顧客名簿が年代順にソートされて出てくる

型のキャスト

整数除算を使って年齢層を算出するクエリ

code:age

postgres=# select customer_name, customer_age / 10 * 10 from customers;

customer_name | ?column?

---------------+----------

森一郎 | 20

丸山冴子 | 10

陣内太郎 | 30

綾小路涼 | 40

(4 rows)

postgres=#

文字列処理

code:char_length

postgres=# select customer_name, char_length(customer_name) from customers;

customer_name | char_length

---------------+-------------

森一郎 | 3

丸山冴子 | 4

陣内太郎 | 4

綾小路涼 | 4

(4 rows)

日付や時間の処理

extract関数

Timestamp型やdate型から任意の単位（年、月、日）で値を取り出せる

select * from access_log where extract(year from request_time) = 2014 limit 10;

JOIN句を使う

join = inner join と書くことができる

code:join

select

*

from

access_log as a

join customers as c

on a.customer_id = c.customer_id

;

access_logテーブル＝a, customersテーブル＝c, としてjoinする

その時の条件は、a.customer_id（access_logのcustomer_id）とc.customer_id（customersのcustomer_id）

joinの種類

join(=inner join)は与えた条件に対応する行がなかったらスキップする

outer joinは対応する行をnull にして残す

left outer join　左、つまりselect文で先に書いてある方のレコード残す（右のテーブルのカラムがnullになる）

right outer join　右を残す、leftの逆

full outer join　両方のこす

PostgreSQLの便利な記法

group byやorder byを簡単に書ける

カラムが出現した順番で2回目以降の出現時に数字で表記できる

select month, year, count(*) from customers group by 1 order by 1

select month, year, count(*) from customers group by month order by month

サブクエリーの構文

select カラムリスト from (select文) as サブクエリー名;

上記のサブクエリー名は正確には、サブクエリーの結果の名前である

これをリレーションという。ジョインはリレーションを連結する機能

サブクエリの結果とテーブルをジョインすることが可能

スカラーサブクエリ

where節の中で使うことができるサブクエリ

サブクエリと使うと遅いのはMySQLだけ？？

そういうコラムがあったけど本当にそうなのか…あとで調べる

Window関数

使いこなすことで複雑な集計を行うことが可能になる

縦持ちと横持ちについて

値がたくさんある時に、、、、

行に並べる＝縦持ち

カラム（列）に並べる＝横持ち

基本的な使い方は縦持ち！テーブル定義によって扱えるデータが制約されてしまうため。

横持ちの利点は速度と行内での処理のしやすさがある（昔よりも複数行の処理が簡単になったみたいなので今ではメリットが少ないかもしれない）