financial-data-engine/create_partition_table.sql at main · whchien/financial-data-engine · GitHub

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
CREATE TABLE `FinancialData`.`taiwan_stock_price`(
    `StockID` VARCHAR(10) NOT NULL,
    `TradeVolume` BIGINT NOT NULL,
    `Transaction` INT NOT NULL,
    `TradeValue` BIGINT NOT NULL,
    `Open` FLOAT NOT NULL,
    `Max` FLOAT NOT NULL,
    `Min` FLOAT NOT NULL,
    `Close` FLOAT NOT NULL,
    `Change` FLOAT NOT NULL,
    `Date` DATE NOT NULL,
    PRIMARY KEY(`StockID`, `Date`)
)
PARTITION BY RANGE(YEAR(Date)) (
    PARTITION p2005 VALUES LESS THAN (2006),
    PARTITION p2006 VALUES LESS THAN (2007),
    PARTITION p2007 VALUES LESS THAN (2008),
    PARTITION p2008 VALUES LESS THAN (2009),
    PARTITION p2009 VALUES LESS THAN (2010),
    PARTITION p2010 VALUES LESS THAN (2011),
    PARTITION p2011 VALUES LESS THAN (2012),
    PARTITION p2012 VALUES LESS THAN (2013),
    PARTITION p2013 VALUES LESS THAN (2014),
    PARTITION p2014 VALUES LESS THAN (2015),
    PARTITION p2015 VALUES LESS THAN (2016),
    PARTITION p2016 VALUES LESS THAN (2017),
    PARTITION p2017 VALUES LESS THAN (2018),
    PARTITION p2018 VALUES LESS THAN (2019),
    PARTITION p2019 VALUES LESS THAN (2020),
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023)
    PARTITION p2023 VALUES LESS THAN (2024)
);

CREATE TABLE `FinancialData`.`taiwan_futures_daily`(
    `Date` DATE NOT NULL,
    `FuturesID` VARCHAR(10) NOT NULL,
    `ContractDate` VARCHAR(30) NOT NULL,
    `Open` FLOAT NOT NULL,
    `Max` FLOAT NOT NULL,
    `Min` FLOAT NOT NULL,
    `Close` FLOAT NOT NULL,
    `Change` FLOAT NOT NULL,
    `ChangePer` FLOAT NOT NULL,
    `Volume` FLOAT NOT NULL,
    `SettlementPrice` FLOAT NOT NULL,
    `OpenInterest` INT NOT NULL,
    `TradingSession` VARCHAR(11) NOT NULL,
    PRIMARY KEY(`FuturesID`, `Date`)
)
PARTITION BY RANGE(YEAR(Date)) (
    PARTITION p2005 VALUES LESS THAN (2006),
    PARTITION p2006 VALUES LESS THAN (2007),
    PARTITION p2007 VALUES LESS THAN (2008),
    PARTITION p2008 VALUES LESS THAN (2009),
    PARTITION p2009 VALUES LESS THAN (2010),
    PARTITION p2010 VALUES LESS THAN (2011),
    PARTITION p2011 VALUES LESS THAN (2012),
    PARTITION p2012 VALUES LESS THAN (2013),
    PARTITION p2013 VALUES LESS THAN (2014),
    PARTITION p2014 VALUES LESS THAN (2015),
    PARTITION p2015 VALUES LESS THAN (2016),
    PARTITION p2016 VALUES LESS THAN (2017),
    PARTITION p2017 VALUES LESS THAN (2018),
    PARTITION p2018 VALUES LESS THAN (2019),
    PARTITION p2019 VALUES LESS THAN (2020),
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023)
    PARTITION p2023 VALUES LESS THAN (2024)
);