用于组织历史股票数据的数据库架构

Question

我正在创建一个用于存储历史股票数据的数据库架构。目前，我的架构如下所示。

我的需求是为多个股票符号存储“条形数据”（日期、开盘价、最高价、最低价、收盘价和成交量）。每个符号也可能有多个时间框架（例如，谷歌周线和谷歌日线）。

我的当前架构将大部分数据存储在OHLCV表中。我离数据库专家还很远，很好奇这是否太幼稚。欢迎提出建设性意见。

CREATE TABLE Exchange (exchange TEXT UNIQUE NOT NULL);

CREATE TABLE Symbol (symbol TEXT UNIQUE NOT NULL, exchangeID INTEGER NOT NULL);

CREATE TABLE Timeframe (timeframe TEXT NOT NULL, symbolID INTEGER NOT NULL);

CREATE TABLE OHLCV (date TEXT NOT NULL CHECK (date LIKE '____-__-__ __:__:__'),
    open REAL NOT NULL,
    high REAL NOT NULL,
    low REAL NOT NULL,
    close REAL NOT NULL,
    volume INTEGER NOT NULL,
    timeframeID INTEGER NOT NULL);

这意味着我的查询目前大致如下：查找给定符号/时间范围的时间范围 ID，然后在 OHLCV 表中执行选择，其中时间范围 ID 匹配。

- nall

---

这里的问题不太清楚，是要进行代码审查吗？ - randomx

问题是：“当考虑到大型数据集时，这个设计是否合理，或者应该重新考虑？” - nall

请问您能提供此模式图吗？您使用的是哪种数据库？我遇到了类似的情况。 - Arun Raja

你能分享一下你最终设计出来的模式吗？ - Sisir

在quant.stackexchange上有一个非常丰富的关于此话题的讨论:https://quant.stackexchange.com/questions/29572/building-financial-data-time-series-database-from-scratch，我真的很推荐。 - Valdek Santana

Answer 1

我们尝试了很长时间来寻找适合存储大量数据的合适数据库结构。下面的解决方案是超过6年经验的结果，现在可以完美地用于我们的定量分析。

我们使用以下方案在SQL Server中存储数百GB的盘中和日常数据：

 Symbol -  char 6
 Date -  date
 Time -  time
 Open -  decimal 18, 4
 High -  decimal 18, 4
 Low -  decimal 18, 4
 Close -  decimal 18, 4
 Volume -  int

所有交易工具都存储在一个表中。我们还在符号、日期和时间列上建立了聚集索引。

对于日常数据，我们有一个单独的表格，并且不使用时间列。成交量的数据类型也是bigint而不是int。

性能如何？我们可以在几毫秒内从服务器获取数据。请记住，数据库大小接近1TB。

我们从Kibot网站购买了所有历史市场数据：http://www.kibot.com/

- boe100

---

那么你如何应对股票分割？ - Lydon Ch

我们每天跟踪股票的拆分和分红信息，对于需要更改的每个股票符号，我们会删除并批量插入数据。 - boe100

@boe100 我也在考虑采用“扁平”数据库模式。我有一个疑问——假设有10k个股票，要存储5年的每日数据，那么单个表中将会有10k * 365 * 5 = 18,250,000行。你的数据库能处理这么大的表吗？请问你使用的是哪种数据库解决方案，是否进行了表分区？谢谢！ - KFL

@boe100，你能否发送数据库的完整结构？因为我们需要考虑基础数据、市场数据等。如果未来需要添加许多表并进行链接，它如何考虑可扩展性？如果可能的话，你能否分享你的邮件地址，以便我可以从你那里获得一些指导。 - Arun Raja

先生，我尝试着实现您的方法。我发现 "select * from stock_table where symbol=xxx" 很快，但是 "select * from stock_table where date=xxx" 却很慢。这是真的吗？ - Xu Hui

Answer 2

首先，积极向上的一面是你有足够明智的意识要先征求意见。这使得你领先于90%不熟悉数据库设计的人。

• 没有明确的外键关系。我认为timeframeID与symbolID有关联吗？
• 不清楚如何以这种方式查找任何内容。阅读上述外键相关的资料应该会在很小的努力下大大提高您的理解能力。
• 您正在将时间框架数据存储为TEXT类型。从性能和可用性的角度来看，这是不好的做法。
• 您当前的方案无法适应股票拆分，股票拆分迟早会发生。最好在价钱数据表和Symbol之间添加一个进一步的间接层。
• open、high、low、close价格最好存储为十进制或货币类型，或者更好的是，作为一个INTEGER字段存储，并且另有一个 INTEGER字段存储除数，因为允许的最小价格分数（美分、八分之一的美元等）因交易所而异。
• 由于您支持多个交易所，因此应支持多种货币。

如果所有这些内容都不太‘具有建设性’，我会感到抱歉，特别是因为我现在太困了，无法提供更加可用的替代方案。我希望以上内容足以让您上路。

- Michiel Buddingh

---

这非常有用，谢谢。时间范围数据很容易转换为整数，但我不确定您所说的从可用性角度来看它是否不好（在内部我有一个对象表示）。 - nall

在查看数据库中的整数时，请考虑卷的大小，数据类型的大小是否足够？ - littlegeek

现在许多数据库都支持固定精度的十进制类型，例如Postgres的NUMERIC类型。这比存储两个整数表示价格更好，因为它将价格数据保存在一列中，同时避免了浮点舍入误差。 - Paul Legato

Answer 3

我不确定Timeframe有什么附加价值 - 它似乎是一种不必要的复杂性，但这可能是我没有理解的东西;-) 一个时间框架可以有多个OHLCV吗？如果不能，那么我建议将它们合并。

我也想指出，股票代码会因为各种原因而不断变化。虽然不是经常发生，但确实会发生。如果您正在考虑将数据作为时间序列处理，那么您应该意识到这个问题，以便在发生之前或之后进行处理。如果您没有跟踪股票（例如，您可能正在开发期货应用程序），那么您可以适当地对此建议保持怀疑态度。

同样与股票相关，分割已经在其他地方提到过，您可能还需要考虑股息 - 股票价格通常会在除权日下跌股息金额（或更准确地说，其现值），如果您不知道已确认的未来现金流是原因，则可能会被误解。权益发行也很有趣。

如果您计划查看特定符号的一系列数据，我建议您了解一下您将获得的性能类型。至少，请确保已经安装了适当的索引。

- Mike Woodhouse

---

每个符号+时间框架组合在OHLCV表中有多个条目。感谢您对符号更改的观察。 - nall