0

We want to calculate the average weight reduced and total weight loss users according to scales collection. The following steps describe our aggregation:

  1. Firstly, we calculate the average weight for each user by date.
  2. Then, we compare the first and last records for each user to determine if they have had a weight loss.
  3. Finally, we sum up all these weight loss values and divide them to total weight loss users to get the average weight loss.

Here is the MongoDB aggregation code using Typegoose:

const scaleStatistics = await this.scaleService
            .aggregate()
            .sort({
                user: 1,
                measuredAt: 1
            })
            .group({
                _id: {
                    date: '$measuredAt',
                    userId: '$user'
                },
                averageWeight: {
                    $avg: '$weight'
                }
            })
            .sort({
                '_id.userId': 1,
                '_id.date': 1
            })
            .group({
                _id: '$_id.userId',
                firstWeight: {
                    $first: '$averageWeight'
                },
                lastWeight: {
                    $last: '$averageWeight'
                }
            })
            .project({
                weightDifference: {
                    $subtract: ['$firstWeight', '$lastWeight']
                }
            })
            .match({
                weightDifference: {
                    $gt: 0
                }
            })
            .group({
                _id: null,
                totalWeightLoss: {
                    $sum: '$weightDifference'
                },
                totalWeightLossUsers: {
                    $sum: 1
                }
            })
            .project({
                totalWeightLossUsers: 1,
                averageWeightLoss: {
                    $round: [
                        {
                            $divide: ['$totalWeightLoss', '$totalWeightLossUsers']
                        },
                        2
                    ]
                }
            });

I have created a compound index for measuredAt and user but does not seem to improve at all.

Our collection design is like this:

@index({ user: 1, measuredAt: 1 })
export class Scale extends BaseModel {
    @prop()
    weight: number;

    ...other attributes

    @prop({ index: true, ref: () => User })
    user: Ref<User>;

    @prop({ index: true })
    measuredAt: Date;
}

How can I optimize this aggregation?

1

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.