基于Elasticsearch实现机器/用户打开的日期维度统计方案问询

问题说明 #

在大众传播场景中，我们追踪消息的打开、点击等事件，需按以下规则区分统计：

• 机器打开：所有打开记录的useragent仅为9808（iOS设备）
• 用户打开：存在至少一条打开记录的useragent为非9808值（若同时包含两种类型，归为用户打开）

原查询通过简单的term过滤opens.useragent:9808，无法区分文档是仅包含9808还是混合了其他useragent，导致统计结果偏差。以下是修正后的查询方案。

原数据结构示例 #

{
  "took": 12,
  "timed_out": false,
  "_shards": {
    "total": 38,
    "successful": 38,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 1,
      "relation": "eq"
    },
    "max_score": 0.0,
    "hits": [
      {
        "_index": "sample-202304",
        "_type": "logs",
        "_id": "16825338034249531",
        "_score": 0.0,
        "_source": {
          "clientid": 61448,
          "dsndate": "2023-04-27 12:15:29",
          "subject": "You've been added to a group!",
          "dtime": "2023-04-27 12:15:29",
          "latency": -1,
          "adate": 20230427,
          "itime": "2023-04-27 12:15:30",
          "inj_type": "WHATSAPP",
          "gtime": "2023-04-27 12:15:30",
          "counts_opens": 2,
          "segment_type": 3,
          "msize": 15994,
          "ptime": "2023-04-27 12:15:28",
          "timestamp": 1682585036,
          "atime": "2023-04-27 12:15:30",
          "rdom": "gmail.com",
          "ip": "172.253.115.27",
          "message_id": "",
          "rcpt_email": "sample@gmail.com",
          "fdom": "sample.com",
          "misc1": "",
          "counts_clicks": 0,
          "opens": [
            {"ip": "103.28.116.12","useragent": 9808,"time": "2023-04-27 13:02:03"},
            {"ip": "173.219.224.46","useragent": 2812,"time": "2023-04-27 15:44:09"},
            {"ip": "172.219.224.46","useragent": 2812,"time": "2023-04-27 15:44:09"}
          ],
          "udate": "2023-04-27 15:44:09",
          "fromaddress": "fantasysports@sample.com",
          "custom_data": "",
          "remarks": "173.253.115.27 - 250 2.0.0 OK e9-20020a0562140d8900b005771786395bsi11489394qve.154 - gsmtp (in reply to EOD command)",
          "status": 2
        }
      }
    ]
  }
}

修正后的Elasticsearch查询 #

curl -X GET "localhost:9200/index-202305,index-202304,index-202303,index-202302/_search?from=0&size=0&pretty" -H 'Content-Type: application/json' -d'{
  "query": {
    "bool": {
      "filter": [
        {"range": {"adate": {"gte": "20230427","lte": "20230427"}}},
        {"term": {"clientid": "61448"}},
        {"exists": {"field": "status"}},
        {"exists": {"field": "msize"}},
        {"exists": {"field": "adate"}}
      ]
    }
  },
  "aggs": {
    "daywise_summary": {
      "date_histogram": {
        "field": "adate",
        "interval": "day",
        "format": "yyyyMMdd"
      },
      "aggs": {
        "mailwise": {"terms": {"field": "status","size": 100000}},
        "total_opens": {"sum": {"field": "counts_opens"}},
        "total_clicks": {"sum": {"field": "counts_clicks"}},
        "total_msize": {"sum": {"field": "msize"}},
        "machine_opens": {
          "filter": {
            "script": {
              "source": "doc[''opens.useragent''].stream().allMatch(u -> u.value == 9808)"
            }
          },
          "aggs": {
            "count": {"sum": {"field": "counts_opens"}}
          }
        },
        "user_opens": {
          "filter": {
            "script": {
              "source": "doc[''opens.useragent''].stream().anyMatch(u -> u.value != 9808)"
            }
          },
          "aggs": {
            "count": {"sum": {"field": "counts_opens"}}
          }
        },
        "iphone_bot_clicks": {"filter": {"term": {"clicks.useragent": "9808"}}},
        "amp_clicks": {"filter": {"term": {"clicks.is_amp": 1}}},
        "amp_opens": {"filter": {"term": {"opens.is_amp": 1}}}
      }
    }
  }
}'

关键逻辑说明 #

1. 机器打开判断：使用脚本doc['opens.useragent'].stream().allMatch(u -> u.value == 9808)，确保当前文档的所有打开记录的useragent都是9808。
2. 用户打开判断：使用脚本doc['opens.useragent'].stream().anyMatch(u -> u.value != 9808)，只要存在一条非9808的打开记录，就归为用户打开（包含混合类型的文档）。
3. 日期维度聚合：保留原有的date_histogram按adate按天分组，确保统计结果按日期维度展示。

内容的提问来源于stack exchange，提问作者sabby