按月对记录进行分组并对其进行计数 - Mongoose、nodeJs、mongoDb [英] Group records by month and count them - Mongoose, nodeJs, mongoDb
本文介绍了按月对记录进行分组并对其进行计数 - Mongoose、nodeJs、mongoDb的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我需要在数据库 (mongoose) 中进行查询，并获取某一特定产品(一年内)一年中每个月的销售额.
我是 node 和 mongoDb 的新手，我提供了一个虚拟"解决方案，我在数据库中查询并返回一个产品的所有结果，然后我使用 3 个循环在几个月内拆分结果，但我认为它使用的资源比应有的多，如果填充更多数据，它将使用更多资源，因此我需要帮助进行数据库查询来解决该问题.
这是我的代码部分:
<块引用>假设需要从 17-02-2020 到 17-02-2019 的结果，我知道如果从 1 月 到 12 月 它将进入一个循环但我还有另一部分代码可以控制是否需要 1 年结果例如:01-01-2020 到 31-12-2020 它不会执行代码下面，我说的那个代码只有一个循环，哈哈.
let startTime = performance.now();Sales.find({productId:req.params.productId, "created_at": { "$gte": oneYearFromNow, "$lte": dateNow}}).then(result => {让 newMonthsArray= new Array();让monthsArray = ['一月','二月','三月','四月','五月','六月','七月','八月','九月','十月','十一月','十二月''];让月 = {};for(let i=parseInt(req.params.startDate.substring(5,7))-1; i<12; i++){让年 = parseInt(req.params.startDate.substring(0,4))-1;让月 = parseInt(req.params.startDate.substring(5,7));newMonth =monthsArray[i] + '-' + 年；newMonthsArray.push(newMonth);月数[新月] = 0;}for(let i=0; i<parseInt(req.params.startDate.substring(5,7)); i++){让年 = parseInt(req.params.startDate.substring(0,4));让月 = parseInt(req.params.startDate.substring(5,7));newMonth =monthsArray[i] + '-' + 年；newMonthsArray.push(newMonth);月数[新月] = 0;}for(i=0; i<块引用>
我希望一切都清楚，我想我需要使用聚合，但我不确定如何使用！
示例数据:
<代码>{{"created_at": "2020-04-04T17:02:07.832Z","updated_at": "2020-04-04T17:02:07.832Z","_id": "5e88bdcda3080736ac70f9c1",价格":16800，"productId": "5e88bf90b9e5102ae46b154e",__v":0},{"created_at": "2020-04-04T17:02:07.832Z","updated_at": "2020-04-04T17:02:07.832Z","_id": "5e88bdf9a3080736ac70f9c2",价格":12800，"productId": "5e88bf90b9e5102ae46b154e",__v":0}}预期结果:
解决方案
这是一个返回预期输出的聚合查询.一些示例文档:
<预><代码>[{ 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 },//未选中{ created_at: "2019-03-15T17:02:07.832Z", productId: 1 }//未选中]输入变量和聚合:
let TODAY = "2020-04-06T23:59:59"让 YEAR_BEFORE = "2019-04-07T00:00:00"让 req = { params: { productId: 1 } }constmonthsArray = ['一月'，'二月'，'三月'，'四月'，'五月'，'六月'，'七月'，'八月'，'九月'，'十月'，'十一月'，'十二月'']db.sales.aggregate( [{$匹配:{productId: req.params.productId,created_at: { $gte: YEAR_BEFORE, $lte: TODAY }}},{$组:{_id: { "year_month": { $substrCP: [ "$created_at", 0, 7 ] } },计数:{ $sum:1}}},{$sort: { "_id.year_month": 1 }},{$项目:{_id: 0,计数:1，月_年:{$连接:[{ $arrayElemAt: [monthsArray, { $subtract: [ { $toInt: { $substrCP: [ "$_id.year_month", 5, 2 ] } }, 1 ] } ] },——",{ $substrCP: [ "$_id.year_month", 0, 4 ] }]}}},{$组:{_id:空，数据:{ $push: { k: "$month_year", v: "$count" } }}},{$项目:{数据:{ $arrayToObject: "$data" },_id:0}}])输出:
<代码>{数据" : {2019 年 5 月":1、2020 年 1 月":1、2020 年 2 月":2，2020 年 4 月":1}}这是更新后的聚合.
请注意以下更改:(1) 新常量 FIRST_MONTH 和 LAST_MONTH，(2) 将 monthsArray 变量名称更改为 MONTHS_ARRAY，(3) 添加了 3 个新的流水线阶段.
前两个管道阶段(新)构建一个包含所有月份的模板(涵盖起始日期和截止日期范围).第三个新阶段将模板与从前一个聚合导出的输出数据合并.
const FIRST_MONTH = 1常量 LAST_MONTH = 12const MONTHS_ARRAY = ['一月'，'二月'，'三月'，'四月'，'五月'，'六月'，'七月'，'八月'，'九月'，'十月'，'十一月'，'十二月'']让今天 = "2020-04-06T23:59:59"让 YEAR_BEFORE = "2019-04-07T00:00:00"db.sales.aggregate( [{$匹配:{productId: req.params.productId,created_at: { $gte: YEAR_BEFORE, $lte: TODAY }}},{$组:{_id: { "year_month": { $substrCP: [ "$created_at", 0, 7 ] } },计数:{ $sum:1}}},{$sort: { "_id.year_month": 1 }},{$项目:{_id: 0,计数:1，月_年:{$连接:[{ $arrayElemAt: [monthsArray, { $subtract: [ { $toInt: { $substrCP: [ "$_id.year_month", 5, 2 ] } }, 1 ] } ] },——",{ $substrCP: [ "$_id.year_month", 0, 4 ] }]}}},{$组:{_id:空，数据:{ $push: { k: "$month_year", v: "$count" } }}},{$addFields:{start_year: { $substrCP: [ YEAR_BEFORE, 0, 4 ] },end_year: { $substrCP: [ TODAY, 0, 4 ] },月 1: { $range: [ { $toInt: { $substrCP: [ YEAR_BEFORE, 5, 2 ] } }, { $add: [ LAST_MONTH, 1 ] } ] },月 2: { $range: [ FIRST_MONTH, { $add: [ { $toInt: { $substrCP: [ TODAY, 5, 2 ] } }, 1 ] } ] }}},{$addFields:{模板数据:{$concatArrays: [{ $地图:{输入:$months1"，如:m1"，在: {计数:0，月_年:{$concat: [ { $arrayElemAt: [ MONTHS_ARRAY, { $subtract: [ "$$m1", 1 ] } ] }, "-", "$start_year" ]}}} },{ $地图:{输入:$months2"，如:m2"，在: {计数:0，月_年:{$concat: [ { $arrayElemAt: [ MONTHS_ARRAY, { $subtract: [ "$$m2", 1 ] } ] }, "-", "$end_year" ]}}} }]}}},{$addFields:{数据: {$地图:{输入:$template_data"，如:t"，在: {k: "$$t.month_year",v:{$减少:{输入:$data"，初始值:0，在: {$cond: [ { $eq: [ "$$t.month_year", "$$this.k"] },{ $add: [ "$$this.v", "$$value" ] },{ $add: [ 0, "$$value" ] }]}}}}}}}},{$项目:{数据:{ $arrayToObject: "$data" },_id:0}}])输出:
<代码>{数据" : {2019 年 4 月":0，2019 年 5 月":1、2019 年 6 月":0，2019 年 7 月":0，2019 年 8 月":0，2019 年 9 月":0，2019 年 10 月":0，2019 年 11 月":0，2019 年 12 月":0，2020 年 1 月":1、2020 年 2 月":2，2020 年 3 月":0，2020 年 4 月":1}}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).
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:
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
]
The input variables and the aggregation:
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
}
}
] )
The output:
{
"data" : {
"May-2019" : 1,
"January-2020" : 1,
"February-2020" : 2,
"April-2020" : 1
}
}
Here is the updated aggregation.
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屋！
查看全文
