SQL-запрос агрегации, соответствующий в asticsearch

Я изучал запросы агрегации эластичного поиска, но не смог найти, поддерживает ли он множественную функцию агрегации. Другими словами, я хочу знать, может ли asticsearch генерировать эквивалент этого запроса агрегации Sql:

  SELECT account_no, transaction_type, count(account_no), sum(amount), max(amount) FROM index_name GROUP BY account_no, transaction_type Having count(account_no) > 10

Если да, то как? Спасибо.

Источник

1 ответ

Решение

Есть два возможных способа сделать то, что вы ищете в ES, и я упомянул их оба ниже.

Я также добавил образцы карт и образцы документов для вашей справки.

Отображение: 

PUT index_name
{
  "mappings": {
    "mydocs":{
      "properties":{
        "account_no":{
          "type": "keyword"
        },
        "transaction_type":{
          "type": "keyword"
        },
        "amount":{
          "type":"double"
        }
      }
    }
  }
}

Образцы документов:

Обратите внимание, я только создаю список из 4 транзакций для 1 клиента.

POST index_name/mydocs/1
{
  "account_no": "1011",
  "transaction_type":"credit",
  "amount": 200
}

POST index_name/mydocs/2
{
  "account_no": "1011",
  "transaction_type":"credit",
  "amount": 400
}

POST index_name/mydocs/3
{
  "account_no": "1011",
  "transaction_type":"cheque",
  "amount": 100
}

POST index_name/mydocs/4
{
  "account_no": "1011",
  "transaction_type":"cheque",
  "amount": 100
}

Есть два способа получить то, что вы ищете:

Решение 1. Использование Elasticsearch Query DSL

Запрос агрегации:

Для DSL Aggregation Query я использовал приведенные ниже запросы агрегации, чтобы решить, что вы ищете.

Ниже показано, как запрос представляет собой сводную версию запроса, чтобы вы получили ясность в отношении того, какие запросы являются родственными, а какие - родительскими.

- Terms Aggregation (For Every Account)
  - Terms Aggregation (For Every Transaction_type)
    - Sum Amount 
    - Max Amount

Ниже актуальный запрос:

POST index_name/_search
{
  "size": 0, 
  "aggs": {
    "account_no_agg": {
      "terms": {
        "field": "account_no"
      },
      "aggs": {
        "transaction_type_agg": {
          "terms": {
            "field": "transaction_type",
            "min_doc_count": 2
          },
          "aggs": {
            "sum_amount": {
              "sum": {
                "field": "amount"
              }
            },
            "max_amount":{
              "max": {
                "field": "amount"
              }
            }
          }
        }
      }
    }
  }
}

Важно отметить, что min_doc_count что ничего кроме having count(account_no)>10, который в моем запросе я фильтрую только те транзакции с having count(account_no) > 2

Ответ на запрос

{
  "took" : 5,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 4,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "aggregations" : {
    "account_no_agg" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "1011",                         <----  account_no
          "doc_count" : 4,                        <----  count(account_no)
          "transaction_type_agg" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "cheque",                 <---- transaction_type
                "doc_count" : 2,
                "sum_amount" : {                  <----  sum(amount)
                  "value" : 200.0
                },
                "max_amount" : {                  <----  max(amount)
                  "value" : 100.0
                }
              },
              {
                "key" : "credit",                 <---- another transaction_type
                "doc_count" : 2,
                "sum_amount" : {                  <---- sum(amount)
                  "value" : 600.0
                },
                "max_amount" : {                  <---- max(amount)
                  "value" : 400.0
                }
              }
            ]
          }
        }
      ]
    }
  }
}

Обратите внимание на приведенный выше результат внимательно, я добавил комментарии, где это необходимо, чтобы они помогли, какую часть SQL-запроса вы ищете.

Решение 2. Использование Elasticsearch SQL(решение _xpack)

Если вы используете функцию xpack SQL-доступа Elasticsearch, вы можете просто скопировать и вставить запрос SELECT, как показано ниже, для отображения и документа, как указано выше:

Elasticsearch SQL:

POST /_xpack/sql?format=txt
{
  "query": "SELECT account_no, transaction_type, sum(amount), max(amount), count(account_no) FROM index_name GROUP BY account_no, transaction_type HAVING count(account_no) > 1"

}

Elasticsearch SQL Результат:

  account_no   |transaction_type|  SUM(amount)  |  MAX(amount)  |COUNT(account_no)
---------------+----------------+---------------+---------------+-----------------
1011           |cheque          |200.0          |100.0          |2                
1011           |credit          |600.0          |400.0          |2

Обратите внимание, что я проверил запрос в ES 6.5.4.

Надеюсь это поможет!

Источник
Другие вопросы по тегам