时间戳

首页 > 解决方案 > 从平衡值数组计算运动值

mongodb - 从平衡值数组计算运动值

问题描述

有谁知道如何计算文档之间的值差异？因此，对当前单据价值减去先前单据价值以创建新价值的变动。每个文件代表一个月和一年的余额。

我在每个月底的文档中有一组 account_balances。它们代表会计应用程序的总账，其中集成仅提供余额而不是逐月移动。

如何计算一个文档数组和上个月文档的余额值的差异？

正确组合在一起的参数是 _id.company、_id.connection、_id.object_snapshot_date，然后是 account_balances.account_id 和 account_balances.value_type。

我想从 2018-06-31 文档的 total_value 中减去 2018-05-30 每个帐户的 total_value 的值。这里可能有多个与一整年相关的文档。

我想要得到的是相同的文档，但 6 月的 total_value 是运动而不是余额。

谢谢，马特

两个不同月份的文件示例：

{
"_id" : {
  "company" : " a8aa7d3f-cef8-4895-a83e-3087b4cf529c ",
  "connection" : "a4b52d3a-0c00-406f-9163-4b1d52df0271",
  "object_snapshot_date" : 20180603135959,
  "object_schema" : "timeline-balance",
  "object_class" : "trial-balance",
  "object_category" : "balance",
  "object_type" : "month",
  "object_origin_category" : "bookkeeping",
  "object_origin_type" : "accounting",
  "object_origin" : "Xero"
 },
"account_balances" : [
{
  "account_id" : "47cf9c6e-4ec7-4853-9efa-9e180636c96f",
  "account_name" : "Sales",
  "account_code" : "200",
  "account_class" : "revenue",
  "account_category" : "sales",
  "account_group" : "",
  "value_type" : "credit",
  "total_value" : 29928.96,
  "value_currency" : "NZD"
},
{
  "account_id" : "47cf9c6e-4ec7-4853-9efa-9e180636aa43",
  "account_name" : "Cost of Goods Sold",
  "account_code" : "300",
  "account_class" : "expense",
  "account_category" : "sales",
  "account_group" : "",
  "value_type" : "debit",
  "total_value" : 12452.50,
  "value_currency" : "NZD"
}
]
},
{
"_id" : {
  "company" : " a8aa7d3f-cef8-4895-a83e-3087b4cf529c ",
  "connection" : "a4b52d3a-0c00-406f-9163-4b1d52df0271",
  "object_snapshot_date" : 20180503035959,
  "object_schema" : "timeline-balance",
  "object_class" : "trial-balance",
  "object_category" : "balance",
  "object_type" : "month",
  "object_origin_category" : "bookkeeping",
  "object_origin_type" : "accounting",
  "object_origin" : "Xero"
 },
"account_balances" : [
{
  "account_id" : "47cf9c6e-4ec7-4853-9efa-9e180636c96f",
  "account_name" : "Sales",
  "account_code" : "200",
  "account_class" : "revenue",
  "account_category" : "sales",
  "account_group" : "",
  "value_type" : "credit",
  "total_value" : 24231.12,
  "value_currency" : "NZD"
},
{
  "account_id" : "47cf9c6e-4ec7-4853-9efa-9e180636aa43",
  "account_name" : "Cost of Goods Sold",
  "account_code" : "300",
  "account_class" : "expense",
  "account_category" : "sales",
  "account_group" : "",
  "value_type" : "debit",
  "total_value" : 6875.10,
  "value_currency" : "NZD"
}
]
}

预期输出将是这样的：

{
"_id" : {
  "company" : " a8aa7d3f-cef8-4895-a83e-3087b4cf529c ",
  "connection" : "a4b52d3a-0c00-406f-9163-4b1d52df0271",
  "object_snapshot_date" : 20180603135959,
  "object_schema" : "timeline-balance",
  "object_type" : "month",
  "object_origin_category" : "bookkeeping",
  "object_origin" : "Xero"
},
"account_movements" : [
 {
  "account_id" : "47cf9c6e-4ec7-4853-9efa-9e180636c96f",
  "account_name" : "Sales",
  "account_code" : "200",
  "account_class" : "revenue",
  "movement" : 5697.84
 },
 {
  "account_id" : "47cf9c6e-4ec7-4853-9efa-9e180636aa43",
  "account_name" : "Cost of Goods Sales",
  "account_code" : "200",
  "account_class" : "revenue",
  "movement" : 5577.4
 }
]
}

标签： mongodb

解决方案

我假设您始终可以设置一个过滤条件，以保证在$match阶段之后只剩下两个文档（如下所示）。然后，您可以使用$unwind获取account_balance每个文档的单个。在下一阶段，您可以$sort by snapshot_date。然后你可以通过$group byaccount_name和$push来获取 all balances。由于假设只有两个元素，您可以使用$subtract和$arrayElemAt来获得运动。

db.col.aggregate([
    {
        $match: {
            "_id.object_snapshot_date": {
                $gte: 20180500000000, 
                $lte: 20180630000000
            }
        }
    },
    {
        $unwind: "$account_balances"
    },
    {
        $sort: { "_id.object_snapshot_date": 1 }
    },
    {
        $group: {
            _id: "$account_balances.account_name",
            balances: { $push: "$account_balances.total_value" }
        }
    },
    {
        $project: {
            _id: 0,
            account_name: "$_id",
            movement: { $subtract: [ { $arrayElemAt: [ "$balances", 1 ] }, { $arrayElemAt: [ "$balances", 0 ] } ] }
        }
    }
])

输出：

{ "account_name" : "Cost of Goods Sold", "movement" : 5577.4 }
{ "account_name" : "Sales", "movement" : 5697.84 }

如果您需要更通用的解决方案（超过两个月），您可以将最后一个管道阶段替换为以下内容：

{
        $project: {
            _id: 0,
            account_name: "$_id",
            movement: {
                $map: {
                    input: { $range: [ 1, { $size: "$balances" } ] },
                    as: "index",
                    in: {
                        $subtract: [
                            { $arrayElemAt: [ "$balances", "$$index" ] },
                            { $arrayElemAt: [ "$balances", { $subtract: [ "$$index", 1 ] } ] }
                        ]
                    }
                }
            }
        }
    }

这将使用计算balances数组中所有值的差异（您将得到大小为 的n-1结果）。nbalances

推荐阅读