填写记录中的缺失日期 [英] Fill missing dates in records
问题描述
我有一个ProductViews
的集合.
产品视图
{
productId: '5b8c0f3204a10228b00a1745,
createdAt: '2018-09-07T17:18:40.759Z',
}
我有一个查询,用于获取特定产品的每日视图.
And I have a query for fetching the daily views for a specific product.
查询
ProductView.aggregate([
{ $match: { productId } },
{ $project: { day: { $substr: ["$createdAt", 0, 10] } } },
{
$group: {
_id: "$day",
count: { $sum: 1 },
time: { $avg: "$createdAt" },
}
},
{ $sort: { _id: 1 } },
{
$project: {
date: '$_id',
views: '$count',
},
},
]).exec((err, result) => ...)
当前结果
[
{ date: '2018-09-01', views: 1 },
{ date: '2018-09-02', views: 3 },
{ date: '2018-09-04', views: 2 },
{ date: '2018-09-05', views: 5 },
// ...
]
问题
问题在于,这种聚合在具有0
视图的天内不会返回{ date: '2018-09-03', views: 0 }
.这将导致数据显示不正确:
The issue is, that this aggregation does not return { date: '2018-09-03', views: 0 }
for days with 0
views. This results in incorrect displaying of the data:
结果应类似于
[
{ date: '2018-09-01', views: 1 },
{ date: '2018-09-02', views: 3 },
{ date: '2018-09-03', views: 0 },
{ date: '2018-09-04', views: 2 },
{ date: '2018-09-05', views: 5 },
// ...
]
P.S .:最好输入开始日期和结束日期以根据此范围输出结果
P.S.: It would be perfect to pass in the start and end dates to output results based on this range
推荐答案
您需要几个附加步骤来返回默认值.首先,您需要使用$group
,将_id
设置为null
,以将所有结果收集到一个文档中.然后,您可以将 $ map 使用几天作为输入.在该$map
内部,您可以使用 $ indexOfArray 来查找该日期是否存在于您当前的结果集中.如果是(index != -1
),则可以返回该值,否则需要返回默认子文档,其中views
设置为0
.然后,您可以使用 $ unwind 返回列表文档和 $ replaceRoot 来促进嵌套stats
达到最高水平.
You need few additional stages to return default values. First of all you need to use $group
with _id
set to null
to collect all results in one document. Then you can use $map with an array of days as an input. Inside that $map
you can use $indexOfArray to find if that date exists in your current result set. If yes (index != -1
) then you can return that value, otherwise you need to return default subdocument with views
set to 0
. Then you can use $unwind to get back a list of documents and $replaceRoot to promote nested stats
to a top level.
ProductView.aggregate([
{ $match: { productId: '5b8c0f3204a10228b00a1745' } },
{ $project: { day: { $substr: ["$createdAt", 0, 10] } } },
{
$group: {
_id: "$day",
count: { $sum: 1 },
time: { $avg: "$createdAt" },
}
},
{ $sort: { _id: 1 } },
{
$project: {
date: '$_id',
views: '$count',
},
},
{
$group: {
_id: null,
stats: { $push: "$$ROOT" }
}
},
{
$project: {
stats: {
$map: {
input: [ "2018-09-01", "2018-09-02", "2018-09-03", "2018-09-04", "2018-09-05" ],
as: "date",
in: {
$let: {
vars: { dateIndex: { "$indexOfArray": [ "$stats._id", "$$date" ] } },
in: {
$cond: {
if: { $ne: [ "$$dateIndex", -1 ] },
then: { $arrayElemAt: [ "$stats", "$$dateIndex" ] },
else: { _id: "$$date", date: "$$date", views: 0 }
}
}
}
}
}
}
}
},
{
$unwind: "$stats"
},
{
$replaceRoot: {
newRoot: "$stats"
}
}
]).exec((err, result) => ...)
您可以使用简单循环在应用程序逻辑中生成静态日期列表.我相信在MongoDB中也是可行的(使用 $ range ),但可能会使此聚合管道复杂化.让我知道您是否满意,或者想尝试在MongoDB中生成该日期数组.
You can generate a static list of dates in your application logic using simple loop. I believe that's possible in MongoDB as well (using $range) but it might complicate this aggregation pipeline. Let me know if you're fine with that or you want to try to generate that array of dates in MongoDB.
这篇关于填写记录中的缺失日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!