elasticsearch 多列 聚合(sql group by)

文档数据格式

{"zone_id":"1","user_id":"100008","try_deliver_times":102,"trade_status":"TRADE_FINISHED","trade_no":"xiaomi.21142736250938334726","trade_currency":"CNY","total_fee":100,"status":"paid","sdk_user_id":"69272363","sdk":"xiaomi","price":1,"platform":"android","paid_channel":"unknown","paid_at":1427370289,"market":"unknown","location":"local","last_try_deliver_at":1427856948,"is_guest":0,"id":"fa6044d2fddb15681ea2637335f3ae6b7f8e76fef53bd805108a032cb3eb54cd","goods_name":"\u4E00\u5C0F\u5806\u5143\u5B9D","goods_id":"ID_001","goods_count":"1","expires_in":2592000,"delivered_at":0,"debug_mode":true,"created_at":1427362509,"cp_result":"exception encountered","cp_order_id":"cp.order.id.test","client_id":"9c98152c6b42c7cb3f41b53f18a0d868","app_user_id":"fvu100006"}

根据时间汇总 "client_id","platform","sdk", sum-》"total_fee"

select client_id,platform,sdk,sum(total_fee) from test  group by client_id,platform,sdk

 

curl  'http://127.0.0.1:9200/test/orders/_search?pretty' -d '
{

  "fields": ["client_id","platform","sdk","total_fee","paid_at"],
              "query": {
                "filtered": {
                   "query": { "query_string":{"query":"*"} },
                  
                    "filter": {"bool": { "must": { "range": { "paid_at": {"from": 1427370289, "to": 1427948457} } } }}
                }
              },
 
  "aggs": {
    "clientids": { "terms": { "field": "client_id" },
    "aggs": {
     "sdks": { "terms": { "field": "sdk" } ,  
      "aggs": {
        "platforms": { "terms": {"field": "platform" } ,
    
       "aggs": {
           "totalfee": { "sum": { "field": "total_fee" } }
         }
        }}
       }
      }
    }
  }
 
}'

 

最后结果

。。。。。

"aggregations" : {
    "clientids" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [ {
        "key" : "9c98152c6b42c7cb3f41b53f18a0d868",
        "doc_count" : 5,
        "sdks" : {
          "doc_count_error_upper_bound" : 0,
          "sum_other_doc_count" : 0,
          "buckets" : [ {
            "key" : "xiaomi",
            "doc_count" : 5,
            "platforms" : {
              "doc_count_error_upper_bound" : 0,
              "sum_other_doc_count" : 0,
              "buckets" : [ {
                "key" : "android",
                "doc_count" : 5,
                "totalfee" : {
                  "value" : 500.0
                }
              } ]
            }
          } ]
        }
      }, {
        "key" : "999999",
        "doc_count" : 3,
        "sdks" : {
          "doc_count_error_upper_bound" : 0,
          "sum_other_doc_count" : 0,
          "buckets" : [ {
            "key" : "oppo",
            "doc_count" : 2,
            "platforms" : {
              "doc_count_error_upper_bound" : 0,
              "sum_other_doc_count" : 0,
              "buckets" : [ {
                "key" : "android",
                "doc_count" : 2,
                "totalfee" : {
                  "value" : 200.0
                }
              } ]
            }
          }, {
            "key" : "baidu",
            "doc_count" : 1,
            "platforms" : {
              "doc_count_error_upper_bound" : 0,
              "sum_other_doc_count" : 0,
              "buckets" : [ {
                "key" : "android",
                "doc_count" : 1,
                "totalfee" : {
                  "value" : 100.0
                }
              } ]
            }
          } ]
        }
      }, {
        "key" : "888888",
        "doc_count" : 2,
        "sdks" : {
          "doc_count_error_upper_bound" : 0,
          "sum_other_doc_count" : 0,
          "buckets" : [ {
            "key" : "baidu",
            "doc_count" : 1,
            "platforms" : {
              "doc_count_error_upper_bound" : 0,
              "sum_other_doc_count" : 0,
              "buckets" : [ {
                "key" : "android",
                "doc_count" : 1,
                "totalfee" : {
                  "value" : 100.0
                }
              } ]
            }
          }, {
            "key" : "oppo",
            "doc_count" : 1,
            "platforms" : {
              "doc_count_error_upper_bound" : 0,
              "sum_other_doc_count" : 0,
              "buckets" : [ {
                "key" : "android",
                "doc_count" : 1,
                "totalfee" : {
                  "value" : 100.0
                }
              } ]
            }
          } ]
        }
      } ]
    }
  }
}

 

现在基本达到要求了,网上好多例子都是单列汇总 做个记录

posted @ 2015-04-02 15:45 幽谷清水 阅读(...) 评论(...) 编辑 收藏