最近在做数据漏斗的需求，就是统计ADX调用到我们DSP系统，我们DSP系统内部，对流量的过滤情况，做一个可视化界面，供PM查看。因为存储采用的ES，之前没有在项目中使用过，这两天看官方文档恶补了下，说实话，英文的官方文档真的是最好的学习资料，写语句时遇到了问题，去论坛发帖，一个外国友人回复了，因为我在提问时没有把代码格式化，以至于可读性非常差，后来改了，可读性增强，这样对别人也是一种尊重，别人浏览到这个帖子后，也能很清晰地看懂。很喜欢这种感觉，以后以官方文档为资料，不过英语真的是硬伤，需要多学习。外国友人给的建议 需求：统计参与竞价的PV数，条件是开始时间和结束时间，adxid，adlist不能为空。指标是对sidcount求和。其中对我来说难点在adlist不能为空这个条件。 下面我们看一下索引的mapping { "lm_dsp_data_search_funnel-2018.08.10": { "mappings": { "doc": { "properties": { "@timestamp": { "type": "date" }, "@version": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "abtest": { "type": "keyword" }, "adlist": { "type": "nested", "properties": { "promotionid": { "type": "integer" }, "sn": { "type": "integer" } } }, "adxid": { "type": "keyword" }, "age": { "type": "integer" }, "deviceidmd5": { "type": "keyword" }, "displocal1": { "type": "integer" }, "displocal2": { "type": "integer" }, "escount": { "type": "long" }, "gender": { "type": "integer" }, "interest": { "type": "integer" }, "mediaid": { "type": "integer" }, "os": { "type": "integer" }, "sid": { "type": "keyword" }, "sidcount": { "type": "integer" }, "slotid": { "type": "integer" }, "stime": { "type": "long" }, "styleids": { "type": "integer" }, "tags": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "userip": { "type": "keyword" } } } } } } 可以发现 adlist 采用了 nested 的方式 查询全部时，返回数据的一个结构，如下图所示：               最开始想使用es的script实现，写了下面的语句: GET lm_dsp_data_search_funnel-2018.08.10/_search { "size": 10, "query": { "bool": { "must": [ { "match": { "adxid": "u-2cbc34ryk3v43nkdq9g" } }, { "range": { "stime": { "gte": 1533859200000, "lte": 1533873600000 } } } ], "filter": { "script": { "script": "doc['adlist'].values.length > 0" } } } }, "aggs": { "sn_count": { "sum": { "field": "sidcount" } } } } 上面的语句报下面的错误： { "error": { "root_cause": [ { "type": "script_exception", "reason": "runtime error", "script_stack": [ "org.elasticsearch.search.lookup.LeafDocLookup.get(LeafDocLookup.java:81)", "org.elasticsearch.search.lookup.LeafDocLookup.get(LeafDocLookup.java:39)", "doc['adlist'].values.length > 0", " ^---- HERE" ], "script": "doc['adlist'].values.length > 0", "lang": "painless" } ], "type": "search_phase_execution_exception", "reason": "all shards failed", "phase": "query", "grouped": true, "failed_shards": [ { "shard": 0, "index": "lm_dsp_data_search_funnel-2018.08.10", "node": "eMpHZINKRxeM1YsKNDcjng", "reason": { "type": "script_exception", "reason": "runtime error", "script_stack": [ "org.elasticsearch.search.lookup.LeafDocLookup.get(LeafDocLookup.java:81)", "org.elasticsearch.search.lookup.LeafDocLookup.get(LeafDocLookup.java:39)", "doc['adlist'].values.length > 0", " ^---- HERE" ], "script": "doc['adlist'].values.length > 0", "lang": "painless", "caused_by": { "type": "illegal_argument_exception", "reason": "No field found for [adlist] in mapping with types []" } } } ] }, "status": 500 } 从网上查资料，也没有解决，写脚本这个技能后面需要再学习一下。后来转换了想法，用别的方式实现。 方法一： GET lm_dsp_data_search_funnel-2018.08.10/_search { "size": 0, "query": { "bool": { "must": [ { "term": { "adxid": { "value": "u-2cbc34ryk3v43nkdq9g" } } }, { "range": { "stime": { "gte": 1533859200000, "lte": 1533898800000 } } }, { "nested" : { "path" : "adlist", "score_mode" : "none", "query" : { "exists":{"field":"adlist"} } } } ] } }, "aggs": { "count": { "sum": { "field": "sidcount" } } } } 上面的会过滤掉{"adlist":[]}这样的数据，当拿不准的时候，看官方文档：     方式二： GET lm_dsp_data_search_funnel-2018.08.10/_search { "size": 0, "query": { "bool": { "must": [ { "term": { "adxid": { "value": "u-2cbc34ryk3v43nkdq9g" } } }, { "range": { "stime": { "from": 1533891139904, "to": null, "include_lower": false, "include_upper": false, "boost": 1 } } }, { "nested" : { "path" : "adlist", "score_mode" : "none", "query" : { "bool" : { "must" : [ { "range" : {"adlist.promotionid" : {"gte" : "0"}} } ] } } } } ] } }, "aggs": { "count": { "sum": { "field": "sidcount" } } } } 上面的两种方法都能实现需求： { "took": 42, "timed_out": false, "_shards": { "total": 1, "successful": 1, "skipped": 0, "failed": 0 }, "hits": { "total": 397105, "max_score": 0, "hits": [] }, "aggregations": { "count": { "value": 397105 } } } 这个需求的时间花在了梳理需求，日志埋点，把日志收集到es中，统计数据，还有就是熟悉es的查询语句，今天是周五，下周一是开始的最后一天，需要快速把语句融入到代码中，这只是其中一个20分之一的需求，任务重大，不过当梳理清楚需求后，知道怎么做，下面的工作就会轻松些，很喜欢这种在工作中学习的感觉。