株価の週足を取得するPostgreSQLのクエリ

## 概要

一日単位の株価（日足）を一週間単位（週足）に変換するポスグレのクエリを作る。


## 各種情報

* 環境
    * OS : Debian 9 (Stretch)
    * Database : PostgreSQL 11.5

* DBのテーブル情報
   * テーブル名はstock_priceで、以下カラムを格納
       * date : 日付
       * code : 銘柄コード
       * start_p : 始値（取引開始時点の株価）
       * high_p : 高値（その日に最も高く取引された株価）
       * low_p : 安値（その日に最も安く取引された株価）
       * end_p : 終値（取引終了時点の株価）
   * 以下のSQL文でテーブルを作成

```sql
CREATE TABLE stock_price
(
  date DATE, 
  code smallint,
  start_p double precision,
  high_p double precision,
  low_p double precision,
  end_p double precision,
  PRIMARY KEY(date, code)
);
```

* 格納データ
    * 以下のように、トヨタ自動車（銘柄コード7203）の日足株価を五週間ぶん格納
    * ※DBを格納する手順は割愛

```
sample=# SELECT * FROM stock_price ORDER BY date ASC;
    date    | code | start_p | high_p | low_p | end_p 
------------+------+---------+--------+-------+-------
 2021-06-28 | 7203 |    9852 |   9878 |  9807 |  9842
 2021-06-29 | 7203 |    9786 |   9793 |  9691 |  9740
 2021-06-30 | 7203 |    9774 |   9785 |  9710 |  9710
 2021-07-01 | 7203 |    9728 |   9739 |  9635 |  9690
 2021-07-02 | 7203 |    9752 |   9860 |  9739 |  9820
 2021-07-05 | 7203 |    9739 |   9805 |  9739 |  9772
 2021-07-06 | 7203 |    9798 |   9822 |  9746 |  9794
 2021-07-07 | 7203 |    9729 |   9751 |  9671 |  9734
 2021-07-08 | 7203 |    9701 |   9768 |  9675 |  9675
 2021-07-09 | 7203 |    9571 |   9679 |  9472 |  9650
 2021-07-12 | 7203 |    9830 |   9849 |  9759 |  9815
 2021-07-13 | 7203 |    9870 |   9884 |  9824 |  9865
 2021-07-14 | 7203 |    9800 |  10010 |  9790 |  9869
 2021-07-15 | 7203 |    9880 |   9919 |  9803 |  9832
 2021-07-16 | 7203 |    9816 |   9930 |  9803 |  9866
 2021-07-19 | 7203 |    9785 |   9814 |  9717 |  9740
 2021-07-20 | 7203 |    9680 |   9689 |  9596 |  9612
 2021-07-21 | 7203 |    9704 |   9826 |  9686 |  9725
 2021-07-26 | 7203 |    9890 |   9916 |  9780 |  9829
 2021-07-27 | 7203 |    9899 |   9919 |  9856 |  9871
 2021-07-28 | 7203 |    9790 |   9869 |  9785 |  9804
 2021-07-29 | 7203 |    9888 |   9934 |  9810 |  9856
 2021-07-30 | 7203 |    9815 |   9896 |  9805 |  9805
```

## まずは答え

以下のクエリでOK。

```sql
-- (3) 週足の株価情報を表示
SELECT
  t1.date_w AS date,
  t1.code AS code,
  t2.start_p AS start_p,
  t1.high_p AS high_p,
  t1.low_p AS low_p,
  t3.end_p AS end_p
FROM
(
  SELECT -- (1) 週足株価の取得に必要なテーブル
    CAST(DATE_TRUNC('week', date) AS date) AS date_w,
    code,
    MAX(high_p) AS high_p,
    MIN(low_p) AS low_p,
    MIN(date) AS date_min,
    MAX(date) AS date_max
  FROM stock_price
  GROUP BY code, DATE_TRUNC('week', date)
) AS t1
-- (2) 内部結合により週の始値と終値をそれぞれ算出
INNER JOIN
(
  -- 各週の始値をstock_priceから取得
  SELECT
    date, code, start_p
  FROM stock_price
) AS t2
  ON t1.code = t2.code
     AND t1.date_min = t2.date
INNER JOIN
(
  -- 各週の終値をstock_priceから取得
  SELECT
    date, code, end_p
  FROM stock_price
) AS t3
  ON t1.code = t3.code
     AND t1.date_max = t3.date
ORDER BY t1.date_w ASC;
```

クエリ結果

```
    date    | code | start_p | high_p | low_p | end_p 
------------+------+---------+--------+-------+-------
 2021-06-28 | 7203 |    9852 |   9878 |  9635 |  9820
 2021-07-05 | 7203 |    9739 |   9822 |  9472 |  9650
 2021-07-12 | 7203 |    9830 |  10010 |  9759 |  9866
 2021-07-19 | 7203 |    9785 |   9826 |  9596 |  9725
 2021-07-26 | 7203 |    9890 |   9934 |  9780 |  9805
```

## 解説

### 手順(1) 週足株価の取得に必要なテーブルをサブクエリを作成

* 日付の切り捨て関数であるDATE_TRUNCを使って、週毎の各種値を算出
* 週の始まり(月曜日)の日付``date_w``
    * DATE_TRUNC関数は戻り値がTimestamp型なので、元テーブルと同じDATE型になるようにCAST関数を被せて変換
* 高値(``high_p``)と安値(``low_p``)
    * 週毎の株価の最大最小値を求めれば良く、この時点で取得可能
* 始値と終値
    * 週単位の集約関数では一発で求められないので（筆者がその方法を知らないだけかもしれないが）、後述のテーブルの内部結合（INNER JOIN）を使って求める。その時のテーブル結合条件として利用する「各週の日付フィールドの最大値（``date_max``）と最小値（``date_min``）」を求めておく

```sql
SELECT -- (1) 週足株価の取得に必要なテーブル
  CAST(DATE_TRUNC('week', date) AS date) AS date_w,
  code,
  MAX(high_p) AS high_p,
  MIN(low_p) AS low_p,
  MIN(date) AS date_min,
  MAX(date) AS date_max
FROM stock_price
GROUP BY code, DATE_TRUNC('week', date);
```

クエリ結果は下記。
※2021年7/19の週は7/22,23が祝日で株式市場もお休みのため、``date_max``が7/21(水曜日)になっていることに注意。同様に、週頭がお休みの場合等、``date_min``が月曜ではないことがある。

```
   date_w   | code | high_p | low_p |  date_min  |  date_max  
------------+------+--------+-------+------------+------------
 2021-06-28 | 7203 |   9878 |  9635 | 2021-06-28 | 2021-07-02
 2021-07-05 | 7203 |   9822 |  9472 | 2021-07-05 | 2021-07-09
 2021-07-12 | 7203 |  10010 |  9759 | 2021-07-12 | 2021-07-16
 2021-07-19 | 7203 |   9826 |  9596 | 2021-07-19 | 2021-07-21
 2021-07-26 | 7203 |   9934 |  9780 | 2021-07-26 | 2021-07-30
```

### 手順(2) 内部結合（INNER JOIN）を使って各週の始値と終値を取得

イメージとしては以下の★のように、手順(1)で取得したクエリ結果のカラム(列)に各週の始値と終値を追加するようなことをやりたい。
この``????``の部分にはそれぞれ、日足の株価テーブル``stock_price``の中で「日付``date_min``の始値」、および「日付``date_max``の終値」を持ってくればよい。

```
   date_w   | ... |  date_min  |  date_max  | ★start_p | ★end_p
------------+ ... +------------+------------+-----------+--------
 2021-06-28 | ... | 2021-06-28 | 2021-07-02 |     ????  |   ????
 2021-07-05 | ... | 2021-07-05 | 2021-07-09 |     ????  |   ????
 2021-07-12 | ... | 2021-07-12 | 2021-07-16 |     ????  |   ????
 2021-07-19 | ... | 2021-07-19 | 2021-07-21 |     ????  |   ????
 2021-07-26 | ... | 2021-07-26 | 2021-07-30 |     ????  |   ????
```

これを実現するのが以下のSQL文。

* テーブルの結合条件に、各州の日付の最小(``date_min``)、最大(``date_max``)値を入れている
* 銘柄が複数ある場合を考慮して、「銘柄コード(``code``)が一致するかどうかも結合条件に追加

```sql
-- 前半は記載を省略
SELECT ... FROM (...) AS t1
-- (2) 内部結合により週の始値と終値をそれぞれ算出
INNER JOIN
(
  -- 結合条件により、各週の始値をstock_priceから取得
  SELECT
    date, code, start_p
  FROM stock_price
) AS t2
  ON t1.code = t2.code
     AND t1.date_min = t2.date
INNER JOIN
(
  -- 結合条件により、各週の終値をstock_priceから取得
  SELECT
    date, code, end_p
  FROM stock_price
) AS t3
  ON t1.code = t3.code
     AND t1.date_max = t3.date
-- ここまで
ORDER BY t1.date_w ASC;
```

### 手順(3) 週足の株価情報を表示

各々、テーブルの別名t1〜t3を定義して表示

* テーブルt1 : 週足の始値終値以外の情報を取得可能
* テーブルt2 : 週足の始値を取得
* テーブルt3 : 週足の終値を取得

```sql
-- (3) 週足の株価情報を表示
SELECT
  t1.date_w AS date,
  t1.code AS code,
  t2.start_p AS start_p,
  t1.high_p AS high_p,
  t1.low_p AS low_p,
  t3.end_p AS end_p
FROM (...) AS t1               -- 週足株価の取得に必要なテーブル（手順(1))
INNER JOIN (...) AS t2 ON ...  -- 週足の始値を取得するテーブル（手順(2))
INNER JOIN (...) AS t3 ON ...  -- 週足の終値を取得するテーブル（手順(2))
ORDER BY t1.date_w ASC;
```

## 未確認事項

* MySQLの場合の書き方。SUBDATE、DATE_FORMATなどを使うといい？
* 他の書き方もできるかもしれないので、わかり次第更新予定