java mysql 按年/月/日时间范围 统计或求和某字段工具类 懒人神器

适用于图表统计sql 如柱状图 折线图

可解决如下类似需求

1.查询   近x日每天  近x月每月  近x年每年 交易金额  

2.查询   近x日每天  近x月每月  近x年每年 订单量

传统方式举例(伪代码)

1. select sum(x字段) value,DATE_FORMAT(时间字段, 某格式) label

    from x表

    where 时间字段 between start and end

    group by label

    order by label

但是这种方式 如果数据库某个label 无数据

那么结果可能是这样的   

2024-01-26  10

2024-01-25  9

2024-01-22  3

还需要在应用程序中插入无数据的时间 和对应value

2.先找到需要查询的时间有哪些如 [2024-01-26,2024-01-25,2024-01-24,2024-01-23]

然后分组 in查询

-------

当您使用本工具类(非常轻量)

可以非常方便的得到需要统计的结果 

并且是由数据库直接统计出的 未经过应用程序的任何处理

如下所示

2024-01-19    0
2024-01-20    0
2024-01-21    0
2024-01-22    0
2024-01-23    0
2024-01-24    0
2024-01-25    0
2024-01-26    0

上代码:

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import lombok.AllArgsConstructor;
import lombok.Getter;
import org.springframework.core.io.ClassPathResource;
import org.springframework.util.FileCopyUtils;import java.nio.charset.StandardCharsets;
import java.text.MessageFormat;
import java.util.List;/*** 数据库按时间统计数据的工具类* 目前仅支持mysql* 采用模板引擎 实现会更简单(无需拆分sql) 但需要引入多余依赖 故采用 MessageFormat 但这种方式需要拆分sql文件* @author xiaoTan* @date 2023/11/03 13:16**/
public class DateRangeUtil {@Getter@AllArgsConstructorpublic enum RangeEnum{///*** 年*/YEAR(6, "DATE_FORMAT( DATE_SUB( CURRENT_DATE, INTERVAL @s YEAR ), '%Y')","DATE_FORMAT({},'%Y')"),/*** 月*/MONTH(6, "DATE_FORMAT( DATE_SUB( CURRENT_DATE, INTERVAL @s MONTH ), '%Y-%m')","DATE_FORMAT({},'%Y-%m')"),/*** 日*/DAY(7,"STR_TO_DATE(DATE(DATE_SUB(CURRENT_DATE,INTERVAL @s DAY)),'%Y-%m-%d')","DATE_FORMAT({},'%Y-%m-%d')");/***  向前统计 时间单位系数*/private final Integer incrementValue;/*** 临时表的sql片段*/private final String tempSqlFragment;/*** 日期格式化的sql片段*/private final String formatSqlFragment;private String formatSQLField(String timeTableFieldName){return StrUtil.format(this.getFormatSqlFragment(), timeTableFieldName);}private String formatSQLRange(String timeTableFieldName){String startTime = "date_add(now(),interval -"+this.incrementValue+" "+ this+")";return StrUtil.format("{} between {} and {}", formatSQLField(timeTableFieldName),formatSQLField(startTime),formatSQLField("now()"));}}@Getter@AllArgsConstructorpublic enum TotalTypeEnum{/*** 求和*/SUM("sum({})"),/*** 统计*/COUNT("count({})");/***  统计写法sql*/private final String totalSql;private String formatSQLField(String dataTableFieldName){return StrUtil.format(this.getTotalSql(), dataTableFieldName);}}public static String generateSQL(RangeEnum rangeEnum,TotalTypeEnum totalTypeEnum,String tableName,String dataTableFieldName,String timeTableFieldName, List<String> whereConditions) {String dataSql = fillDataSQL(rangeEnum,totalTypeEnum, tableName, dataTableFieldName, timeTableFieldName, whereConditions);return MessageFormat.format(getSqlByClassPath("baseSql"), rangeEnum.tempSqlFragment, rangeEnum.incrementValue,dataSql);}private static String fillDataSQL(RangeEnum rangeEnum, TotalTypeEnum totalTypeEnum, String tableName, String dataTableFieldName, String timeTableFieldName, List<String> whereConditions) {String dataSql = getSqlByClassPath("dataSql");String sumSql = totalTypeEnum.formatSQLField(dataTableFieldName);String defaultWhereSql = rangeEnum.formatSQLRange(timeTableFieldName);String whereSql =builderWhereSQL(defaultWhereSql, whereConditions);String dateField = rangeEnum.formatSQLField(timeTableFieldName);return MessageFormat.format(dataSql,sumSql,dateField, tableName,whereSql);}private static String builderWhereSQL(String defaultWhereSql, List<String> whereConditions) {if (CollUtil.isEmpty(whereConditions)) {return defaultWhereSql;}else {return defaultWhereSql + " and " + String.join(" and ", whereConditions);}}private static String getSqlByClassPath(String fileName){try {ClassPathResource classPathResource = new ClassPathResource("rangeSql/"+fileName);byte[] bytes= FileCopyUtils.copyToByteArray(classPathResource.getInputStream());return new String(bytes, StandardCharsets.UTF_8);} catch (Exception e) {throw new RuntimeException(e);}}}

使用方式:

String sql = DateRangeUtil.generateSQL(DateRangeUtil.RangeEnum.DAY
,DateRangeUtil.TotalTypeEnum.COUNT, "xxx表", "统计的字段", "create_time"
, Collections.singletonList(" 1=1 "));执行使用持久层框架执行sql即可
最后一个参数支持传入自定义where条件

需要在工具类所在项目的resource中创建rangeSql文件夹 并放入两个文件 如图所示:

baseSql:

SELECTtemp._date AS timeItem,IFNULL( dateTable.dateItem, 0 ) AS dateItem
FROM(SELECT( @s := @s + 1 ) AS _index,{0} AS _dateFROMinformation_schema.CHARACTER_SETS,(SELECT@s :=- 1) AS initWHERE@s < {1}ORDER BY_date) AS tempLEFT JOIN({2}) AS dateTable ON temp._date = dateTable.timeItem
GROUP BYtemp._date

dataSql:

select {0} dateItem,{1} timeItem
from {2}
where {3}
group by timeItem
order by timeItem

备注: 工具类实际可以更优雅 查询的结果字段名不标准 嫌弃的话自己改下

缺点无法连表 但是可以通过创建视图来解决