mongodb aggregate、group化のような集計 #mongodb #node #javascript
■ 概要:
前の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を利用した。レポート機能の追加( 参考 )
・データ構成
・売上レポードで、商品(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();
}
});...
この記事が気に入ったらサポートをしてみませんか？