Elasticsearch 使用terms对long类型日期统计按月销售

索引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:每个月商品销售数量

最近更新

  1. docker php8.1+nginx base 镜像 dockerfile 配置

    2024-07-21 00:50:02       103 阅读
  2. Could not load dynamic library ‘cudart64_100.dll‘

    2024-07-21 00:50:02       114 阅读
  3. 在Django里面运行非项目文件

    2024-07-21 00:50:02       93 阅读
  4. Python语言-面向对象

    2024-07-21 00:50:02       99 阅读

热门阅读

  1. 轮播图变成响应式数据

    2024-07-21 00:50:02       29 阅读
  2. 基于python实现医院信息管理系统的设计与实现

    2024-07-21 00:50:02       24 阅读
  3. 为什么人们致力于解决深度学习的黑箱模型?

    2024-07-21 00:50:02       26 阅读
  4. 什么是TCP

    2024-07-21 00:50:02       28 阅读
  5. Ubuntu64新安装时问题的解决

    2024-07-21 00:50:02       22 阅读
  6. 【Unity C#基础】浅谈List底层逻辑

    2024-07-21 00:50:02       26 阅读
  7. 24、获取NCL色标并将其保存为Excel文件

    2024-07-21 00:50:02       26 阅读
  8. 算法学习day18(字符串)

    2024-07-21 00:50:02       21 阅读
  9. 【总结】计组第三章大局观:访存相关

    2024-07-21 00:50:02       20 阅读