Tóm lược. trong hướng dẫn này, bạn sẽ học cách sử dụng $sum MongoDB để trả về tổng của các giá trị số
Giới thiệu về MongoDB $sum
MongoDB
4 trả về tổng các giá trị số. Đây là cú pháp củaCode language: JavaScript [javascript]
db.sales.insertMany[[ { "_id" : 1, "item" : "Americanos", "price" : 5, "size": "Short", "quantity" : 22, "date" : ISODate["2022-01-15T08:00:00Z"] }, { "_id" : 2, "item" : "Cappuccino", "price" : 6, "size": "Short","quantity" : 12, "date" : ISODate["2022-01-16T09:00:00Z"] }, { "_id" : 3, "item" : "Lattes", "price" : 15, "size": "Grande","quantity" : 25, "date" : ISODate["2022-01-16T09:05:00Z"] }, { "_id" : 4, "item" : "Mochas", "price" : 25,"size": "Tall", "quantity" : 11, "date" : ISODate["2022-02-17T08:00:00Z"] }, { "_id" : 5, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 12, "date" : ISODate["2022-02-18T21:06:00Z"] }, { "_id" : 6, "item" : "Cappuccino", "price" : 7, "size": "Tall","quantity" : 20, "date" : ISODate["2022-02-20T10:07:00Z"] }, { "_id" : 7, "item" : "Lattes", "price" : 25,"size": "Tall", "quantity" : 30, "date" : ISODate["2022-02-21T10:08:00Z"] }, { "_id" : 8, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 21, "date" : ISODate["2022-02-22T14:09:00Z"] }, { "_id" : 9, "item" : "Cappuccino", "price" : 10, "size": "Grande","quantity" : 17, "date" : ISODate["2022-02-23T14:09:00Z"] }, { "_id" : 10, "item" : "Americanos", "price" : 8, "size": "Tall","quantity" : 15, "date" : ISODate["2022-02-25T14:09:00Z"]} ]];
4Code language: JavaScript [javascript]
db.sales.insertMany[[ { "_id" : 1, "item" : "Americanos", "price" : 5, "size": "Short", "quantity" : 22, "date" : ISODate["2022-01-15T08:00:00Z"] }, { "_id" : 2, "item" : "Cappuccino", "price" : 6, "size": "Short","quantity" : 12, "date" : ISODate["2022-01-16T09:00:00Z"] }, { "_id" : 3, "item" : "Lattes", "price" : 15, "size": "Grande","quantity" : 25, "date" : ISODate["2022-01-16T09:05:00Z"] }, { "_id" : 4, "item" : "Mochas", "price" : 25,"size": "Tall", "quantity" : 11, "date" : ISODate["2022-02-17T08:00:00Z"] }, { "_id" : 5, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 12, "date" : ISODate["2022-02-18T21:06:00Z"] }, { "_id" : 6, "item" : "Cappuccino", "price" : 7, "size": "Tall","quantity" : 20, "date" : ISODate["2022-02-20T10:07:00Z"] }, { "_id" : 7, "item" : "Lattes", "price" : 25,"size": "Tall", "quantity" : 30, "date" : ISODate["2022-02-21T10:08:00Z"] }, { "_id" : 8, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 21, "date" : ISODate["2022-02-22T14:09:00Z"] }, { "_id" : 9, "item" : "Cappuccino", "price" : 10, "size": "Grande","quantity" : 17, "date" : ISODate["2022-02-23T14:09:00Z"] }, { "_id" : 10, "item" : "Americanos", "price" : 8, "size": "Tall","quantity" : 15, "date" : ISODate["2022-02-25T14:09:00Z"]} ]];
Code language: HTML, XML [xml]
{ $sum: }
Thông thường, bạn áp dụng
4 cho các giá trị số. Tuy nhiên, nếu một trường chứa giá trị không phải là số, thìCode language: JavaScript [javascript]
db.sales.insertMany[[ { "_id" : 1, "item" : "Americanos", "price" : 5, "size": "Short", "quantity" : 22, "date" : ISODate["2022-01-15T08:00:00Z"] }, { "_id" : 2, "item" : "Cappuccino", "price" : 6, "size": "Short","quantity" : 12, "date" : ISODate["2022-01-16T09:00:00Z"] }, { "_id" : 3, "item" : "Lattes", "price" : 15, "size": "Grande","quantity" : 25, "date" : ISODate["2022-01-16T09:05:00Z"] }, { "_id" : 4, "item" : "Mochas", "price" : 25,"size": "Tall", "quantity" : 11, "date" : ISODate["2022-02-17T08:00:00Z"] }, { "_id" : 5, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 12, "date" : ISODate["2022-02-18T21:06:00Z"] }, { "_id" : 6, "item" : "Cappuccino", "price" : 7, "size": "Tall","quantity" : 20, "date" : ISODate["2022-02-20T10:07:00Z"] }, { "_id" : 7, "item" : "Lattes", "price" : 25,"size": "Tall", "quantity" : 30, "date" : ISODate["2022-02-21T10:08:00Z"] }, { "_id" : 8, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 21, "date" : ISODate["2022-02-22T14:09:00Z"] }, { "_id" : 9, "item" : "Cappuccino", "price" : 10, "size": "Grande","quantity" : 17, "date" : ISODate["2022-02-23T14:09:00Z"] }, { "_id" : 10, "item" : "Americanos", "price" : 8, "size": "Tall","quantity" : 15, "date" : ISODate["2022-02-25T14:09:00Z"]} ]];
4 sẽ bỏ qua giá trị đó. Ngoài ra, nếu trường không tồn tại trong bất kỳ tài liệu nào, thì
db.sales.insertMany[[ { "_id" : 1, "item" : "Americanos", "price" : 5, "size": "Short", "quantity" : 22, "date" : ISODate["2022-01-15T08:00:00Z"] }, { "_id" : 2, "item" : "Cappuccino", "price" : 6, "size": "Short","quantity" : 12, "date" : ISODate["2022-01-16T09:00:00Z"] }, { "_id" : 3, "item" : "Lattes", "price" : 15, "size": "Grande","quantity" : 25, "date" : ISODate["2022-01-16T09:05:00Z"] }, { "_id" : 4, "item" : "Mochas", "price" : 25,"size": "Tall", "quantity" : 11, "date" : ISODate["2022-02-17T08:00:00Z"] }, { "_id" : 5, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 12, "date" : ISODate["2022-02-18T21:06:00Z"] }, { "_id" : 6, "item" : "Cappuccino", "price" : 7, "size": "Tall","quantity" : 20, "date" : ISODate["2022-02-20T10:07:00Z"] }, { "_id" : 7, "item" : "Lattes", "price" : 25,"size": "Tall", "quantity" : 30, "date" : ISODate["2022-02-21T10:08:00Z"] }, { "_id" : 8, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 21, "date" : ISODate["2022-02-22T14:09:00Z"] }, { "_id" : 9, "item" : "Cappuccino", "price" : 10, "size": "Grande","quantity" : 17, "date" : ISODate["2022-02-23T14:09:00Z"] }, { "_id" : 10, "item" : "Americanos", "price" : 8, "size": "Tall","quantity" : 15, "date" : ISODate["2022-02-25T14:09:00Z"]} ]];
Code language: JavaScript [javascript]
4 trả vềCode language: JavaScript [javascript]
db.sales.insertMany[[ { "_id" : 1, "item" : "Americanos", "price" : 5, "size": "Short", "quantity" : 22, "date" : ISODate["2022-01-15T08:00:00Z"] }, { "_id" : 2, "item" : "Cappuccino", "price" : 6, "size": "Short","quantity" : 12, "date" : ISODate["2022-01-16T09:00:00Z"] }, { "_id" : 3, "item" : "Lattes", "price" : 15, "size": "Grande","quantity" : 25, "date" : ISODate["2022-01-16T09:05:00Z"] }, { "_id" : 4, "item" : "Mochas", "price" : 25,"size": "Tall", "quantity" : 11, "date" : ISODate["2022-02-17T08:00:00Z"] }, { "_id" : 5, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 12, "date" : ISODate["2022-02-18T21:06:00Z"] }, { "_id" : 6, "item" : "Cappuccino", "price" : 7, "size": "Tall","quantity" : 20, "date" : ISODate["2022-02-20T10:07:00Z"] }, { "_id" : 7, "item" : "Lattes", "price" : 25,"size": "Tall", "quantity" : 30, "date" : ISODate["2022-02-21T10:08:00Z"] }, { "_id" : 8, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 21, "date" : ISODate["2022-02-22T14:09:00Z"] }, { "_id" : 9, "item" : "Cappuccino", "price" : 10, "size": "Grande","quantity" : 17, "date" : ISODate["2022-02-23T14:09:00Z"] }, { "_id" : 10, "item" : "Americanos", "price" : 8, "size": "Tall","quantity" : 15, "date" : ISODate["2022-02-25T14:09:00Z"]} ]];
9 cho trường đóCode language: JavaScript [javascript]
db.sales.insertMany[[ { "_id" : 1, "item" : "Americanos", "price" : 5, "size": "Short", "quantity" : 22, "date" : ISODate["2022-01-15T08:00:00Z"] }, { "_id" : 2, "item" : "Cappuccino", "price" : 6, "size": "Short","quantity" : 12, "date" : ISODate["2022-01-16T09:00:00Z"] }, { "_id" : 3, "item" : "Lattes", "price" : 15, "size": "Grande","quantity" : 25, "date" : ISODate["2022-01-16T09:05:00Z"] }, { "_id" : 4, "item" : "Mochas", "price" : 25,"size": "Tall", "quantity" : 11, "date" : ISODate["2022-02-17T08:00:00Z"] }, { "_id" : 5, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 12, "date" : ISODate["2022-02-18T21:06:00Z"] }, { "_id" : 6, "item" : "Cappuccino", "price" : 7, "size": "Tall","quantity" : 20, "date" : ISODate["2022-02-20T10:07:00Z"] }, { "_id" : 7, "item" : "Lattes", "price" : 25,"size": "Tall", "quantity" : 30, "date" : ISODate["2022-02-21T10:08:00Z"] }, { "_id" : 8, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 21, "date" : ISODate["2022-02-22T14:09:00Z"] }, { "_id" : 9, "item" : "Cappuccino", "price" : 10, "size": "Grande","quantity" : 17, "date" : ISODate["2022-02-23T14:09:00Z"] }, { "_id" : 10, "item" : "Americanos", "price" : 8, "size": "Tall","quantity" : 15, "date" : ISODate["2022-02-25T14:09:00Z"]} ]];
MongoDB $sum ví dụ
Chúng tôi sẽ sử dụng bộ sưu tập
0 sau đây để chứng minhCode language: PHP [php]
db.sales.aggregate[[ { $group: { _id: null, totalQty: { $sum: '$quantity' }, }, }, ]];
4Code language: JavaScript [javascript]
db.sales.insertMany[[ { "_id" : 1, "item" : "Americanos", "price" : 5, "size": "Short", "quantity" : 22, "date" : ISODate["2022-01-15T08:00:00Z"] }, { "_id" : 2, "item" : "Cappuccino", "price" : 6, "size": "Short","quantity" : 12, "date" : ISODate["2022-01-16T09:00:00Z"] }, { "_id" : 3, "item" : "Lattes", "price" : 15, "size": "Grande","quantity" : 25, "date" : ISODate["2022-01-16T09:05:00Z"] }, { "_id" : 4, "item" : "Mochas", "price" : 25,"size": "Tall", "quantity" : 11, "date" : ISODate["2022-02-17T08:00:00Z"] }, { "_id" : 5, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 12, "date" : ISODate["2022-02-18T21:06:00Z"] }, { "_id" : 6, "item" : "Cappuccino", "price" : 7, "size": "Tall","quantity" : 20, "date" : ISODate["2022-02-20T10:07:00Z"] }, { "_id" : 7, "item" : "Lattes", "price" : 25,"size": "Tall", "quantity" : 30, "date" : ISODate["2022-02-21T10:08:00Z"] }, { "_id" : 8, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 21, "date" : ISODate["2022-02-22T14:09:00Z"] }, { "_id" : 9, "item" : "Cappuccino", "price" : 10, "size": "Grande","quantity" : 17, "date" : ISODate["2022-02-23T14:09:00Z"] }, { "_id" : 10, "item" : "Americanos", "price" : 8, "size": "Tall","quantity" : 15, "date" : ISODate["2022-02-25T14:09:00Z"]} ]];
db.sales.insertMany[[ { "_id" : 1, "item" : "Americanos", "price" : 5, "size": "Short", "quantity" : 22, "date" : ISODate["2022-01-15T08:00:00Z"] }, { "_id" : 2, "item" : "Cappuccino", "price" : 6, "size": "Short","quantity" : 12, "date" : ISODate["2022-01-16T09:00:00Z"] }, { "_id" : 3, "item" : "Lattes", "price" : 15, "size": "Grande","quantity" : 25, "date" : ISODate["2022-01-16T09:05:00Z"] }, { "_id" : 4, "item" : "Mochas", "price" : 25,"size": "Tall", "quantity" : 11, "date" : ISODate["2022-02-17T08:00:00Z"] }, { "_id" : 5, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 12, "date" : ISODate["2022-02-18T21:06:00Z"] }, { "_id" : 6, "item" : "Cappuccino", "price" : 7, "size": "Tall","quantity" : 20, "date" : ISODate["2022-02-20T10:07:00Z"] }, { "_id" : 7, "item" : "Lattes", "price" : 25,"size": "Tall", "quantity" : 30, "date" : ISODate["2022-02-21T10:08:00Z"] }, { "_id" : 8, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 21, "date" : ISODate["2022-02-22T14:09:00Z"] }, { "_id" : 9, "item" : "Cappuccino", "price" : 10, "size": "Grande","quantity" : 17, "date" : ISODate["2022-02-23T14:09:00Z"] }, { "_id" : 10, "item" : "Americanos", "price" : 8, "size": "Tall","quantity" : 15, "date" : ISODate["2022-02-25T14:09:00Z"]} ]];
Code language: JavaScript [javascript]
1] Một ví dụ MongoDB $sum đơn giản
Ví dụ sau tính toán tổng số lượng bán cà phê trong bộ sưu tập
0Code language: PHP [php]
db.sales.aggregate[[ { $group: { _id: null, totalQty: { $sum: '$quantity' }, }, }, ]];
Code language: PHP [php]
db.sales.aggregate[[ { $group: { _id: null, totalQty: { $sum: '$quantity' }, }, }, ]];
đầu ra
2Code language: JavaScript [javascript]
db.sales.insertMany[[ { "_id" : 1, "item" : "Americanos", "price" : 5, "size": "Short", "quantity" : 22, "date" : ISODate["2022-01-15T08:00:00Z"] }, { "_id" : 2, "item" : "Cappuccino", "price" : 6, "size": "Short","quantity" : 12, "date" : ISODate["2022-01-16T09:00:00Z"] }, { "_id" : 3, "item" : "Lattes", "price" : 15, "size": "Grande","quantity" : 25, "date" : ISODate["2022-01-16T09:05:00Z"] }, { "_id" : 4, "item" : "Mochas", "price" : 25,"size": "Tall", "quantity" : 11, "date" : ISODate["2022-02-17T08:00:00Z"] }, { "_id" : 5, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 12, "date" : ISODate["2022-02-18T21:06:00Z"] }, { "_id" : 6, "item" : "Cappuccino", "price" : 7, "size": "Tall","quantity" : 20, "date" : ISODate["2022-02-20T10:07:00Z"] }, { "_id" : 7, "item" : "Lattes", "price" : 25,"size": "Tall", "quantity" : 30, "date" : ISODate["2022-02-21T10:08:00Z"] }, { "_id" : 8, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 21, "date" : ISODate["2022-02-22T14:09:00Z"] }, { "_id" : 9, "item" : "Cappuccino", "price" : 10, "size": "Grande","quantity" : 17, "date" : ISODate["2022-02-23T14:09:00Z"] }, { "_id" : 10, "item" : "Americanos", "price" : 8, "size": "Tall","quantity" : 15, "date" : ISODate["2022-02-25T14:09:00Z"]} ]];
Để xóa trường
3 khỏi tài liệu đầu ra, bạn có thể sử dụngCode language: PHP [php]
db.sales.aggregate[[ { $group: { _id: null, totalQty: { $sum: '$quantity' }, }, }, ]];
4 như thế này
db.sales.aggregate[[ { $group: { _id: null, totalQty: { $sum: '$quantity' }, }, }, ]];
Code language: PHP [php]
5Code language: JavaScript [javascript]
db.sales.insertMany[[ { "_id" : 1, "item" : "Americanos", "price" : 5, "size": "Short", "quantity" : 22, "date" : ISODate["2022-01-15T08:00:00Z"] }, { "_id" : 2, "item" : "Cappuccino", "price" : 6, "size": "Short","quantity" : 12, "date" : ISODate["2022-01-16T09:00:00Z"] }, { "_id" : 3, "item" : "Lattes", "price" : 15, "size": "Grande","quantity" : 25, "date" : ISODate["2022-01-16T09:05:00Z"] }, { "_id" : 4, "item" : "Mochas", "price" : 25,"size": "Tall", "quantity" : 11, "date" : ISODate["2022-02-17T08:00:00Z"] }, { "_id" : 5, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 12, "date" : ISODate["2022-02-18T21:06:00Z"] }, { "_id" : 6, "item" : "Cappuccino", "price" : 7, "size": "Tall","quantity" : 20, "date" : ISODate["2022-02-20T10:07:00Z"] }, { "_id" : 7, "item" : "Lattes", "price" : 25,"size": "Tall", "quantity" : 30, "date" : ISODate["2022-02-21T10:08:00Z"] }, { "_id" : 8, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 21, "date" : ISODate["2022-02-22T14:09:00Z"] }, { "_id" : 9, "item" : "Cappuccino", "price" : 10, "size": "Grande","quantity" : 17, "date" : ISODate["2022-02-23T14:09:00Z"] }, { "_id" : 10, "item" : "Americanos", "price" : 8, "size": "Tall","quantity" : 15, "date" : ISODate["2022-02-25T14:09:00Z"]} ]];
đầu ra
6Code language: JavaScript [javascript]
db.sales.insertMany[[ { "_id" : 1, "item" : "Americanos", "price" : 5, "size": "Short", "quantity" : 22, "date" : ISODate["2022-01-15T08:00:00Z"] }, { "_id" : 2, "item" : "Cappuccino", "price" : 6, "size": "Short","quantity" : 12, "date" : ISODate["2022-01-16T09:00:00Z"] }, { "_id" : 3, "item" : "Lattes", "price" : 15, "size": "Grande","quantity" : 25, "date" : ISODate["2022-01-16T09:05:00Z"] }, { "_id" : 4, "item" : "Mochas", "price" : 25,"size": "Tall", "quantity" : 11, "date" : ISODate["2022-02-17T08:00:00Z"] }, { "_id" : 5, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 12, "date" : ISODate["2022-02-18T21:06:00Z"] }, { "_id" : 6, "item" : "Cappuccino", "price" : 7, "size": "Tall","quantity" : 20, "date" : ISODate["2022-02-20T10:07:00Z"] }, { "_id" : 7, "item" : "Lattes", "price" : 25,"size": "Tall", "quantity" : 30, "date" : ISODate["2022-02-21T10:08:00Z"] }, { "_id" : 8, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 21, "date" : ISODate["2022-02-22T14:09:00Z"] }, { "_id" : 9, "item" : "Cappuccino", "price" : 10, "size": "Grande","quantity" : 17, "date" : ISODate["2022-02-23T14:09:00Z"] }, { "_id" : 10, "item" : "Americanos", "price" : 8, "size": "Tall","quantity" : 15, "date" : ISODate["2022-02-25T14:09:00Z"]} ]];
2] Sử dụng mongoDB $sum để tính tổng các nhóm
Ví dụ sau sử dụng
4 để tính tổng số tiền bằng cách nhân giá với số lượng cho nhóm bán cà phê theo kích cỡCode language: JavaScript [javascript]
db.sales.insertMany[[ { "_id" : 1, "item" : "Americanos", "price" : 5, "size": "Short", "quantity" : 22, "date" : ISODate["2022-01-15T08:00:00Z"] }, { "_id" : 2, "item" : "Cappuccino", "price" : 6, "size": "Short","quantity" : 12, "date" : ISODate["2022-01-16T09:00:00Z"] }, { "_id" : 3, "item" : "Lattes", "price" : 15, "size": "Grande","quantity" : 25, "date" : ISODate["2022-01-16T09:05:00Z"] }, { "_id" : 4, "item" : "Mochas", "price" : 25,"size": "Tall", "quantity" : 11, "date" : ISODate["2022-02-17T08:00:00Z"] }, { "_id" : 5, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 12, "date" : ISODate["2022-02-18T21:06:00Z"] }, { "_id" : 6, "item" : "Cappuccino", "price" : 7, "size": "Tall","quantity" : 20, "date" : ISODate["2022-02-20T10:07:00Z"] }, { "_id" : 7, "item" : "Lattes", "price" : 25,"size": "Tall", "quantity" : 30, "date" : ISODate["2022-02-21T10:08:00Z"] }, { "_id" : 8, "item" : "Americanos", "price" : 10, "size": "Grande","quantity" : 21, "date" : ISODate["2022-02-22T14:09:00Z"] }, { "_id" : 9, "item" : "Cappuccino", "price" : 10, "size": "Grande","quantity" : 17, "date" : ISODate["2022-02-23T14:09:00Z"] }, { "_id" : 10, "item" : "Americanos", "price" : 8, "size": "Tall","quantity" : 15, "date" : ISODate["2022-02-25T14:09:00Z"]} ]];