关于MongoDB中一些复杂的聚合分组查询操作

---

前言

本文章主要是记录个人在学习或者工作中使用MongoDB开发时遇到的一些奇葩经历；由于MongoDB存储数据比较自由，导致在查询或者统计数据的时候，需要把数据类型进行转换或者空数据处理等等…如果家人们看完这篇文章发现有问题或者有好的建议，可以提出来喔

---

一、字段切割&条件判断

汇总各个店铺【$shop_name】在某个时间段【$patrol_time】内违规【$whether_violates == ‘Y’】的记录数

1.$substr:[‘字段名’,开始下标,结束下标]

2.$cond:{if…then…else}

查询SQL如下：

db.t_tbshop_patrol.aggregate([
    {
        "$project": 
        {
            "yearMonthDay": {
                "$substr": ["$patrol_time", 0, 10]
            },
            "patrol_time": 1,
            "shop_name": 1,
            "whether_violates": 1,
            "y_count": {
                "$sum": {
                    "$cond": {
                        "if": {
                            "$eq": ["$whether_violates", "Y"]
                        },
                        "then": 1,
                        "else": 0
                    }
                }
            }
        }
    },
    {
        "$match": {
            "patrol_time": {
                "$gte": "2021-09-29 00:00:00",
                "$lte": "2021-09-29 23:59:59"
            }
        }
    },
    {
        "$group": {
            "_id": {
                "patrol_time": "$yearMonthDay",
                "shop_name": "$shop_name"
            },
            "violate_y": {
                "$sum": "$y_count"
            }
        }
    },
    /* 只展示汇总结果大于0的店铺数据，去掉下面{'$match'}则展示所有店铺数据 */
    {
        "$match": {
            "violate_y": {
                "$gt": 0
            }
        }
    }
])

查询结果如下：

/* 1 */
{
    "_id" : {
        "patrol_time" : "2021-09-29",
        "shop_name" : "VETA个护海外旗舰店"
    },
    "violate_y" : 5.0
}

/* 2 */
{
    "_id" : {
        "patrol_time" : "2021-09-29",
        "shop_name" : "swisseFuJun海外专卖店"
    },
    "violate_y" : 24.0
}

/* 3 */
{
    "_id" : {
        "patrol_time" : "2021-09-29",
        "shop_name" : "咿儿润旗舰店"
    },
    "violate_y" : 1.0
}

---

二、联表查询&类型转换&拆分数组

根据日期查询订单表中每条订单数据每种商品（非赠品）的总金额（业务需要显示仓库，但原始数据中只有仓库编号，所以和仓库信息表【t_warehouse_info】使用联表查询）

1.$lookup

2.$toDouble、$convert

3.$unwind

原始数据示例：

{
    "_id" : ObjectId("5f33abcdfc12b1f2d21b2e5d"),
    /* 订单编号 */
    "order_sn" : "12023232223920",
    "add_time" : "2021-09-29 20:01:25",
    "complete_time" : "1596330674",
     /* 仓库编号 */
    "fhck" : "151",
    "last_update" : "2021-09-29 09:14:03",
    "lylx" : "1",
    /* 订单商品明细：同一笔订单下的同一商品可能有多条，所以需要根据商品编号【goods_sn】分组求和 */
    "orderDetailGets" : [ 
        {
            "goods_sn" : "6925425422173",
            "goods_id" : "2518",
            "goods_number" : "3",
            "goods_price" : "35.19",
            "shop_price" : "79.00",
            "share_price" : "35.19",
             /* 支付金额 */
            "share_payment" : "35.19",
            "original_order_sn" : "12023232223920",
            "goods_name" : "口力橡皮糖500g",
            /* 是否赠品（1：是；0：否）*/
            "is_gift" : "0",
            "share_shipping_fee" : "0.00"
        }, 
        {
            "goods_sn" : "6971083865227",
            "goods_id" : "1569",
            "goods_number" : "1",
            "goods_price" : "1.87",
            "shop_price" : "4.50",
            "share_price" : "1.87",
            "share_payment" : "1.87",
            "original_order_sn" : "12023232223920",
            "goods_name" : "口力组合橡皮糖随手包30g",
            "is_gift" : "0",
            "share_shipping_fee" : "0.00"
        }, 
        {
            "goods_sn" : "6971083865227",
            "goods_id" : "1569",
            "sku_id" : "1569",
            "goods_number" : "2",
            "goods_price" : "1.87",
            "shop_price" : "4.50",
            "goods_price" : "1.87",
            "share_payment" : "3.74",
            "original_order_sn" : "12023232223920",
            "goods_name" : "口力组合橡皮糖随手包30g",
            "is_gift" : "0",
            "share_shipping_fee" : "0.00"
        }
    ],
    "order_msg" : "",
    "order_status" : "5",
    "pay_time" : "2021-09-29 20:03:13",
    "payment" : "37.06",
    "sd_name" : "口力旗舰店",
    "total_amount" : "84.54",
    "weigh" : "0.800000"
}

查询SQL如下：

db.getCollection('t_shop_order').aggregate([
    {
        "$match": {
            "add_time": {
                "$gte": "2021-12-14 00:00:00",
                "$lte": "2021-12-14 23:59:59"
            }
        }
    },
    /* $lookup联表查询：相当于left join；关联后的子表数据无论是1对1还是1对多，都会形成一个数组对象 */
    {
        "$lookup": {
            "from": "t_warehouse_info",
            /* 主表的关联字段名 */
            "localField": "fhck",
            /* 关联表的字段名 */
            "foreignField": "ckdm",
            /* 关联表别名，作为数据的字段名 */
            "as": "warehouse_info"
        }
    },
    /* $unwind拆分数组对象，扁平化展示 */
    {
        "$unwind": "$orderDetailGets"
    },
    {
        "$project": {
            "order_sn": 1,
            "add_time": 1,
            "goods_sn": "$orderDetailGets.goods_sn",
            "is_gift": "$orderDetailGets.is_gift",
            /* $convert类型转换：将字符串转换为int类型，以助于后面的数据统计 */
            "goods_number": {
                "$convert": {
                    "input": "$orderDetailGets.goods_number",
                    "to": "int"
                }
            },
            "goods_name": "$orderDetailGets.goods_name",
            /* $ifNull空判断：如果对应字符串为null，则设置默认值为0 */
            /* $toDouble转换为浮点型：将数据转换为浮点型数据 */
            "share_payment": {
                "$ifNull": [{
                    "$toDouble": "$orderDetailGets.share_payment"
                }, 0]
            },
            "ckmc": "$warehouse_info.ckmc"
        }
    },
    {
        "$match": {
            "is_gift": "0"
        }
    },
    {
        "$unwind": "$ckmc"
    },
    {
        "$group": {
            _id: {
                "order_sn": "$order_sn",
                "add_time": "$add_time",
                "goods_sn": "$goods_sn",
                "goods_name": "$goods_name"
            },
            "goods_number": {
                "$sum": "$goods_number"
            },
            "share_payment": {
                "$sum": "$share_payment"
            }
        }
    }
])

查询SQL如下：

/* 1 */
{
    "_id" : {
        "order_sn" : "12023232223920",
        "add_time" : "2021-12-14 12:22:16",
        "goods_sn" : "6925425422173",
        "goods_name" : "口力橡皮糖500g"
    },
    "goods_number" : 3,
    "share_payment" : 35.19
}

/* 2 */
{
    "_id" : {
        "order_sn" : "12023232223920",
        "add_time" : "2021-12-14 22:37:09",
        "goods_sn" : "6971083865227",
        "goods_name" : "口力组合橡皮糖随手包30g"
    },
    "goods_number" : 3,
    "share_payment" : 5.61
}

总结

以上，这篇文章会持续更新，家人们有疑问或者建议可以留下您的评论或者私信哈，大家一起进步呀~