• 表单生成器(Form Builder)之mongodb表单数据查询——统计查询求和


      上一篇笔记仅是记录了一下简单的关联查询,根据笔记中的场景:将某一车辆关联的耗损记录全部放在了一个字段当中。不知道现在中有没有这种场景,我们的应用中没有类似的场景,可能我们更关注的是某车辆的总耗损金额和总营收金额、总里程数等,因此这篇笔记记录一下怎么得到这些数据。要想获取这些记录我们首先想到的就是先按照车辆ID分组之后再对指定字段求和,可能是先入为主的原因(有了上一篇笔记),我没有按照传统的思路走下去,而是想着看看能不能在上一篇笔记的基础之上得到想要的结果。

      我们沿着这条路在分析一下,车辆相关的耗损和营收都可以放到一个字段中。也就是说我们不用分组了,车辆相关的耗损和营收记录都冗余到了车辆信息记录中,那么接下来就是对数组中的某一字段或者多个进行求和了……查了半天并没有找到类似$sum这样的聚合管道可以对数组中的字段求和,不过得益于mongodb管道的强大,用另外一个管道间接实现了这个功能。

    db.getCollection('FormInstace').aggregate([
        {
            $match: {
                "_id": { $in: ["1","2","3"] },
                "FormItems.key": { $ne: null }
            }
        },
        {
            $addFields: {
                FormValueObj: {
                    $arrayToObject: {
                        $map: {
                            input: "$FormItems",
                            as: "field",
                            in: [ "$$field.key", "$$field.value" ]
                        }
                    }
                }
            }
        },
        {
          $replaceRoot: { newRoot: { $mergeObjects: [ "$FormValueObj", "$$ROOT" ] } }
        },
        {
            $project: {
                FormItems:0,
                FormValueObj:0
            }
        },
        {   
            $lookup:{
                from:"FormInstace",
                let: { tempMainRelationKey: "$_id" },
                pipeline:[
                    {
                        $match: { 
                            FormId: "507048044944692000",
                            "FormItems.key": { $ne: null }                        
                        }
                    },
                    {
                        $addFields: {
                            FormValueObj: {
                                $arrayToObject: {
                                    $map: {
                                        input: "$FormItems",
                                        as: "field",
                                        in: [ "$$field.key", "$$field.value" ]
                                    }
                                }
                            }
                        }
                    },
                    {
                      $replaceRoot: { newRoot: { $mergeObjects: [ "$FormValueObj", "$$ROOT" ] } }
                    },
                    {
                        $project: {
                            FormItems:0,
                            FormValueObj:0
                        }
                    },
                    { $match:
                         { $expr:
                                { $eq: [ "$1572493552001.id","$$tempMainRelationKey" ] }
                         }
                    },
                ],
                as:'carWastage'
            }
        },
        {   
            $lookup:{
                from:"FormInstace",
                let: { tempMainRelationKey: "$_id" },
                pipeline:[
                    {
                        $match: { 
                            FormId: "507048044944693000",
                            "FormItems.key": { $ne: null }                        
                        }
                    },
                    {
                        $addFields: {
                            FormValueObj: {
                                $arrayToObject: {
                                    $map: {
                                        input: "$FormItems",
                                        as: "field",
                                        in: [ "$$field.key", "$$field.value" ]
                                    }
                                }
                            }
                        }
                    },
                    {
                      $replaceRoot: { newRoot: { $mergeObjects: [ "$FormValueObj", "$$ROOT" ] } }
                    },
                    {
                        $project: {
                            FormItems:0,
                            FormValueObj:0
                        }
                    },
                    { $match:
                         { $expr:
                                { $eq: [ "$1572493553001.id","$$tempMainRelationKey" ] }
                         }
                    },
                ],
                as:'carRevenue'
            }
        },
        {
            $addFields: {
                carWastageStatistics: {
                   $reduce: {
                      input: "$carWastage",
                      initialValue: {wastageMoney:0},
                      in: {
                          wastageMoney:{ $add : ["$$value.wastageMoney", "$$this.1572493552005"] }
                      }
                   }
                }
            }
        },
        {
            $addFields: {
                carRevenueStatistics: {
                   $reduce: {
                      input: "$carRevenue",
                      initialValue: {revenueMoney:0,mileage:0},
                      in: { 
                        revenueMoney:{$add : ["$$value.revenueMoney", "$$this.1572493553005"]},
                        mileage:{$add : ["$$value.mileage", "$$this.1572493553006"]}
                      }
                   }
                }
            }
        }
    ]);

      这次统计查询就查出了某些车辆的总耗损金额和总营收金额、总里程数;只不过将结果放到对象中了(方便统计一个表中的多个字段),但是现在看不到具体的结果,将代码在修改一下,看一下结果

    db.getCollection('FormInstace').aggregate([
        {
            $match: {
                "_id": { $in: ["1","2","3"] },
                "FormItems.key": { $ne: null }
            }
        },
        {
            $addFields: {
                FormValueObj: {
                    $arrayToObject: {
                        $map: {
                            input: "$FormItems",
                            as: "field",
                            in: [ "$$field.key", "$$field.value" ]
                        }
                    }
                }
            }
        },
        {
          $replaceRoot: { newRoot: { $mergeObjects: [ "$FormValueObj", "$$ROOT" ] } }
        },
        {
            $project: {
                FormItems:0,
                FormValueObj:0
            }
        },
        {   
            $lookup:{
                from:"FormInstace",
                let: { tempMainRelationKey: "$_id" },
                pipeline:[
                    {
                        $match: { 
                            FormId: "507048044944692000",
                            "FormItems.key": { $ne: null }                        
                        }
                    },
                    {
                        $addFields: {
                            FormValueObj: {
                                $arrayToObject: {
                                    $map: {
                                        input: "$FormItems",
                                        as: "field",
                                        in: [ "$$field.key", "$$field.value" ]
                                    }
                                }
                            }
                        }
                    },
                    {
                      $replaceRoot: { newRoot: { $mergeObjects: [ "$FormValueObj", "$$ROOT" ] } }
                    },
                    {
                        $project: {
                            FormItems:0,
                            FormValueObj:0
                        }
                    },
                    { $match:
                         { $expr:
                                { $eq: [ "$1572493552001.id","$$tempMainRelationKey" ] }
                         }
                    },
                ],
                as:'carWastage'
            }
        },
        {   
            $lookup:{
                from:"FormInstace",
                let: { tempMainRelationKey: "$_id" },
                pipeline:[
                    {
                        $match: { 
                            FormId: "507048044944693000",
                            "FormItems.key": { $ne: null }                        
                        }
                    },
                    {
                        $addFields: {
                            FormValueObj: {
                                $arrayToObject: {
                                    $map: {
                                        input: "$FormItems",
                                        as: "field",
                                        in: [ "$$field.key", "$$field.value" ]
                                    }
                                }
                            }
                        }
                    },
                    {
                      $replaceRoot: { newRoot: { $mergeObjects: [ "$FormValueObj", "$$ROOT" ] } }
                    },
                    {
                        $project: {
                            FormItems:0,
                            FormValueObj:0
                        }
                    },
                    { $match:
                         { $expr:
                                { $eq: [ "$1572493553001.id","$$tempMainRelationKey" ] }
                         }
                    },
                ],
                as:'carRevenue'
            }
        },
        {
            $addFields: {
                carWastage_Money: {
                   $reduce: {
                      input: "$carWastage",
                      initialValue: 0,
                      in: { $add : ["$$value", "$$this.1572493552005"] }
                   }
                }
            }
        },
        {
            $addFields: {
                carRevenue_Money: {
                   $reduce: {
                      input: "$carRevenue",
                      initialValue: 0,
                      in: { $add : ["$$value", "$$this.1572493553005"] }
                   }
                }
            }
        },
        {
            $addFields: {
                carRevenue_Mileage: {
                   $reduce: {
                      input: "$carRevenue",
                      initialValue: 0,
                      in: { $add : ["$$value", "$$this.1572493553006"] }
                   }
                }
            }
        }
    ]);

      这样就一目了然了。统计结果对不对呢?我们用传统的分组之后再求和在查询一下,将结果做一下对比(顺便记录一下,分组求和)

    db.getCollection('FormInstace').aggregate([
        {
            $match: {
                "FormId":"507048044944692000",
                "FormItems.key": { $ne: null }
            }
        },
        {
            $addFields: {
                FormValueObj: {
                    $arrayToObject: {
                        $map: {
                            input: "$FormItems",
                            as: "field",
                            in: [ "$$field.key", "$$field.value" ]
                        }
                    }
                }
            }
        },
        {
            $replaceRoot: { newRoot: { $mergeObjects: [ "$FormValueObj", "$$ROOT" ] } }
        },
        {
            $match: {
                "1572493552001.id": { $in:["1","2","3"]}
            }
        },
        {
            $project: {
                FormItems:0,
                FormValueObj:0
            }
        },
        {
            $group:{
               _id: "$1572493552001.id",
               wastageMoney: { $sum: "$1572493552005"}
            }
        },
        { $sort : { _id: 1 } }
    ]);
    统计指定车辆的耗损总额
    db.getCollection('FormInstace').aggregate([
        {
            $match: {
                "FormId":"507048044944693000",
                "FormItems.key": { $ne: null }
            }
        },
        {
            $addFields: {
                FormValueObj: {
                    $arrayToObject: {
                        $map: {
                            input: "$FormItems",
                            as: "field",
                            in: [ "$$field.key", "$$field.value" ]
                        }
                    }
                }
            }
        },
        {
            $replaceRoot: { newRoot: { $mergeObjects: [ "$FormValueObj", "$$ROOT" ] } }
        },
        {
            $match: {
                "1572493553001.id": { $in:["1","2","3"]}
            }
        },
        {
            $project: {
                FormItems:0,
                FormValueObj:0
            }
        },
        {
            $group:{
               _id: "$1572493553001.id",
               wastageMoney: { $sum: "$1572493553005"},
               mileage: { $sum: "$1572493553006"}
            }
        },
        { $sort : { _id: 1 } }
    ]);
    统计指定车辆的营收总额和里程数

      对比两次查询结果,两次不同的统计方式的结果相同。这样就放心了。好了就到这里了。

      ********************以下是修改补充部分********************

      打脸了,好疼,┭┮﹏┭┮……曾记得以前使用$sum对数组中的某一属性求和时没有得到预期的结果,这篇笔记写完之后,打算在按照常规思路($unwind、$group、$sum等)在写一种查询方式,但是但是但是测试的时候发现$sum好使了,写放一下代码

    db.getCollection('FormInstace').aggregate([
        {
            $match: {
                "_id": { $in: ["1","2","3"] },
                "FormItems.key": { $ne: null }
            }
        },
        {
            $addFields: {
                FormValueObj: {
                    $arrayToObject: {
                        $map: {
                            input: "$FormItems",
                            as: "field",
                            in: [ "$$field.key", "$$field.value" ]
                        }
                    }
                }
            }
        },
        {
          $replaceRoot: { newRoot: { $mergeObjects: [ "$FormValueObj", "$$ROOT" ] } }
        },
        {
            $project: {
                FormItems:0,
                FormValueObj:0
            }
        },
        {   
            $lookup:{
                from:"FormInstace",
                let: { tempMainRelationKey: "$_id" },
                pipeline:[
                    {
                        $match: { 
                            FormId: "507048044944692000",
                            "FormItems.key": { $ne: null }                        
                        }
                    },
                    {
                        $addFields: {
                            FormValueObj: {
                                $arrayToObject: {
                                    $map: {
                                        input: "$FormItems",
                                        as: "field",
                                        in: [ "$$field.key", "$$field.value" ]
                                    }
                                }
                            }
                        }
                    },
                    {
                      $replaceRoot: { newRoot: { $mergeObjects: [ "$FormValueObj", "$$ROOT" ] } }
                    },
                    {
                        $project: {
                            FormItems:0,
                            FormValueObj:0
                        }
                    },
                    { $match:
                         { $expr:
                                { $eq: [ "$1572493552001.id","$$tempMainRelationKey" ] }
                         }
                    },
                ],
                as:'carWastage'
            }
        },
        {   
            $lookup:{
                from:"FormInstace",
                let: { tempMainRelationKey: "$_id" },
                pipeline:[
                    {
                        $match: { 
                            FormId: "507048044944693000",
                            "FormItems.key": { $ne: null }                        
                        }
                    },
                    {
                        $addFields: {
                            FormValueObj: {
                                $arrayToObject: {
                                    $map: {
                                        input: "$FormItems",
                                        as: "field",
                                        in: [ "$$field.key", "$$field.value" ]
                                    }
                                }
                            }
                        }
                    },
                    {
                      $replaceRoot: { newRoot: { $mergeObjects: [ "$FormValueObj", "$$ROOT" ] } }
                    },
                    {
                        $project: {
                            FormItems:0,
                            FormValueObj:0
                        }
                    },
                    { $match:
                         { $expr:
                                { $eq: [ "$1572493553001.id","$$tempMainRelationKey" ] }
                         }
                    },
                ],
                as:'carRevenue'
            }
        },
        {
            $addFields: {
                "carWastageStatistics.wastageMoney": { $sum: "$carWastage.1572493552005"},
                "carRevenueStatistics.revenueMoney": { $sum: "$carRevenue.1572493553005"},
                "carRevenueStatistics.mileage": { $sum: "$carRevenue.1572493553006"}
            }
        }
    ]);
    在文中第一个查询语句基础上修改
    db.getCollection('FormInstace').aggregate([
        {
            $match: {
                "_id": { $in: ["1","2","3"] },
                "FormItems.key": { $ne: null }
            }
        },
        {
            $addFields: {
                FormValueObj: {
                    $arrayToObject: {
                        $map: {
                            input: "$FormItems",
                            as: "field",
                            in: [ "$$field.key", "$$field.value" ]
                        }
                    }
                }
            }
        },
        {
          $replaceRoot: { newRoot: { $mergeObjects: [ "$FormValueObj", "$$ROOT" ] } }
        },
        {
            $project: {
                FormItems:0,
                FormValueObj:0
            }
        },
        {   
            $lookup:{
                from:"FormInstace",
                let: { tempMainRelationKey: "$_id" },
                pipeline:[
                    {
                        $match: { 
                            FormId: "507048044944692000",
                            "FormItems.key": { $ne: null }                        
                        }
                    },
                    {
                        $addFields: {
                            FormValueObj: {
                                $arrayToObject: {
                                    $map: {
                                        input: "$FormItems",
                                        as: "field",
                                        in: [ "$$field.key", "$$field.value" ]
                                    }
                                }
                            }
                        }
                    },
                    {
                      $replaceRoot: { newRoot: { $mergeObjects: [ "$FormValueObj", "$$ROOT" ] } }
                    },
                    {
                        $project: {
                            FormItems:0,
                            FormValueObj:0
                        }
                    },
                    { $match:
                         { $expr:
                                { $eq: [ "$1572493552001.id","$$tempMainRelationKey" ] }
                         }
                    },
                ],
                as:'carWastage'
            }
        },
        {   
            $lookup:{
                from:"FormInstace",
                let: { tempMainRelationKey: "$_id" },
                pipeline:[
                    {
                        $match: { 
                            FormId: "507048044944693000",
                            "FormItems.key": { $ne: null }                        
                        }
                    },
                    {
                        $addFields: {
                            FormValueObj: {
                                $arrayToObject: {
                                    $map: {
                                        input: "$FormItems",
                                        as: "field",
                                        in: [ "$$field.key", "$$field.value" ]
                                    }
                                }
                            }
                        }
                    },
                    {
                      $replaceRoot: { newRoot: { $mergeObjects: [ "$FormValueObj", "$$ROOT" ] } }
                    },
                    {
                        $project: {
                            FormItems:0,
                            FormValueObj:0
                        }
                    },
                    { $match:
                         { $expr:
                                { $eq: [ "$1572493553001.id","$$tempMainRelationKey" ] }
                         }
                    },
                ],
                as:'carRevenue'
            }
        },
        {
            $addFields: {
                "carWastage_Money": { $sum: "$carWastage.1572493552005"},
                "carRevenue_Money": { $sum: "$carRevenue.1572493553005"},
                "carRevenue_Mileage": { $sum: "$carRevenue.1572493553006"},
            }
        }
    ]);
    在文中第二个查询语句基础上修改

      下面看一下对比图,看一下改动情况

      再看一下,执行结果截图

      和上面的结果一模一样……对mongodb管道认识不够深入,应用中有需求就是查一下,太片面了,没有验证的东西千万别乱说,切记切记切记!!!特尴尬, ̄□ ̄||

  • 相关阅读:
    学习进度条
    学习进度条
    《软件需求模式》读书笔记04
    大型网站技术架构阅读笔记5
    大型网站技术架构阅读笔记4
    大型网站技术架构阅读笔记3
    大型网站技术架构阅读笔记2
    大型网站技术架构阅读笔记1
    《uml大战需求分析》阅读笔记06
    《uml大战需求分析》阅读笔记05
  • 原文地址:https://www.cnblogs.com/du-blog/p/11840660.html
Copyright © 2020-2023  润新知