索引mapping:
{
"properties" : {
"_class" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"createDate" : {
"type" : "long"
},
"goodsCode" : {
"type" : "keyword"
},
"id" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"orderNumber" : {
"type" : "keyword"
},
"payDate" : {
"type" : "long"
},
"saleQty" : {
"type" : "double"
}
}
}
payDate字段映射为long类型,在使用date_histogram统计时,统计不出想要的效果。
{
...
"aggs": {
"agg_name": {
"date_histogram": {
"field": "payDate",
"interval": "1m",
"format": "yyyy-MM",
"min_doc_count": 0
}
}
}
}
结果输出的是"yyyy-MM1577836800000",并不是期待的2023-06.
{
"key_as_string": "yyyy-MM1577836800000",
"key": 1577836800000,
"doc_count": 3
}
改用terms+script+子聚合方式统计:
{
"query": {
"range": {
"payDate": {
"gte": 1685548800000
}
}
},
"size":0,
"aggs": {
"month_sales":{
"terms": {
"script": {
"source": """
DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM").withZone(ZoneId.systemDefault());
return df.format(Instant.ofEpochMilli(doc['payDate'].value));
""",
"lang":"painless"
},
"order": {
"_key": "asc"
}
},
"aggs": {
"sale_sum": {
"sum": {
"field": "saleQty"
}
}
}
}
}
}
script:使用脚本把pay格式化成yyyy-MM的字符串。
order:根据日期排序
sale_sum:统计每个桶里面商品销售数量
输出结果:
{
"month_sales": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 9723368,
"buckets": [
{
"key": "2023-06",
"doc_count": 49,
"sale_sum": {
"value": 77.0
}
},
{
"key": "2023-07",
"doc_count": 217,
"sale_sum": {
"value": 40012.0
}
},
{
"key": "2023-08",
"doc_count": 2187911,
"sale_sum": {
"value": 3486633.0
}
},
{
"key": "2023-09",
"doc_count": 2746235,
"sale_sum": {
"value": 4556942.0
}
},
{
"key": "2023-10",
"doc_count": 2346896,
"sale_sum": {
"value": 4097690.0
}
},
{
"key": "2023-11",
"doc_count": 2442224,
"sale_sum": {
"value": 4120708.0
}
},
{
"key": "2023-12",
"doc_count": 2359840,
"sale_sum": {
"value": 4167825.0
}
},
{
"key": "2024-01",
"doc_count": 3619202,
"sale_sum": {
"value": 6506941.0
}
},
{
"key": "2024-02",
"doc_count": 2673919,
"sale_sum": {
"value": 5066871.0
}
},
{
"key": "2024-03",
"doc_count": 3078439,
"sale_sum": {
"value": 4810836.0
}
}
]
}
}
key:每个月份
doc_count:每个月的订单数量
sale_sum:每个月商品销售数量