在 MongoDB 聚合和 NodeJS 中计算过去 200 天的平均值 [英] Calculating the average for last 200 days in MongoDB aggregation and NodeJS
问题描述
我有一个脚本，用于计算 MongoDB 中每个集合的平均 Volume 数据.但由于我有超过 1000 天的记录，我希望仅计算过去 200 天的平均交易量.
I have a script which is calculating the average Volume data for every collection in my MongoDB. But since I have more that a 1000 days in record I wish to calculate the Average Volume only for last 200 days.
目前我的脚本获取了收集中的全部数据(1000 天以上).
Currently my script taking the whole data which is in collection (1000days+).
目标:我如何设置过去 200 天的范围来计算它们的平均 Volume.还有很重要的一点是，每天我仍然想只使用最后 200 天.
Goal: How I can setup a range of last 200 days to calculate the average Volume for them. Also pretty important thing that every next day I still want to use only last 200 days.
平均聚合框架
const saveToDatebase = async(symbol, BTCdata) => {
try {
const url = 'mongodb://username:passwprd@ipaddress/port?retryWrites=true&w=majority';
let dateTime = getDateTime();
let db = await MongoClient.connect(url, { useUnifiedTopology: true });
const dbo = db.db('Crypto');
const myobj = Object.assign({ Name: symbol, Date: dateTime }, BTCdata[0]);
await dbo.collection(symbol).insertOne(myobj);
const average = await dbo.collection(symbol).aggregate([{
'$group': {
_id:null,
'Volume': {
'$avg': '$Volume'
}
}
}]).toArray();
console.log('1 document inserted');
console.log(average);
db.close();
} catch (e) {
console.error(e)
}
};
EDIT1像这样编辑代码后>>
EDIT1 After editing the code like this >>
const average = await dbo.collection(symbol).aggregate([{
$match: {
$gte: ["$dateTime", { $subtract: ["$$NOW", 200 * 60 * 60 * 24 * 1000] }]
},
"$group": {
_id: null,
"Volume": {
"$avg": "$Volume"
}
}
}]).toArray();
我收到此错误 >MongoError:管道阶段规范对象必须只包含一个字段.
I receive this error > MongoError: A pipeline stage specification object must contain exactly one field.
EDIT2代码 使用如下代码后，我收到此错误 - MongoError: unknown top level operator: $gte
EDIT2
Code After having code like below I receive this error - MongoError: unknown top level operator: $gte
const average = await dbo.collection(symbol).aggregate([{
$match: { $gte: ["$dateTime", { $subtract: ["$$NOW", 200 * 60 * 60 * 24 * 1000] }] }
},
{
"$group": {
_id: null,
"Volume": {
"$avg": "$Volume"
}
}
}
])
这是文档在 MongoDB 中的样子.
Here is how document looks like in MongoDB.
EDIT3这是我的代码关于最后改进的样子.代码正在工作唯一的问题它没有给我平均.在我的终端中，我收到一个空数组 [] 但我没有收到任何错误.我认为问题出在这里 - "$dateTime" 我认为对它的查询是错误的.因为我也在尝试在 MongoDB Compass 中实现它.效果也不好.
EDIT3
Here is how my code looks like regarding the last improvement. Code is working the only problem It didn't return me average. In my terminal I receive an empty array [] but I don't receive any error. I'm thinking that the problem is here - "$dateTime" I think the query for it is wrong. Cause I'm also trying to make it in MongoDB Compass. Doesn't work as well.
代码在这里
const average = await dbo.collection(symbol).aggregate([{
$match: {
$expr: {
$gte: [
"$dateTime",
{
$subtract: [
"$$NOW",
200 * 60 * 60 * 24 * 1000
]
}
]
}
}
},
{
"$group": {
_id: null,
"Volume": {
"$avg": "$Volume"
}
}
}
]).toArray();
EDIT4
现在唯一的问题是我收到一个空数组 >[] 我收到一个空数组，因为 Date 正在保存为 string 到 MongoDB 但为了使聚合框架工作，我需要将它存储为 <代码>对象.
The only problem now that I receive an empty array > [] I receive an empty array cause the Date is saving as string to MongoDB but to make aggregation framework work I need to store it as object.
在下面的代码中有我创建日期函数的方法，它将它作为 string 保存在 MongoDB 中，我如何更改它以将其保存为 object
In code below there is my way of creating Date function it saves it in MongoDB as string how I can change it to save it as object
const getDateTime = () => {
let today = new Date();
let date = today.getFullYear() + '-' + (today.getMonth() + 1) + '-' + today.getDate();
let time = today.getHours() + ":" + today.getMinutes() + ":" + today.getSeconds();
return date + ' ' + time;
};
const saveToDatebase = async(symbol, BTCdata) => {
try {
const url = 'mongodb://username:password@ipadress/port?dbname?retryWrites=true&w=majority';
let dateTime = getDateTime();
let db = await MongoClient.connect(url, { useUnifiedTopology: true });
const dbo = db.db('Crypto');
const myobj = Object.assign({ Name: symbol, Date: dateTime }, BTCdata[0]);
await dbo.collection(symbol).insertOne(myobj);
const average = await dbo.collection(symbol).aggregate([{
'$match': {
'dateTime': { '$gte': new Date((new Date().getTime() - (7 * 24 * 60 * 60 * 1000))) }
},
},
{
'$group': {
_id: null,
'Volume': { '$avg': '$Volume' }
},
}
]).toArray();
console.log('1 document inserted');
console.log(average);
db.close();
} catch (e) {
console.error(e)
}
};
来自 MongoDB 的文档作为文本:
Document from MongoDB as text:
{
"_id": {
"$oid": "5f158c9d80d84408f6c38d8b"
},
"Name": "ADABTC",
"Date": "2020-7-20 13:22:53",
"Open": 0.0000133,
"High": 0.0000133,
"Low": 0.00001325,
"Close": 0.00001326,
"Volume": 1189734,
"Timespan": 30
}
推荐答案
在对文档进行分组之前，您必须对其进行过滤.应该是这样的:
You have to filter documents before you group them. Should be like this:
db.collection.aggregate([
{
$addFields: {
DateObj: {
$regexFindAll: { input: "$Date", regex: "\\d+" }
}
}
},
{
$set: {
DateObj: {
$dateFromParts: {
year: { $toInt: { $arrayElemAt: ["$DateObj.match", 0] } },
month: { $toInt: { $arrayElemAt: ["$DateObj.match", 1] } },
day: { $toInt: { $arrayElemAt: ["$DateObj.match", 2] } },
hour: { $toInt: { $arrayElemAt: ["$DateObj.match", 3] } },
minute: { $toInt: { $arrayElemAt: ["$DateObj.match", 4] } },
second: { $toInt: { $arrayElemAt: ["$DateObj.match", 5] } },
timezone: "Europe/Zurich"
}
}
}
},
{
$match: {
$expr: {
$gte: ["$DateObj", { $subtract: ["$$NOW", 200 * 60 * 60 * 24 * 1000] }]
}
}
},
{
"$group": {
_id: null,
"Volume": {
"$avg": "$Volume"
}
}
}
])
这篇关于在 MongoDB 聚合和 NodeJS 中计算过去 200 天的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
