ElasticSearch 常用聚合查询

#ElasticSearch 常用聚合查询| 来源: 网络整理| 查看: 265

持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第29天，点击查看活动详情

首先展示一下我们要分析的文档结构:

{ "video_id": 1289643545120062253, // 视频id "video_uid": 3931482202390368051, // 视频发布者id "uid": 47381776787453866, // 观看用户id "time": 1533891263224, // 时间发生时间 "watch_duration": 30 // 观看时长 } 复制代码

每个文档记录了一个观看事件，我们通过聚合分析用户的观看行为。

ElasticSearch引入了两个相关概念：

桶(Buckets): 满足特定条件的文档的集合指标(Metrics): 桶中文档的统计值，如特定字段的平均值查询用户观看视频数和观看时长

首先用sql语句描述这个查询:

SELECT uid, count(*) as view_count FROM view_log WHERE time >= #{since} AND time = #{since} AND time 200; 复制代码 GET /view_log/_search { "size": 0, "aggs": { "view_count": { "terms": { "field": "video_id" }, "aggs": { "having": { "bucket_selector": { "buckets_path": { // 选择 view_count 聚合的 doc_count 进行过滤 "view_count": "_count" }, "script": { "source": "params.view_count > 200" } } } } } } } 复制代码

response:

{ "took": 83, "timed_out": false, "_shards": { "total": 5, "successful": 5, "skipped": 0, "failed": 0 }, "hits": { "total": 775, "max_score": 0, "hits": [] }, "aggregations": { "view_count": { "buckets": [ { "key": 35025417499764062, "doc_count": 529 }, { "key": 19913672446898144, "doc_count": 759 } ] } } } 复制代码

ElasticSearch实现类似HAVING查询的关键在于使用bucket_selector选择聚合结果进行过滤。

根据其它指标进行过滤

接下来我们尝试查询平均观看时长大于5分钟的视频, 用SQL描述该查询:

SELECT video_id FROM view_log GROUP BY video_id HAVING avg(watch_duration) > 300; 复制代码 GET /view_log/_search { "size": 0, "aggs": { "video": { "terms": { "field": "video_id" }, "aggs": { "avg_duration": { "avg": { "field": "watch_duration" } }, "avg_duration_filter": { "bucket_selector": { "buckets_path": { "avg_duration": "avg_duration" }, "script": { "source": "params.avg_duration > 200" } } } } } } } 复制代码

response:

{ "took": 137, "timed_out": false, "_shards": { "total": 5, "successful": 5, "skipped": 0, "failed": 0 }, "hits": { "total": 255, "max_score": 0, "hits": [] }, "aggregations": { "video": { "buckets": [ { "key": 5417499764062, "doc_count": 91576, "avg_duration": { "value": 103 } }, { "key": 19913672446898144, "doc_count": 15771, "avg_duration": { "value": 197 } } ] } } } 复制代码

【本文地址】

公司简介

联系我们