填写记录中缺失的日期 [英] Fill missing dates in records

问题描述

我有一个 ProductViews 的集合:

<代码>{产品 ID:5b8c0f3204a10228b00a1745"，createdAt:2018-09-07T17:18:40.759Z"}

我有一个查询来获取特定产品的每日浏览量:

ProductView.aggregate([{ $match: { productId } },{ $project: { day: { $substr: ["$createdAt", 0, 10] } } },{$组:{_id: "$day",计数: { $sum: 1 },时间:{ $avg: "$createdAt";},}},{ $sort: { _id: 1 } },{$项目:{日期:'$_id'，意见:'$count'，},},]).exec((错误，结果) => ...)

目前给出:

<预><代码>[{ 日期:'2018-09-01'，观看次数:1}，{ 日期:'2018-09-02'，观看次数:3 }，{ 日期:'2018-09-04'，观看次数:2 }，{ 日期:'2018-09-05'，观看次数:5 }，//...]

问题:

问题是，对于具有 0 视图的天数，此聚合不会返回 { date: '2018-09-03', views: 0 }.这会导致数据显示不正确:[![enter image description here][1]][1]

结果应如下所示:

<预><代码>[{ 日期:'2018-09-01'，观看次数:1}，{ 日期:'2018-09-02'，观看次数:3 }，{ 日期:'2018-09-03'，观看次数:0 }，<={ 日期:'2018-09-04'，观看次数:2 }，{ 日期:'2018-09-05'，观看次数:5 }，//...]

P.S.:最好是传入开始日期和结束日期来输出基于这个范围的结果[1]:https://i.stack.imgur.com/uHPBs.png

解决方案

您需要几个额外的阶段来返回默认值.首先，您需要使用 $group 并将 _id 设置为 null 以将所有结果收集到一个文档中.然后你可以使用 $map 和一组天数作为输入.在那个 $map 里面你可以使用 $indexOfArray 以查找当前结果集中是否存在该日期.如果是 (index != -1) 那么你可以返回那个值，否则你需要返回默认的子文档，views 设置为 0.然后你可以使用 $unwind 来获取列表文档和 $replaceRoot 以促进嵌套 统计数据 到顶级.

ProductView.aggregate([{ $match: { productId: '5b8c0f3204a10228b00a1745' } },{ $project: { day: { $substr: ["$createdAt", 0, 10] } } },{$组:{_id: "$day",计数: { $sum: 1 },时间:{ $avg: "$createdAt" },}},{ $sort: { _id: 1 } },{$项目:{日期:'$_id'，意见:'$count'，},},{$组:{_id:空，统计信息:{ $push: "$$ROOT" }}},{$项目:{统计:{$地图:{输入:[ "2018-09-01", "2018-09-02", "2018-09-03", "2018-09-04", "2018-09-05" ],如:日期"，在: {$let: {vars: { dateIndex: { "$indexOfArray": [ "$stats._id", "$$date" ] } },在: {$cond: {如果:{ $ne: [ "$$dateIndex", -1 ] },然后:{ $arrayElemAt: [ "$stats", "$$dateIndex" ] },其他:{_id:$$date"，日期:$$date"，视图:0}}}}}}}}},{$unwind: "$stats"},{$替换根:{newRoot: "$stats"}}]).exec((错误，结果) => ...)

您可以使用简单的循环在应用程序逻辑中生成静态日期列表.我相信这在 MongoDB 中也是可能的(使用 $range) 但它可能会使这个聚合管道复杂化.如果您对此感到满意，或者您想尝试在 MongoDB 中生成该日期数组，请告诉我.

I have a collection of 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) => ...)

which currently gives:

[
    { date: '2018-09-01', views: 1 },
    { date: '2018-09-02', views: 3 },
    { date: '2018-09-04', views: 2 },
    { date: '2018-09-05', views: 5 },
    // ...
]

Issue:

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: [![enter image description here][1]][1]

Results should look like:

[
    { 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.: It would be perfect to pass in the start and end dates to output results based on this range [1]: https://i.stack.imgur.com/uHPBs.png

解决方案

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) => ...)

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屋！