景区景区评论分组查询

作者: 陈文瑜 | 来源:发表于2019-10-04 09:15 被阅读0次

景区景区评论分组查询
2020-04-27 终南独秀，风吹麦浪
贵州行之黄果树瀑布(一)：陡坡塘和天星桥景区
涠洲岛之二
景区评论文本处理与评价分值预测 - 竞赛总结
跟着高速游广东（第四天）
景区
景区
慢游南京（二）
泸州老窖博物馆

景区、评论操作

查询语句

db.spot.aggregate(
    {
        $lookup:{
            from:'spot_comment_1',
            localField:'ota_spot_id',
            foreignField:'ota_spot_id',
            as: 'spot_comment'
        }
    },
    {
    $unwind: {
        path: "$spot_comment",
        preserveNullAndEmptyArrays: true
    }
    },
    {
        $project:{
                ota_spot_id : '$ota_spot_id',
                spot_name : '$spot_name',
                create_at: '$spot_comment.create_at',
                ota_spot_1515791 : {$cond : [{$eq : ['$spot_comment.ota_spot_id',1515791]},1,0]},
                ota_spot_30067 : {$cond : [{$eq : ['$spot_comment.ota_spot_id',30067]},1,0]},
                ota_spot_339 : {$cond : [{$eq : ['$spot_comment.ota_spot_id',339]},1,0]},
                ota_spot_5427075 : {$cond : [{$eq : ['$spot_comment.ota_spot_id',5427075]},1,0]},
                ota_spot_127339 : {$cond : [{$eq : ['$spot_comment.ota_spot_id',127339]},1,0]},
                ota_spot_62931 : {$cond : [{$eq : ['$spot_comment.ota_spot_id',62931]},1,0]},
                ota_spot_100025 : {$cond : [{$eq : ['$spot_comment.ota_spot_id',100025]},1,0]},
                ota_spot_103113 : {$cond : [{$eq : ['$spot_comment.ota_spot_id',103113]},1,0]}
        }
    },
    {
        $match:{
                $and:[
                    {$or:[
                        {'ota_spot_id':{$eq:103113}},
                        {'ota_spot_id':{$eq:100025}},
                        {'ota_spot_id':{$eq:5427075}},
                        {'ota_spot_id':{$eq:339}}
                    ]},
                    {'create_at':{$gte:'2017-12-04'}}
                ]
        }
    },
    {
        $group:{
            _id:{ota_spot_id:'$ota_spot_id','spot_name':'$spot_name'},
            ota_spot_1515791_count : {$sum : '$ota_spot_1515791'},
            ota_spot_30067_count : {$sum : '$ota_spot_30067'},
            ota_spot_339_count : {$sum : '$ota_spot_339'},
            ota_spot_5427075_count : {$sum : '$ota_spot_5427075'},
            ota_spot_127339_count : {$sum : '$ota_spot_127339'},
            ota_spot_62931_count : {$sum : '$ota_spot_62931'},
            ota_spot_100025_count : {$sum : '$ota_spot_100025'},
            ota_spot_103113_count : {$sum : '$ota_spot_103113'}
        }
    }
)

返回结果

// 1
{
    "_id": {
        "ota_spot_id": NumberInt("339"),
        "spot_name": "石燕湖生态旅游公园"
    },
    "ota_spot_1515791_count": 0,
    "ota_spot_30067_count": 0,
    "ota_spot_339_count": 75,
    "ota_spot_5427075_count": 0,
    "ota_spot_127339_count": 0,
    "ota_spot_62931_count": 0,
    "ota_spot_100025_count": 0,
    "ota_spot_103113_count": 0
}

// 2
{
    "_id": {
        "ota_spot_id": NumberInt("5427075"),
        "spot_name": "石牛寨国家地质公园"
    },
    "ota_spot_1515791_count": 0,
    "ota_spot_30067_count": 0,
    "ota_spot_339_count": 0,
    "ota_spot_5427075_count": 49,
    "ota_spot_127339_count": 0,
    "ota_spot_62931_count": 0,
    "ota_spot_100025_count": 0,
    "ota_spot_103113_count": 0
}

// 3
{
    "_id": {
        "ota_spot_id": NumberInt("100025"),
        "spot_name": "长沙石燕湖"
    },
    "ota_spot_1515791_count": 0,
    "ota_spot_30067_count": 0,
    "ota_spot_339_count": 0,
    "ota_spot_5427075_count": 0,
    "ota_spot_127339_count": 0,
    "ota_spot_62931_count": 0,
    "ota_spot_100025_count": 219,
    "ota_spot_103113_count": 0
}

// 4
{
    "_id": {
        "ota_spot_id": NumberInt("103113"),
        "spot_name": "玻璃桥景区石牛寨"
    },
    "ota_spot_1515791_count": 0,
    "ota_spot_30067_count": 0,
    "ota_spot_339_count": 0,
    "ota_spot_5427075_count": 0,
    "ota_spot_127339_count": 0,
    "ota_spot_62931_count": 0,
    "ota_spot_100025_count": 0,
    "ota_spot_103113_count": 8
}