按月对记录进行分组并计数-Mongoose，nodeJs，mongoDb [英] Group records by month and count them - Mongoose, nodeJs, mongoDb
问题描述
我需要在数据库(猫鼬)中进行查询，并获取一年中某个特定产品(在一年之内)每年每个月的销售数量.
I need to query in database (mongoose) and get back the number of sales made each month of the year for one particular product (within one year period).
我是node和mongoDb的新手，我提供了一个虚拟"解决方案，其中我在数据库中查询并取回一种产品的所有结果，然后我使用3个循环将结果分成数月，但我认为它使用了比应有的资源更多的资源，并且如果它充满了更多的数据，它将使用甚至更多的资源，因此我需要帮助进行数据库查询来解决该问题.
I am new to node and mongoDb and I have come with a 'dummy' solution where I query in database and get back all the results for one product and than I use 3 loops to split the results in months but I think that it uses more resources than it should and it will use even more if it gets filled with more data, so I need help making a database query to solve that problem.
这是我代码中执行此操作的部分:
Here is the part of my code that does that:
假设需要从 17-02-2020 到 17-02-2019 的结果，我知道如果它是从 一月 到 十二月 ，它将一无所获但是我还有另一部分代码来控制是否需要1年结果: 01-01-2020 到 31-12-2020 不会执行代码在下面，我正在谈论的代码只有一个循环哈哈.
Assuming that the results are required from 17-02-2020 to 17-02-2019, I know that if it is from January to December it will go into one loop for nothing but I have another part of code which controls if it requires 1 year result ex: 01-01-2020 to 31-12-2020 it will not execute the code below, that code I am talking about has only one loop lol.
let startTime = performance.now();
Sales.find({productId:req.params.productId, "created_at": { "$gte": oneYearFromNow, "$lte": dateNow}}).then(result => {
let newMonthsArray= new Array();
let monthsArray = ['January','February','March','April','May','June','July','August','September','October', 'November','December'];
let months = {};
for(let i=parseInt(req.params.startDate.substring(5,7))-1; i<12; i++){
let year = parseInt(req.params.startDate.substring(0,4))-1;
let month = parseInt(req.params.startDate.substring(5,7));
newMonth = monthsArray[i] + '-' + year;
newMonthsArray.push(newMonth);
months[newMonth] = 0;
}
for(let i=0; i<parseInt(req.params.startDate.substring(5,7)); i++){
let year = parseInt(req.params.startDate.substring(0,4));
let month = parseInt(req.params.startDate.substring(5,7));
newMonth = monthsArray[i] + '-' + year;
newMonthsArray.push(newMonth);
months[newMonth] = 0;
}
for(i=0; i<result.length; i++){
let getDate = result[i].created_at.toISOString();
let year = getDate.substring(0,4);
let month = parseInt(getDate.substring(5,7));
let monthName = monthsArray[month-1];
let date = monthName + '-' + year;
let count = Number(months[date]) + 1;
months[date] = count;
}
let endTime = performance.now();
res.status(200).send({Data: months, 'Execution time': endTime - startTime + ' mls'});
});
我希望一切都清楚了，我想我需要使用聚合，但我不确定如何使用！
I hope everything is clear, I think I need to use aggregation but I'm not sure how!
样本数据:
Sample data:
{
{
"created_at": "2020-04-04T17:02:07.832Z",
"updated_at": "2020-04-04T17:02:07.832Z",
"_id": "5e88bdcda3080736ac70f9c1",
"price": 16800,
"productId": "5e88bf90b9e5102ae46b154e",
"__v": 0
},
{
"created_at": "2020-04-04T17:02:07.832Z",
"updated_at": "2020-04-04T17:02:07.832Z",
"_id": "5e88bdf9a3080736ac70f9c2",
"price": 12800,
"productId": "5e88bf90b9e5102ae46b154e",
"__v": 0
}
}
所需结果:
Desired result:
推荐答案
这里是一个聚合查询，它返回预期的输出.一些样本文件:
Here is an aggregation query which returns the expected output. Some sample documents:
[
{ created_at: "2020-04-04T17:02:07.832Z", productId: 1 },
{ created_at: "2020-02-01T17:02:07.832Z", productId: 1 },
{ created_at: "2020-02-19T17:02:07.832Z", productId: 1 },
{ created_at: "2019-05-22T17:02:07.832Z", productId: 1 },
{ created_at: "2020-01-15T17:02:07.832Z", productId: 1 },
{ created_at: "2020-01-30T17:02:07.832Z", productId: 2 }, // not selected
{ created_at: "2019-03-15T17:02:07.832Z", productId: 1 } // not selected
]
输入变量和聚合:
let TODAY = "2020-04-06T23:59:59"
let YEAR_BEFORE = "2019-04-07T00:00:00"
let req = { params: { productId: 1 } }
const monthsArray = [ 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December' ]
db.sales.aggregate( [
{
$match: {
productId: req.params.productId,
created_at: { $gte: YEAR_BEFORE, $lte: TODAY }
}
},
{
$group: {
_id: { "year_month": { $substrCP: [ "$created_at", 0, 7 ] } },
count: { $sum: 1 }
}
},
{
$sort: { "_id.year_month": 1 }
},
{
$project: {
_id: 0,
count: 1,
month_year: {
$concat: [
{ $arrayElemAt: [ monthsArray, { $subtract: [ { $toInt: { $substrCP: [ "$_id.year_month", 5, 2 ] } }, 1 ] } ] },
"-",
{ $substrCP: [ "$_id.year_month", 0, 4 ] }
]
}
}
},
{
$group: {
_id: null,
data: { $push: { k: "$month_year", v: "$count" } }
}
},
{
$project: {
data: { $arrayToObject: "$data" },
_id: 0
}
}
] )
输出:
{
"data" : {
"May-2019" : 1,
"January-2020" : 1,
"February-2020" : 2,
"April-2020" : 1
}
}
这是更新的汇总.
请注意以下更改:(1)新的常量FIRST_MONTH和LAST_MONTH，(2)将 monthsArray 变量名更改为MONTHS_ARRAY，(3)添加了3个新的管道阶段.
Note the following changes: (1) new constants FIRST_MONTH and LAST_MONTH, (2) changed the monthsArray variable name to MONTHS_ARRAY, (3) added 3 new pipeline stages.
前两个管道阶段(新)将构建一个包含所有月份的模板(覆盖起始日期和输入日期范围).第三个新阶段将模板与从先前聚合中获得的输出数据合并.
The first two pipeline stages (new) build a template with all the months (covering the from and to input date range). The third new stage merges the template with the output data derived from the previous aggregation.
const FIRST_MONTH = 1
const LAST_MONTH = 12
const MONTHS_ARRAY = [ 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December' ]
let TODAY = "2020-04-06T23:59:59"
let YEAR_BEFORE = "2019-04-07T00:00:00"
db.sales.aggregate( [
{
$match: {
productId: req.params.productId,
created_at: { $gte: YEAR_BEFORE, $lte: TODAY }
}
},
{
$group: {
_id: { "year_month": { $substrCP: [ "$created_at", 0, 7 ] } },
count: { $sum: 1 }
}
},
{
$sort: { "_id.year_month": 1 }
},
{
$project: {
_id: 0,
count: 1,
month_year: {
$concat: [
{ $arrayElemAt: [ monthsArray, { $subtract: [ { $toInt: { $substrCP: [ "$_id.year_month", 5, 2 ] } }, 1 ] } ] },
"-",
{ $substrCP: [ "$_id.year_month", 0, 4 ] }
]
}
}
},
{
$group: {
_id: null,
data: { $push: { k: "$month_year", v: "$count" } }
}
},
{
$addFields: {
start_year: { $substrCP: [ YEAR_BEFORE, 0, 4 ] },
end_year: { $substrCP: [ TODAY, 0, 4 ] },
months1: { $range: [ { $toInt: { $substrCP: [ YEAR_BEFORE, 5, 2 ] } }, { $add: [ LAST_MONTH, 1 ] } ] },
months2: { $range: [ FIRST_MONTH, { $add: [ { $toInt: { $substrCP: [ TODAY, 5, 2 ] } }, 1 ] } ] }
}
},
{
$addFields: {
template_data: {
$concatArrays: [
{ $map: {
input: "$months1", as: "m1",
in: {
count: 0,
month_year: {
$concat: [ { $arrayElemAt: [ MONTHS_ARRAY, { $subtract: [ "$$m1", 1 ] } ] }, "-", "$start_year" ]
}
}
} },
{ $map: {
input: "$months2", as: "m2",
in: {
count: 0,
month_year: {
$concat: [ { $arrayElemAt: [ MONTHS_ARRAY, { $subtract: [ "$$m2", 1 ] } ] }, "-", "$end_year" ]
}
}
} }
]
}
}
},
{
$addFields: {
data: {
$map: {
input: "$template_data", as: "t",
in: {
k: "$$t.month_year",
v: {
$reduce: {
input: "$data", initialValue: 0,
in: {
$cond: [ { $eq: [ "$$t.month_year", "$$this.k"] },
{ $add: [ "$$this.v", "$$value" ] },
{ $add: [ 0, "$$value" ] }
]
}
}
}
}
}
}
}
},
{
$project: {
data: { $arrayToObject: "$data" },
_id: 0
}
}
] )
输出:
The output:
{
"data" : {
"April-2019" : 0,
"May-2019" : 1,
"June-2019" : 0,
"July-2019" : 0,
"August-2019" : 0,
"September-2019" : 0,
"October-2019" : 0,
"November-2019" : 0,
"December-2019" : 0,
"January-2020" : 1,
"February-2020" : 2,
"March-2020" : 0,
"April-2020" : 1
}
}
这篇关于按月对记录进行分组并计数-Mongoose，nodeJs，mongoDb的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
