MongoDB的聚合筆記

2023-12-10 18:01:42

1,聚合

聚合(aggregate)主要用於計算資料,類似sql中的sum()、avg()。

          

 常用的表示式如上圖。

1.1,aggregate  語法

語法:     db.集合名稱.aggregate([{管道:{表示式}}])

 

常用的管道:

 

1.2,$match 和 $group

 

 往集合 items 中先插入資料

db.items.insert(
[
{quantity:2,price:5.0,pnumber:"p003"},
{quantity:2,price:8.0,pnumber:"p002"},
{quantity:1,price:4.0,pnumber:"p002"},
{quantity:2,price:4.0,pnumber:"p001"},
{quantity:4,price:10.0,pnumber:"p003"},
{quantity:10,price:20.0,pnumber:"p001"},
{quantity:10,price:20.0,pnumber:"p003"},
{quantity:5,price:10.0,pnumber:"p002"}
]
)

 檢視視覺化工具插入的結果如下:

 例1:

   查詢 pnumber:"p001" , 再來看語法 :db.集合名稱.aggregate([{管道:{表示式}}])

db.items.aggregate([{"$match":{"pnumber":"p001"}}])

 

例2:

 

    查詢price 大於8的 quantity ,以平均值 avg_quantity表示,並按 pnumber分組。所以分組欄位是 $pnumber,平均值avg_quantity,avg_quantity是新的欄位名,聚合操作符是 $avg。

db.items.aggregate([
   {"$match":{"price":{"$gt":8}}},
   {"$group":{"_id":"$pnumber","avg_quantity":{"$avg":"$quantity"}}}
])

 

例3:

    查詢 price大於8的quantity,以平均值 avg_quantity 表示,並過濾出平均值大於5的avg_quantity。前半句話是上個例子的,過濾出 是用 $match。

db.items.aggregate([
   {"$match":{"price":{"$gt":8}}},
   {"$group":{"_id":"$pnumber","avg_quantity":{"$avg":"$quantity"}}},
   {"$match":{"avg_quantity":{"$gt":5}}}
])

 

例4:

   按 pnumber分組

db.items.aggregate([
      {"$group":{"_id":"$pnumber"}}   
])

   按 quantity 和 price 分組

db.items.aggregate([
      {"$group":{"_id":{"quantity":"$quantity","price":"$price"}}}   
])

  截了部分屏

 

 1.3,聚合操作符($sum,$avg,$max,$min,$first,$last)

例1($max):

   按 pnumber 分組,並求出 每組 price的最大值max_price

db.items.aggregate([
      {"$group":
          {"_id":"$pnumber",
           "max_price":{"$max":"$price"}
           }
       }   
])

 

例2($min):

   按 pnumber分組,並求出每組 price 的最大值 max_price,最小值 min_price

db.items.aggregate([
      {"$group":
          {"_id":"$pnumber",
           "max_price":{"$max":"$price"},
           "min_price":{"$min":"$price"}
           }
       }   
])

 

 

 這個把上面寫的 $max,$min一替換就行。

 

 例3($sum):

    按pnumber分組,並計算每組的 quantity的總和 count,並按count升序排。這裡用了 $sort,注意要在分組完才能 $sort(看好格式)。

db.items.aggregate([
      {"$group":
          {"_id":"$pnumber","count":{"$sum":"$quantity"}},
       },
       {"$sort":{"count": 1}}   
])

 

 例4:

     查詢分組後 每個 pnumber 出現的次數,並按升序排

db.items.aggregate([
      {"$group":
          {"_id":"$pnumber","count":{"$sum":1}},
       },
       {"$sort":{"count": 1}}   
])

 

 例5($addToSet,$push):

    按 pnumber 分組,並查出各 pnumber內的price ,price可以重複 (用 $push)

db.items.aggregate([
      {"$group":
               {"_id":"$pnumber","prices":{"$push":"$price"}}             
       }
])

  結果 是因為新增的資料不太好,所以看不出有沒有重複還是重複了。

   按 pnumber 分組,並查出各 pnumber內的price ,price不可以重複 (用 $addToSet)

db.items.aggregate([
      {"$group":
               {"_id":"$pnumber","prices":{"$addToSet":"$price"}}             
       }
])

 

 

 1.4,投射 ($project),$add

     只保留 _id 和 pnumber,新增 new_price,new_price 就是讓 price加1(用到了 $add)

db.items.aggregate([
      {
          $project:{
              "_id":1,
              "pnumber":1,
              "new_price":{"$add":["$price", 1]}
                    }
       }
])

 

 

 1.5,排序($sort),限制($limit),跳過($skip)

 

 例1:

    取平均價格最貴的前兩個pnumber,所以是按pnumber分組($group),再降序排($sort),再限制是前兩個($limit)。

db.items.aggregate([
    {
        "$group":{"_id":"$pnumber","平均價格":{"$avg":"$price"}}   
    },
    {
        "$sort":{"平均價格": -1}
    },
    {
        "$limit": 2
    }  
])

 

 例2:

      取平均價格最貴的第二個 pnumber,就是在上個例子的基礎上,跳過1個, $skip:1

db.items.aggregate([
    {
        "$group":{"_id":"$pnumber","平均價格":{"$avg":"$price"}}   
    },
    {
        "$sort":{"平均價格": -1}
    },
    {
        "$limit": 2
    },
    {
        "$skip":1
    }  
])

 

 1.6,隨機獲取,$sample

db.items.aggregate([
     {$sample: {size: 3}}
])

   再點執行,就出來不一樣的結果

 

 1.7,擷取($substr),拼接($concat),轉小寫($toLower),轉大寫($toUpper)

 

 例1:

   把 pnumber 的下標為 0到2 位置的 截出來,_id 隱藏

db.items.aggregate([
    {
      $project:{
         "_id":0,
         "str": {$substr: ["$pnumber", 0, 2]}
                }  
    }

])

   因為擷取是 左閉右開的截,所以顯示是兩個字元。

 

 例2:

    把 pnumber 欄位下的內容 和 "測試" 拼接起來

db.items.aggregate([
    {
      "$project":{
         "_id":0,
         "str": {"$concat": ["$pnumber", "測試"]}
                }  
    }
])

 

例3:

    將pnumber的 英文轉換為大寫

db.items.aggregate([
     {
         "$project":{"pnumber":{"$toUpper":"$pnumber"}}
     }
])