時系列データをposgreでこねる③ 時系列データ


時系列データセットをテーブルに登録
列を見る
csvをインポート

リクルートレストランデータセットを使用します。
・air_reserve.csvに目的変数が入っているのでこのテーブルから取り込みます。
基本的にデータを追加する予定がないのでnot null制約はなしで、制約をつけるとnull行が入らないので。

さて、①と同様の方法でair_reserve.csvを取り込みました。が、リクルートレストランデータセットには様々なcsvファイルがあります。

次は過去の訪問者数データであるair_visit_data.csvを取り込みます。

この時、テーブルにコメントをつけておきます。
`COMMENT ON TABLE air_visit_data IS '過去のデータです。';`
として、簡易的に区別がつくようにしました。つけたコメントは`\d+`で表示できます。

```
recruit_resuaurant=# \d+
                                リレーション一覧
 スキーマ |      名前      |    型    |  所有者  | サイズ  |        説明
----------+----------------+----------+----------+---------+--------------------
 public   | air_reserve    | テーブル | postgres | 6184 kB |
 public   | air_visit_data | テーブル | postgres | 15 MB   | 過去のデータです。
(2 行)
```

```command.
create table air_visit_data
(air_store_id char(20),
visit_date date,
visitors integer)

\copy air_visit_data from 'Recruit_Restaurant/input/air_visit_data.csv' with csv header
select *
from air_visit_data
order by visit_date desc
limit 100;

--テーブルにコメント
COMMENT ON TABLE air_visit_data IS '過去のデータです。';
```

さて、労働集約的にedaしていきます。今回はsql慣れが目的で、特徴量を作成するときはこのような作業は基本的に必要なので問題ありません。

date型からの時刻の取得は以下の通りです。意外と簡単。

```
SELECT
EXTRACT(YEAR FROM visit_datetime) AS year,
AS month,AS day,AS hour,AS minute,AS second
from hoge
```

これを使用して、年月日曜日ごとの訪問者数を算出してみます。
① 年単位のトレンドがある
② 12月/GWは売り上げが高い
③ 金土日は売り上げが高い
以上の結果が見られそうで、年月日曜日すべてについて特徴量を作成して問題ないはずです。
①sql上でインタラクティブに折れ線グラフまでかけないとトレンドは確認できない。いちいちexportするわけにもいかないし、この辺を考えると何らかのbiツールが必要。一応pandasで集計したときは明確にトレンドがありました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1347377/d43c3520-a3e1-15a5-74dc-ee03732f799f.png)



②月ごとの訪問者数の
・平均
・中央値
・99%点
・合計
・カウント
を出しました。
仮説は以下の通り。
・10，15，99％点を出してようやくわかったのですが、商戦期のGW,12月あたりの平均値・中央値がほかの月と比べて低い理由はおそらく分布が二峰性を持つ(左右端に峰がある)ためでしょう。このあたりヒストグラムを描けば一発ですが、、

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1347377/cb8fcb66-abc4-254b-0b92-2002f74f1197.png)

<details><summary>コード</summary><div>

```
SELECT
MONTH,
avg(visitors),
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY visitors) AS median,
PERCENTILE_CONT(0.10) WITHIN GROUP (ORDER BY visitors) AS ten,
PERCENTILE_CONT(0.15) WITHIN GROUP (ORDER BY visitors) AS fifteen,
PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY visitors) AS ninetynine,
sum(visitors),
count(visitors)
from
------------------------------------
(SELECT
air_store_id,
EXTRACT(YEAR FROM visit_datetime) AS year,
EXTRACT(MONTH FROM visit_datetime) AS month,
EXTRACT(DAY FROM visit_datetime) AS day,
reserve_visitors as visitors
from air_reserve
UNION
SELECT
air_store_id,
EXTRACT(YEAR FROM visit_date) AS year,
EXTRACT(MONTH FROM visit_date) AS month,
EXTRACT(DAY FROM visit_date) AS day,
visitors
from air_visit_data) as hoge --なんでもよい
------------------------------------
group by month
order by month
```
</div></details>

・12月の売り上げの分布が二峰性を持つ
という仮説を調べてみましょう。以下の結果から、平均的に12月の訪問者にはムラがあることが言えそうです。具体的には、訪問者平均マイナス12月平均は5,6,0のときのみ平均して1以上を、それ以外の平日のときは平均してマイナス1以上を返しているといえます。これをもって12月の訪問者数、ひいては繁忙期の訪問者数にはムラがある=dow,やday情報を使用して予測精度を上げられる可能性があるといえます。繁忙期全体に拡大解釈してもいいと思います。一般にクリスマスに出かける予定があるとしたら、その1，2週間前の平日にレストランにはいかず、土日にいくと考えられます。GWの場合も同様でしょう。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1347377/0bacb55e-75c2-22cd-13ea-3bf3b5343fbb.png)

<details><summary>コード</summary><div>



```
SELECT
MONTH,
day,
avg(visitors),
avg(dow) as dow,

------------------------------------
avg(visitors) -
(SELECT avg(visitors)FROM 
(SELECT
air_store_id,
EXTRACT(YEAR FROM visit_datetime) AS year,
EXTRACT(MONTH FROM visit_datetime) AS month,
EXTRACT(DAY FROM visit_datetime) AS day,
reserve_visitors as visitors
from air_reserve
UNION
SELECT
air_store_id,
EXTRACT(YEAR FROM visit_date) AS year,
EXTRACT(MONTH FROM visit_date) AS month,
EXTRACT(DAY FROM visit_date) AS day,
visitors
from air_visit_data
) as hoge --なんでもよい
WHERE month = 12
group by month
) as 訪問者マイナス12月平均,
------------------------------------


-- PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY visitors) AS median,
-- PERCENTILE_CONT(0.10) WITHIN GROUP (ORDER BY visitors) AS ten,
-- PERCENTILE_CONT(0.15) WITHIN GROUP (ORDER BY visitors) AS fifteen,
-- PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY visitors) AS ninetynine,
sum(visitors),
count(visitors)
from
------------------------------------
(SELECT
air_store_id,
EXTRACT(YEAR FROM visit_datetime) AS year,
EXTRACT(MONTH FROM visit_datetime) AS month,
EXTRACT(DAY FROM visit_datetime) AS day,
date_part('dow', visit_datetime) as dow,
reserve_visitors as visitors
from air_reserve
UNION
SELECT
air_store_id,
EXTRACT(YEAR FROM visit_date) AS year,
EXTRACT(MONTH FROM visit_date) AS month,
EXTRACT(DAY FROM visit_date) AS day,
date_part('dow', visit_date) as dow,
visitors
from air_visit_data
) as hoge --なんでもよい
------------------------------------
WHERE month = 12
group by month,day
order by month,day

```

</div></details>


③一応確認してみましょう。結果は以下の通り。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1347377/739f7203-120e-42c3-261a-6884dac36ed4.png)


<details><summary>コード</summary><div>

```

SELECT
avg(dow) as dow,
avg(visitors),
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY visitors) AS median,
PERCENTILE_CONT(0.10) WITHIN GROUP (ORDER BY visitors) AS ten,
PERCENTILE_CONT(0.15) WITHIN GROUP (ORDER BY visitors) AS fifteen,
PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY visitors) AS ninetynine,
sum(visitors),
count(visitors)
from
------------------------------------
(SELECT
air_store_id,
EXTRACT(YEAR FROM visit_datetime) AS year,
EXTRACT(MONTH FROM visit_datetime) AS month,
EXTRACT(DAY FROM visit_datetime) AS day,
date_part('dow', visit_datetime) as dow,
reserve_visitors as visitors
from air_reserve
UNION
SELECT
air_store_id,
EXTRACT(YEAR FROM visit_date) AS year,
EXTRACT(MONTH FROM visit_date) AS month,
EXTRACT(DAY FROM visit_date) AS day,
date_part('dow', visit_date) as dow,
visitors
from air_visit_data
) as hoge --なんでもよい
------------------------------------
group by dow
order by dow

```