I'm trying to get all orders between two dates, group them by day of week, then average them. Currently the code looks like this:
export const getOrderValuesBetweenTwoDates = async (
from: number,
to: number,
) => {
// from, and to are guaranteed to be Mondays, 00:00
const orders = await OrderModel.find({
createdAt: { $lt: to, $gte: from },
}).exec();
const totalOfDaysOfWeek = [0, 0, 0, 0, 0, 0, 0];
orders.forEach((order) => {
const daysSinceFrom = (order.createdAt - from) / dayInMilliseconds;
const dayOfWeek = Math.floor(daysSinceFrom) % 7;
totalOfDaysOfWeek[dayOfWeek] =
(totalOfDaysOfWeek[dayOfWeek] || 0) + order.value;
});
const numberOfWeeks = Math.floor((to - from) / dayInMilliseconds / 7);
const averageOfDaysOfWeek = totalOfDaysOfWeek.map((v) =>
Number((v / numberOfWeeks).toFixed(2)),
);
return averageOfDaysOfWeek;
};
However, this is not really performant, and I guess if it could be written in aggregation, it would be. Is that possible to convert the above into aggregation?
Sample input (2 weeks):
[
// 1st mon (total 5)
{ createdAt: 345600000, value: 2 },
{ createdAt: 345600000, value: 3 },
// 1st tue
{ createdAt: 432000000, value: 1 },
// 1st wed
{ createdAt: 518400000, value: 1 },
// 1st thu
{ createdAt: 604800000, value: 1 },
// 1st fri
{ createdAt: 691200000, value: 1 },
// 1st sat
{ createdAt: 777600000, value: 1 },
// 1st sun (2 total)
{ createdAt: 864000000, value: 2 },
// 2nd mon (1 total)
{ createdAt: 950400000, value: 1 },
// 2nd tue
{ createdAt: 1036800000, value: 1 },
// 2nd wed
{ createdAt: 1123200000, value: 1 },
// 2nd thu
{ createdAt: 1209600000, value: 1 },
// 2nd fri
{ createdAt: 1296000000, value: 1 },
// 2nd sat
{ createdAt: 1382400000, value: 1 },
// 2nd sun (4 total)
{ createdAt: 1468800000, value: 1 },
{ createdAt: 1468800000, value: 1 },
{ createdAt: 1468800000, value: 2 },
]
In the above example I've made 2 special cases, for Monday, and Sunday. There are multiple orders for those days.
For the first Monday there is an order with value 2, and 3, to 5 in total. For the second Monday there is only one order with value 1. The average should be 3.
For Sunday, the first one, there's an order with value 2, and for the second Sunday, there are 3 orders with total value of 4. I'm expecting the average to be 3.
I'm expecting the result to be [3,1,1,1,1,1,3]
$sum to get sum of same day of weekweekDay in output will be number between 1 (Sunday) and 7 (Saturday).
test it at mongoPlayground
db.collection.aggregate([
{
"$addFields": {
createdAt: {
"$dateToString": {
"date": {
"$toDate": "$createdAt"
},
"format": "%Y-%m-%d"
}
}
}
},
{
"$group": {
"_id": "$createdAt",
"value": {
"$sum": "$value"
}
}
},
{
"$addFields": {
"createdAt": {
$dayOfWeek: {
"$toDate": "$_id"
}
}
}
},
{
"$group": {
"_id": "$createdAt",
"average": {
"$avg": "$value"
}
}
},
{
"$project": {
_id: 0,
weekDay: "$_id",
average: 1
}
}
])