当前位置：首页 > 数据库 > 正文内容

MongoDB分组查询和聚合查询

2024-12-04数据库3

1、单一字段分组

分组后不会展示其他字段, _id为固定写法，userid为分组字段

1
2
# 比如对比sql：select userid from testgroup by userid
db.test.aggregate({"$group":{"_id":"$userid"}} )

效果：

2、多字段分组

_id为固定写法，userid与articleid为分组字段，多字段时需要设置一个别名

1
2
# 比如对比sql：select userid,articleid from testgroup by userid,articleid
db.test.aggregate({"$group":{"_id":{"userid":"$userid","articleid":"$articleid"}}})

效果：

3、条件匹配分组

$match表示匹配条件，在group前表示where，在后表示having，userid为分组字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 比如对比sql：select userid from testwhere userid in("1","2")  group by userid
db.test.aggregate(
    {"$match":{"userid":{"$in":["1","2"]}}},
    {"$group":{"_id":"$userid"}}
)
或者
# 比如对比sql：select userid,articleid from test where userid in("1","2")  group by userid
db.test.aggregate(
    {"$match":{"userid":{"$in":["1","2"]}}},
    {"$group":{"_id":{"userid":"$userid","articleid":"$articleid"}}}
)
或者having聚合查询
# 比如：select userid,avg(price) as avg from test where userid >'10' group by userid having avg>35
db.test.aggregate(
    {"$match":{"userid":{"$gt":"10"}}},
    {"$group":{"_id":"$userid",'avg':{"$avg":"$price"}}},
    {"$match":{"avg":{"$gt":35}}}
)

效果：

4、分组分页排序

限制：$limit、跳过：$skip

1
2
3
4
5
db.test.aggregate(
        {"$group":{"_id":"$userid"}},
        {"$skip":1},
        {"$limit":2}
    )

5、聚合分组

聚合分组,分为：$sum、$avg、$max、$min、$first、$last，聚合结果需要使用别名，并且可以结合条件和排序等元素

为了测试聚合，增加一下文件：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
db.test.insert([{
    "articleid":"1",
    "price":200,
    "userid":"5"
},{
    "articleid":"2",
    "price":300,
    "userid":"5"
},{
    "articleid":"3",
    "price":400,
    "userid":"5"
},{
    "articleid":"1",
    "price":200,
    "userid":"6"
},{
    "articleid":"2",
    "price":300,
    "userid":"7"
}]);

5.1、sum聚合-求数值总和

求某个数值总和，userid分组字段[可以用于多字段聚合],{"$sum":$price}表示求price合计

1
db.test.aggregate({"$group":{"_id":"$userid","total":{"$sum":"$price"}}})

5.2、sum聚合-求分组后数量

1
db.test.aggregate({"$group":{"_id":"$userid","total":{"$sum":1}}})

效果：

5.3、avg、max、min聚合

avg、max、min可以分开使用，也可以一起使用

1
db.test.aggregate({"$group":{"_id":"$userid","avg":{"$avg":"$price"},"max":{"$max":"$price"},"min":{"$min":"$price"}}})

效果：

5.4、复合聚合

可以多字段聚合求值，通过sum与avg、max、min进行组合

1
2
3
db.test.aggregate({"$group":{"_id":"$userid"
    ,"total":{"$sum":"$price"}
    ,"avg":{"$avg":"$price"}}})

效果：

5.5、聚合分组排序

对聚合结果进行排序，$sort-排序，avg-聚合结果的别名，1：升序，-1：降序

1
2
3
4
5
# 先按userid降序,再avg升序
    db.test.aggregate({"$group":{"_id":"$userid"
    ,"avg":{"$avg":"$price"}}}
    ,{"$sort":{"avg":1}}
    ,{"$sort":{"_id":-1}})

效果：

5.6、首尾取值聚合

$first-第一个元素、$last-最后一个元素

1
vdb.test.aggregate({"$group":{"_id":"$userid","prices":{"$first":"$price"}}})

效果：

5.7、聚合分组拼接

等同于group_concat，$push-元素可重复，$addToSet-元素不可重复，将分组后的数据拼接为一个数组

1
db.test.aggregate({"$group":{"_id":"$userid","names":{"$push":"$price"}}})

效果：

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

一、字段切割&条件判断

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

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

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

查询SQL如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
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

原始数据示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
{
    "_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如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
/* 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
}

总结


相关文章

PostgreSQL自动递增2024-11-06
linux数据库管理软件推荐2024-11-09
SQL Server 数据太多怎么优化2024-11-29
CentOS 9 Stream 安装部署 SQL Server 20192024-11-29
SQL Server怎么导入Excel数据2024-11-29
SQL server 中 CROSS APPLY的操作2024-11-29
SQL Server怎么创建用户只能访问指定数据库2024-11-29
SQL SERVER数据库登陆错误184562024-11-29
SQL报错注入之updatexml2024-11-29

Copyright © 2015-2024 广为博客. 闽ICP备2024075899号-1