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

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')) ;