ES中语句查询

es语句

#get 请求logstash-zsy-api索引，logs类型的内容
GET /logstash-zsy-api/logs/_search  

{
  # 查询结构不展示
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "requestUri": "GET[/ims/goods/up/list]"
          }
        },
        {
          "range": {
            "@timestamp": {
              "gt": "2020-10-01T00:00:00.002Z",
              "lt": "2020-12-31T00:00:00.002Z"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    # 将@timestamp 转化为 yyyy-MM 格式，并且升序排列
    "month": {
      "date_histogram": {
        "field": "@timestamp",
        "interval": "month",
        "format": "yyyy-MM",
        "order": {
          "_key": "asc"
        }
      },
      # 嵌套 聚合，在年月聚合的基础上根据 requestIp 进行聚合
      "aggs": {
        "requestIp": {
          "terms": {
            "field": "requestIp",
            "order": {
              "_term": "asc"
            }
          },
          # 度量计算 count distinct 
          "aggs": {
            "count": {
              "cardinality": {
                "field": "userId"
              }
            }
          }
        }
      }
    }
  }
}

# 结果
{
	"took": 25,
	"timed_out": false,
	"_shards": {
		"total": 46,
		"successful": 41,
		"failed": 5,
		"failures": [{
			"shard": 0,
			"index": "logstash-zsy-api",
			"node": "LQBE3ZJ9TLuNjGZWE9z2xA",
			"reason": {
				"type": "illegal_argument_exception",
				"reason": "Fielddata is disabled on text fields by default. Set fielddata=true on [requestIp] in order to load fielddata in memory by uninverting the inverted index. Note that this can however use significant memory. Alternatively use a keyword field instead."
			}
		}]
	},
	"hits": {
		"total": 2250,
		"max_score": 0,
		"hits": []
	},
	"aggregations": {
		"month": {
			"buckets": [{
			        # 年月
					"key_as_string": "2020-10",
					# @timestamp
					"key": 1601510400000,
					# count(*)数量
					"doc_count": 623,
					"requestIp": {
						"doc_count_error_upper_bound": 0,
						"sum_other_doc_count": 0,
						"buckets": [{
						    # requestIp 为  192.168.1.240
							"key": "192.168.1.240",
							# 总数
							"doc_count": 623,
							# 去重
							"count": {
								"value": 6
							}
						}]
					}
				},
				{
					"key_as_string": "2020-11",
					"key": 1604188800000,
					"doc_count": 1031,
					"requestIp": {
						"doc_count_error_upper_bound": 0,
						"sum_other_doc_count": 0,
						"buckets": [{
							"key": "192.168.1.240",
							"doc_count": 1031,
							"count": {
								"value": 4
							}
						}]
					}
				},
				{
					"key_as_string": "2020-12",
					"key": 1606780800000,
					"doc_count": 596,
					"requestIp": {
						"doc_count_error_upper_bound": 0,
						"sum_other_doc_count": 0,
						"buckets": [{
							"key": "192.168.1.240",
							"doc_count": 596,
							"count": {
								"value": 5
							}
						}]
					}
				}
			]
		}
	}
}

相当于sql 
select  
from_unixtime(@timestamp,'yyyy-MM'),requestIp,
count(distinct  userId) from logstash-ims-api.logs group by from_unixtime(@timestamp,'yyyy-MM'),requestIp