使用MongoDB聚合实现常见的SQL汇总查询

使用MongoDB聚合实现常见的SQL汇总查询

SQL (Structured Query Language) 是关系型数据库的查询语言，语法简洁，功能强大。相较而言，MongoDB是NoSQL数据库，在性能、灵活性方面具有明显优势，但甘蔗没有两头甜，MongoDB在查询方面的易用性要稍差一些，好在聚合功能也非常灵活和强大。下面是针对SQL和Mongdb在查询方面的一些对照和举例。来源于官方文档，仅供参考。

常见SQL和聚合命令对照表

SQL命令	MongoDB聚合操作	含义
SELECT	$project	输出字段
WHERE	$match	筛选条件
GROUP BY	$group	分组汇总
ORDER BY	$sort	排序
JOIN	$lookup	连接
HAVING	$match	筛选条件
LIMIT	$limit	记录范围
SUM()	$sum	合计
COUNT()	$sum，$sortByCount	计数
SELECT INTO NEW_TABLE	$out	结果存入新表
MERGE INTO TABLE	$mere	合并两个结果
UNION ALL	$unionWith	合并多个结果

一些例子

分别以关系型数据库的表和Mongodb的集合为例来说明：

• 关系型数据库中有两个表，分别是订单表orders和订单详情orders_details，这两张表使用订单ID字段order_id进行关联。
• Mongodb中有一个集合orders，文档的结构如下：

{
  "customer_id": "abc123",
  "order_date": ISODate("2012-11-02T17:04:11.102Z"),
  "status": "A",
  "price": 50,
  "detials": [ { "sku": "xxx", "qty": 25, "price": 1 },
           { "sku": "yyy", "qty": 25, "price": 1 } ]
}

例1：统计订单数量

SQL：

SELECT COUNT(*) FROM orders

Mongodb：

db.orders.aggregate([
    {$group:{
        _id:null, 
        count:{
            $sum:1
            }
        }
    }
    ])

例2：价格合计

SQL：

SELECT SUM(price) AS total FROM orders

Mongodb：

db.orders.aggregate( [
   {
     $group: {
        _id: null,
        total: { $sum: "$price" }
     }
   }
] )

例3 按客户统计订单总价

SQL：

SELECT customer_id,
       SUM(price) AS total
FROM orders
GROUP BY customer_id

Mongodb：

db.orders.aggregate( [
   {
     $group: {
        _id: "$customer_id",
        total: { $sum: "$price" }
     }
   }
] )

例4 按客户统计订单总价并按价格排序

SQL：

SELECT customer_id,
       SUM(price) AS total
FROM orders
GROUP BY customer_id
ORDER BY total

Mongodb：

db.orders.aggregate( [
   {
     $group: {
        _id: "$customer_id",
        total: { $sum: "$price" }
     }
   },
   { $sort: { total: 1 } }
] )

例5 按客户和日期统计订单总价

SQL：

SELECT customer_id,
       order_date,
       SUM(price) AS total
FROM orders
GROUP BY customer_id,
         order_date

Mongodb：

db.orders.aggregate( [
   {
     $group: {
        _id: {
           customer_id: "$customer_id",
           order_date: { $dateToString: {
              format: "%Y-%m-%d",
              date: "$order_date"
           }}
        },
        total: { $sum: "$price" }
     }
   }
] )

例6 统计客户的订单数量

SQL：

SELECT customer_id,
       count(*)
FROM orders
GROUP BY customer_id
HAVING count(*) > 1

Mongodb：

db.orders.aggregate( [
   {
     $group: {
        _id: "$customer_id",
        count: { $sum: 1 }
     }
   },
   { $match: { count: { $gt: 1 } } }
] )

例7 按照日期和客户汇总订单金额大于200订单金额

SQL：

SELECT customer_id,
       order_date,
       SUM(price) AS total
FROM orders
GROUP BY customer_id,
         order_date
HAVING total > 250

Mongodb：

db.orders.aggregate( [
   {
     $group: {
        _id: {
           customer_id: "$customer_id",
           order_date: { $dateToString: {
              format: "%Y-%m-%d",
              date: "$order_date"
           }}
        },
        total: { $sum: "$price" }
     }
   },
   { $match: { total: { $gt: 250 } } }
] )

例8 根据用户汇总订单状态为’A’的订单价格

SQL：

SELECT customer_id,
       SUM(price) as total
FROM orders
WHERE status = 'A'
GROUP BY customer_id

Mongodb：

db.orders.aggregate( [
   { $match: { status: 'A' } },
   {
     $group: {
        _id: "$customer_id",
        total: { $sum: "$price" }
     }
   }
] )

例9 按照用户汇总订单状态为’A’且订单金额大于250的订单价格

SQL：

SELECT customer_id,
       SUM(price) as total
FROM orders
WHERE status = 'A'
GROUP BY customer_id
HAVING total > 250

Mongodb：

db.orders.aggregate( [
   { $match: { status: 'A' } },
   {
     $group: {
        _id: "$customer_id",
        total: { $sum: "$price" }
     }
   },
   { $match: { total: { $gt: 250 } } }
] )

例10 按照用户统计订单中的项目数量

SQL：

SELECT customer_id,
       SUM(li.qty) as qty
FROM orders o,
     order_lineitem li
WHERE li.order_id = o.id
GROUP BY customer_id

Mongodb：

db.orders.aggregate( [
   { $unwind: "$items" },
   {
     $group: {
        _id: "$customer_id",
        qty: { $sum: "$items.qty" }
     }
   }
] )

例11 按照客户和日期统计订单数量

SQL：

SELECT COUNT(*)
FROM (SELECT customer_id,
             order_date
      FROM orders
      GROUP BY customer_id,
               order_date)
      as DerivedTable

Mongodb：

db.orders.aggregate( [
   {
     $group: {
        _id: {
           customer_id: "$customer_id",
           order_date: { $dateToString: {
              format: "%Y-%m-%d",
              date: "$order_date"
           }}
        }
     }
   },
   {
     $group: {
        _id: null,
        count: { $sum: 1 }
     }
   }
] )