ES DSL 学习

1.普通的基础查询

SQL:

SELECT
	*
FROM
	student_task_status T
WHERE
	1 = 1
AND t.gradeLevel = '7'
AND t.studentName LIKE '%北京%'
AND (
	t.taskStatus = '0'
	OR t.taskStatus = 'BEGIN'
)
AND t.beginTime >= '2020-04-09, 10:11:00.000'
AND t.beginTime <= '2020-04-12, 10:11:00.000'

DSL:

GET student_task_status*/_search
{
    "query":{
        "bool":{ // 第一层bool
            "must": [  // and
              {
                "term": {
                  "gradeLevel": {
                    "value": "7"
                  }
                }
              },
              { // like
                "match": {
                  "schoolName": "北京"
                }
              },
              { // 第二层bool 放入 or
                "bool": {
                  "should": [
                    {
                      "term": {
                        "taskStatus.keyword": {
                          "value": "0"
                        }
                      }
                    },
                    {
                      "term": {
                        "taskStatus.keyword": {
                          "value": "BEGIN"
                        }
                      }
                    }
                  ]
                }
              },
              { // 范围
                "range": {
                  "beginTime": {
                    "gte": "2020-04-08 10:26:00:000",
                    "lte": "2020-04-10 10:26:00:000"
                  }
                }
              }
            ]
        }
    },
    "from":0, // 第几页
    "size":20 // 几条数据
}

返回：

{
  "took": 6,
  "timed_out": false,
  "_shards": {
    "total": 5, 
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 4, // 一共几条数据
    "max_score": 7.703906,
    "hits": [
      {
        "_index": "student_task_status",
        "_type": "studentTaskStatusDoc",
        "_id": "c86ba704-83be-456f-a481-a5859e50bdfb",
        "_score": 7.703906,
        "_source": {
          "id": "c86ba704-83be-456f-a481-a5859e50bdfb",
          "creTime": "2020-04-09 16:00:00:000",
          "modTime": "2020-04-10 05:48:40:000",
          "taskStatus": "BEGIN",
          "creTaskUser": "a9176a3d-78ed-4c1d-961e-779ea824e50e",
          "creTaskType": "STUDENT",
          "taskId": "5334a191-ebc0-4fa9-a029-e06e9fd30ac5",
          "taskTitle": "个人练习",
          "taskType": "EXERCISES",
          "schoolId": "22f6fae1-2e3d-4145-b113-e13ac84e0e6e",
          "schoolName": "北京附小",
          "classId": "7b70c547-5861-429a-876c-2430e8d2c017",
          "className": "78",
          "gradeLevel": "7",
          "studentId": "a9176a3d-78ed-4c1d-961e-779ea824e50e",
          "studentName": "董沙0051",
          "subjectCode": "subject.czsx",
          "timeScope": 0,
          "beginTime": "2020-04-10 05:48:36:000",
          "endTime": "2020-04-10 05:48:40:000",
          "versionId": "ff8080814371757b0143763113902b0b",
          "areaId": "1",
          "areaCode": "-1",
          "provinceId": "1",
          "cityId": null,
          "countyId": null,
          "domainId": "1",
          "sessionId": null
        }
      }
    ]
  }
}

2.进阶查询

1.count

只需要普通查询取hit的total值就可以

2.distinct + count

sql:

select count(distinct t.gradeLevel) from student_task_status t

dsl:

GET student_task_status*/_search
{
    "query": {
      "bool": {
        "must": [
          {
            "match_all": {}
          }
        ]
      }
    },
    "from": 0,
    "size": 0,
    "aggs": {
      "gradeLevel": { // 这里是起名字
        "cardinality": {
          "field": "gradeLevel.keyword" // 根据什么基数
        }
      }
    }
    
}

返回：

{
  "took": 12,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 26108, // 一共多少数据
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "gradeLevel": { // 这个数据的distinct 为 3
      "value": 3
    }
  }
}

3.count + distinct +  group

sql:

select count(dinstinct t.studentId) from student_task_status t group by t.gradeLevel

dsl:

GET student_task_status*/_search
{
    "query": {
      "bool": {
        "must": [
          {
            "match_all": {}
          }
        ]
      }
    },
    "from": 0,
    "size": 0,
    "aggs": {
      "gradeLevel":
      {
        "terms": {
          "field": "gradeLevel.keyword"
        },
        "aggs": {
          "count": {
            "cardinality": {
              "field": "studentId.keyword"
            }
          }
        }
      }
    }
}

返回：

{
  "took": 2,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 26108,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "gradeLevel": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "7",
          "doc_count": 17408,
          "count": {
            "value": 306
          }
        },
        {
          "key": "8",
          "doc_count": 3107,
          "count": {
            "value": 83
          }
        },
        {
          "key": "9",
          "doc_count": 243,
          "count": {
            "value": 24
          }
        }
      ]
    }
  }
}

4.5分钟内每个人的最后一个记录

GET self*/_search
{
    "from":0,
    "size": 0, 
    "query":{
        "bool":{
            "must":[
                {
                    "term":{
                        "hasLogin":true
                    }
                }
            ],
            "filter":[
                {
                     "range" : {
          "@timestamp" : {
            "from" : "2020-05-18T03:10:28.430Z",
            "to" : null,
            "include_lower" : true,
            "include_upper" : true,
            "format" : "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'",
            "boost" : 1.0
          }
        }
                }
            ],
            "adjust_pure_negative":true,
            "boost":1
        }
    },
   "aggs": {
     "data": {
       "terms": {
         "field": "userId.keyword",
         "size": 10000
       },
       "aggs": {
         "top_page":{
           "top_hits":{
                    "from":0,
                    "size":1,
                    "version":false,
                    "explain":false,
                    "_source":{
                        "includes":[

                        ],
                        "excludes":[

                        ]
                    },
                    "sort":[
                        {
                            "@timestamp":{
                                "order":"desc"
                            }
                        }
                    ]
                }
         }
         
         
       }
     }
   }, 
    "version":true
}