mongodb 按分钟分组

Question

我有一张 table :

id   value   date
1      1     2014-08-10 10:00:00
1      2     2014-08-10 10:05:00
1      1     2014-08-10 12:00:00
1      1     2014-08-11 12:05:00
1      2     2014-08-11 12:06:00
2      2     2014-08-10 10:00:00
2      2     2014-08-11 10:05:00
2      1     2014-08-11 12:00:00
2      1     2014-08-20 12:05:00
2      2     2014-08-20 12:06:00

我想获取第一天每个 id 的总值(value)数。我可以使用以下命令在 MySQL 中执行此操作:

SELECT test.id, COUNT(CASE WHEN value=1 THEN 1 END) AS 'total_value_1',
                COUNT(CASE WHEN value=2 THEN 1 END) AS 'total_value_2'
FROM test,    
(SELECT id, date(min(date)) as firstDay
 FROM test
 GROUP BY id) AS temp
WHERE  test.id = temp.id AND date(test.date) = temp.firstDay
GROUP BY test.id;

结果是:

id total_value_1 total_value_2
1         2           1
2         0           1

我如何在 MongoDB 中执行此操作？

Answer 1

这可以通过使用 a collection of operators 的聚合框架来实现 在管道阶段定义和操作文档，这将为您提供所需的结果。您的第一个管道阶段是 $project运算符，您可以在其中转换您的值字段，以便在以后的管道阶段更容易操作。此操作的等效 SQL 是:

SELECT id, date, CASE WHEN value=1 THEN 1 END AS 'value_1',
                 CASE WHEN value=2 THEN 1 END AS 'value_2'
FROM test

下一个管道阶段使用 $group和 $project运算符计算日期字段上月份的最小日期和转换值字段上的总和聚合:

db.test.aggregate([
    {        
        "$project": {  
            "id": 1,            
            "date": 1,            
            "value_1": { "$cond": [{ "$eq": [ "$value", 1 ] }, 1, 0] },
            "value_2": { "$cond": [{ "$eq": [ "$value", 2 ] }, 1, 0] }
        }
    },
    {
        "$group": {
            "_id": {
                "id": "$id",
                "date": "$date"
            },
            "minimumDayofMonth": {
                "$min": { "$dayOfMonth": "$date"}
            },
            "total1" : {
                "$sum": "$value_1"
            },
            "total2" : {
                "$sum": "$value_2"
            }             
        }
    },
    {
        "$group": {
            "_id": {
                "id": "$_id.id",
                "day": "$minimumDayofMonth"
            }, 
            "firstDay": { "$min": "$minimumDayofMonth"  },            
            "total_value_1" : {
                "$sum": "$total1"
            },
            "total_value_2" : {
                "$sum": "$total2"
            }            
        }
    },
    {
        "$group": {
            "_id": "$_id.id",             
            "result": { "$first": "$$ROOT" }                
        }
    },
    {        
        "$project": {  
            "_id": 0,
            "id": "$_id",
            "total_value_1": "$result.total_value_1",
            "total_value_2": "$result.total_value_2"
        }
    }    
])

输出:

/* 0 */
{
    "result" : [ 
        {
            "id" : 2,
            "total_value_1" : 0,
            "total_value_2" : 1
        }, 
        {
            "id" : 1,
            "total_value_1" : 2,
            "total_value_2" : 1
        }
    ],
    "ok" : 1
}