ES實現(xiàn)類似sql的group by后如何分頁？

{
    "query": {
        ...... //搜索條件
    },
    "aggs": {
        "count": {   // COUNT(*)，統(tǒng)計GROUP BY后的總數(shù)
            "cardinality": {
                "field": "goods_id"    // 因為我這里GROUP BY的字段是goods_id，所以就用goods_id來計數(shù)了
            }
        },
        "goods_id": {
            "terms": {
                "field": "goods_id",    // 選擇GROUP BY的字段
                "size": 20    // 取出20條GROUP BY的數(shù)據(jù)。數(shù)量應(yīng)設(shè)置為sql中offset+limit的數(shù)量。注：其實es聚合操作不是很支持分頁，于是只能先將數(shù)據(jù)取出，再對其做分頁操作，可想而知頁數(shù)越往后效率越低
            },
            "aggs": {
                "group": {
                    "top_hits": {
                        "sort": [
                            {
                                "stock_num": {
                                    "order": "desc"    // GROUP BY的數(shù)據(jù)如何排序，這里是根據(jù)stock_num 降序排列
                                }
                            }
                        ],
                        "_source": {    // 對應(yīng)SQL的SELECT 
                            "includes": [
                                "goods_no"    // SELECT的列
                            ]
                        },
                        "size": 1    // es聚合時需要指定返回幾條數(shù)據(jù)（即返回幾條同一個goods_id的數(shù)據(jù)）我們做GROUP BY操作就只要寫1就完事了
                    }
                },
                "r_bucket_sort": {    // 分頁操作
                    "bucket_sort": {
                        "sort": [],
                        "from": 0,   // 對上面取出的20條數(shù)據(jù)分頁，等價于SQL的OFFSET
                        "size": 10   // SQL的LIMIT
                    }
                }
            }
        }
    },
    "size": 0,   // 因為是做聚合操作，所以直接無視query篩選出的數(shù)據(jù)
    "from": 0
}

案例：統(tǒng)計業(yè)務(wù)應(yīng)用流量數(shù)據(jù)文章來源地址http://www.zghlxwxcb.cn/news/detail-507284.html

curl -XGET /action*/_search
{
  "from": 0,
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "occurredAt": {
              "gte": "1659316790000",
              "lt": "1659323990000"
            }
          }
        }
      ],
      "must": [
        {
          "term": {
            "subtype.keyword": {
              "value": "/datatrans/traffic"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "count": {
      "cardinality": {
        "field": "applicationType.keyword"
      }
    },
    "group_by_app_type": {
      "terms": {
        "field": "applicationType.keyword",
        "min_doc_count": 1,
        "shard_min_doc_count": 0,
        "show_term_doc_count_error": false,
        "size": 40,
        "order": [
          {
            "_count": "desc"
          }
        ]
      },
      "aggs": {
        "sum_bytes_sent": {
          "sum": {
            "field": "bytesSent"
          }
        },
        "sum_bytes_received": {
          "sum": {
            "field": "bytesReceived"
          }
        },
        "sum_total_flow": {
          "sum": {
            "script": {
              "source": "(doc[\"bytesSent\"].value + doc[\"bytesReceived\"].value)"
            }
          }
        },
        "max_date": {
          "max": {
            "field": "occurredAt"
          }
        },
        "min_date": {
          "min": {
            "field": "occurredAt"
          }
        },
        "bucket_filed": {
          "bucket_sort": {
            "sort": [
              {
                "sum_bytes_sent": {
                  "order": "asc"
                }
              }
            ],
            "from": 0,
            "size": 40
          }
        }
      }
    }
  }
}'

到了這里，關(guān)于ES聚合分頁（group by分組后分頁）的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！