search

HirokiTanaka / Stock

0 stars 0 forks source link

ChinaShock時に株価が下がらなかったデータを抽出する #7

Closed HirokiTanaka closed 9 years ago

HirokiTanaka commented 9 years ago
HirokiTanaka commented 9 years ago

調査対象期間

2015-08-17 〜 2015-08-25に決定

HirokiTanaka commented 9 years ago

調査データをテーブルに読み込み

CREATE EXTERNAL TABLE IF NOT EXISTS stocks (
  code string,
  market string,
  brand string,
  industry string,
  opening int,
  high int,
  low int,
  closing int,
  turnover int,
  sales int
)
PARTITIONED BY (dt string)
ROW FORMAT
  DELIMITED
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
;

ALTER TABLE stocks ADD PARTITION ( dt='2015-08-17' ) LOCATION 's3n://hirokitanaka-stock/hdfs/data/stocks/2015-08-17';
ALTER TABLE stocks ADD PARTITION ( dt='2015-08-18' ) LOCATION 's3n://hirokitanaka-stock/hdfs/data/stocks/2015-08-18';
ALTER TABLE stocks ADD PARTITION ( dt='2015-08-19' ) LOCATION 's3n://hirokitanaka-stock/hdfs/data/stocks/2015-08-19';
ALTER TABLE stocks ADD PARTITION ( dt='2015-08-20' ) LOCATION 's3n://hirokitanaka-stock/hdfs/data/stocks/2015-08-20';
ALTER TABLE stocks ADD PARTITION ( dt='2015-08-21' ) LOCATION 's3n://hirokitanaka-stock/hdfs/data/stocks/2015-08-21';
ALTER TABLE stocks ADD PARTITION ( dt='2015-08-22' ) LOCATION 's3n://hirokitanaka-stock/hdfs/data/stocks/2015-08-22';
ALTER TABLE stocks ADD PARTITION ( dt='2015-08-23' ) LOCATION 's3n://hirokitanaka-stock/hdfs/data/stocks/2015-08-23';
ALTER TABLE stocks ADD PARTITION ( dt='2015-08-24' ) LOCATION 's3n://hirokitanaka-stock/hdfs/data/stocks/2015-08-24';
ALTER TABLE stocks ADD PARTITION ( dt='2015-08-25' ) LOCATION 's3n://hirokitanaka-stock/hdfs/data/stocks/2015-08-25';
HirokiTanaka commented 9 years ago

対象データ抽出

select
    a.code
    , min(a.market)
    , min(a.brand)
    , min(a.industry)
    , min(a.opening)
    , max(a.closing)
    , max(a.closing) - min(a.opening) as diff
  from
    stocks as a
  where
    a.code not in (
      select
          code
        from
          stocks as b
        where
          b.closing <= b.opening
          or b.opening is null
          or b.closing is null
    )
  group by
    a.code
  order by
    diff desc
;
HirokiTanaka commented 9 years ago

1433-T 東証マザーズ ベステラ 建設業 8230 8510 280 3415-T 東証マザーズ STUDIOUS 小売業 3660 3915 255 3711-T JQスタンダード 創通 情報・通信業 2000 2135 135 6172-T 東証マザーズ メタップス サービス業 2162 2285 123 3919-T 東証1部 パイプドHD 情報・通信業 1060 1077 17 1473-T 東証 DIAM ETF トピックス その他 1457 1465 8

HirokiTanaka commented 9 years ago

ベステラは9/2上場の銘柄なのでこの抽出項目はおかしい・・・ どうもテスト用に取得したデータが混じっていたせいと考えられる

HirokiTanaka commented 9 years ago

株式市場が休みの場合、データダウンロード時にダウンロード日付のデータが取得される仕様になっていた。

16 にて対応

HirokiTanaka commented 9 years ago

22（土）と23（日）は除く

CREATE EXTERNAL TABLE IF NOT EXISTS stocks (
  code string,
  market string,
  brand string,
  industry string,
  opening int,
  high int,
  low int,
  closing int,
  turnover int,
  sales int
)
PARTITIONED BY (dt string)
ROW FORMAT
  DELIMITED
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
;

ALTER TABLE stocks ADD PARTITION ( dt='2015-08-17' ) LOCATION 's3n://hirokitanaka-stock/hdfs/data/stocks/2015-08-17';
ALTER TABLE stocks ADD PARTITION ( dt='2015-08-18' ) LOCATION 's3n://hirokitanaka-stock/hdfs/data/stocks/2015-08-18';
ALTER TABLE stocks ADD PARTITION ( dt='2015-08-19' ) LOCATION 's3n://hirokitanaka-stock/hdfs/data/stocks/2015-08-19';
ALTER TABLE stocks ADD PARTITION ( dt='2015-08-20' ) LOCATION 's3n://hirokitanaka-stock/hdfs/data/stocks/2015-08-20';
ALTER TABLE stocks ADD PARTITION ( dt='2015-08-21' ) LOCATION 's3n://hirokitanaka-stock/hdfs/data/stocks/2015-08-21';
ALTER TABLE stocks ADD PARTITION ( dt='2015-08-24' ) LOCATION 's3n://hirokitanaka-stock/hdfs/data/stocks/2015-08-24';
ALTER TABLE stocks ADD PARTITION ( dt='2015-08-25' ) LOCATION 's3n://hirokitanaka-stock/hdfs/data/stocks/2015-08-25';
HirokiTanaka commented 9 years ago

1472-T 東証 NEXT FUNDS JPX日経400ダブルインバース・インデックス連動型上場投信 その他 4230 4720 490 1552-T 東証 国際のETF VIX短期先物指数 その他 498 719 221 3711-T JQスタンダード 創通 情報・通信業 2000 2135 135