有序的时间表数据/使用结构变体的股票市场数据 [英] Ordered Timeseries data / Stock Market data using structure variants

问题描述

我正在寻求将时间序列信息组织到数据库中的一些建议。我目前的实现是PostgreSQL与单表的所有符号。
获取定制信息很慢，但很容易追加。
根据此处中的信息，我想复制数据库，每个符号创建一个单独的表。仔细考虑之后，我意识到我可以根据以下一些标准来查询多个符号：

  SELECT * FROMall_symbolsWHERE closePrice> = 50 
  

因为我需要组合这些表不知何故搜索（这可能比我想象的容易得多）。我还会增加一些不切实际的数量的表，因为我在一个单独的表中记录了每个时间框架每个符号的一些统计分析（如stddev）。

我的目标是拥有快速高效的数据库，我可以在这里阅读并将数据结合到任何可能的方式进行分析和研究。

几乎忘记提到我正在研究开源实现

提前谢谢。

解决方案

将索引放在 closePrice 上，然后使用，而不是> = ：

  SELECT * FROM all_symbols 
 WHERE closePrice在50和<一些大值之间> 
  

单面范围通常不使用索引，但应使用索引。 p>

I'm looking into getting some advice on organizing the time-series information into the database. My current implementation is PostgreSQL with single table for all symbols. It is slow on getting customized information but very easy to append. Based on the information from here I wanted to replicate the structure of the database with creating a separate table per symbol. After thinking about it more careful I realized that it would be a bit hard for me to query multiple symbols based on some criteria like:

SELECT * FROM "all_symbols" WHERE closePrice >= 50

since I would need to combine those tables somehow for the search(It might be much easier than I think). I would also grow the number of tables by some unrealistic amount due to the fact that I record some statistical analysis(like stddev) per symbol per timeframe in a separate table.

My goal is to have fast and efficient database where I can read and combine the data any possible way for the analysis and research.

Almost forgot to mention that I am looking into the Open Source implementation.

Thank you in advance.

解决方案

Put an index on closePrice, and use between instead of >=:

SELECT * FROM all_symbols
WHERE closePrice between 50 and <some large value>

one-sided ranges usually don't use an index, but between should use the index.

这篇关于有序的时间表数据/使用结构变体的股票市场数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！