titanicをposgreでこねる②

①の続きです。EDAしていきます。Titanicコンペには参加していないし精度追及もしていませんし、edaよりsqlを思い出すためにやっているのでこのedaはedaとしては不十分です。
時系列データセットの方がよかったかも。

以下の内容でedaします。
- 欠損値数の確認
- embarkedごとの生存率
- fareのビニング→ビンごとの生存率
- チケットの前処理→

--------------------------------------
- 欠損値数の確認

```
SELECT
sum(CASE WHEN passengerid is null then 1 else 0 end) as cntnull_passengerid,
sum(CASE WHEN survived is null then 1 else 0 end) as cntnull_survived,
sum(CASE WHEN pclass is null then 1 else 0 end) as cntnull_pclass,
sum(CASE WHEN name is null then 1 else 0 end) as cntnull_name,
sum(CASE WHEN sex is null then 1 else 0 end) as cntnull_sex,
sum(CASE WHEN age is null then 1 else 0 end) as cntnull_age,
sum(CASE WHEN sibsp is null then 1 else 0 end) as cntnull_sibsp,
sum(CASE WHEN parch is null then 1 else 0 end) as cntnull_parch,
sum(CASE WHEN ticket is null then 1 else 0 end) as cntnull_ticket,
sum(CASE WHEN fare is null then 1 else 0 end) as cntnull_fare,
sum(CASE WHEN cabin is null then 1 else 0 end) as cntnull_cabin,
sum(CASE WHEN embarked is null then 1 else 0 end) as cntnull_embarked
from train
```
結果は以下の通り。もしkaggleで精度追及するとしたら、影響が少ないからdropnaするか、まずは木系のモデルで予測してみるかだと思う。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1347377/98ab6de3-e1d3-9ef6-15d1-2faf31b6d6e5.png)

------------------------------------------------------------------------
- PCLASSごとの生存率
コードは略しますが、検定しても母平均に有意差がありそう。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1347377/e31a2f56-8791-7633-577c-3df0c9bc0f0e.png)


------------------------------------------------------------------------
- embarkedごとの生存率

```embarked.
select
embarked,
avg(survived)
from train
group by(embarked)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1347377/a5ac871d-e951-cf4d-4875-4ed7fb159aec.png)
embarkedの意味は忘れましたが、生存に関係するカテゴリかもですね。meanはそのまま生存率と解釈できます。

------------------------------------------------------------------------
- fareのビニング→ビンごとの生存率

ビンごとの生存率を観察するためにはビンの数を決めたいところですが、posgreには直でヒストグラムを書く方法がありません。そのため統計量を観察して大体で決め打ちしてみます。

```

SELECT max(fare) as max_fare,
    min(fare) as min_fare,
    avg(fare) as avg_fare,
    stddev(fare) as std_fare,
    PERCENTILE_CONT(0.01) WITHIN GROUP (ORDER BY fare) AS FARE_001,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY fare) AS FARE_025,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY fare) AS FARE_05,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY fare) AS FARE_075,
    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY fare) AS FARE_099
    -- std(fare) as std_fare
from train;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1347377/2105c34c-eee7-6696-7c61-36103d6dbc71.png)


中央値はpercentile_contほげほげと指定するみたいですね。
かなり右に歪んでいます。とりあえず分位点でビニングします。ほんと大体なのでご容赦を。


```
SELECT
passengerid,
survived,
fare,
CASE WHEN fare < 8 THEN '01'
    WHEN fare >= 8 AND fare < 15 THEN '02'
    WHEN fare >= 15 AND fare < 32 THEN '03'
    WHEN fare >= 32 THEN '04'
    ELSE null END AS fare_class
FROM train;
-- 0	7.9104	14.4542	31	249.00622000000035
```
結果は以下の通り。これで生存率を求めます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1347377/c5f8519a-d696-d7d8-d0d3-f646806a82c9.png)

せっかく作ったのでビューに保存しておきましょう。

```

CREATE VIEW fare_class (passengerid, survived, fare, fare_class) AS (
    SELECT passengerid,
        survived,
        fare,
        CASE
            WHEN fare < 8 THEN '01'
            WHEN fare >= 8
            AND fare < 15 THEN '02'
            WHEN fare >= 15
            AND fare < 32 THEN '03'
            WHEN fare >= 32 THEN '04'
            ELSE null
        END AS fare_class
    FROM train
);
0	7.9104	14.4542	31	249.00622000000035
```
一度間違えて作成したので`DROP VIEW fare_class`して再作成しました。

ビューから呼び出してクラスごとの生存率を見ます。ビュー名が列と被るのは避けた方がよさそうですね。

```
SELECT
    fare_class,
    avg(survived)
FROM fare_class
GROUP by fare_class
ORDER by fare_class;
```
結果は以下の通り。以下の仮説が考えられます。
・運賃が高いほど顧客が優先的にレスキューされる
・運賃が高いほど地上に近い階で寝泊まりでき、船から脱出しやすかった
・運賃が高いほど同伴の数が多く、同伴者は女性子供が多い。女性子供は優先的に救助されたので(ドメイン知識)、結果として生存率が高く出た。
一番目以外はどれも有力ですがここではこれ以上追求しません。この後はfare_class>embarkedなどの絞り込みも当然できます。そう考えるとビューはなるべく多くの列を保存しておいた方がいいかもしれない。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1347377/ad6689df-a409-39df-726c-3845573c0a6f.png)








----------------------------------------------------------
微妙だったコード

全列のグループごとの生存率を一気に算出する。SQLには順序の概念がないのでこれでやってしまうと見れたもんじゃない。が数列見るくらいならこれでいいかも

```
select cast(passengerid as varchar(1000)),
avg(survived),
count(passengerid)
FROM train
GROUP by passengerid
UNION
select cast(survived as varchar(1000)),
avg(survived),
count(survived)
FROM train
GROUP by survived
UNION
select cast(pclass as varchar(1000)),
avg(survived),
count(pclass)
FROM train
GROUP by pclass
UNION
select cast(name as varchar(1000)),
avg(survived),
count(name)
FROM train
GROUP by name
UNION
select cast(sex as varchar(1000)),
avg(survived),
count(sex)
FROM train
GROUP by sex
UNION
select cast(age as varchar(1000)),
avg(survived),
count(age)
FROM train
GROUP by age
UNION
select cast(sibsp as varchar(1000)),
avg(survived),
count(sibsp)
FROM train
GROUP by sibsp
UNION
select cast(parch as varchar(1000)),
avg(survived),
count(parch)
FROM train
GROUP by parch
UNION
select cast(ticket as varchar(1000)),
avg(survived),
count(ticket)
FROM train
GROUP by ticket
UNION
select cast(fare as varchar(1000)),
avg(survived),
count(fare)
FROM train
GROUP by fare
UNION
select cast(cabin as varchar(1000)),
avg(survived),
count(cabin)
FROM train
GROUP by cabin
UNION
select cast(embarked as varchar(1000)),
avg(survived),
count(embarked)
FROM train
GROUP by embarked
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1347377/b09db786-0070-565c-e952-620fa049e37b.png)