-
Notifications
You must be signed in to change notification settings - Fork 282
Expand file tree
/
Copy pathschema.sql
More file actions
108 lines (99 loc) · 2.68 KB
/
Copy pathschema.sql
File metadata and controls
108 lines (99 loc) · 2.68 KB
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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
-- marketdb schema v1.0.0
-- Raw layer mirrors Parquet/API dumps verbatim. Calc layer holds recomputable
-- derivations. v_* layer is the stable interface for SDK / notebooks / backtests.
CREATE TABLE IF NOT EXISTS _meta (
key VARCHAR PRIMARY KEY,
value VARCHAR,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS _import_batches (
batch_id VARCHAR PRIMARY KEY,
source VARCHAR NOT NULL, -- parquet | rest | mcp
kind VARCHAR NOT NULL, -- kline_daily | adjustment_events | symbols | snapshot
started_at TIMESTAMP NOT NULL,
finished_at TIMESTAMP,
row_count BIGINT,
notes VARCHAR
);
CREATE TABLE IF NOT EXISTS raw_kline_daily (
thscode VARCHAR NOT NULL,
date DATE NOT NULL,
open DOUBLE,
high DOUBLE,
low DOUBLE,
close DOUBLE,
volume DOUBLE,
turnover DOUBLE,
currency VARCHAR,
interval VARCHAR,
adjusted VARCHAR,
source_batch_id VARCHAR,
PRIMARY KEY (thscode, date)
);
CREATE TABLE IF NOT EXISTS raw_adjustment_events (
thscode VARCHAR NOT NULL,
ticker VARCHAR,
ex_date DATE NOT NULL,
dividend_per_share DOUBLE,
per_share_bonus DOUBLE,
allotment_ratio DOUBLE,
allotment_price DOUBLE,
currency VARCHAR,
source_batch_id VARCHAR,
PRIMARY KEY (thscode, ex_date)
);
CREATE TABLE IF NOT EXISTS dim_symbol (
thscode VARCHAR PRIMARY KEY,
ticker VARCHAR,
name VARCHAR,
exchange VARCHAR,
asset_type VARCHAR,
currency VARCHAR,
source_batch_id VARCHAR,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS calc_adjust_factor_daily (
thscode VARCHAR NOT NULL,
date DATE NOT NULL,
forward_factor DOUBLE NOT NULL,
backward_factor DOUBLE NOT NULL,
factor_version VARCHAR,
source_event_batch_id VARCHAR,
calculated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (thscode, date)
);
-- Staging tables: drop+create per batch.
CREATE TABLE IF NOT EXISTS stg_kline_daily (
thscode VARCHAR,
date DATE,
open DOUBLE,
high DOUBLE,
low DOUBLE,
close DOUBLE,
volume DOUBLE,
turnover DOUBLE,
currency VARCHAR,
interval VARCHAR,
adjusted VARCHAR,
source_batch_id VARCHAR
);
CREATE TABLE IF NOT EXISTS stg_adjustment_events (
thscode VARCHAR,
ticker VARCHAR,
ex_date DATE,
dividend_per_share DOUBLE,
per_share_bonus DOUBLE,
allotment_ratio DOUBLE,
allotment_price DOUBLE,
currency VARCHAR,
source_batch_id VARCHAR
);
CREATE TABLE IF NOT EXISTS stg_symbols (
thscode VARCHAR,
ticker VARCHAR,
name VARCHAR,
exchange VARCHAR,
asset_type VARCHAR,
currency VARCHAR,
source_batch_id VARCHAR
);