英文:
How Do I Structure MongoDB To Query By Date Time Per Minute?
问题
我正在尝试存储每分钟的股票价格，这样我就可以根据每分钟的日期时间轻松返回结果，并存储历史数据，以便可以查询最近24小时、最近30天等（如果这种方法不正确，请告诉我）。
例如，如果我使用fmt.Println("time now: ", time.Now())检查当前时间，我会得到以下日期时间：2022-01-29 11:47:02.398118591 +0000 UTC m=+499755.770119738。
所以我想要的是只获取到分钟级别的时间，这样我就可以按分钟存储。
所以我想使用这个日期时间：2022-01-29 11:47:00 +0000 UTC。
我希望使用UTC时间，这样我可以坚持使用这个通用时区来存储和检索数据。
每一行将是一个包含多个股票价格数据的列表。
我是否需要_id字段？我不确定，所以只是寻求最佳实践的帮助。
数据库名称："stock-price-db"
集合名称："stock-price"
想象一下，只是举个例子
[
{
"_id" : ObjectId("5458b6ee09d76eb7326df3a4"),
"2022-01-29 11:48:00 +0000 UTC":
[
{
"stock": "TSLA",
"price": "859.83",
"marketcap": "8938289305"
},
{
"stock": "AAPL",
"price": "175.50",
"marketcap": "3648289305"
}
]
},
{
"_id" : ObjectId("5458b6ee09d76eb7326df3a4"),
"2022-01-29 11:47:00 +0000 UTC":
[
{
"stock": "TSLA",
"price": "855.50",
"marketcap": "8848289305"
},
{
"stock": "AAPL",
"price": "172.96",
"marketcap": "3638289305"
}
]
}
]
首先，这种方式存储数据在MongoDB中是否正确？然后，我应该如何构建模型以便按分钟间隔存储数据，以便可以按分钟间隔查询数据？
英文:
I am trying to store the price of stocks price per minute, so I can easily return results based on the minute date time per minute interval and store historical data, so i can query like last 24 hours, last 30 days etc (please also let me know if this is wrong approach)
for example if i check current time with fmt.Println("time now: ", time.Now()) i get the following date time 2022-01-29 11:47:02.398118591 +0000 UTC m=+499755.770119738
so what i want is to only get up to minute level, so i can store per minute
so i will liek to use this date time 2022-01-29 11:47:00 +0000 UTC
I will like to UTC, so i can stick to that universal time zone to store and retrive data
Each row will be a list of multiple stock price data
Do i need to have the _id field? Am not sure, so just looking for best practice as help.
database name: "stock-price-db"
collection name: "stock-price"
> Thinking of something like this, just for example
[
{
"_id" : ObjectId("5458b6ee09d76eb7326df3a4"),
"2022-01-29 11:48:00 +0000 UTC":
[
{
"stock": "TSLA",
"price": "859.83",
"marketcap": "8938289305",
},
{
"stock": "AAPL",
"price": "175.50",
"marketcap": "3648289305",
},
]
},
{
"_id" : ObjectId("5458b6ee09d76eb7326df3a4"),
"2022-01-29 11:47:00 +0000 UTC":
[
{
"stock": "TSLA",
"price": "855.50",
"marketcap": "8848289305",
},
{
"stock": "AAPL",
"price": "172.96",
"marketcap": "3638289305",
},
]
},
]
First, is this the right way to do store this type of data in mongodb and how do I structure the model to store the data this way so I can store the data per minute interval, so I can query per minute interval?
答案1
得分: 2
你的设计有几个缺点：
- 不要使用动态键 - 这样会导致使用额外的聚合管道。
- 将日期存储在静态键字段中，例如 time:ISODate()。
- 最好存储所有可用的时间单位，直到毫秒级，这将有助于处理未来的需求变化。
- 如果股票变动过多，这个设计就不具备可扩展性。
- 如果你想要查找某只股票的历史数据，提供的设计可能会有性能问题。
- 你可能会在分片方面遇到问题。
其他的替代方案：
- 并不是所有的用例都可以通过一个设计来解决。
- 如果这个用例纯粹是用于时间序列，我建议你使用时间序列设计/时间序列数据库，例如 InfluxDB、TSDB。
- 如果你需要涵盖所有的用例，可以进行规范化并使用 GQL。
英文:
There are few drawbacks in your design.
- Do not use dynamic keys - you will end up using few extra aggregation pipelines.
- Store the date in a static-key field i.e time:ISODate()
- Better store all the available time units, till milliseconds, it will be helpful to handle the future requirement changes
- If there are too many stocks changes, it is not a scalable design.
- If you want to find out historical data for a stock, provided design may have performance issues.
- You will end up with issues in sharding.
What other alternatives:
- Not all the use-cases can be solved by one design.
- If this use case is purely for time series use case, I would recommend you to use a time series design/ time series database i.e influx, tsdb.
- If you need to cover all the use-cases, normalise and use GQL.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论