Elasticsearch 直方图统计（时间统计）和 postgreSql的级数生成

通常统计一定时间范围内，每隔多少时间间隔的统计数，以实现图表类的展示；

在ES中，提供直方图统计，会自动填充没有数据的月份数据，而在pg数据库中，一般的根据时间group by 统计是不会返回没有数据的时间段，展示图表的时候就会不连续，被测试和项目经理诟病，so咱想方法，网搜，广捞...

一、Elasticsearch中使用date_histogram 聚合函数

{
  "size":0,
  "aggs": {
    "sales": {
      "date_histogram": {//按照日期时间聚合分析数据
        "field": "time",//分析的字段
        "calendar_interval": "day",//按天间隔，有second,minute,hour,week,month,quarter,year
        "format": "yyyy-MM-dd HH:mm:ss",//日期格式
        "min_doc_count": 0,// 设定每个聚合区间的数量>=0的才返回
        "extended_bounds":{//强制返回的日期区间；如果不加这个就只返回有数据的区间
          "min":"2021-11-01",
          "max":"2021-12-01"
        }
      }
    }
  }
}

查询结果如下： 

对应的Java Api中这样实现：

public DateHistogramAggregationBuilder getDateHisAgg(String startTime, String endTime, String timeField){
        //获取时间解析单元（小时/天/月/年）
        DateHistogramInterval interval = getInterval(startTime, endTime);
        //日期直方图聚合
        DateHistogramAggregationBuilder dhAggsBuilder = AggregationBuilders.dateHistogram("dateHis");
        dhAggsBuilder.field(timeField);
        dhAggsBuilder.calendarInterval(interval);
        dhAggsBuilder.format("yyyy-MM-dd HH:mm:ss");
        dhAggsBuilder.minDocCount(0L);
        dhAggsBuilder.extendedBounds(new ExtendedBounds(startTime, endTime));
        return dhAggsBuilder;
    }

二、postgreSql中时间统计，填充不存在的时间

应用generate_series() 级数生成函数方法，coalesce()方法不存在则置换

官网：PostgreSQL: documentation: 9.2: Set Returning Functions

 SELECt  a.time, COALESCE(b.count, 0) as count  from ( 
 select to_char(t,  'yyyy-MM-dd HH24') as time 
FROM generate_series( '2021-12-25 00:00:00'::timestamp ,'2021-12-25 18:00:00'::timestamp ,'1 hours') t
) a  
LEFT OUTER JOIN (  
SELECt count(id), to_char(update_time, 'yyyy-MM-dd HH24') as time  FROM assist_request 
where 1=1 and update_time >= '2021-12-25 00:10:10'::timestamp and update_time <= '2021-12-25 18:10:10'::timestamp GROUP BY time ORDER BY time  
) b  
ON a.time = b.time ORDER BY a.time

填充结果：

 Java 实现：

public List> getDateAnalysis(String startTime, String endTime, long days){
    String format = "yyyy";
    String step = "1 year";
    if (days <= 1) {
        format = "yyyy-MM-dd HH24";
        step = "1 hours";
    } else if (days <= 31) {
        format = "MM-dd";
        step = "1 day";
    } else if (days <= 366) {
        format = "yyyy-MM";
        step = "1 month";
    }
    return assistRequestDao.getDateGroup(format, startTime, endTime, step);
}

查询条件拼接：

@SelectProvider(type = SqlBuilder.class, method = "queryByParams")
List> getDateGroup(@Param("format") String format, @Param("startTime") String startTime, @Param("endTime") String endTime, @Param("step") String step);

class SqlBuilder {
    public String queryByParams(@Param("format") String format, @Param("startTime") String startTime, @Param("endTime") String endTime, @Param("step") String step) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT a.time, COALESCE(b.count, 0) as count  from ( ");
        sql.append(" select to_char(t,  '").append(format).append("') as time FROM generate_series( '").append(startTime).append("'::timestamp ,'").append(endTime).append("'::timestamp ,'").append(step).append("') t ");
        sql.append(" ) a ");
        sql.append(" LEFT OUTER JOIN ( ");
        sql.append(" SELECt count(id), to_char(update_time, '").append(format).append("') as time ");
        sql.append(" FROM assist_request where 1=1 and update_time >= '").append(startTime).append("'::timestamp and update_time <= '").append(endTime).append("'::timestamp");
        sql.append(" GROUP BY time ORDER BY time ");
        sql.append(" ) b ");
        sql.append(" on a.time = b.time order by a.time");
        return sql.toString();
}