closeの値が会計期間末になっていない: 暫定対応

[icoxfog417]

closeの値が会計年(fiscal year)でなく暦年(calender year)の末の値になっているように思われます。

淺沼組
399 @fy end: 2018/3/30
2728 @cy end: 2018/12/28 -> documents.csvのcloseと一致
AGC
4880 @fy&CY end: 2017/12/29 -> documents.csvのcloseと一致
日本電気
2991 @fy end: 2018/3/30
3265 @cy end: 2018/12/28 -> documents.csvのcloseと一致
IHI
3305 @fy end: 2018/3/30
3030 @cy end: 2018/12/28 -> documents.csvのcloseと一致

[icoxfog417]

株価データのAthenaクエリが、year単位でPARTITIONをかけている
⇒yearは1~12月なので、これでPARTITIONをかけた場合年度末ではなく年末の値になってしまう。

CREATE OR REPLACE VIEW stock_data_annual AS 
SELECT
  "year"
, "edinet_sec_code"
, "min"("open") "open"
, "max"("high") "high"
, "min"("low") "low"
, "min"("close") "close"
, "min"("average") "average"
FROM
  (
   SELECT
     "year"
   , "edinet_sec_code"
   , "first_value"("open") OVER (PARTITION BY "year", "edinet_sec_code" ORDER BY "year_month" ASC) "open"
   , "max"("high") OVER (PARTITION BY "year", "edinet_sec_code" ORDER BY "year_month" ASC) "high"
   , "min"("low") OVER (PARTITION BY "year", "edinet_sec_code" ORDER BY "year_month" ASC) "low"
   , "first_value"("close") OVER (PARTITION BY "year", "edinet_sec_code" ORDER BY "year_month" DESC) "close"
   , "avg"("close") OVER (PARTITION BY "year", "edinet_sec_code" ORDER BY "year_month" ASC) "average"
   FROM
     stock_data
) 
GROUP BY "year", "edinet_sec_code"

[icoxfog417]

暫定対応: 多くの会社に当てはまる4月~翌3月で集計する。
会計年度/期間は4月開始の3月終了で機械的に設定

CREATE OR REPLACE VIEW stock_data_annual AS 
SELECT 
  "fiscal_year"
, "edinet_sec_code"
, "min"("open") "open"
, "max"("high") "high"
, "min"("low") "low"
, "min"("close") "close"
, "min"("average") "average"
FROM
  (
   SELECT
     "fiscal_year"
   , "edinet_sec_code"
   , "first_value"("open") OVER (PARTITION BY "fiscal_year", "edinet_sec_code" ORDER BY "fiscal_period" ASC) "open"
   , "max"("high") OVER (PARTITION BY "fiscal_year", "edinet_sec_code" ORDER BY "fiscal_period" ASC) "high"
   , "min"("low") OVER (PARTITION BY "fiscal_year", "edinet_sec_code" ORDER BY "fiscal_period" ASC) "low"
   , "first_value"("close") OVER (PARTITION BY "fiscal_year", "edinet_sec_code" ORDER BY "fiscal_period" DESC) "close"
   , "avg"("close") OVER (PARTITION BY "fiscal_year", "edinet_sec_code" ORDER BY "fiscal_period" ASC) "average"
   FROM
     data_stock
) 
GROUP BY "fiscal_year", "edinet_sec_code"