在SQL中模拟组concat [英] 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 运算符.不幸的是，所有 Date运算符都会产生一个整数作为结果，对于所需的日期字符串类型， $ 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中模拟组concat的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！