我有一堆每小时捕获的价格数据,这些数据在mongodb中进行打包,我有一个要求,我需要在一系列时间间隔内(如1天、1周、1个月、3个月等(挑选出一件商品的价值/价格。
数据非常简单,只是在格式下的一个集合中
"history" : [
{
"value" : 3600,
"date" : ISODate("2021-10-19T11:48:19.811Z"),
},
{...},
{...}
]
目前,我已经制定了一个解决方案来实现这一点,但我认为这不是正确的、性能最好的解决方案,我相信有更好的方法可以做到这一点。这是我目前的实现方式。
exports.getCollectibleChangeSummary = (req,res) => {
const slug = req.params.slug
MarketPriceHistoric.find({ collectibleId: slug })
.exec((err, data) => {
if (err){
return res.status(400).json({
error: errorHandler(err)
})
}
const currentPrice = data[0].history[0] ? data[0].history[0].value : 0
const calcThis = (endDay) => {
return (currentPrice - endDay) / endDay * 100
}
const results = {
"currentPrice": currentPrice,
"one_day_change": data[0].history[23] ? calcThis(data[0].history[23].value) : null,
"one_week_change": data[0].history[161] ? calcThis(data[0].history[161].value) : null,
"one_month_change": data[0].history[644] ? calcThis(data[0].history[961].value) : null,
"three_month_change": data[0].history[1932] ? calcThis(data[0].history[1932].value) : null,
"six_month_change": data[0].history[3864] ? calcThis(data[0].history[3864].value) : null,
"one_year_change": data[0].history[7728] ? calcThis(data[0].history[7728].value) : null
}
res.json(results)
})
}
有更好的方法来实现这一点吗?可能具有聚合函数?
谢谢,
也许使用新的$setWindowFields
运算符可以得到所需的结果
db.collection.aggregate([
{ $unwind: "$history" },
{ $replaceRoot: { newRoot: "$history" } },
{ $sort: { date: -1 } },
{
$setWindowFields: {
sortBy: { date: 1 },
output: {
last_day_price: {
$first: "$value",
window: { range: [-1, "current"], unit: "day" }
},
last_week_price: {
$first: "$value",
window: { range: [-1, "current"], unit: "week" }
},
last_month_price: {
$first: "$value",
window: { range: [-1, "current"], unit: "month" }
},
three_month_price: {
$first: "$value",
window: { range: [-3, "current"], unit: "month" }
},
six_month_price: {
$first: "$value",
window: { range: [-6, "current"], unit: "month" }
},
last_year_price: {
$first: "$value",
window: { range: [-1, "current"], unit: "year" }
}
}
}
},
{
$set: {
currentPrice: "$value",
one_day_change: { $multiply: [100, { $divide: [{ $subtract: ["$last_day_price", "$value"] }, "$value"] }] },
one_week_change: { $multiply: [100, { $divide: [{ $subtract: ["$last_week_price", "$value"] }, "$value"] }] },
one_month_change: { $multiply: [100, { $divide: [{ $subtract: ["$last_month_price", "$value"] }, "$value"] }] },
three_month_change: { $multiply: [100, { $divide: [{ $subtract: ["$three_month_price", "$value"] }, "$value"] }] },
six_month_change: { $multiply: [100, { $divide: [{ $subtract: ["$six_month_price", "$value"] }, "$value"] }] },
one_year_change: { $multiply: [100, { $divide: [{ $subtract: ["$last_year_price", "$value"] }, "$value"] }] }
}
},
{ $limit: 1 }
])
查询
- 它很大,但代码完全相同6x唯一的区别是
$multiply
参数 - 取一个
target-date
,可以放置任意日期并替换$$NOW
,即服务器的当前日期 - 例如,如果
target-date
是ISODate("2021-10-19T11:48:19.811Z")
,我将计算- 价格范围
target-date
-24小时(天范围( - 价格区间
target-date
-7*24小时(周区间( - 价格区间
target-date
-30*24小时(月区间( - 价格范围
target-date
-3*30*24小时(3个月范围( - 价格区间
target-date
-6*30*24小时(6个月区间( - 价格范围
target-date
-12*30*24小时(12个月范围(
- 价格范围
*它计算最高价格和最低价格,对于每个周期,你可以减去它们或取百分比等,以获得你想要的价格范围
*我们没有数据或预期的输出,但我认为这是你需要
PlayMongo
aggregate(
[{"$set": {"target-date": "$$NOW"}},
{"$facet":
{"one-day":
[{"$match":
{"$expr":
{"$lte":
[{"$subtract": ["$target-date", "$date"]},
{"$multiply": [24, 60, 60, 1000]}]}}},
{"$group":
{"_id": null,
"max-price": {"$max": "$value"},
"min-price": {"$min": "$value"}}},
{"$unset": ["_id"]}],
"one-week":
[{"$match":
{"$expr":
{"$lte":
[{"$subtract": ["$target-date", "$date"]},
{"$multiply": [7, 24, 60, 60, 1000]}]}}},
{"$group":
{"_id": null,
"max-price": {"$max": "$value"},
"min-price": {"$min": "$value"}}},
{"$unset": ["_id"]}],
"one-month":
[{"$match":
{"$expr":
{"$lte":
[{"$subtract": ["$target-date", "$date"]},
{"$multiply": [30, 24, 60, 60, 1000]}]}}},
{"$group":
{"_id": null,
"max-price": {"$max": "$value"},
"min-price": {"$min": "$value"}}},
{"$unset": ["_id"]}],
"three-months":
[{"$match":
{"$expr":
{"$lte":
[{"$subtract": ["$target-date", "$date"]},
{"$multiply": [3, 30, 24, 60, 60, 1000]}]}}},
{"$group":
{"_id": null,
"max-price": {"$max": "$value"},
"min-price": {"$min": "$value"}}},
{"$unset": ["_id"]}],
"six-months":
[{"$match":
{"$expr":
{"$lte":
[{"$subtract": ["$target-date", "$date"]},
{"$multiply": [6, 30, 24, 60, 60, 1000]}]}}},
{"$group":
{"_id": null,
"max-price": {"$max": "$value"},
"min-price": {"$min": "$value"}}},
{"$unset": ["_id"]}],
"one-year":
[{"$match":
{"$expr":
{"$lte":
[{"$subtract": ["$target-date", "$date"]},
{"$multiply": [12, 30, 24, 60, 60, 1000]}]}}},
{"$group":
{"_id": null,
"max-price": {"$max": "$value"},
"min-price": {"$min": "$value"}}}, {"$unset": ["_id"]}]}}])