fc2ブログ

より汎用的な時系列データを扱うためのテーブル

前回、株価を保存するために、4本値のフィールドを持ったテーブルでは良くないことを説明しました。 では、少し視点を変えて、株価データ、信用残データ、ティックデータに共通することは何でしょうか。この観点から、テーブルのフィールドを考えてみます。
スポンサーリンク
共通点は2つあります。まず、時系列のデータであること。もう一つは、株に関するデータであることです。つまり、言い換えると、「時系列(日時)」で「株に関する(銘柄コード)(市場)」「データ(値)」となるわけです。ここで( )でくくった言葉はフィールドとして抽出できるものです。
  • 日時
  • 銘柄コード
  • 市場
では今度は、この3つのフィールドにさらにどのようなフィールドがあれば、前回指摘した問題点が解決するか考えてみます。まず、日足データと分足データのようなデータを区別するために、データの期間というフィールドが必要になってきます。例えば、日足であれば「1日」、1分足であれば「1分」、30分足であれば「30分」、信用残データは「1週」、ティックデータは少し特殊ですが「ティック」、などとなるでしょう。
  • 期間
次に、株価なのか、信用残なのか、ティックなのかを区別する必要があります。ここでは、カテゴリーというフィールドにします。
  • カテゴリー
さらに、株価の何のデータなのか、信用残の何のデータなのか、ティックの何のデータなのかを区別する必要があります。サブカテゴリーという位置づけなのですが、ここでは、アイテム(項目)というフィールドにします。
  • アイテム
さあ、これで情報は出揃いました。ポイントは、テーブルに持たせる値のフィールドは、1つにして、カテゴリーとアイテムという名前をつけることです。そして、4本値の形で、データが見たければ、そのようなビューを作れば良いのです。では、ここまでの説明の内容をER図にしてみます。カテゴリーとアイテムの名前は別テーブルにしています。

実際にどういったデータがどのように格納されるかは、以下のようなイメージになります(一部のフィールドを省略して表現しています)。
t_category
category_idcategory_name
1stockprice
2creditbalance
3tick

t_item
category_iditem_iditem_name
11volume
12open_price
13high_price
14low_price
15close_price
21buy
22sell
31price
42volume

t_value
category_iditem_iddatetimevalueterm
112011/12/102000d
122011/12/10150d
132011/12/10170d
142011/12/10140d
152011/12/10160d
112011/12/10 10:00151m
122011/12/10 10:002051m
132011/12/10 10:002071m
142011/12/10 10:002041m
152011/12/10 10:002061m
212011/12/1015000w
222011/12/1020000w
312011/12/10 10:00205tc
322011/12/10 10:0010tc


いかがだったでしょうか。拡張性が高く、管理もしやすい、テーブル設計が行えました。このようにテーブル設計をしておくことで、例えば、今後、テクニカル分析を行って、移動平均、RSI、一目均衡表の値などもテーブルを増やすことなく、データを保存することができるようになります。読者の皆さん、独自に拡張することに挑戦してみてください。
最後に、各テーブルをCreateするSQLと株価4本値を取得するビューのSQL例を掲載しておきます。参考にしてみてください。
CREATE TABLE t_category
(
  category_id smallint NOT NULL,
  category_name character varying(64) NOT NULL,
  CONSTRAINT t_category_pkey PRIMARY KEY (category_id)
)

CREATE TABLE t_item
(
  category_id smallint NOT NULL,
  item_id smallint NOT NULL,
  item_name character varying(64) NOT NULL,
  CONSTRAINT t_item_pkey PRIMARY KEY (category_id, item_id),
  CONSTRAINT t_item_category_id FOREIGN KEY (category_id)
)

CREATE TABLE t_value
(
  term character varying(2) NOT NULL,
  company_id integer NOT NULL,
  market_id smallint NOT NULL,
  category_id smallint NOT NULL,
  item_id smallint NOT NULL,
  "value" double precision NOT NULL,
  datetime timestamp without time zone NOT NULL,
  CONSTRAINT t_value_pkey PRIMARY KEY (term, company_id, market_id, datetime, category_id, item_id),
  CONSTRAINT t_value_company_id FOREIGN KEY (company_id),
  CONSTRAINT t_value_item_id FOREIGN KEY (category_id, item_id),
  CONSTRAINT t_value_market_id FOREIGN KEY (market_id)
)

CREATE VIEW v_stockprice AS 
select  
    a.term,
    a.company_id,
    a.market_id,
    a.datetime,
    a.value volume,
    b.value open_price,
    c.value high_price,
    d.value low_price,
    e.value close_price
from
    t_value a,
    t_value b,
    t_value c,
    t_value d,
    t_value e
where
    a.term=b.term and
    a.term=c.term and
    a.term=d.term and
    a.term=e.term and
    a.term=f.term and
    a.company_id=b.company_id and
    a.company_id=c.company_id and
    a.company_id=d.company_id and
    a.company_id=e.company_id and
    a.company_id=f.company_id and
    a.market_id=b.market_id and
    a.market_id=c.market_id and
    a.market_id=d.market_id and
    a.market_id=e.market_id and
    a.market_id=f.market_id and
    a.datetime=b.datetime and
    a.datetime=c.datetime and
    a.datetime=d.datetime and
    a.datetime=e.datetime and
    a.datetime=f.datetime and
    a.category_id=(select category_id from t_category where category_name='stockprice') and
    b.category_id=(select category_id from t_category where category_name='stockprice') and
    c.category_id=(select category_id from t_category where category_name='stockprice') and
    d.category_id=(select category_id from t_category where category_name='stockprice') and
    e.category_id=(select category_id from t_category where category_name='stockprice') and
    a.item_id=(select item_id from t_item where item_name='volume'      and category_id=(select category_id from t_category where category_name='stockprice')) and
    b.item_id=(select item_id from t_item where item_name='open_price'  and category_id=(select category_id from t_category where category_name='stockprice')) and
    c.item_id=(select item_id from t_item where item_name='high_price'  and category_id=(select category_id from t_category where category_name='stockprice')) and
    d.item_id=(select item_id from t_item where item_name='low_price'   and category_id=(select category_id from t_category where category_name='stockprice')) and
    e.item_id=(select item_id from t_item where item_name='close_price' and category_id=(select category_id from t_category where category_name='stockprice')) ;
スポンサーリンク
<<4本値と出来高をフィールドに持つテーブルが良くない理由 | ホーム | インデックスを設定し検索を高速化する>>
コメント(0)
コメントの投稿
トラックバック(0)