首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >问答首页 >如何从mongodb中已有的数据生成时序数据

如何从mongodb中已有的数据生成时序数据
EN

Stack Overflow用户
提问于 2020-09-15 21:56:44
回答 2查看 191关注 0票数 0

我必须生成这个报告。

在我的MongoDB数据库中,有一个订单集合,如下所示:

代码语言:javascript
运行
复制
[
  {
    _id: "mongoId", // 5f5ea6276ba53b06944de28c
    createdAt: "2020-09-15T23:07:19.370Z",
    totalPrice: 34, // is calculated from the client (quantity * price)
    orderDetail: [
      {
        _id: "product-A-Id", // 5f5ea403e91ed91a44b62c92
        quantity: 4,
        price: 5.5,
      },
      {
        _id: "product-B-Id",
        quantity: 1,
        price: 3.5,
      },
      {
        _id: "product-C-Id",
        quantity: 1,
        price: 8.5,
      },
    ],
  },
  {
    _id: "mongoId",
    createdAt: "2020-09-15T23:08:20.370Z",
    totalPrice: 15.5,
    orderDetail: [
      {
        _id: "product-C-Id",
        quantity: 3,
        price: 3,
      },
      {
        _id: "product-D-Id",
        quantity: 1,
        price: 6.5,
      },
    ],
  },
  {
    _id: "mongoId",
    createdAt: "2020-09-15T23:09:25.370Z",
    totalPrice: 22.5,
    orderDetail: [
      {
        _id: "product-D-Id",
        quantity: 5,
        price: 4.5,
      },
    ],
  },
]

为此,我必须从timestamp now (在每个请求中)每两小时生成一次时间序列数据,所需的响应示例如下:

代码语言:javascript
运行
复制
[
  {
    id: "sales",
    data: [
      {
        x: "00:00",
        y: 150,
      },
      {
        x: "22:00",
        y: 100,
      },
      {
        x: "20:00",
        y: 150,
      },
      {
        x: "18:00",
        y: 50,
      },
      {
        x: "16:00",
        y: 100,
      },
    ],
  },
]

使用nodejs和类似express的框架,我可以生成过去2小时的销售额:

代码语言:javascript
运行
复制
const valueDateRange = 2 * 60 * 60 * 1000; // 2 hours
const currentPeriod = new Date(new Date().getTime() - valueDateRange);

// The last 2 hours sales
const calculateTotalSales = await Order.aggregate([
  {
    $match: { createdAt: { $gte: currentPeriod } },
  },
  {
    $group: { _id: null, TotalAmount: { $sum: "$totalPrice" } },
  },
]);

但是现在如何生成每2小时一次的时间序列数据,非常感谢大家的关注

EN

回答 2

Stack Overflow用户

发布于 2020-09-15 23:18:58

我找到的最干净的方法是使用模运算符重新处理每个文档日期,按小时块对它们进行分组。如果将来需要更大的块,您可以很容易地进行更改。

https://mongoplayground.net/p/aYAJKL_5dMD (我添加了额外的样本数据)

代码语言:javascript
运行
复制
db.orders.aggregate([
  {$addFields: {
    date: {
      $let: {
        vars: {
          hour: {$hour: '$createdAt'},
          remainder: {$mod: [
            {$hour: '$createdAt'},
            2 // Two hour blocks, can be 2,3,4,6,8,12
          ]},
        },
        in: {
          $dateFromParts: {
            year: {$year: '$createdAt'},
            month: {$month: '$createdAt'},
            day: {$dayOfMonth: '$createdAt'},
            hour: {$subtract: ['$$hour', '$$remainder']}
         }
        }
      }    
    }
  }},
  {$group: {
    _id: '$date',
    x: {$last: '$date'},
    y: {$sum: '$totalPrice'}
  }}
]);

更新:

在再次阅读您的问题后,我认为您正在寻找每小时的总计,而不是一天。您可以这样做:

https://mongoplayground.net/p/cpW9JKllDIN

代码语言:javascript
运行
复制
const totals = await db.orders.aggregate([
  {$addFields: {
    hour: {
      $let: {
        vars: {
          hour: {$hour: '$createdAt'},
          remainder: {$mod: [
            {$hour: '$createdAt'},
            2 // Two hour blocks, can be 2,3,4,6,8,12
          ]},
        },
        in: {$subtract: ['$$hour', '$$remainder']}
      }
    }
  }},
  {$group: {
    _id: '$hour',
    x: {$last: '$hour'},
    y: {$sum: '$totalPrice'}
  }}
])

然后,要包括没有销售额的小时数,您可以映射一个数组:

代码语言:javascript
运行
复制
let points = [0, 2, 4, 6, 8, 10, 12, 14, 16, 18, 20, 22].map(x => {
  let total = totals.find(t => t.x === x);
  return {
    x: `${x < 10 ? `0${x}` : x}:00`,
    y: total ? total.y : 0
  };
});
票数 1
EN

Stack Overflow用户

发布于 2020-09-17 00:52:51

经过多次尝试,我最终得到了这段代码,我希望它能在未来对某些人有所帮助,以证明它,确保您在发出请求时拥有更新的数据,否则结果为0,因为将日期与当前时刻进行比较。

Mongo Playgournd

node.js: v12.5.0 express:"^4.17.1“Mongodb版本v4.2.3

代码语言:javascript
运行
复制
router.get("/orders", async (req, res) => {
  let valueDateRange = 24 * 60 * 60 * 1000; // 24 hours

  const current = new Date();
  const previous = new Date(new Date().getTime() - valueDateRange);

  try {
    const order = await Order.aggregate([
      {
        $match: {
          createdAt: { $lt: current, $gte: previous },
        },
      },
      {
        $sort: { createdAt: 1 },
      },
      {
        $group: {
          _id: null,
          docs: {
            $push: {
              createdAt: "$createdAt",
              totalPrice: "$totalPrice",
              missing: false,
            },
          },
          start: {
            $first: {
              $toInt: {
                $divide: [
                  {
                    $subtract: [
                      { $toLong: "$$NOW" },
                      { $multiply: [24, 60, 60, 1000] },
                    ],
                  },
                  1000,
                ],
              },
            },
          },
          end: {
            $last: { $toInt: { $divide: [{ $toLong: "$$NOW" }, 1000] } },
          },
        },
      },
      {
        $addFields: {
          docs: {
            $map: {
              input: {
                $range: [
                  { $toInt: "$start" },
                  { $add: [{ $toInt: "$end" }, 7200] }, // 2 hours range
                  7200,
                ],
              },
              as: "ts",
              in: {
                ts_exists: {
                  $filter: {
                    input: "$docs",
                    as: "d",
                    cond: {
                      $and: [
                        {
                          $gte: [
                            {
                              $toInt: {
                                $divide: [{ $toLong: "$$d.createdAt" }, 1000],
                              },
                            },
                            { $subtract: ["$$ts", 7200] },
                          ],
                        },
                        {
                          $lt: [
                            {
                              $toInt: {
                                $divide: [{ $toLong: "$$d.createdAt" }, 1000],
                              },
                            },
                            "$$ts",
                          ],
                        },
                      ],
                    },
                  },
                },
                ts: "$$ts",
              },
            },
          },
        },
      },
      {
        $unwind: "$docs",
      },
      {
        $project: {
          _id: 0,
          y: {
            $reduce: {
              input: "$docs.ts_exists",
              initialValue: 0,
              in: { $add: ["$$value", "$$this.totalPrice"] },
            },
          },
          x: {
            $dateToString: {
              format: "%Y-%m-%d %H:%M",
              date: { $toDate: { $multiply: ["$docs.ts", 1000] } },
            },
          },
        },
      },
    ]);

    const firstDeleted = order.shift(); // the first always send 0, therefore I delete it

    res.send(order);
  } catch (error) {
    res.send(error);
  }
});

每2小时返回一次数据,如果没有数量,则放入0

代码语言:javascript
运行
复制
[
    {
        "y": 0,
        "x": "2020-09-15 18:24"
    },
    {
        "y": 0,
        "x": "2020-09-15 20:24"
    },
    {
        "y": 0,
        "x": "2020-09-15 22:24"
    },
    {
        "y": 0,
        "x": "2020-09-16 00:24"
    },
    {
        "y": 0,
        "x": "2020-09-16 02:24"
    },
    {
        "y": 0,
        "x": "2020-09-16 04:24"
    },
    {
        "y": 0,
        "x": "2020-09-16 06:24"
    },
    {
        "y": 0,
        "x": "2020-09-16 08:24"
    },
    {
        "y": 0,
        "x": "2020-09-16 10:24"
    },
    {
        "y": 0,
        "x": "2020-09-16 12:24"
    },
    {
        "y": 0,
        "x": "2020-09-16 14:24"
    },
    {
        "y": 3,
        "x": "2020-09-16 16:24"
    }
]
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/63903485

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档