mongodb - 使用 Mongo DB 进行统计

Question

我有以下数据库结构：

{
    "uploadedAt": "2021-09-22T22:09:12.133Z",
    "paidAt: "2021-09-30T22:09:12.133Z",
    "amount": {
        "currency": "EUR",
        "expected": 70253,
        "paid": 0
    },
}

我想知道如何计算仍需要支付的总金额（预期 - 已支付），以及uploadAt 和paidAt 之间的平均日期。这适用于多条记录。

我获取数据的功能是（应更新标准以获取此数据）。

const invoiceParams = new FindParams();
invoiceParams.criteria = { company: company._id }

const invoices = await this.findAll(invoiceParams);

FindAll 函数如下所示：

  async findAll(
    params: FindParams,
    ability?: Ability,
    includeDeleted: boolean = false,
  ): Promise<Entity[]> {
    let queryCriteria: Criteria = params.criteria;
    let query: DocumentQuery<Entity[], Entity> = null;

    if (!includeDeleted) {
      queryCriteria = {
        ...queryCriteria,
        deleted: { $ne: true },
      };
    }

    try {
      if (ability) {
        ability.throwUnlessCan('read', this.entityModel.modelName);

        queryCriteria = {
          ...toMongoQuery(ability, this.entityModel.modelName),
          ...queryCriteria,
        };
      }

      query = this.entityModel.find(queryCriteria);

      if (params.populate) {
        query = query.populate(params.populate);
      }

      if (params.sort) {
        query = query.sort(params.sort);
      }

      if (params.select) {
        query = query.select(params.select);
      }

      return query.exec();
    } catch (error) {
      if (error instanceof ForbiddenError) {
        throw new ForbiddenException(error.message);
      }

      throw error;
    }
  }

更新：

const paymentTime = await this.invoiceModel.aggregate([
      {
          $group: {
              _id: "$account",
              averageSpread: { $avg: { $subtract: ["$paidAt", "$uploadedAt"] } },
              count: { $sum: 1 }
          }
      }
  ]);

标签： mongodbmongodb-querynestjs

Answer 1

试试这个聚合管道：

db.invoiceParams.aggregate([
   {
      $set: {
         expectedPaid: { $subtract: ["$amount.expected", "$amount.paid"] },
         averageDate: { $toDate: { $avg: [{ $toLong: "$uploadedAt" }, { $toLong: "$paidAt" }] } }
      }
   }
])