按月和流失/收入图表汇总收入对MongoDB集合进行分组 [英] group MongoDB collection by month and aggregate revenue for churn/revenue chart

问题描述

db.orders（仅是小节摘录）

db.orders (only small excerpt)

{ "_id" : "20101200221", "order_date" : ISODate("2010-12-12T13:11:15Z"), "net_revenue" : 19.36, "customer_id" : "[e-mail]" }
{ "_id" : "20111003701", "order_date" : ISODate("2011-10-12T08:40:55Z"), "net_revenue" : 15.13, "customer_id" : "[e-mail]" }
{ "_id" : "20111204977", "order_date" : ISODate("2011-12-07T09:13:47Z"), "net_revenue" : 15.13, "customer_id" : "[e-mail]" }
{ "_id" : "20120206778", "order_date" : ISODate("2012-02-04T09:46:35Z"), "net_revenue" : 15.13, "customer_id" : "[e-mail]" }
{ "_id" : "20120408856", "order_date" : ISODate("2012-04-15T10:37:55Z"), "net_revenue" : 12.65, "customer_id" : "[e-mail]" }
{ "_id" : "20101200001", "order_date" : ISODate("2010-12-07T17:40:54Z"), "net_revenue" : 26.34, "customer_id" : "[e-mail]" }
{ "_id" : "20100100473", "order_date" : ISODate("2011-01-31T06:38:03Z"), "net_revenue" : 34.34, "customer_id" : "[e-mail]" }
{ "_id" : "20110601857", "order_date" : ISODate("2011-06-14T08:40:39Z"), "net_revenue" : 16.13, "customer_id" : "[e-mail]" }
{ "_id" : "20111003719", "order_date" : ISODate("2011-10-13T06:43:50Z"), "net_revenue" : 60.75, "customer_id" : "[e-mail]" }
{ "_id" : "20120912597", "order_date" : ISODate("2012-09-20T09:16:08Z"), "net_revenue" : 38.6, "customer_id" : "[e-mail]" }
{ "_id" : "20130101745", "order_date" : ISODate("2013-01-28T11:40:17Z"), "net_revenue" : 20.14, "customer_id" : "[e-mail]" }
{ "_id" : "20140615910", "order_date" : ISODate("2014-06-10T14:55:11Z"), "net_revenue" : 32.45, "customer_id" : "[e-mail]" }`

要创建客户流失/收入图，我需要按每个客户的初始订单/首次订单的月份对订单进行分组。在摘录中，您可以看到两个初始订单都是在12/2010完成的，因此以下所有订单都应属于该组，将其称为1，第2组将是拥有初始订单01/2011的每个人，等等。
每个组将是它自己的图，并像这样堆叠：

to create a churn/revenue graph i need to group orders by month of their initial/first order per customer. in the excerpt you can see both initial orders were done 12/2010 so all the following orders should fall into this group, lets call it 1, group 2 would be everyone who had his initial order 01/2011 etc. every group will be its own graph and will be stacked like this:

组中的订单也必须重新分组为几个月，并且必须将收入相加。
我希望我能弄清楚我要达到的目标！我整天都在努力寻找一种方法来生成图表数据。谢谢您的帮助！

the orders in the groups also have to be grouped again into months and the revenue has to be summed. i hope i could make it clear what i am trying to achieve! it's all day i am trying to figure out a way how to produce the data for the graph. Any help is appreciated!

推荐答案

如果我正确理解，则希望按客户和按月收入。

If I understand correctly you want the revenue by customer and by month.

聚合查询中的$ group运算符正是您在此处查找的内容。

The $group operator in an aggregate query is exactly what you're looking for here.

db.orders.aggregate([
    { $group : { 
            _id: { 
                customer_id: "$customer_id",
                month: {$month: "$order_date"}
            },
            revenue: {$sum: "$net_revenue"} 
        }
    }], ... );

使用上述查询，如果客户在不同月份订购，将出现多次。我不知道那是不是您想要的。相反，如果您想要该客户的所有订单的总价值，但也按他们第一次订购的时间进行排序，请执行以下操作：

With the above query, a customer will appear more than once if they order on separate months. I didn't understand if that is what you wanted or not. If instead you're wanting the total value for ALL orders from that customer, but sorted also by the FIRST time they ever ordered, do this instead:

db.orders.aggregate([
    { $sort: { order_date : 1 } }, //order by date so that $first is correct
    { $group : { 
            _id: { 
                customer_id: "$customer_id",                
            },
            month: { $first: {$month: "$order_date"} }
            revenue: {$sum: "$net_revenue"} 
        }
    }], ... );

这篇关于按月和流失/收入图表汇总收入对MongoDB集合进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！