我必须生成这个报告。
在我的MongoDB数据库中,有一个订单集合,如下所示:
[
{
_id: "mongoId", // 5f5ea6276ba53b06944de28c
createdAt: "2020-09-15T23:07:19.370Z",
totalPrice: 34, // is calculated from the client (quantity * price)
orderDetail: [
{
_id: "product-A-Id", // 5f5ea403e91ed91a44b62c92
quantity: 4,
price: 5.5,
},
{
_id: "product-B-Id",
quantity: 1,
price: 3.5,
},
{
_id: "product-C-Id",
quantity: 1,
price: 8.5,
},
],
},
{
_id: "mongoId",
createdAt: "2020-09-15T23:08:20.370Z",
totalPrice: 15.5,
orderDetail: [
{
_id: "product-C-Id",
quantity: 3,
price: 3,
},
{
_id: "product-D-Id",
quantity: 1,
price: 6.5,
},
],
},
{
_id: "mongoId",
createdAt: "2020-09-15T23:09:25.370Z",
totalPrice: 22.5,
orderDetail: [
{
_id: "product-D-Id",
quantity: 5,
price: 4.5,
},
],
},
]
为此,我必须从timestamp now (在每个请求中)每两小时生成一次时间序列数据,所需的响应示例如下:
[
{
id: "sales",
data: [
{
x: "00:00",
y: 150,
},
{
x: "22:00",
y: 100,
},
{
x: "20:00",
y: 150,
},
{
x: "18:00",
y: 50,
},
{
x: "16:00",
y: 100,
},
],
},
]
使用nodejs和类似express的框架,我可以生成过去2小时的销售额:
const valueDateRange = 2 * 60 * 60 * 1000; // 2 hours
const currentPeriod = new Date(new Date().getTime() - valueDateRange);
// The last 2 hours sales
const calculateTotalSales = await Order.aggregate([
{
$match: { createdAt: { $gte: currentPeriod } },
},
{
$group: { _id: null, TotalAmount: { $sum: "$totalPrice" } },
},
]);
但是现在如何生成每2小时一次的时间序列数据,非常感谢大家的关注
发布于 2020-09-15 23:18:58
我找到的最干净的方法是使用模运算符重新处理每个文档日期,按小时块对它们进行分组。如果将来需要更大的块,您可以很容易地进行更改。
https://mongoplayground.net/p/aYAJKL_5dMD (我添加了额外的样本数据)
db.orders.aggregate([
{$addFields: {
date: {
$let: {
vars: {
hour: {$hour: '$createdAt'},
remainder: {$mod: [
{$hour: '$createdAt'},
2 // Two hour blocks, can be 2,3,4,6,8,12
]},
},
in: {
$dateFromParts: {
year: {$year: '$createdAt'},
month: {$month: '$createdAt'},
day: {$dayOfMonth: '$createdAt'},
hour: {$subtract: ['$$hour', '$$remainder']}
}
}
}
}
}},
{$group: {
_id: '$date',
x: {$last: '$date'},
y: {$sum: '$totalPrice'}
}}
]);
更新:
在再次阅读您的问题后,我认为您正在寻找每小时的总计,而不是一天。您可以这样做:
https://mongoplayground.net/p/cpW9JKllDIN
const totals = await db.orders.aggregate([
{$addFields: {
hour: {
$let: {
vars: {
hour: {$hour: '$createdAt'},
remainder: {$mod: [
{$hour: '$createdAt'},
2 // Two hour blocks, can be 2,3,4,6,8,12
]},
},
in: {$subtract: ['$$hour', '$$remainder']}
}
}
}},
{$group: {
_id: '$hour',
x: {$last: '$hour'},
y: {$sum: '$totalPrice'}
}}
])
然后,要包括没有销售额的小时数,您可以映射一个数组:
let points = [0, 2, 4, 6, 8, 10, 12, 14, 16, 18, 20, 22].map(x => {
let total = totals.find(t => t.x === x);
return {
x: `${x < 10 ? `0${x}` : x}:00`,
y: total ? total.y : 0
};
});
发布于 2020-09-17 00:52:51
经过多次尝试,我最终得到了这段代码,我希望它能在未来对某些人有所帮助,以证明它,确保您在发出请求时拥有更新的数据,否则结果为0,因为将日期与当前时刻进行比较。
node.js: v12.5.0 express:"^4.17.1“Mongodb版本v4.2.3
router.get("/orders", async (req, res) => {
let valueDateRange = 24 * 60 * 60 * 1000; // 24 hours
const current = new Date();
const previous = new Date(new Date().getTime() - valueDateRange);
try {
const order = await Order.aggregate([
{
$match: {
createdAt: { $lt: current, $gte: previous },
},
},
{
$sort: { createdAt: 1 },
},
{
$group: {
_id: null,
docs: {
$push: {
createdAt: "$createdAt",
totalPrice: "$totalPrice",
missing: false,
},
},
start: {
$first: {
$toInt: {
$divide: [
{
$subtract: [
{ $toLong: "$$NOW" },
{ $multiply: [24, 60, 60, 1000] },
],
},
1000,
],
},
},
},
end: {
$last: { $toInt: { $divide: [{ $toLong: "$$NOW" }, 1000] } },
},
},
},
{
$addFields: {
docs: {
$map: {
input: {
$range: [
{ $toInt: "$start" },
{ $add: [{ $toInt: "$end" }, 7200] }, // 2 hours range
7200,
],
},
as: "ts",
in: {
ts_exists: {
$filter: {
input: "$docs",
as: "d",
cond: {
$and: [
{
$gte: [
{
$toInt: {
$divide: [{ $toLong: "$$d.createdAt" }, 1000],
},
},
{ $subtract: ["$$ts", 7200] },
],
},
{
$lt: [
{
$toInt: {
$divide: [{ $toLong: "$$d.createdAt" }, 1000],
},
},
"$$ts",
],
},
],
},
},
},
ts: "$$ts",
},
},
},
},
},
{
$unwind: "$docs",
},
{
$project: {
_id: 0,
y: {
$reduce: {
input: "$docs.ts_exists",
initialValue: 0,
in: { $add: ["$$value", "$$this.totalPrice"] },
},
},
x: {
$dateToString: {
format: "%Y-%m-%d %H:%M",
date: { $toDate: { $multiply: ["$docs.ts", 1000] } },
},
},
},
},
]);
const firstDeleted = order.shift(); // the first always send 0, therefore I delete it
res.send(order);
} catch (error) {
res.send(error);
}
});
每2小时返回一次数据,如果没有数量,则放入0
[
{
"y": 0,
"x": "2020-09-15 18:24"
},
{
"y": 0,
"x": "2020-09-15 20:24"
},
{
"y": 0,
"x": "2020-09-15 22:24"
},
{
"y": 0,
"x": "2020-09-16 00:24"
},
{
"y": 0,
"x": "2020-09-16 02:24"
},
{
"y": 0,
"x": "2020-09-16 04:24"
},
{
"y": 0,
"x": "2020-09-16 06:24"
},
{
"y": 0,
"x": "2020-09-16 08:24"
},
{
"y": 0,
"x": "2020-09-16 10:24"
},
{
"y": 0,
"x": "2020-09-16 12:24"
},
{
"y": 0,
"x": "2020-09-16 14:24"
},
{
"y": 3,
"x": "2020-09-16 16:24"
}
]
https://stackoverflow.com/questions/63903485
复制相似问题