GreenplumDataBase存储万得宏汇高频数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
-- Table: hffd.tdb_tickab

-- DROP TABLE hffd.tdb_tickab;

CREATE TABLE hffd.tdb_tickab
(
  id bigint NOT NULL DEFAULT nextval('hffd.tdb_tickab_seq'::regclass),
  windcode character(20), --万得代码
  code character(20),     --交易所代码
  date date,              --日期
  "time" numeric,         --时间
  price numeric,          --成交价
  volume numeric,         --成交量
  turover numeric,        --成交额（元）
  matchitem numeric,      --
  interest numeric,       --
  tradeflag character(50),--成交标志
  bsflag character(50),   --BS标志
  accvolume numeric,      --当日累计成交量
  accturover numeric,     --当日成交额（元）
  askprice10 numeric,     --五档叫卖价
  askvolume10 numeric,    --五档叫卖量
  bidprice10 numeric,     --五档叫买价
  bidvolume10 numeric,    --五档叫买量
  askaveprice numeric,    --平均叫卖价
  bidaveprice numeric,    --平均叫买价
  totalaskvolume numeric, --叫卖量
  totalbidvolume numeric, --叫买量
  open numeric,           --开盘价
  high numeric,           --最高价
  low numeric,            --最低价
  preclose numeric        --前收盘
)
WITH (APPENDONLY=true, COMPRESSLEVEL=1, ORIENTATION=column, COMPRESSTYPE=quicklz, 
  OIDS=FALSE
)
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
( START (date '2013-01-01') INCLUSIVE
   END (date '2017-01-01') EXCLUSIVE
   EVERY (INTERVAL '1 mon') );
ALTER TABLE hffd.tdb_tickab
  OWNER TO gpadmin;
GRANT ALL ON TABLE hffd.tdb_tickab TO gpadmin;
GRANT SELECT ON TABLE hffd.tdb_tickab TO fitl;

-- View: tdb_tickab

-- DROP VIEW tdb_tickab;

CREATE OR REPLACE VIEW tdb_tickab AS 
 SELECT tdb_tickab.id, tdb_tickab.windcode, tdb_tickab.code, tdb_tickab.date, tdb_tickab."time", tdb_tickab.price, tdb_tickab.volume, tdb_tickab.turover, tdb_tickab.matchitem, tdb_tickab.interest, tdb_tickab.tradeflag, tdb_tickab.bsflag, tdb_tickab.accvolume, tdb_tickab.accturover, tdb_tickab.askprice10, tdb_tickab.askvolume10, tdb_tickab.bidprice10, tdb_tickab.bidvolume10, tdb_tickab.askaveprice, tdb_tickab.bidaveprice, tdb_tickab.totalaskvolume, tdb_tickab.totalbidvolume, tdb_tickab.open, tdb_tickab.high, tdb_tickab.low, tdb_tickab.preclose
   FROM hffd.tdb_tickab;

ALTER TABLE tdb_tickab
  OWNER TO fitl;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-- Table: hffd.tdb_transaction

-- DROP TABLE hffd.tdb_transaction;

CREATE TABLE hffd.tdb_transaction
(
  id bigint NOT NULL DEFAULT nextval('hffd.tdb_transaction_seq'::regclass),
  windcode character(20),      --万得代码
  code character(20),          --交易所代码
  "date" date,                 --日期
  "time" integer,              --时间
  functioncode character(50),  --成交代码
  orderkind character(50),     --委托代码
  bsflag character(1),         --BS标志
  price numeric,               --成交价格
  volume integer,              --成交数量
  askorder integer,            --叫卖序号
  bidorder numeric             --叫买序号
)
WITH (APPENDONLY=true, COMPRESSLEVEL=1, ORIENTATION=column, COMPRESSTYPE=quicklz, 
  OIDS=FALSE
)
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
( START (date '2013-01-01') INCLUSIVE
   END (date '2017-01-01') EXCLUSIVE
   EVERY (INTERVAL '1 mon') );
ALTER TABLE hffd.tdb_transaction
  OWNER TO gpadmin;
GRANT ALL ON TABLE hffd.tdb_transaction TO gpadmin;
GRANT SELECT ON TABLE hffd.tdb_transaction TO fitl;

-- View: tdb_transaction

-- DROP VIEW tdb_transaction;

CREATE OR REPLACE VIEW tdb_transaction AS 
 SELECT tdb_transaction.id, tdb_transaction.windcode, tdb_transaction.code, tdb_transaction.date, tdb_transaction."time", tdb_transaction.functioncode, tdb_transaction.orderkind, tdb_transaction.bsflag, tdb_transaction.price, tdb_transaction.volume, tdb_transaction.askorder, tdb_transaction.bidorder
   FROM hffd.tdb_transaction;

ALTER TABLE tdb_transaction
  OWNER TO fitl;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Table: hffd.tdb_orderqueue

-- DROP TABLE hffd.tdb_orderqueue;

CREATE TABLE hffd.tdb_orderqueue
(
  id bigint NOT NULL DEFAULT nextval('hffd.tdb_orderqueue_seq'::regclass),
  windcode character(20),  --万得代码
  code character(20),      --交易所代码
  date date,               --日期
  "time" numeric,          --时间
  side character(50),      --买卖方向
  price numeric,           --成交价格
  orderitems numeric,      --订单数量
  abitems numeric,         --明细个数
  abvolume numeric         --订单明细
)
WITH (APPENDONLY=true, COMPRESSLEVEL=1, ORIENTATION=column, COMPRESSTYPE=quicklz, 
  OIDS=FALSE
)
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
( START (date '2013-01-01') INCLUSIVE
   END (date '2017-01-01') EXCLUSIVE
   EVERY (INTERVAL '1 mon') );
ALTER TABLE hffd.tdb_orderqueue
  OWNER TO gpadmin;
GRANT ALL ON TABLE hffd.tdb_orderqueue TO gpadmin;
GRANT SELECT ON TABLE hffd.tdb_orderqueue TO fitl;

-- View: tdb_orderqueue

-- DROP VIEW tdb_orderqueue;

CREATE OR REPLACE VIEW tdb_orderqueue AS 
 SELECT tdb_orderqueue.id, tdb_orderqueue.windcode, tdb_orderqueue.code, tdb_orderqueue.date, tdb_orderqueue."time", tdb_orderqueue.side, tdb_orderqueue.price, tdb_orderqueue.orderitems, tdb_orderqueue.abitems, tdb_orderqueue.abvolume
   FROM hffd.tdb_orderqueue;

ALTER TABLE tdb_orderqueue
  OWNER TO fitl;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- Table: hffd.tdb_order

-- DROP TABLE hffd.tdb_order;

CREATE TABLE hffd.tdb_order
(
  id bigint NOT NULL DEFAULT nextval('hffd.tdb_order_seq'::regclass),
  windcode character(20),             --万得代码
  code character(20),                 --交易所代码
  date date,                          --日期
  "time" numeric,                     --时间
  ordernumber character varying(20),  --交易所委托号
  orderkind character(20),            --委托类型
  functioncode character(1),          --委托代码 B，S，C
  price numeric,                      --委托价格
  volume numeric                      --委托数量
)
WITH (APPENDONLY=true, COMPRESSLEVEL=1, ORIENTATION=column, COMPRESSTYPE=quicklz, 
  OIDS=FALSE
)
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
( START (date '2013-01-01') INCLUSIVE
   END (date '2017-01-01') EXCLUSIVE
   EVERY (INTERVAL '1 mon') );
ALTER TABLE hffd.tdb_order
  OWNER TO gpadmin;
GRANT ALL ON TABLE hffd.tdb_order TO gpadmin;
GRANT SELECT ON TABLE hffd.tdb_order TO fitl;

-- View: tdb_order

-- DROP VIEW tdb_order;

CREATE OR REPLACE VIEW tdb_order AS 
 SELECT tdb_order.id, tdb_order.windcode, tdb_order.code, tdb_order.date, tdb_order."time", tdb_order.ordernumber, tdb_order.orderkind, tdb_order.functioncode, tdb_order.price, tdb_order.volume, tdb_order.tdate
   FROM hffd.tdb_order;

ALTER TABLE tdb_order
  OWNER TO fitl;
GRANT ALL ON TABLE tdb_order TO fitl;