MongoDB中基于时间字段计算Total_ti的实现方法咨询

需求说明 #

需要基于ti字段计算Total_ti，计算Total_ti时需取首个文档的ts字段值、末尾文档的te字段值作为汇总记录的对应字段值，其余原有文档内容保持不变。

输入文档示例 #

[
  {
    "ts": "2021-09-04T12:18:41Z",
    "te": "2021-09-04T14:59:50Z",
    "td": "2021-09-04",
    "ti": 2.69
  },
  {
    "ts": "2021-09-04T15:00:00Z",
    "te": "2021-09-04T15:05:00Z",
    "td": "2021-09-04",
    "ti": 0.08
  },
  {
    "ts": "2021-09-04T15:40:00Z",
    "te": "2021-09-04T16:00:00Z",
    "td": "2021-09-04",
    "ti": 0.33
  }
]

预期输出文档示例 #

[
 {
    "ts": "2021-09-04T12:18:41Z",
    "te": "2021-09-04T16:00:00Z",
    "Total_ti": 3.10
    },
  {
    "ts": "2021-09-04T12:18:41Z",
    "te": "2021-09-04T14:59:50Z",
    "td": "2021-09-04",
    "ti": 2.69
  },
  {
    "ts": "2021-09-04T15:00:00Z",
    "te": "2021-09-04T15:05:00Z",
    "td": "2021-09-04",
    "ti": 0.08
  },
  {
    "ts": "2021-09-04T15:40:00Z",
    "te": "2021-09-04T16:00:00Z",
    "td": "2021-09-04",
    "ti": 0.33
  }
]

Python实现代码 #

import json
from decimal import Decimal, ROUND_HALF_UP

def process_docs(doc_list):
    # 计算ti总和，用Decimal避免浮点数精度误差，保留两位小数
    total = sum(Decimal(str(item["ti"])) for item in doc_list)
    total_ti = float(total.quantize(Decimal("0.00"), rounding=ROUND_HALF_UP))
    # 构造首条汇总记录
    summary = {
        "ts": doc_list[0]["ts"],
        "te": doc_list[-1]["te"],
        "Total_ti": total_ti
    }
    # 汇总记录前置，拼接原有数据
    return [summary] + doc_list

# 测试运行
if __name__ == "__main__":
    # 替换这里的输入内容即可处理实际数据
    input_data = json.loads("""
    [
      {
        "ts": "2021-09-04T12:18:41Z",
        "te": "2021-09-04T14:59:50Z",
        "td": "2021-09-04",
        "ti": 2.69
      },
      {
        "ts": "2021-09-04T15:00:00Z",
        "te": "2021-09-04T15:05:00Z",
        "td": "2021-09-04",
        "ti": 0.08
      },
      {
        "ts": "2021-09-04T15:40:00Z",
        "te": "2021-09-04T16:00:00Z",
        "td": "2021-09-04",
        "ti": 0.33
      }
    ]
    """)
    output = process_docs(input_data)
    print(json.dumps(output, indent=2, ensure_ascii=False))

注意要点 #

• 用Decimal处理浮点数求和，避免出现0.1+0.2=0.30000000000000004这类精度问题
• 原有文档内容完全保留，不会做任何修改
• 汇总记录固定放在返回数组的第一个位置

内容的提问来源于stack exchange，提问作者bc110402922