我已經搜索了一段時間，并沒有真正找到足夠清晰的答案來理解如何去做。我在 MongoDB 中以分鐘時間為基礎（Unix 時間戳）保存了圖表資料。現在我明白了，如果我想以每小時或每天的時間可視化圖表，我需要聚合資料，但我并不完全理解需要做什么。
我想創建 1 小時和 1 天的組，這些組需要包含其時間范圍的最后一條記錄。然后是否需要進行一次聚合并將其保存到資料庫中？或者每次我查詢它。以及如何在貓鼬中設定查詢？
這是我收集圖表資料的示例
collection:[
{
time: 1649083392,
volume: '20384.28',
open: 444.42,
close: 444.56,
high: 444.76,
low: 444.36
},
{
time: 1649083448,
volume: '20384.28',
open: 444.42,
close: 444.56,
high: 444.76,
low: 444.36
},
{
time: 1649083501,
volume: '20384.28',
open: 444.42,
close: 444.56,
high: 444.76,
low: 444.36
}
]
假設我需要每天時間范圍內的圖表。一天有1440分鐘。
I first need to make a group per day to add up volume per day and then project the last item for each day. it should project the volume per day and the last item of the day for each day.
Hope someone can explain me a bit how this works. Thanks.
---- Update ---
Sorry. so I made a mistake.
[{
"pairAddress": "0x58F876857a02D6762E0101bb5C46A8c1ED44Dc16",
"chart": [
{
"time": 1648978488,
"high": "442.93181339228767",
"low": "440.89881857342505",
"open": "440.89901371910537",
"close": "442.9168809785855",
"marketcap": "2505922284.61",
"volume": "14264.118014884118",
"_id": {
"$oid": "62496a3b8741c95e7661a0c2"
}
},
{
"time": 1648978536,
"high": "442.9603776582797",
"low": "442.9122490168528",
"open": "442.9292814855807",
"close": "442.9478700257827",
"marketcap": "2506097613.54",
"volume": "19482.73456302384",
"_id": {
"$oid": "62496a778741c95e7661a971"
}
},
{
"time": 1648978608,
"high": "442.9893218041529",
"low": "442.941310936878",
"open": "442.9481594715175",
"close": "442.9893218041529",
"marketcap": "2506332138.21",
"volume": "16138.024513587941",
"_id": {
"$oid": "62496ab38741c95e7661b53a"
}
},
{
"time": 1648978668,
"high": "443.5010551781398",
"low": "442.9032561370158",
"open": "442.9893789835573",
"close": "443.5010551781398",
"marketcap": "2509227408.46",
"volume": "24664.532500429723",
"_id": {
"$oid": "62496aef8741c95e7661c000"
}
},
{
"time": 1648978728,
"high": "443.5205214040826",
"low": "443.4918353053875",
"open": "443.50216033083433",
"close": "443.5202071089899",
"marketcap": "2509335765.70",
"volume": "5548.645723580672",
"_id": {
"$oid": "62496b2b8741c95e7661c951"
}
},
{
"time": 1648978788,
"high": "443.6375372213781",
"low": "443.470378539243",
"open": "443.50698654937736",
"close": "443.5999403093497",
"marketcap": "2509786877.88",
"volume": "52212.176474500986",
"_id": {
"$oid": "62496b678741c95e7661d396"
}
},
{
"time": 1648978848,
"high": "443.61143763713756",
"low": "443.58718500668306",
"open": "443.59987943714646",
"close": "443.5872533304441",
"marketcap": "2509715097.86",
"volume": "14691.325842608467",
"_id": {
"$oid": "62496ba38741c95e7661e2d3"
}
}
]
}]
This is an actual example of my document, I need to aggregate over the chart array. and using the existing code I cant get it to work.
uj5u.com熱心網友回復：
我知道這已經得到了回答，但這是我的看法，將時間戳設定為間隔，然后根據 timestampBoundary 對資料進行分組
db.data.aggregate([
{
$addFields: {
// Your Group Interval In Seconds eg.
// - 86400 For Per Day
// - 3600 Per Hour
// - 900 Per 15 minute
timestampBoundary: {
$subtract: ["$time", {$mod: ["$time", 3600]}]
},
}
},
{
$sort: {
time: -1
}
},
{
$group: {
_id: "$timestampBoundary",
lastItem: {$first: "$$ROOT"},
totalVolume: {
$sum: {
$toDecimal: "$volume"
}
},
}
}
])
uj5u.com熱心網友回復：
db.collection.aggregate([
{
$sort: { time: -1 }
},
{
$set: {
d: { $toDate: { $multiply: [ "$time", 1000 ] } },
volume: { $toDecimal: "$volume" }
}
},
{
$facet: {
day: [
{
$group: {
_id: {
$dateTrunc: {
date: "$d",
unit: "day"
}
},
volume: { $sum: "$volume" },
lastItem: { $first: "$$ROOT" }
}
}
],
hour: [
{
$group: {
_id: {
year: { $year: "$d" },
dayOfYear: { $dayOfYear: "$d" },
hour: { $hour: "$d" }
},
volume: { $sum: "$volume" },
lastItem: { $first: "$$ROOT" }
}
}
],
15min: [
{
$group: {
_id: {
year: { $year: "$d" },
dayOfYear: { $dayOfYear: "$d" },
hour: { $hour: "$d" },
interval: {
$subtract: [
{ $minute: "$d" },
{ $mod: [ { $minute: "$d" }, 15 ] }
]
}
},
volume: { $sum: "$volume" },
lastItem: { $first: "$$ROOT" }
}
}
]
}
}
])
mongoplayground
uj5u.com熱心網友回復：
所以，我仍然有這個問題，因為需要聚合的資料在檔案的陣列中。
我找到了 $unwind 函式來解決這個問題。
Chart.aggregate([
{$match:{"pairAddress": "0x58F876857a02D6762E0101bb5C46A8c1ED44Dc16"}},
{$unwind: "$chart"},
{$addFields: {
timestampBoundary: {$subtract: ["$chart.time",{$mod: ["$chart.time", 900]}]},
}},
{$sort: {"chart.time": -1}},
{$group: {
_id: "$timestampBoundary",
lastItem: {$first: "$$ROOT"},
volume: {$sum: {$toDecimal: "$chart.volume"}}
}},
{$sort:{ _id: 1}}
])
首先我使用 $match 來確保我只得到我想要聚合的檔案，展開我想要聚合的圖表陣列。然后添加一個新欄位以將 UNIX 時間戳轉換為按時間戳排序。之后我可以按時間戳排序并制作一組指定的時間間隔。將每組的體積相加并進行最后一次排序。在 $mod: 900 代表秒，這是我可以指定組的時間間隔的地方。
感謝@YuTing 和@Calvin Coomer，我現在得到了完美的解決方案。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/457732.html
上一篇：如何將聚合用于模型？