elasticsearch聚合统计——pipeline使用、与mysql对比

elasticsearch的pipeline给使用者提供一种方式可以在统计具体分组数据的同时还可以做汇总统计、窗口聚合等，因为在遍历数据的时候同时运算，可以节省一般数据库中汇总运算带来的资源。

使用方式

在aggs下与需要汇总的数据平级配置，使用buckets_path配置路径。例如计算每天的用户播放时间统计值，再汇总计算最大播放量及日期、播放量总和、每天播放量平均值。

GET user_action/_search
{
  "size": 0, 
  "track_total_hits": true, 
  "aggs": {
    "date": {
      "terms": {
        "field": "date_",
        "size": 15
      },
      "aggs": {
        "stats": {
          "stats": {
            "field": "play"
          }
        }
      }
    },
    "max-pipeline" : {
      "max_bucket": {
        "buckets_path": "date>stats.sum"
      }
    },
    "avg-pipeline" : {
      "avg_bucket": {
        "buckets_path": "date>stats.avg"
      }
    },
    "sum-pipeline" : {
      "sum_bucket": {
        "buckets_path": "date>stats.sum"
      }
    }
  }
}

结果输出

{
  "took" : 313,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 2999999,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "date" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : 2,
          "doc_count" : 187899,
          "stats" : {
            "count" : 187899,
            "min" : 0.0,
            "max" : 6807500.0,
            "avg" : 25130.466356925794,
            "sum" : 4.721989498E9
          }
        },
        {
          "key" : 5,
          "doc_count" : 181125,
          "stats" : {
            "count" : 181125,
            "min" : 0.0,
            "max" : 9487241.0,
            "avg" : 25250.485968253968,
            "sum" : 4.573494271E9
          }
        }
      ]
    },
    "max-pipeline" : {
      "value" : 6.63517314E9,
      "keys" : [
        "14"
      ]
    },
    "avg-pipeline" : {
      "value" : 26102.651684794455
    },
    "sum-pipeline" : {
      "value" : 7.83587774E10
    }
  }
}

mysql执行

select 
    date_,count(date_) as cnt, sum(play) as sm, max(play) as mx 
from 
    user_play_stay_time 
    group by date_ 
union all 
(select 
    "all" as date_,count(date_) as cnt, sum(play) as sm, b.mx 
from 
    user_play_stay_time 
    join 
    (select 
        max(sm) as mx 
    from 
        (select date_, sum(play) as sm from user_play_stay_time group by date_) a) b);

结果

 

 对比结果

 

比对结果，elasticsearch和mysql都是可以正确计算的。mysql执行时间是普通的分组查询差不多3倍，而elasticsearch执行时间相差不大。在查询语法上，mysql执行需要多个子查询嵌套，逻辑比较复杂不容易编写，而elasticsearch都是同级别添加，比较直观。 

窗口函数的使用

elasticsearch的使用和汇总差不多，都是放到需要统计的平级路径下

 

 

而mysql用的是开窗函数window，窗口函数mysql是8.0版本后才支持的。开窗的方式是使用partition作为窗口的分组，between划分窗口的大小。例如between 3 preceding and 3 following。

 

 

因为是先生成表再做窗口统计，我猜测是没有办法使用像elasticsearch的cumulative_cardinality这种独立数累计的（看文档也没有发现类似的函数），而其他窗口函数的功能、灵活性感觉和elasticsearch差不太多，基本都能覆盖，当然elasticsearch的脚本还是强大一点。