elasticsearch 多列 聚合（sql group by）
时间:2019-11-06 16:44 来源:it 作者:it
以下聚合对应的sql为select * from test group by model,brand,upbymemberid
{
"query": {
"bool": {
"must": [
{
"terms": {
"model": [
"LM358",
"BU406",
"BAV99"
]
}
}
],
"must_not": [],
"should": []
}
},
"from": 0,
"size": 0,
"sort": [],
"aggs": {
"types_count": {
"terms": {
"field": "model"
},
"aggs": {
"brand": {
"terms": {
"field": "brand"
},
"aggs": {
"upbymemberid": {
"cardinality": {
"field": "upByMemberID"
}
}
}
}
}
}
}
}
结果如下：
{
"took": 4,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"failed": 0
},
"hits": {
"total": 35,
"max_score": 0,
"hits": []
},
"aggregations": {
"types_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "BAV99",
"doc_count": 24,
"brand": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 3,
"buckets": [
{
"key": "FAIRCHILD",
"doc_count": 7,
"upbymemberid": {
"value": 2
}
},
{
"key": "FAIRCHILD/ON SEMICONDUCTOR",
"doc_count": 3,
"upbymemberid": {
"value": 1
}
},
{
"key": "DIODES",
"doc_count": 2,
"upbymemberid": {
"value": 1
}
},
{
"key": "LITE-ON SEMICONDUCTOR CORPORATION",
"doc_count": 2,
"upbymemberid": {
"value": 1
}
},
{
"key": "MULTICOMP",
"doc_count": 2,
"upbymemberid": {
"value": 1
}
},
{
"key": "DIOTEC",
"doc_count": 1,
"upbymemberid": {
"value": 1
}
},
{
"key": "FSC",
"doc_count": 1,
"upbymemberid": {
"value": 1
}
},
{
"key": "Fairchild Semiconductor Corporation",
"doc_count": 1,
"upbymemberid": {
"value": 1
}
},
{
"key": "INFINEON",
"doc_count": 1,
"upbymemberid": {
"value": 1
}
},
{
"key": "Lite-On Semiconductor Corporation",
"doc_count": 1,
"upbymemberid": {
"value": 1
}
}
]
}
},
{
"key": "LM358",
"doc_count": 7,
"brand": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "qwe",
"doc_count": 3,
"upbymemberid": {
"value": 1
}
},
{
"key": "td",
"doc_count": 2,
"upbymemberid": {
"value": 1
}
},
{
"key": "TI",
"doc_count": 1,
"upbymemberid": {
"value": 1
}
},
{
"key": "tr",
"doc_count": 1,
"upbymemberid": {
"value": 1
}
}
]
}
},
{
"key": "BU406",
"doc_count": 4,
"brand": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "FSC",
"doc_count": 1,
"upbymemberid": {
"value": 1
}
},
{
"key": "MULTICOMP",
"doc_count": 1,
"upbymemberid": {
"value": 1
}
},
{
"key": "ST",
"doc_count": 1,
"upbymemberid": {
"value": 1
}
},
{
"key": "厚聲",
"doc_count": 1,
"upbymemberid": {
"value": 1
}
}
]
}
}
]
}
}
}
(责任编辑：IT)
| 以下聚合对应的sql为select * from test group by model,brand,upbymemberid { "query": { "bool": { "must": [ { "terms": { "model": [ "LM358", "BU406", "BAV99" ] } } ], "must_not": [], "should": [] } }, "from": 0, "size": 0, "sort": [], "aggs": { "types_count": { "terms": { "field": "model" }, "aggs": { "brand": { "terms": { "field": "brand" }, "aggs": { "upbymemberid": { "cardinality": { "field": "upByMemberID" } } } } } } } } 结果如下： { "took": 4, "timed_out": false, "_shards": { "total": 5, "successful": 5, "failed": 0 }, "hits": { "total": 35, "max_score": 0, "hits": [] }, "aggregations": { "types_count": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 0, "buckets": [ { "key": "BAV99", "doc_count": 24, "brand": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 3, "buckets": [ { "key": "FAIRCHILD", "doc_count": 7, "upbymemberid": { "value": 2 } }, { "key": "FAIRCHILD/ON SEMICONDUCTOR", "doc_count": 3, "upbymemberid": { "value": 1 } }, { "key": "DIODES", "doc_count": 2, "upbymemberid": { "value": 1 } }, { "key": "LITE-ON SEMICONDUCTOR CORPORATION", "doc_count": 2, "upbymemberid": { "value": 1 } }, { "key": "MULTICOMP", "doc_count": 2, "upbymemberid": { "value": 1 } }, { "key": "DIOTEC", "doc_count": 1, "upbymemberid": { "value": 1 } }, { "key": "FSC", "doc_count": 1, "upbymemberid": { "value": 1 } }, { "key": "Fairchild Semiconductor Corporation", "doc_count": 1, "upbymemberid": { "value": 1 } }, { "key": "INFINEON", "doc_count": 1, "upbymemberid": { "value": 1 } }, { "key": "Lite-On Semiconductor Corporation", "doc_count": 1, "upbymemberid": { "value": 1 } } ] } }, { "key": "LM358", "doc_count": 7, "brand": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 0, "buckets": [ { "key": "qwe", "doc_count": 3, "upbymemberid": { "value": 1 } }, { "key": "td", "doc_count": 2, "upbymemberid": { "value": 1 } }, { "key": "TI", "doc_count": 1, "upbymemberid": { "value": 1 } }, { "key": "tr", "doc_count": 1, "upbymemberid": { "value": 1 } } ] } }, { "key": "BU406", "doc_count": 4, "brand": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 0, "buckets": [ { "key": "FSC", "doc_count": 1, "upbymemberid": { "value": 1 } }, { "key": "MULTICOMP", "doc_count": 1, "upbymemberid": { "value": 1 } }, { "key": "ST", "doc_count": 1, "upbymemberid": { "value": 1 } }, { "key": "厚聲", "doc_count": 1, "upbymemberid": { "value": 1 } } ] } } ] } } } (责任编辑：IT) |