java实现日报表、月报表统计，没数据补0

产品需求：

1、日报表

2、月报表

需求点： 前端传日期或月份区间，当数据库中指定的日期或月份没数据时也需要界面显示，但领取人数和使用人数需要自动补齐0；
举例：2020-11-28、2020-11-29，数据库中没数据，但也需要在界面显示，领取人数和使用人数为0；

思路很重要

1、首先根据前端传来的时间区间，查询出区间中所有的日期或月份；
2、然后循环日期或月份和数据库查询返回的List<实体对象>的日期或月份进行匹配；
2.1、不存在则在List<实体对象>中新增一条实体对象数据；
3、最后统一把处理后的List<实体对象>返回前端；

sql语句（后面Mapper.xml中全部详细列出）

注：这里是Mysql数据库，以日报表为例，** 月报表则是将 DATE_FORMAT格式修改为 %Y-%m 即可 **

<!-- 根据日期分组查询 “领取人数” 和 “使用人数”，然后将两个Sql查询结果汇总 -->

SELECT
	t.days,
	sum( t.receiveQty ) AS receiveQty,
	sum( t.userQty ) AS userQty 
FROM
	(
        <!-- 领取人数 -->
		SELECT
			DATE_FORMAT( r.created_time, '%Y-%m-%d' ) AS days,
			count( r.id ) AS receiveQty,
			0 AS userQty 
		FROM
			dg_experience_receive r
			LEFT JOIN dg_experience_order o ON r.user_id = o.user_id 
		WHERE
			DATE_FORMAT( r.created_time, '%Y-%m-%d' ) >= '2020-11-01' 
			AND DATE_FORMAT( r.created_time, '%Y-%m-%d' ) <= '2020-12-16' 
		GROUP BY
			days 

        UNION ALL

        <!-- 使用人数 -->
		SELECT
			DATE_FORMAT( r.created_time, '%Y-%m-%d' ) AS days,
			0 AS receiveQty,
			count( r.id ) AS userQty 
		FROM
			dg_experience_receive r
			JOIN dg_experience_order o ON r.user_id = o.user_id 
		WHERE
			r.is_used = 1 
			AND DATE_FORMAT( r.created_time, '%Y-%m-%d' ) >= '2020-11-01' 
			AND DATE_FORMAT( r.created_time, '%Y-%m-%d' ) <= '2020-12-16' 
		GROUP BY
			days 
	) t 
GROUP BY
	days 
ORDER BY
	days DESC;

请求实体

@Data
public class DepositExperienceDayReq {
    /**
     * 开始日期
     */
    @NotBlank(message = "起始日期不能为空！")
    private String startDate;

    /**
     * 结束日期
     */
    @NotBlank(message = "截至日期不能为空！")
    private String endDate;
}

返回实体

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class DepositExperienceDayRes {
    /**
     * 日期
     */
    private String days;

    /**
     * 领取人数
     */
    private Long receiveQty;

    /**
     * 使用人数
     */
    private Long userQty;
}

Controller

/**
     * 体验金日报表--查询
     */
    @PostMapping("/experienceDay/list")
    public ResponseData listExperienceDayByReq(@Valid @RequestBody DepositExperienceDayReq experienceDayReq){
        return ResponseData.success(experienceOrderService.listExperienceDayByReq(experienceDayReq));
    }

    /**
     * 体验金月报表--查询
     */
    @PostMapping("/experienceMonth/list")
    public ResponseData listExperienceMonthByReq(@Valid @RequestBody DepositExperienceDayReq experienceDayReq){
        return ResponseData.success(experienceOrderService.listExperienceMonthByReq(experienceDayReq));
    }

Service

/**
     * 运营报表--体验金日报表--查询
     * @param experienceDayReq
     * @return
     */
    List<DepositExperienceDayRes> listExperienceDayByReq(DepositExperienceDayReq experienceDayReq);

    /**
     * 运营报表--体验金月报表--查询
     * @param experienceDayReq
     * @return
     */
    List<DepositExperienceDayRes> listExperienceMonthByReq(DepositExperienceDayReq experienceDayReq);

ServiceImpl(这里是重点，需要看一下)

注： DateUtil使用的是 hutool-core-5.0.6.jar

    /**
     * 运营报表--体验金日报表--查询
     * @param experienceDayReq
     * @return
     */
    @Override
    public List<DepositExperienceDayRes> listExperienceDayByReq(DepositExperienceDayReq experienceDayReq) {

        // 首先根据前端传来的时间区间，查询出区间中所有的日期；
        List<Date> lDate = findDates(DateUtil.parseDate(experienceDayReq.getStartDate()), DateUtil.parseDate(experienceDayReq.getEndDate()), Calendar.DAY_OF_MONTH);
        if (CollectionUtils.isEmpty(lDate)){
            return null;
        }
        List<DepositExperienceDayRes> experienceDayResList = baseMapper.listExperienceDayByReq(experienceDayReq);
        for (Date date : lDate){
            // 当前日期不存在则新增
            String dateStr = DateUtil.format(date, "yyyy-MM-dd");
            // 进行匹配
            if(!experienceDayResList.stream().filter(item -> dateStr.equals(item.getDays())).findAny().isPresent()){
                DepositExperienceDayRes experienceDayRes = DepositExperienceDayRes.builder()
                        .days(dateStr)
                        .receiveQty(0L)
                        .userQty(0L)
                        .build();
                experienceDayResList.add(experienceDayRes);
            }
        }
        List<DepositExperienceDayRes> collect = experienceDayResList.stream().sorted(Comparator.comparing(DepositExperienceDayRes::getDays).reversed()).collect(Collectors.toList());
        return collect;
    }


    /**
     * 运营报表--体验金月报表--查询
     * @param experienceDayReq
     * @return
     */
    @Override
    public List<DepositExperienceDayRes> listExperienceMonthByReq(DepositExperienceDayReq experienceDayReq) {
        // 首先根据前端传来的时间区间，查询出区间中所有的月份；
        DateTime startDate = DateUtil.parse(experienceDayReq.getStartDate(), "yyyy-MM");
        DateTime endDate = DateUtil.parse(experienceDayReq.getEndDate(), "yyyy-MM");
        List<Date> lDate = findDates(startDate, endDate, Calendar.MONTH);
        if (CollectionUtils.isEmpty(lDate)){
            return null;
        }
        List<DepositExperienceDayRes> experienceDayResList = baseMapper.listExperienceMonthByReq(experienceDayReq);
        for (Date date : lDate){
            // 当前日期不存在则新增
            String dateStr = DateUtil.format(date, "yyyy-MM");
            // 进行匹配
            if(!experienceDayResList.stream().filter(item -> dateStr.equals(item.getDays())).findAny().isPresent()){
                DepositExperienceDayRes experienceDayRes = DepositExperienceDayRes.builder()
                        .days(dateStr)
                        .receiveQty(0L)
                        .userQty(0L)
                        .build();
                experienceDayResList.add(experienceDayRes);
            }
        }
        List<DepositExperienceDayRes> collect = experienceDayResList.stream().sorted(Comparator.comparing(DepositExperienceDayRes::getDays).reversed()).collect(Collectors.toList());
        return collect;
    }

    /**
     * 获取指定时间区间的所有数据（包含日期和月份）
     * @param dBegin
     * @param dEnd
     * @param rule 日历规则 如：Calendar.DAY_OF_MONTH
     * @return
     */
    public static List<Date> findDates(Date dBegin, Date dEnd, int rule) {
        List lDate = new ArrayList();
        if (dEnd.before(dBegin)){
            return lDate;
        }
        lDate.add(dBegin);
        Calendar calBegin = Calendar.getInstance();
        // 使用给定的 Date 设置此 Calendar 的时间
        calBegin.setTime(dBegin);
        Calendar calEnd = Calendar.getInstance();
        // 使用给定的 Date 设置此 Calendar 的时间
        calEnd.setTime(dEnd);
        // 测试此日期是否在指定日期之后
        while (dEnd.after(calBegin.getTime())) {
            // 根据日历的规则，为给定的日历字段添加或减去指定的时间量
            calBegin.add(rule, 1);
            lDate.add(calBegin.getTime());
        }
        return lDate;
    }

Mapper.xml

 <!-- 日报表 -->
<select id="listExperienceDayByReq"
            resultType="com.dg.mall.financial.vo.res.report.operation.DepositExperienceDayRes">
        SELECT
            t.days,
            sum(t.receiveQty) as receiveQty,
            sum(t.userQty) as userQty
        FROM (
            SELECT
                DATE_FORMAT(r.created_time , '%Y-%m-%d' ) AS days,
                count(r.id) as receiveQty,
                0 as userQty
            FROM dg_experience_receive r
            LEFT JOIN dg_experience_order o on r.user_id = o.user_id
            <where>
                <if test="experienceDayReq.startDate != null and experienceDayReq.startDate != ''">
                    <![CDATA[ AND DATE_FORMAT(r.created_time, '%Y-%m-%d') >= #{experienceDayReq.startDate} ]]>
                </if>
                <if test="experienceDayReq.endDate != null and experienceDayReq.endDate != ''">
                    <![CDATA[ AND DATE_FORMAT(r.created_time, '%Y-%m-%d') <= #{experienceDayReq.endDate} ]]>
                </if>
            </where>
            GROUP BY days

            UNION ALL

            SELECT
                DATE_FORMAT(r.created_time , '%Y-%m-%d' ) AS days,
                0 as receiveQty,
                count(r.id) as userQty
            FROM dg_experience_receive r
            JOIN dg_experience_order o on r.user_id = o.user_id
            <where>
                AND r.is_used = 1
                <if test="experienceDayReq.startDate != null and experienceDayReq.startDate != ''">
                    <![CDATA[ AND DATE_FORMAT(r.created_time, '%Y-%m-%d') >= #{experienceDayReq.startDate} ]]>
                </if>
                <if test="experienceDayReq.endDate != null and experienceDayReq.endDate != ''">
                    <![CDATA[ AND DATE_FORMAT(r.created_time, '%Y-%m-%d') <= #{experienceDayReq.endDate} ]]>
                </if>
            </where>
            GROUP BY days
        ) t
        GROUP BY days
        order by days desc
    </select>

    <!-- 月报表 -->
    <select id="listExperienceMonthByReq"
            resultType="com.dg.mall.financial.vo.res.report.operation.DepositExperienceDayRes">
        SELECT
        t.days,
        sum(t.receiveQty) as receiveQty,
        sum(t.userQty) as userQty
        FROM (
        SELECT
        DATE_FORMAT(r.created_time , '%Y-%m' ) AS days,
        count(r.id) as receiveQty,
        0 as userQty
        FROM dg_experience_receive r
        LEFT JOIN dg_experience_order o on r.user_id = o.user_id
        <where>
            <if test="experienceDayReq.startDate != null and experienceDayReq.startDate != ''">
                <![CDATA[ AND DATE_FORMAT(r.created_time, '%Y-%m') >= #{experienceDayReq.startDate} ]]>
            </if>
            <if test="experienceDayReq.endDate != null and experienceDayReq.endDate != ''">
                <![CDATA[ AND DATE_FORMAT(r.created_time, '%Y-%m') <= #{experienceDayReq.endDate} ]]>
            </if>
        </where>
        GROUP BY days

        UNION ALL

        SELECT
        DATE_FORMAT(r.created_time , '%Y-%m' ) AS days,
        0 as receiveQty,
        count(r.id) as userQty
        FROM dg_experience_receive r
        JOIN dg_experience_order o on r.user_id = o.user_id
        <where>
            AND r.is_used = 1
            <if test="experienceDayReq.startDate != null and experienceDayReq.startDate != ''">
                <![CDATA[ AND DATE_FORMAT(r.created_time, '%Y-%m') >= #{experienceDayReq.startDate} ]]>
            </if>
            <if test="experienceDayReq.endDate != null and experienceDayReq.endDate != ''">
                <![CDATA[ AND DATE_FORMAT(r.created_time, '%Y-%m') <= #{experienceDayReq.endDate} ]]>
            </if>
        </where>
        GROUP BY days
        ) t
        GROUP BY days
        order by days desc
    </select>

PostMan结果展示：

之前是将该文章放在简书中，后面发现简书的页面编辑功能没有这里多，今天就把它移过来了。

下一篇将会记录使用：Elasticsearch(简称ES)实现日报表、月报表、年报表统计，没数据补0功能
https://blog.csdn.net/JRocks/article/details/113842092