SQL как GROUP BY И ИМЕЯ
Я хочу получить количество групп, которые удовлетворяют определенному условию. В терминах SQL я хочу сделать следующее в Elasticsearch.
SELECT COUNT(*) FROM
(
SELECT
senderResellerId,
SUM(requestAmountValue) AS t_amount
FROM
transactions
GROUP BY
senderResellerId
HAVING
t_amount > 10000 ) AS dum;
До сих пор я мог группировать по senderResellerId по совокупности терминов. Но когда я применяю фильтры, это не работает, как ожидалось.
Эластичный запрос
{
"aggregations": {
"reseller_sale_sum": {
"aggs": {
"sales": {
"aggregations": {
"reseller_sale": {
"sum": {
"field": "requestAmountValue"
}
}
},
"filter": {
"range": {
"reseller_sale": {
"gte": 10000
}
}
}
}
},
"terms": {
"field": "senderResellerId",
"order": {
"sales>reseller_sale": "desc"
},
"size": 5
}
}
},
"ext": {},
"query": { "match_all": {} },
"size": 0
}
Актуальный ответ
{
"took" : 21,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"failed" : 0
},
"hits" : {
"total" : 150824,
"max_score" : 0.0,
"hits" : [ ]
},
"aggregations" : {
"reseller_sale_sum" : {
"doc_count_error_upper_bound" : -1,
"sum_other_doc_count" : 149609,
"buckets" : [
{
"key" : "RES0000000004",
"doc_count" : 8,
"sales" : {
"doc_count" : 0,
"reseller_sale" : {
"value" : 0.0
}
}
},
{
"key" : "RES0000000005",
"doc_count" : 39,
"sales" : {
"doc_count" : 0,
"reseller_sale" : {
"value" : 0.0
}
}
},
{
"key" : "RES0000000006",
"doc_count" : 57,
"sales" : {
"doc_count" : 0,
"reseller_sale" : {
"value" : 0.0
}
}
},
{
"key" : "RES0000000007",
"doc_count" : 134,
"sales" : {
"doc_count" : 0,
"reseller_sale" : {
"value" : 0.0
}
}
}
}
}
]
}
}
}
Как видно из приведенного выше ответа, он возвращает посредников, но агрегирование reseller_sale в результатах равно нулю.
Более подробная информация здесь.
1 ответ
Реализация HAVING- подобного поведения
Вы можете использовать один из pipeline aggregations а именно агрегация селектора ковша. Запрос будет выглядеть так:
POST my_index/tdrs/_search
{
"aggregations": {
"reseller_sale_sum": {
"aggregations": {
"sales": {
"sum": {
"field": "requestAmountValue"
}
},
"max_sales": {
"bucket_selector": {
"buckets_path": {
"var1": "sales"
},
"script": "params.var1 > 10000"
}
}
},
"terms": {
"field": "senderResellerId",
"order": {
"sales": "desc"
},
"size": 5
}
}
},
"size": 0
}
После помещения следующих документов в указатель:
"hits": [
{
"_index": "my_index",
"_type": "tdrs",
"_id": "AV9Yh5F-dSw48Z0DWDys",
"_score": 1,
"_source": {
"requestAmountValue": 7000,
"senderResellerId": "ID_1"
}
},
{
"_index": "my_index",
"_type": "tdrs",
"_id": "AV9Yh684dSw48Z0DWDyt",
"_score": 1,
"_source": {
"requestAmountValue": 5000,
"senderResellerId": "ID_1"
}
},
{
"_index": "my_index",
"_type": "tdrs",
"_id": "AV9Yh8TBdSw48Z0DWDyu",
"_score": 1,
"_source": {
"requestAmountValue": 1000,
"senderResellerId": "ID_2"
}
}
]
Результат запроса:
"aggregations": {
"reseller_sale_sum": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "ID_1",
"doc_count": 2,
"sales": {
"value": 12000
}
}
]
}
}
Т.е. только те senderResellerId чьи совокупные продажи >10000,
Подсчет ведер
Для реализации эквивалента SELECT COUNT(*) FROM (... HAVING) можно использовать комбинацию агрегации сценариев сегмента с агрегацией суммы сегмента. Хотя, кажется, нет прямого способа подсчитать, сколько ведер bucket_selector на самом деле выберите, мы можем определить bucket_script что производит 0 или же 1 в зависимости от состояния и sum_bucket что производит его sum:
POST my_index/tdrs/_search
{
"aggregations": {
"reseller_sale_sum": {
"aggregations": {
"sales": {
"sum": {
"field": "requestAmountValue"
}
},
"max_sales": {
"bucket_script": {
"buckets_path": {
"var1": "sales"
},
"script": "if (params.var1 > 10000) { 1 } else { 0 }"
}
}
},
"terms": {
"field": "senderResellerId",
"order": {
"sales": "desc"
}
}
},
"max_sales_stats": {
"sum_bucket": {
"buckets_path": "reseller_sale_sum>max_sales"
}
}
},
"size": 0
}
Выход будет:
"aggregations": {
"reseller_sale_sum": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
...
]
},
"max_sales_stats": {
"value": 1
}
}
Желаемое количество ведра находится в max_sales_stats.value,
Важные соображения
Я должен указать на 2 вещи:
- Эта функция является экспериментальной (начиная с ES 5.6 она все еще является экспериментальной, хотя она была добавлена в 2.0.0-бета1).
- конвейерные агрегации применяются на основе результатов предыдущих агрегаций:
Конвейерные агрегации работают с выходами, полученными из других агрегатов, а не из наборов документов, добавляя информацию в дерево вывода.
Это означает, что bucket_selector агрегация будет применяться после и в результате terms агрегация на senderResellerId, Например, если есть больше senderResellerId чем size из terms агрегация определяет, вы не получите все идентификаторы в коллекции с sum(sales) > 10000, но только те, которые появляются на выходе terms агрегация. Рассмотрите возможность использования сортировки и / или установите достаточно size параметр.
Это также относится ко второму случаю, COUNT() (... HAVING), который будет считать только те сегменты, которые фактически присутствуют в выходных данных агрегации.
Если этот запрос слишком тяжелый или количество сегментов слишком велико, рассмотрите возможность денормализации ваших данных или сохраните эту сумму непосредственно в документе, чтобы вы могли использовать обычный range запрос для достижения вашей цели.
Надеюсь, это поможет!