MongoDB 聚合管道中使用算术表达式运算符_mongodb 管道聚合求数量

算术表达式运算符主要用于实现数字之间的算术运算，主要包含了对加、减、乘、除、余数、截取、舍入等算术操作。

下面我们进行详细介绍：

一、准备数据

初始化商品数据

db.goods.insertMany([
    { "_id": 1,  name: "薯片", size: "S", quantity: 10, sale: 50, price: 8, salePrice: 5, expirationTime: ISODate( "2023-08-08T00:00:00Z" ) },
    { "_id": 2,  name: "薯片", size: "L", quantity: 8, sale: 30, price: 12, salePrice: 10, expirationTime: ISODate( "2023-08-08T00:00:00Z" ) },
    { "_id": 3,  name: "牛肉干", size: "L", quantity: 5, sale: 10, price: 30, salePrice: 30, expirationTime: ISODate( "2023-10-10T00:00:00Z" ) },
    { "_id": 4,  name: "可口可乐", size: "S", quantity: 10, sale: 100, price: 3, salePrice: 3,  expirationTime: ISODate( "2025-01-06T00:00:00Z" ) },
    { "_id": 5,  name: "可口可乐", size: "L", quantity: 6, sale: 10, price: 10, salePrice: 5, expirationTime: ISODate( "2025-01-06T00:00:00Z" ) },
    { "_id": 6,  name: "旺仔牛奶", size: "L", quantity: 10, sale: 10, price: 5, salePrice: 4.5, expirationTime: ISODate( "2023-08-10T00:00:00Z" )}
])

 其中，

        quantity代表的是商品剩余数量

        sale代表的是已售商品数量

        price代表的是商品指导价

        salePrice代表的是商品当前价格

二、加（$add）

语法：{ $add: [ <expression1>, <expression2>, ... ] }

将多个表达式的结果相加。

例子：统计商品的总数量

db.goods.aggregate([
    { 
        $project: {
            "name": 1,
            "size": 1,
            "total": { $add: [ "$quantity", "$sale" ] }
        }
    }
])

聚合查询的结果如下：

{ "_id" : 1, "name" : "薯片", "size" : "S", "total" : 60 }
{ "_id" : 2, "name" : "薯片", "size" : "L", "total" : 38 }
{ "_id" : 3, "name" : "牛肉干", "size" : "L", "total" : 15 }
{ "_id" : 4, "name" : "可口可乐", "size" : "S", "total" : 110 }
{ "_id" : 5, "name" : "可口可乐", "size" : "L", "total" : 16 }
{ "_id" : 6, "name" : "旺仔牛奶", "size" : "L", "total" : 20 }

三、减（$subtract）

语法：{ $subtract: [ <expression1>, <expression2> ] }

将两个表达式的结果相减。expression1 - expression2

例子：计算商品的优惠价格

db.goods.aggregate([
    { 
        $project: {
            "name": 1,
            "size": 1,
            "discount": { $subtract: [ "$price", "$salePrice" ] }
        }
    }
])

聚合查询的结果如下：

{ "_id" : 1, "name" : "薯片", "size" : "S", "discount" : 3 }
{ "_id" : 2, "name" : "薯片", "size" : "L", "discount" : 2 }
{ "_id" : 3, "name" : "牛肉干", "size" : "L", "discount" : 0 }
{ "_id" : 4, "name" : "可口可乐", "size" : "S", "discount" : 0 }
{ "_id" : 5, "name" : "可口可乐", "size" : "L", "discount" : 5 }
{ "_id" : 6, "name" : "旺仔牛奶", "size" : "L", "discount" : 0.5 }

四、乘（$multiply）

语法：{ $multiply: [ <expression1>, <expression2>, ... ] }

将多个表达式的结果相乘。

例子：计算剩余商品的总价值

db.goods.aggregate([
    {
        $project: {
            "name": 1,
            "size": 1,
            "totalValue": { $multiply: [ "$quantity", "$salePrice" ] }
        }
    }
])

聚合查询的结果如下：

{ "_id" : 1, "name" : "薯片", "size" : "S", "totalValue" : 50 }
{ "_id" : 2, "name" : "薯片", "size" : "L", "totalValue" : 80 }
{ "_id" : 3, "name" : "牛肉干", "size" : "L", "totalValue" : 150 }
{ "_id" : 4, "name" : "可口可乐", "size" : "S", "totalValue" : 30 }
{ "_id" : 5, "name" : "可口可乐", "size" : "L", "totalValue" : 30 }
{ "_id" : 6, "name" : "旺仔牛奶", "size" : "L", "totalValue" : 45 }

五、除（$divide）

语法：{ $divide: [ <expression1>, <expression2> ] }

将两个表达式的结果相除。expression1 / expression2

例子：计算商品的折扣

db.goods.aggregate([
    {
        $project: {
            "name": 1,
            "size": 1,
            "discount": { $divide: [ "$salePrice", "$price" ] }
        }
    }
])

聚合查询的结果如下：

{ "_id" : 1, "name" : "薯片", "size" : "S", "discount" : 0.625 }
{ "_id" : 2, "name" : "薯片", "size" : "L", "discount" : 0.8333333333333334 }
{ "_id" : 3, "name" : "牛肉干", "size" : "L", "discount" : 1 }
{ "_id" : 4, "name" : "可口可乐", "size" : "S", "discount" : 1 }
{ "_id" : 5, "name" : "可口可乐", "size" : "L", "discount" : 0.5 }
{ "_id" : 6, "name" : "旺仔牛奶", "size" : "L", "discount" : 0.9 }

六、余数（$mod）

语法：{ $mod: [ <expression1>, <expression2> ] }

将两个表达式的结果求余数。expression1 % expression2

例子：过滤出商品出售价格能被3整除的商品

db.goods.aggregate([
    {
        "$set": {
            "mod": { $mod: [ "$salePrice", 3 ] }
        }
    },
    {
        $match: {
            "mod": 0      
        }
    },
    {
        $project: {
            "name": 1,
            "size": 1,
            "salePrice": 1
        }
    }
])

等效于：

db.goods.aggregate([
    {
        $match: {
            $expr: { $eq: [ { $mod: [ "$salePrice", 3 ] }, 0 ] }
        }
    },
    {
        $project: {
            "name": 1,
            "size": 1,
            "salePrice": 1
        }
    }
])

聚合查询的结果如下：

{ "_id" : 3, "name" : "牛肉干", "size" : "L", "salePrice" : 30 }
{ "_id" : 4, "name" : "可口可乐", "size" : "S", "salePrice" : 3 }

七、截取（$trunc）

语法：{ $trunc : [ <number>, <place> ] }

<number>：代表需要截取的数字

<place>：可选，默认是0，代表的是截取的位置

例如数字：123456.7890，如果place为2，则截取的结果为123456.78

如果place为-2，则截取的结果是123400

如果place为-6，则截取的结果是0

如果place为0，则截取的结果是123456

如果<place>为负数且绝对值等于或者超过小数点左侧的整数位数，返回0

例子：计算商品的折扣

db.goods.aggregate([
    {
        $project: {
            "name": 1,
            "size": 1,
            "discount": {
                $trunc: [
                    {
                        $multiply: [ 
                            { $divide: [ "$salePrice", "$price" ] }, 
                            10 
                        ]
                    },
                    1
                ]
            }
        }
    }
])

聚合查询的结果如下：

{ "_id" : 1, "name" : "薯片", "size" : "S", "discount" : 6.2 }
{ "_id" : 2, "name" : "薯片", "size" : "L", "discount" : 8.3 }
{ "_id" : 3, "name" : "牛肉干", "size" : "L", "discount" : 10 }
{ "_id" : 4, "name" : "可口可乐", "size" : "S", "discount" : 10 }
{ "_id" : 5, "name" : "可口可乐", "size" : "L", "discount" : 5 }
{ "_id" : 6, "name" : "旺仔牛奶", "size" : "L", "discount" : 9 }

八、舍入（$round）

语法：{ $round : [ <number>, <place> ] }

舍入到最接近的数字，当舍入值为5时，会舍入到最近的偶数值

<number>代表需要舍入的数字

<place>：可选，默认是0，代表的是保留的位数

例如数字：123456.7890，如果place为2，则四舍五入的结果为123456.79

如果place为-2，则舍入的结果是123400

如果place为-7，则舍入的结果是0

如果place为0，则截取的结果是123457

如果<place>为负数且绝对值超过小数点左侧的整数位数，返回0

当舍入值为5时，会舍入到最近的偶数值

例子：计算商品的折扣

db.goods.aggregate([
    {
        $project: {
            "name": 1,
            "size": 1,
            "discount": {
                $round: [
                    {
                        $multiply: [ 
                            { $divide: [ "$salePrice", "$price" ] }, 
                            10 
                        ]
                    },
                    1
                ]
            }
        }
    }
])

聚合查询的结果：

{ "_id" : 1, "name" : "薯片", "size" : "S", "discount" : 6.2 }
{ "_id" : 2, "name" : "薯片", "size" : "L", "discount" : 8.3 }
{ "_id" : 3, "name" : "牛肉干", "size" : "L", "discount" : 10 }
{ "_id" : 4, "name" : "可口可乐", "size" : "S", "discount" : 10 }
{ "_id" : 5, "name" : "可口可乐", "size" : "L", "discount" : 5 }
{ "_id" : 6, "name" : "旺仔牛奶", "size" : "L", "discount" : 9 }