es的聚合mysql聚合速度_es聚合之实现日期按周一到周日的聚合统计

#### 前要说明

由于业务要查看日志，按周一到周天展示日志统计量


我们数据是存储在es中，找了半天聚合函数，发现可以用脚本定义周一到周日。

这里就需要用到es中的日期类org.elasticsearch.script.JodaCompatibleZonedDateTime

它其中就提供了一个函数getDayOfWeek()获取一周星期几对应的数值，这个函数刚好可以实现我们的需求。

#### getDayOfWeek函数的使用

我们先用script_fields来输出一下这个函数具体有哪些数值。

```json

{

"script_fields":{

"day":{

"script" : {

"lang": "painless",

"source":"def day = doc['collect_time'].value.getDayOfWeek(); return day;"

}

}

},

"size": 10,

"query": {

"range" : {

"collect_time" : {

"gte" : "2020-11-01 00:00:00",

"lte" : "2020-11-08 23:59:59"

}

}

}

}

```

结果如下：

```json

{

"took" : 10,

"timed_out" : false,

"_shards" : {

"total" : 2,

"successful" : 2,

"skipped" : 0,

"failed" : 0

},

"hits" : {

"total" : 3612,

"max_score" : 1.0,

"hits" : [

{

"_index" : "base_engs_comp_clg_result",

"_type" : "doc",

"_id" : "3ad92e63785df1cef01aa4af113aa31",

"_score" : 1.0,

"fields" : {

"day" : [

7

]

}

},

{

"_index" : "base_engs_comp_clg_result",

"_type" : "doc",

"_id" : "6974580b43297c9a3e1475f3fb298f17",

"_score" : 1.0,

"fields" : {

"day" : [

7

]

}

}

]

}

}

```

上面用day字段显示了getDayOfWeek()函数的返回值为1到7，表示周一到周日。

ps:这里我只展示部分数据，大家可以根据自己的数据来测试这个函数。

#### 实现周一到周天的聚合查询

那如何在聚合中用到这个函数呢？

我查了很久Es的文档，发现它的范围查询可以支持上面的脚本。具体实现的DSL语句如下：

```json

{

"size": 0,

"query": {

"range" : {

"collect_time" : {

"gte" : "2020-11-01 00:00:00",

"lte" : "2020-11-08 23:59:59"

}

}

},

"aggs" : {

"collect_time_ranges" : {

"range" : {

"script" : {

"lang": "painless",

"source": "doc['collect_time'].value.getDayOfWeek()"

},

"ranges" : [

{ "key":"1","from" : 1, "to" : 2 },

{ "key":"2","from" : 2, "to" : 3 },

{ "key":"3","from" : 3, "to" : 4 },

{ "key":"4","from" : 4, "to" : 5 },

{ "key":"5","from" : 5, "to" : 6 },

{ "key":"6","from" : 6, "to" : 7 },

{ "key":"7","from" : 7 }

]

}

}

}

}

```

返回结果如下：

```json

{

"took" : 47,

"timed_out" : false,

"_shards" : {

"total" : 2,

"successful" : 2,

"skipped" : 0,

"failed" : 0

},

"hits" : {

"total" : 3612,

"max_score" : 0.0,

"hits" : [ ]

},

"aggregations" : {

"collect_time_ranges" : {

"buckets" : [

{

"key" : "1",

"from" : 1.0,

"to" : 2.0,

"doc_count" : 1063

},

{

"key" : "2",

"from" : 2.0,

"to" : 3.0,

"doc_count" : 371

},

{

"key" : "3",

"from" : 3.0,

"to" : 4.0,

"doc_count" : 233

},

{

"key" : "4",

"from" : 4.0,

"to" : 5.0,

"doc_count" : 159

},

{

"key" : "5",

"from" : 5.0,

"to" : 6.0,

"doc_count" : 107

},

{

"key" : "6",

"from" : 6.0,

"to" : 7.0,

"doc_count" : 0

},

{

"key" : "7",

"from" : 7.0,

"doc_count" : 1679

}

]

}

}

}

```