mongodb aggregate、group化のような集計 #mongodb #node #javascript

knaka Tech-Blog

■ 概要:

前のmongodb　aggregate機能で、グループ集計のようなメモ
となります

■ 環境

mongoDb : 3.6.3
node 14
・npm
mongodb: 3.6.3

■ データの追加

db.book_groups.insertMany([
	{ dept_name: "dept-1", name: "group-1" },
	{ dept_name: "dept-1", name: "group-2" },
	{ dept_name: "dept-2", name: "group-11" },
]);

​

■　取得
・グループ化

db.book_groups.aggregate(
{
   $group : { _id: "$dept_name" }
});

>
{ "_id" : "dept-2" }
{ "_id" : "dept-1" }

・行数の計算,  $sum 各グループの、件数を集計できるようです

db.book_groups.aggregate(
{
   $group : { _id: "$dept_name", total : { $sum : 1 } }
});
>
{ "_id" : "dept-2", "total" : 1 }
{ "_id" : "dept-1", "total" : 2 }

■ node実装

・
https://gist.github.com/kuc-arc-f/f2270e996f6549a4976522975ab00c41#file-t3_group-js

async function join_test(){
   let client = await MongoClient.connect( "mongodb://localhost:27017" );
   const db = client.db("db1");
   var book_groups= []
   await db.collection("book_groups").aggregate([
   { $group : { _id: "$dept_name" ,total : { $sum : 1 } } },
   ]).toArray().then((docs) => {
//        console.log(docs);
       book_groups = docs;
   })
   .catch((err) => {
       console.log(err);
   })
   .then(() => {
       client.close();
   });
console.log( book_groups );
   book_groups.forEach(function(item){
//        console.log( item._id  );
   })
}

・
aggregate実行で、$groupを指定すると。
arrayが取得できました。

■ Update

・合計の、出力

・データ追加

db.test_orders.insertMany([
	{ dept_name: "dept-1", name: "group-1" , num: 11 },
	{ dept_name: "dept-1", name: "group-2" , num: 22 },
	{ dept_name: "dept-2", name: "group-12" , num: 1 },
]);

・各グループ毎の、合計
　dept_name列で、グループ化。　num合計値を計算、出力

db.test_orders.aggregate(
{
  $group : { _id: "$dept_name", total : { $sum : "$num" } }
});
>
{ "_id" : "dept-2", "total" : 1 }
{ "_id" : "dept-1", "total" : 33 }

■ Update 2020/12/12

・groupを利用した。レポート機能の追加( 参考 )

・データ構成

画像1


・売上レポードで、商品(breads) 毎の売上一覧
routes/api_report_breads.js

router.get('/index', async function(req, res) {
   try{
       var bread_orders = []
       const collection = await LibMongo.get_collection("bread_orders" )
       var page = req.query.page;
       LibPagenate.init();
       var page_info = LibPagenate.get_page_start(page);       
console.log( "page=",  page, page_info ); 
       await collection.aggregate([
           {$skip: page_info.start },
           {$limit: page_info.limit },
           {$sort: {created_at: -1} },
           {
               $lookup: {
                   from: "breads",
                   localField: "bread_id",
                   foreignField: "_id",
                   as: "breads"
               }
           },
           {
               $group : { _id: "$bread_id", num_total: { $sum : "$order_num" }} 
           }

       ]).toArray().then((docs) => {
           bread_orders = docs
console.log(bread_orders);
       })
       var bread_ids = await LibReportBreads.get_bread_ids(bread_orders)
//console.log(bread_ids);
       var breads = await LibReportBreads.get_bread_items(bread_ids)
       var report_items = await LibReportBreads.get_report_items(breads , bread_orders)
// console.log(report_items);
       var param = LibPagenate.get_page_items(report_items )
       res.json(param);
   } catch (err) {
       console.log(err);
       res.status(500).send();    
   }   
});

・注文( bread_orders) から、グループ化で商品と、注文数の合計計算後、
　breads単価から、各小計の計算レポートの様な処理で、リスト作成するAPI

■ Update 2020/12/14、　レポート機能を追加しました

localhost:3000/report_category
・report_category: カテゴリ単位、売り上げ集計
routes/api_report_category.js

router.get('/index', async function(req, res) {
   try{
       var bread_orders = []
       const collection = await LibMongo.get_collection("bread_orders" )
       var page = req.query.page;
       LibPagenate.init();
       var page_info = LibPagenate.get_page_start(page);       
       await collection.aggregate([
           {$sort: {created_at: -1} },
           {
               $lookup: {
                   from: "breads",
                   localField: "bread_id",
                   foreignField: "_id",
                   as: "breads"
               }
           },
           {
               $group : { _id: "$bread_id", num_total: { $sum : "$order_num" }} 
           }

       ]).toArray().then((docs) => {
           bread_orders = docs
//console.log(bread_orders);
       })
       var bread_ids = await LibReportCategory.get_bread_ids(bread_orders)
//console.log(bread_ids);
       var bread_category = await LibReportCategory.get_bread_items(bread_ids) 
//console.log(bread_category );
       var category_ids = await LibReportCategory.get_category_ids( bread_category )
//console.log(category_ids );
       var category_items = await LibReportCategory.get_category_items( category_ids )
//console.log(category_items );
       var report_items = await LibReportCategory.get_report_items(bread_category , bread_orders)
//console.log(report_items);
       var report_category = await LibReportCategory.get_report_category(category_items, report_items)
//console.log(report_category);        
       var param = LibPagenate.get_page_items(report_category )
       res.json(param);
   } catch (err) {
       console.log(err);
       res.status(500).send();    
   }   
});

・type: type単位の売り上げ集計
localhost:3000/report_type
routes/api_report_type.js

router.get('/index', async function(req, res) {
   try{
       var bread_orders = []
       const collection = await LibMongo.get_collection("bread_orders" )
       var page = req.query.page;
       LibPagenate.init();
       var page_info = LibPagenate.get_page_start(page);       
       await collection.aggregate([
           {$sort: {created_at: -1} },
           {
               $lookup: {
                   from: "breads",
                   localField: "bread_id",
                   foreignField: "_id",
                   as: "breads"
               }
           },
           {
               $group : { _id: "$bread_id", num_total: { $sum : "$order_num" }} 
           }
       ]).toArray().then((docs) => {
           bread_orders = docs
// console.log(bread_orders);
       })
       var bread_ids = await LibReportCategory.get_bread_ids(bread_orders)
//console.log(bread_ids);
       var bread_type = await LibReportType.get_bread_items(bread_ids) 
//console.log(bread_type );
       var type_ids = await LibReportType.get_type_ids(bread_type)
//console.log(type_ids );
       var type_items = await LibReportType.get_type_items(type_ids)
// console.log(type_items );
       var report_items = await LibReportType.get_report_items(bread_type , bread_orders)
//console.log(report_items );
       var report_type = await LibReportType.get_report_types( type_items, report_items)
console.log(report_type );
       var param = LibPagenate.get_page_items(report_type )
       res.json(param);
   } catch (err) {
       console.log(err);
       res.status(500).send();    
   }   
});



...


いいなと思ったら応援しよう！