Mongodb

在 Mongodb 中使用 group by 和 sum 加入兩個集合

  • November 1, 2021

我有這兩個集合，我想加入，在此之後我想對每個 orderdate 的 TotalPrice 的值求和：

我的兩個系列中的一些

第一次收集日期：

{"Id_Date":1,"OrderDate":"2018-01-03"},
{"Id_Date":2,"OrderDate":"2018-01-05"},
{"Id_Date":3,"OrderDate":"2018-01-09"},
{"Id_Date":4,"OrderDate":"2018-01-10"},
{"Id_Date":5,"OrderDate":"2018-01-11"},
{"Id_Date":6,"OrderDate":"2018-01-12"},
{"Id_Date":7,"OrderDate":"2018-01-18"},
{"Id_Date":8,"OrderDate":"2018-01-21"},
{"Id_Date":9,"OrderDate":"2018-01-22"},
{"Id_Date":10,"OrderDate":"2018-02-02"},

第二個集合 order_fact ：

{"orderid":"c3e15faf-e338-4ed5-9bbb-bbcd15295fd7","Id_Cust":"10995116278711","Id_Date":"91","TotalPrice":1933.53,"Orderline":[{"productId":"4165","asin":"B005G2G2SQ","title":"ESEE-6 Plain Black Blade With Grey Removable Lined Micarta Handles 1095 Carbon Steel 57-Rc","price":140.05,"brand":"Fila_(company)"},{"productId":"5916","asin":"B001CJ2LQ4","title":"Aimpoint M4s 2 Minute of Angle QRP2 CompM4 Sight with Mount","price":804.95,"brand":"Atletica"},{"productId":"7779","asin":"B003DO5L3G","title":"Lee Precision Load Master 223 Remington Reloading Rifle Kit (Red)","price":249.26,"brand":"BURRDA"},{"productId":"2722","asin":"B0017U1MJU","title":"Casio Mens PAW1500-1V Pathfinder Multi-Band Solar Atomic Ultimate Watch","price":190.27,"brand":"Reebok"},{"productId":"52","asin":"B007SYGLZO","title":"Armasight Spark CORE Multi-Purpose Night Vision Monocular","price":549.0,"brand":"Signia_(sportswear)"}],"Livreur":{"Id_livreur":"91bcee9f-8896-4e18-a24c-a8e86b97b3a1","Nom":"Tyson Anderson","gender":"Male","phone":"7-458-665-3444"}},
{"orderid":"35b26e6d-e450-4edc-9071-306cea10a083","Id_Cust":"10995116278711","Id_Date":"249","TotalPrice":1540.91,"Orderline":[{"productId":"1978","asin":"B001CZBMRC","title":"Benchmade Osborne Design Rift Knife","price":178.5,"brand":"CCM_(ice_hockey)"},{"productId":"857","asin":"B004IIKA2I","title":"Barnett Quad 400 Crossbow Package (Quiver 3 - 22-Inch Arrows and 4x32mm Scope)","price":354.99,"brand":"Fischer_(company)"},{"productId":"8362","asin":"B00635GTTW","title":"Schwinn IC2 Indoor Cycling Exercise Bike","price":422.49,"brand":"Admiral_Sportswear"},{"productId":"216","asin":"B000GKN45C","title":"Swagman XTC-4 Cross-Country 4-Bike Hitch Mount Rack (2-Inch Receiver Hitch)","price":349.99,"brand":"Elfin_Sports_Cars"},{"productId":"7766","asin":"B000KKEPJ2","title":"Lyman Reloading Press T-Mag Turret Press","price":234.94,"brand":"BURRDA"}],"Livreur":{"Id_livreur":"48d64c09-7a63-4fca-815a-a96921a82ee7","Nom":"Abdul Coll","gender":"Male","phone":"1-273-204-4721"}},
{"orderid":"ffaec62e-9875-42af-8789-0a97eabe77e4","Id_Cust":"10995116278711","Id_Date":"67","TotalPrice":1058.41,"Orderline":[{"productId":"2675","asin":"B00BPRM2O4","title":"Large Exercise Mat Large Workout Mat With A Unique Fabric Finish That Provides Excellent Traction And Floor Protection. Can Be Used In Conjunction With The Square36 YOGA Mat. The Square36 CARDIO Mat - The BIG Exercise Mat- Step Into the Square.","price":159.99,"brand":"Reebok"},{"productId":"1380","asin":"B000EQCVQ6","title":"Eureka! Apex 2XT Two-Person Tent","price":175.16,"brand":"Topper_(sports)"},{"productId":"9665","asin":"B004H4VSI8","title":"IRONMAN HIGH CAPACITY GRAVITY 3000 INVERSION TABLE","price":239.8,"brand":"Wilson_Sporting_Goods"},{"productId":"7603","asin":"B0017SC9H6","title":"Zero Tolerance Combat Folding Knife","price":199.0,"brand":"Tramontana_(sports_car)"},{"productId":"887","asin":"B00BCLL8C0","title":"SportRack SR7018 Vista XL Rear Opening Cargo Box 18-Cubic Feet","price":284.46,"brand":"Fischer_(company)"}],"Livreur":{"Id_livreur":"7548ac7b-5bf7-4c5a-a80d-5390b73b9b90","Nom":"Henry Wilde","gender":"Male","phone":"3-624-033-5243"}},
{"orderid":"125e7887-c41d-4a5a-aa6b-d2a3679d58e7","Id_Cust":"10995116278711","Id_Date":"477","TotalPrice":1624.98,"Orderline":[{"productId":"6250","asin":"B005DYQ9V2","title":"Invicta Men s 1463 Reserve Collection Chronograph Silver Dial Stainless Steel Watch","price":199.95,"brand":"Keuka_(brand)"},{"productId":"1138","asin":"B000AMRN12","title":"Stamina AeroPilates Reformer with Free-Form Cardio Rebounder","price":449.0,"brand":"Topper_(sports)"},{"productId":"7575","asin":"B005D7FXMA","title":"Cygolite Expilion 400 Usb Rechargeable Headlight with Quick Release Li-Ion Battery Stick and Wall Charger","price":113.99,"brand":"Derbi"},{"productId":"6529","asin":"B005Z2CYX4","title":"Zero Tolerance ZT0550 Hinderer Design Folding Knife","price":160.0,"brand":"CA_Sports"},{"productId":"7125","asin":"B007INYN6O","title":"Barnett Zombie 350 CRT Crossbow","price":702.04,"brand":"Elan_Snowboards"}],"Livreur":{"Id_livreur":"a1060a87-fdff-4acc-b728-c805b72b0740","Nom":"Percy Thornton","gender":"Male","phone":"5-744-522-0782"}},
{"orderid":"b259eafc-2f47-47dc-927b-5b6d6edbba1d","Id_Cust":"10995116278711","Id_Date":"617","TotalPrice":1484.76,"Orderline":[{"productId":"7667","asin":"B008W1TUAK","title":"MAGPUL ACS CARB STOCK MIL-SPEC BLACK","price":98.99,"brand":"POC_Sports"},{"productId":"6406","asin":"B001CJX50K","title":"Kinetic Rock n Roll Trainer w/Road Resistance Unit","price":389.99,"brand":"MYLAPS_Sports_Timing"},{"productId":"1883","asin":"B000O8Z7TW","title":"KNEX Education - Exploring Machines","price":126.79,"brand":"TRYMAX"},{"productId":"4158","asin":"B003AQKQAA","title":"Vortex Viper PST 6-24x50 Rifle PST-624S1-M","price":749.0,"brand":"Fila_(company)"},{"productId":"7578","asin":"B00D43A93I","title":"Under Armour Hammer 8600014-4808 Polarized Wrap Sunglasses","price":119.99,"brand":"Derbi"}],"Livreur":{"Id_livreur":"eb135eef-7ee2-4d90-b073-cbd4570935b3","Nom":"Violet Adler","gender":"Female","phone":"5-466-075-2676"}},
{"orderid":"b5755c1e-fc3f-4481-a175-edfc82290ea5","Id_Cust":"10995116278711","Id_Date":"208","TotalPrice":2487.71,"Orderline":[{"productId":"965","asin":"B000O3AVNY","title":"Advanced Elements AdvancedFrame Expedition Kayak","price":599.99,"brand":"Donnay_(sports)"},{"productId":"6888","asin":"B0029KL3S2","title":"Precor 240i Commercial Series StretchTrainer","price":704.2,"brand":"Elan_(company)"},{"productId":"6439","asin":"B004PJ1J4S","title":"Invicta Men s 6566 Subaqua Noma IV Collection Chronograph Black Polyurethane Watch","price":340.65,"brand":"MYLAPS_Sports_Timing"},{"productId":"9503","asin":"B0034PXYRY","title":"Eotech EXPS3-0 Holographic Weapon Site","price":606.92,"brand":"EA_Sports"},{"productId":"7740","asin":"B00271ERVI","title":"Crimson Trace Lasergrip for Ruger Lcr","price":235.95,"brand":"BURRDA"}],"Livreur":{"Id_livreur":"c75a672e-37cf-498e-8bf9-8dae7857165f","Nom":"Renee Eyres","gender":"Female","phone":"6-311-624-4031"}},
{"orderid":"8a129f4e-e736-4919-9a22-c98373f1df3b","Id_Cust":"10995116278711","Id_Date":"390","TotalPrice":1233.67,"Orderline":[{"productId":"1929","asin":"B004P4HH8U","title":"Barnett Ghost 350 CRT Crossbow Package (Quiver 3 - 20-Inch Arrows and Illuminated 3x32mm Scope)","price":509.99,"brand":"TRYMAX"},{"productId":"2940","asin":"B004IIZVQ8","title":"Barnett Wildcat C5 Crossbow Package (Quiver 3 - 20-Inch Arrows and Premium Red Dot Sight)","price":342.99,"brand":"Volkl"},{"productId":"3174","asin":"B006U0YZPA","title":"Bushnell X-8 6MP Trail Camera with Night Vision and Field Scan","price":136.94,"brand":"Daei_Sport"},{"productId":"6527","asin":"B003MA1SSI","title":"Sadlak Industries M14 National Match Spring Guide","price":40.75,"brand":"CA_Sports"},{"productId":"1759","asin":"B007L4ZGXE","title":"Zero Tolerance ZT0560 Hinderer Design Black Folding Knife","price":203.0,"brand":"Olympikus"}],"Livreur":{"Id_livreur":"2ccb39c1-5c1a-46d8-b394-fc887bd4cecd","Nom":"Faith Flanders","gender":"Female","phone":"2-586-775-3278"}},
{"orderid":"bb671476-5f67-414a-9b84-77498b05f0bd","Id_Cust":"10995116278711","Id_Date":"205","TotalPrice":1601.81,"Orderline":[{"productId":"810","asin":"B009K28INA","title":"Schwinn 425 Elliptical Trainer (2013)","price":599.99,"brand":"Fischer_(company)"},{"productId":"6802","asin":"B000WY8ZHO","title":"Luminox Men s Navy Seal ColorMark Watch 3051","price":229.09,"brand":"Onda_(sportswear)"},{"productId":"3178","asin":"B007O5B0LC","title":"Weslo Cadence G 5.9 Treadmill","price":351.0,"brand":"Daei_Sport"},{"productId":"2126","asin":"B0025YCYK8","title":"Spyderco Temperence 2 Canvas Micarta Plain Edge Knife","price":207.72,"brand":"Li-Ning"},{"productId":"6736","asin":"B002QWB8HY","title":"CamelBak BFM 3L Backpack","price":214.01,"brand":"Onda_(sportswear)"}],"Livreur":{"Id_livreur":"629b2edd-8bd4-4d50-8e63-33f4904ffd2c","Nom":"Stephanie Garcia","gender":"Female","phone":"6-618-288-6820"}},
{"orderid":"d3016947-f478-4782-941d-37132482a10a","Id_Cust":"10995116278711","Id_Date":"694","TotalPrice":1534.78,"Orderline":[{"productId":"3029","asin":"B00AJK9CW8","title":"Seiko Men s SRP307 Classic Automatic Dive Watch","price":192.87,"brand":"Kettler"},{"productId":"2964","asin":"B004V956P0","title":"Crosman Optimus Break Barrel Air Rifle (.177)","price":91.98,"brand":"Volkl"},{"productId":"4115","asin":"B003UNZHNY","title":"Schwinn A10 Upright Exercise Bike (2011)","price":199.99,"brand":"Fila_(company)"},{"productId":"6853","asin":"B0081MPIBA","title":"ETEK4 - Planet Eclipse Etek 4 LT / AM Paintball Guns","price":499.95,"brand":"Elan_(company)"},{"productId":"893","asin":"B001AS697O","title":"Bowflex PR1000 Home Gym","price":549.99,"brand":"Fischer_(company)"}],"Livreur":{"Id_livreur":"787192a7-8417-44a9-a5bb-dd9060afd524","Nom":"Isla Denton","gender":"Female","phone":"1-260-416-8800"}},
{"orderid":"30dd4a79-4dac-4043-8fbc-e746e78aced3","Id_Cust":"10995116278711","Id_Date":"702","TotalPrice":1329.04,"Orderline":[{"productId":"2726","asin":"B005SSWKMK","title":"Casio Men s PRW2500T-7CR Pathfinder Triple Sensor Tough Solar Digital Multi-Function Titanium Pathfinder Casual Watch","price":266.12,"brand":"Reebok"},{"productId":"6563","asin":"B0014NDXGA","title":"Bushnell Tour V2 Standard Edition Golf Laser Rangefinder","price":290.06,"brand":"Onda_(sportswear)"},{"productId":"4856","asin":"B001QD48GC","title":"FreeForm Hideaway Home Gym","price":641.93,"brand":"ASICS"},{"productId":"7653","asin":"B007ZNUG9K","title":"Blackhawk Black TraverseTrack Bipod - 10 - BH71BP10BK","price":50.98,"brand":"POC_Sports"},{"productId":"4166","asin":"B00AJT98OQ","title":"Universal Brass Catcher","price":79.95,"brand":"Fila_(company)"}],"Livreur":{"Id_livreur":"5acb6f8a-fb71-4b32-b40d-fde782d70600","Nom":"Belinda Marshall","gender":"Female","phone":"6-671-643-4273"}},

我如何計算每個日期的總價格？

以下查詢將連接兩個表並根據訂單日期返回總和：

db.order_fact.aggregate([
 {
   "$lookup": {
     "from": "date",
     "localField": "Id_Date",
     "foreignField": "Id_Date",
     "as": "dateDetails"
   }
 },
 {
   "$unwind": "$dateDetails"
 },
 {
   "$group": {
     "_id": {
       "date": "$dateDetails.OrderDate"
     },
     "totalPrice": {
       "$sum": "$TotalPrice"
     }
   }
 }
])

根據此處的 MongoDB 文件在3.2 版中更改：$sum在$group和$project階段可用。在以前的 MongoDB 版本中，$sum僅在$group階段可用。

在$group階段使用時，$sum 具有以下語法，並返回將指定表達式應用於一組文件中的每個文件所產生的所有數值的總和，這些文件按鍵共享同一組：

{ $sum: <expression> }

例如在 $group 階段使用

考慮包含以下文件的銷售集合：

{ "_id" : 1, "item" : "abc", "price" : 10, "quantity" : 2, "date" : ISODate("2014-01-01T08:00:00Z") }
{ "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1, "date" : ISODate("2014-02-03T09:00:00Z") }
{ "_id" : 3, "item" : "xyz", "price" : 5, "quantity" : 5, "date" : ISODate("2014-02-03T09:05:00Z") }
{ "_id" : 4, "item" : "abc", "price" : 10, "quantity" : 10, "date" : ISODate("2014-02-15T08:00:00Z") }
{ "_id" : 5, "item" : "xyz", "price" : 5, "quantity" : 10, "date" : ISODate("2014-02-15T09:05:00Z") }

按日期欄位的日期和年份對文件進行分組，以下操作使用 $sum 累加器來計算每組文件的總金額和計數。

db.sales.aggregate(
  [
    {
      $group:
        {
          _id: { day: { $dayOfYear: "$date"}, year: { $year: "$date" } },
          totalAmount: { $sum: { $multiply: [ "$price", "$quantity" ] } },
          count: { $sum: 1 }
        }
    }
  ]
)

該操作返回以下結果：

{ "_id" : { "day" : 46, "year" : 2014 }, "totalAmount" : 150, "count" : 2 }
{ "_id" : { "day" : 34, "year" : 2014 }, "totalAmount" : 45, "count" : 2 }
{ "_id" : { "day" : 1, "year" : 2014 }, "totalAmount" : 20, "count" : 1 }

在不存在的欄位上使用$sum會返回 的值0。以下操作嘗試對 qty 進行 $sum：

db.sales.aggregate(
  [
    {
      $group:
        {
          _id: { day: { $dayOfYear: "$date"}, year: { $year: "$date" } },
          totalAmount: { $sum: "$quantity" },
          count: { $sum: 1 }
        }
    }
  ]
)

操作返回：

{ "_id" : { "day" : 46, "year" : 2014 }, "totalAmount" : 0, "count" : 2 }
{ "_id" : { "day" : 34, "year" : 2014 }, "totalAmount" : 0, "count" : 2 }
{ "_id" : { "day" : 1, "year" : 2014 }, "totalAmount" : 0, "count" : 1 }

在$project階段使用時，$sum 返回每個文件的指定表達式或表達式列表的總和，並具有以下兩種語法之一：

  • $sum 有一個指定的表達式作為其操作數：

{$總和：}

  • $sum 有一個指定表達式的列表作為其操作數：

{ $總和：

$$ , … $$ }

例如在 $project 階段使用

學生集合包含以下文件：

{ "_id": 1, "quizzes": [ 10, 6, 7 ], "labs": [ 5, 8 ], "final": 80, "midterm": 75 }
{ "_id": 2, "quizzes": [ 9, 10 ], "labs": [ 8, 8 ], "final": 95, "midterm": 80 }
{ "_id": 3, "quizzes": [ 4, 5, 5 ], "labs": [ 6, 5 ], "final": 78, "midterm": 70 }

以下範例使用 $ sum in the $ 項目階段計算測驗總分、實驗總分、期中總分：

db.students.aggregate([
  {
    $project: {
      quizTotal: { $sum: "$quizzes"},
      labTotal: { $sum: "$labs" },
      examTotal: { $sum: [ "$final", "$midterm" ] }
    }
  }
])

操作結果如下文件：

{ "_id" : 1, "quizTotal" : 23, "labTotal" : 13, "examTotal" : 155 }
{ "_id" : 2, "quizTotal" : 19, "labTotal" : 16, "examTotal" : 175 }
{ "_id" : 3, "quizTotal" : 14, "labTotal" : 11, "examTotal" : 148 }

進一步你參考這裡

引用自：https://dba.stackexchange.com/questions/243947

相關問答

Mongodb

連接操作後根據值過濾 - MongoDB

  • November 28, 2019
檢視問答
Mongodb

mongodb conf節點無法啟動 - 兼容性錯誤

  • April 6, 2022
檢視問答
Mongodb

‘Ftd_CF噸dCftdcserverStatus 非常慢” - MongoDB 4.0.4

  • March 5, 2022
檢視問答
Mongodb

將嵌套 json 結構中 mongo db 中的數據類型欄位 int 更新為 decimal128

  • January 12, 2022
檢視問答
Mongodb

無法在 Mongodb 4.0.5 上更新使用者密碼（使用 SCRAM-SHA-256 需要未消化的密碼）

  • October 23, 2021
檢視問答
Mongodb

mongodump 無法連接副本集

  • May 25, 2021
檢視問答