Group By Then Limit And Offset in Elastic Search

前言

傳統的 Relational database 可以使用 SQL 語言以 GROUP BY 的方式將資料分群然後做計算

SELECT  country, COUNT(*) AS Count
FROM `bi_dataset.usr` 
GROUP BY country DESC
ORDER BY 1

若想要在 Elastic Search  上實現相同的結果的話, 可以使用 Terms Aggregation

{
  "size": 0,
  "aggs" : {
      "country" : {
          "terms" : { "field" : "country" }
      }
  }
}

但實務上基於效能的考量, 通常不會希望回傳全部的結果, 而是以分頁的方式傳回部分資料

SQL的做法是在語法裡加入 LIMIT 以及 OFFSET 來控制結果

SELECT  country, COUNT(*) AS count
FROM `dataset.usr`
GROUP BY country
ORDER BY 1 DESC
LIMIT 20    /* 限制資料量   */
OFFSET 100  /* 取得某特定區段的資料, 即 skip 前100筆 */ 

而在 Elastic Search 裡, 可以藉由 bucket sort aggregation 來實現, 分頁的部分可以透過 size, 以及from 來控制

{
  "size": 0,
  "aggs": {
    "country": {
      "aggs": {
        "sort_country": {
          "bucket_sort": {
            "sort": [
              {
                "_count": {
                  "order": "DESC"
                }
              }
            ],
            "size": 20,   /* 限制資料量   */
            "from": 100   /* 取得某特定區段的資料, 即 skip 前100筆 */ 
          }
        }
      },
      "terms": {
        "field": "country",
        "size":  2147483647
      }
    }
  }
}

這邊補充說明一下, bucket sort aggregation  是根據 term aggregation 的結果去做排序及分頁, 而 Elastic Search 由於本身的限制,  term aggregation 最多只能返回 2147483647 而不是 Index 內的所有資料,  所以當 term aggregation 的結果大於 2147483647 時, bucket sort aggregation 將會失去作用

REF:
https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-bucket-sort-aggregation.html