转换mysql查询包含sum和group_concat到mongodb查询 [英] convert mysql query contains sum and group_concat to mongodb query

问题描述

我想将下面的mysql查询转换为mongodb查询.

I would like to convert below mysql query to mongodb query.

SELECT substring(o.schedule_datetime,1,4) 'Year',
SUM(IF(o.order_status in ('SUCCESS','#SUCCESS'),1,0)) 'SUCCESS'
FROM (
      select group_concat(distinct ifnull(os.order_status,'') order by os.order_status 
      separator '#') 'order_status',schedule_datetime
      from order_summary os group by order_number
     )o group by 1 desc;

例如:我有样品表

id order_number product_number order_status schedule_datetime
1  001          001.1          SUCCESS      20180103
2  001          001.2          SUCCESS      20180102
3  111          111.1          SUCCESS      20171225
4  111          111.2          SUCCESS      20171224
5  222          222.1          INPROGRESS   20171122
6  222          222.2          SUCCESS      20171121

我使用上面的mysql查询获取订单状态为SUCCESS的输出

I get the output using above mysql query for order status SUCCESS

Year SUCCESS
2018 1
2017 1

我已经使用了分隔符(#)将多个雕像组合成字符串，并按状态获得了预期的结果，为了获得进步，我只是在改变SUM功能，如下所示:

I have used separator(#) to combine multiple statues as string and get the desired result by status, to get INPROGRESS i will be just changing SUM funtion as shown below :

SUM(IF(o.order_status in ('INPROGRESS','INPROGRESS#SUCCESS', '#INPROGRESS','#INPROGRESS#SUCCESS'),1,0)) 'INPROGRESS' 

我试图编写mongodb查询，但是卡住了如何将sum和if条件以及group_concat与分隔符组合在一起，就像我在mysql查询中使用的那样.

I have tried to write the mongodb query, but got stuck how to combine sum and if condition as well group_concat with seperator as i used in mysql query.

db.order_summary.aggregate([ 

 { "$project" :
    { "orderDate" : 1 , "subOrderDate" : { "$substr" : [ "$order_date" , 0 , 4]},
        "order_number":"$order_number"
    },

 } ,
 { "$group":{
     "_id": { "order_number" : "$order_number", "Year": "$subOrderDate", "order_status":{"$addToSet":{"$ifNull":["$order_status",'']}}}
    }
 },
 { "$group": {
     "_id": "$_id.Year", "count": { "$sum": 1 }
    }
 },
 { "$sort" : { "_id" : -1}}
 ])

非常感谢任何人的帮助

推荐答案

mongodb中没有Group_Concat类型的功能.

There is no Group_Concat kind of functionality in mongodb.

您可以使用 $比较数组以匹配最后一组中的值. in 运算符(在3.4版本中).

You can compare arrays for matching values in last group with $in operator in 3.4 version.

第一个$group获取订单号和订单状态组合的所有不同的订单状态.

First $group to get all the distinct order status for a combination for order number and order status.

$sort对订单状态进行排序.

$sort to sort the order statuses.

第二个$group按订单号推送所有已排序的状态值.

Second $group to push all the sorted status values by order number.

最终$group，将每年的状态与输入的状态列表和所有比赛的输出总数进行比较.

Final $group to compare the statuses for each year against the input list of status and output total count for all matches.

db.order_summary.aggregate([{"$project":{
  "schedule_datetime":1,
  "order_number":1,
  "order_status":{"$ifNull":["$order_status",""]}
}},
{"$group":{
  "_id":{
    "order_number":"$order_number",
    "order_status":"$order_status"
   },
  "schedule_datetime":{"$first": "$schedule_datetime"} 
}},
{"$sort":{"_id.order_status": 1}},
{"$group":{
   "_id":{
    "order_number":"$_id.order_number"
   },
  "schedule_datetime":{"$first": "$schedule_datetime"},
  "order_status":{"$push": "$_id.order_status"}
}},
{"$group":{
  "_id":{"$substr":["$schedule_datetime",0,4]},
  "count":{
    "$sum":{
      "$cond": [
        {"$in": ["$order_status",[["SUCCESS"], ["","SUCCESS"]]]}, 
        1,
        0]
    }
  }
}},
{"$sort":{"_id":-1}}])

这篇关于转换mysql查询包含sum和group_concat到mongodb查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！