模拟 sql 中的组连接 [英] Analog for group concat in sql

问题描述

在聚合过程中，我得到了这些数据:

In an aggregation process I've got this data:

{
    "_id" : "billing/DefaultController/actionIndex",
    "min_time" : 0.033,
    "max_time" : 5.25,
    "exec_time" : 555.490999999997,
    "qt" : 9059,
    "count" : 2,
    "date" : [ 
        ISODate("2014-02-10T00:00:00.000Z"), 
        ISODate("2014-02-11T00:00:00.000Z")
    ]
},

如何更改我的查询:

db.page_speed_reduced.aggregate([
    {$group: {
        _id: "$value.route",
        min_time: {$min: "$value.min_time"},
        max_time: {$max: "$value.max_time"},
        exec_time: {$sum: "$value.exec_time"},
        qt: {$sum: "$value.qt"},
        count: {$sum: NumberInt(1)},
        date: {$push: "$_id.date"},
    }}
]);

获取$date"作为连接字符串:

for getting "$date" as concatenated string:

2014-02-10, 2014-02-11

更新:

我尝试了这个变体，但 mongodb 产生了错误:

I tried this variant, but mongodb generated the error:

db.page_speed_reduced.aggregate([
    {$group: {
        _id: "$value.route",
        min_time: {$min: "$value.min_time"},
        max_time: {$max: "$value.max_time"},
        exec_time: {$sum: "$value.exec_time"},
        qt: {$sum: "$value.qt"},
        count: {$sum: NumberInt(1)},
        date: {$push: "test sting"},
    }},
    {$project: {
        'date': {$concat: ['$date']}
        //'date': {$concat: '$date'} //some error
    }}
]);

uncaught exception: aggregate failed: {
 "errmsg" : "exception: $concat only supports strings, not Array",
 "code" : 16702,
 "ok" : 0
}
'date': {$concat: '$date'}

推荐答案

根据目前的评论，目前尚不清楚您要分组的内容或您想要的最终结果，除了说您希望将日期连接成诸如仅一天"之类的内容，而没有小时或分钟.大概您出于某种目的想要那些不同的日子.

As per comments so far it is unclear what you are grouping or what you want as the end result, other than to say that you want to get your dates concatenated into something like "just the day" with no hours or minutes together. Presumably you want those distinct days for some purpose.

管道中有各种日期运算符，您可以在日期上使用，也是 $concat 运算符.不幸的是，所有 日期运算符 都会产生一个整数作为它们的结果，而对于您想要的日期字符串类型，$concat 仅适用于字符串.另一个问题是您不能在聚合中将整数强制转换为字符串类型.

There are various Date Operators in the pipeline you can use on dates, and the is the $concat operator as well. Unfortunately all of the Date Operators produce an integer as their result, and for the sort of Date string you want, $concat will only work with strings. The other problem being that you cannot cast the integer into a string type within aggregation.

但是您可以使用子文档，这里我们只处理日期:

But you can use sub-documents, here we'll just work with the date:

db.record.aggregate([
    // Unwind the array to work with it
    {$unwind: "$date"},

    // project into our new 'day' document
    {$project:{ 
        day: { 
            year: {$year: "$date"},
            month: {$month: "$date"}, 
            day: {$dayOfMonth: "$date"}
        }
     } },

     // optionalally sort if date order is important [ oldest -> newest ] 
     {$sort: { "day.year": -1, "day.month": -1, "day.day": -1}},

     // Wind back unique values into the array
     {$group: {_id:"$_id", days: {$addToSet: "$day"} }}
])

所以，它不是一个字符串，但它可以很容易地被后处理成一个，但最重要的是它是可分组和可排序的.

So, it's not a string, but it can easily be post-processed into one, but most importantly it's grouped and sortable.

如果您希望以这种方式将唯一的 dates 作为数组末尾，或者您是否希望按这些日期对总数进行分组，则原则保持不变.所以首先要记住使用日期运算符的 $unwind 和 $project 部分.

The principles remain the same if you want the unique dates this way as an array at the end or whether you want to group totals by those dates. So primarily keep in mind the $unwind and $project parts using the date operators.

--编辑--

感谢社区，如这篇文章所示，有这种未记录的行为$substr，其中整数可以转换为字符串.

With thanks to the community as shown in this post there is this undocumented behavior of $substr, in which integers can be cast as strings.

db.record.aggregate([
    // Unwind the array to work with it
    {$unwind: "$date"},

    // project into our new 'day' document
    {$project:{ 
        day: { 
            year: {$year: "$date"},
            month: {$month: "$date"}, 
            day: {$dayOfMonth: "$date"}
        }
     } },

     // optionalally sort if date order is important [ oldest -> newest ] 
     {$sort: { "day.year": -1, "day.month": -1, "day.day": -1}},

     // now we are going to project to a string ** magic @heinob **
     {$project: { 
         day: {$concat: [
             {$substr: [ "$day.year", 0, 4 ]},
             "-",
             {$substr: [ "$day.month", 0, 2 ]},
             "-",
             {$substr: [ "$day.day", 0, 2 ]}
         ]}
     }},

     // Wind back unique values into the array
     {$group: {_id:"$_id", days: {$addToSet: "$day"} }}
])

现在 days 是字符串.正如我之前提到的，如果排序对您很重要，那么最好的方法是像已经完成的那样投影到文档类型中并按数字键排序.为简洁起见，转换日期的 $project 自然可以绕到 $group 阶段，这可能是您在处理整个文档时想要做的.

And now the days are strings. As I noted before, if the ordering is important to you then the best approach is to project into a document type as has been done and sort on the numeric keys. Naturally the $project that transforms the date can be wound into the $group stage for brevity, which is probably what you want to do when working with the whole document.

这篇关于模拟 sql 中的组连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！