从平衡值数组中计算移动值
从平衡值数组中计算移动值
提问于 2019-02-07 21:04:11
有人知道如何计算文档之间的值差吗？因此，在当前的文档值中扣除以前的文档值来创建一个新的运动值。每个文档表示一个月和一年的余额。
我在一个文档中有一套account_balances，日期是每个月底。它们代表的是会计应用程序中的总分类账，在该应用程序中，整合只提供余额，而不是逐月变动。
如何计算一个文档数组和上个月文档的余额值的差异？
要正确分组的参数是_id.company、_id.connection、_id.object_snapshot_date，然后是account_balances.account_id和account_balances.value_type。
我想从2018-05-30年从2018-06-31文档的total_value中扣除每个帐户的total_value值。这里可能有多个与全年相关的文件。
我想要的是同样的文档，但是六月的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
}
]
}回答 1
Stack Overflow用户
回答已采纳
发布于 2019-02-12 22:44:33
我假设您总是可以设置一个过滤条件，以保证在$match阶段之后只保留两个文档(如下面所示)。然后可以使用$unwind获取每个文档的单个account_balance。在下一个阶段，您可以通过$sort通过snapshot_date。然后您可以通过$group通过account_name和$push获得所有的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结果，其中n的大小为balances)。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/54582251
复制相关文章
相似问题
腾讯云开发者
