订单统计与导入导出

最新推荐文章于 2024-12-16 17:03:04 发布

菜鸡且互啄69

最新推荐文章于 2024-12-16 17:03:04 发布

阅读量1k

点赞数 24

文章标签： java

本文链接：https://blog.csdn.net/2201_75600005/article/details/144399076

版权

经营看板

本项目在运营端工作台页面展示系统经营看板，内容包括订单分析、用户分析等，如下图：

经营看板是一种用于实时监控关键业务指标的工具，它不仅帮助团队保持敏捷、透明和高效，还促进了团队的协作和创新。

需求分析

下边梳理本项目运营端经营看板的功能。

首先选择一个时间区间（不能大于365天），统计在此时间区间内的订单数据，只统计已取消、已关闭、已完成的订单数据。

订单分析内容如下：

有效订单数：在统计时间内，订单状态为已完成的订单数。（订单被取消、退款、关闭状态均不属于有效订单）
取消订单数：在统计时间内，提交订单后被取消订总量
关闭订单数：在统计时间内，提交订单后被关闭订总量
有效订单总额：在统计时间内，有效订单的总订单交易总额
实付单均价：在统计时间内，平均每单实际支付额（不包含失效订单）

实付单均价=有效订单总额/有效订单数

订单趋势：

订单趋势的显示分两种情况：

如果统计时间区间大于1天，订单趋势显示时间区间内每天的下单总数。
如果统计时间区间小于等于1天，订单趋势显示当天每小时的下单总数。

技术方案

根据需求，我们要统计一个时间区间的订单总数、订单均价等指标。统计出结果后通过接口将数据返回给前端，前端在界面展示即可。

基于什么平台进行统计分析？

通常统计分析要借助大数据平台进行，流程如下：

说明：

大数据统计系统对数据进行统计，并统计结果存入MySQL。

Java程序根据看板的需求提供查询接口，从统计结果表查询数据。这里使用缓存，将看板需要的数据存入Redis，提高查询性能。

如果数据量不大在千万级别可以基于数据库进行统计。

本项目通过分布式数据库存储历史订单数据，可以满足统计分析的需求。

本项目对订单的统计基于数据库进行统计。

如何基于数据库进行统计呢？

当用户进入看板页面面向全部数据进行实时统计其统计速度较慢。

为了提高统计效率可以分层次聚合，再基于分层聚合的统计结果进行二次统计。

举例：

我们要统计2023年10月1日到2023年11月30日的订单总数等指标，我们可以提前按天把每天的订单总数等指标统计出来，当用户去统计2023年10月1日到2023年11月30日的订单总数时基于按天统计的结果进行二次统计。

按天统计结果：

统计数据的分层次聚合需要根据需求确定统计的维度，例如除了按照时间，还可能按地区、产品类别等进行聚合。

根据需求在订单趋势图上除了显示每天的订单总数以外还会按小时进行显示，所以还需要按小时进行统计。

本项目采用滚动式统计，每次统计近15天的数据（如果数据量大可减少统计时段长度），采用滚动式统计的好处是防止统计任务执行失败漏掉统计数据，如下图：

15日统计1到15日的订单。

16日统计2到16日的订单。

依此类推。

分层聚合的粒度有两种：

按天统计，将统计结果存储至按天统计表。

按小时，将统计结果存储至按小时统计表。

有了分层聚合的统计结果，根据用户需求基于分层聚合的统计结果进行二次统计，其统计效率会大大提高，并且有此需求无需进行二次统计直接查询分层聚合结果表即可。

数据流如下：

订单统计

定义mapper

首先编写SQL：

这里面的SQL是行变列，并把数据封装到一个list的实体类中

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jzo2o.orders.history.mapper.HistoryOrdersSyncMapper">

    <select id="statForDay" parameterType="java.util.Map" resultType="com.jzo2o.orders.history.model.domain.StatDay">
        select day as id,
        day as statTime,
        sum(if(orders_status=500,1,0)) effective_order_num,
        sum(if(orders_status=600,1,0)) cancel_order_num,
        sum(if(orders_status=700,1,0)) close_order_num,
        sum(if(orders_status=500,total_amount,0)) effective_order_total_amount
        from history_orders_sync where day >= #{queryDay}
        GROUP BY day
    </select>
    ...

只统计一部分，另外一部分用流的形式统计

package com.jzo2o.orders.history.service.impl;

@Service
@Slf4j
public class HistoryOrdersSyncServiceImpl extends ServiceImpl<HistoryOrdersSyncMapper, HistoryOrdersSync> implements IHistoryOrdersSyncService {

@Override
public List<StatDay> statForDay(Integer statDay) {
    //统计15天以内的订单
    List<StatDay> statForDay = baseMapper.statForDay(statDay);
    if(CollUtils.isEmpty(statForDay)) {
        return Collections.emptyList();
    }
    // 按天统计订单，计算订单总数、均价等信息
    List<StatDay> collect = statForDay.stream().peek(sd -> {
        // 订单总数
        sd.setTotalOrderNum(NumberUtils.add(sd.getEffectiveOrderNum(), sd.getCloseOrderNum(), sd.getCancelOrderNum()).intValue());
        // 实付订单均价
        if (sd.getEffectiveOrderNum().compareTo(0) == 0) {
            sd.setRealPayAveragePrice(BigDecimal.ZERO);
        } else {
           //RoundingMode.HALF_DOWN 表示四舍五入 向下舍弃，如2.345，保留两位小数为2.34
            BigDecimal realPayAveragePrice = sd.getEffectiveOrderTotalAmount().divide(new BigDecimal(sd.getEffectiveOrderNum()), 2, RoundingMode.HALF_DOWN);
            sd.setRealPayAveragePrice(realPayAveragePrice);
        }
    }).collect(Collectors.toList());

    return collect;
}
...

定时任务

@Service
public class StatDayServiceImpl extends ServiceImpl<StatDayMapper, StatDay> implements IStatDayService {
@Override
public void statAndSaveData() {
    // 1.数据统计
    // 15天前时间
    LocalDateTime statDayLocalDateTime = DateUtils.now().minusDays(15);
    long statDayTime = DateUtils.getFormatDate(statDayLocalDateTime, "yyyMMdd");
    // 统计数据
    List<StatDay> statDays = historyOrdersSyncService.statForDay((int) statDayTime);
    if(ObjectUtils.isEmpty(statDays)){
        return ;
    }    
    // 2.数据保存至按天统计表
    saveOrUpdateBatch(statDays);
}

@Component
public class XxlJobHandler {

    /**
     * 按天统计保存15天内的订单数据
     * 按小时统计保存15天内的订单数据
     */
    @XxlJob("statAndSaveData")
    public void statAndSaveDataForDay() {
        //按天统计保存15天内的订单数据
        statDayService.statAndSaveData();
        //按小时统计保存15天内的订单数据
        statHourService.statAndSaveData();
    }

    @GetMapping("/homePage")
    @ApiOperation("运营端首页数据")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "minTime", value = "开始时间", required = true, dataTypeClass = LocalDateTime.class),
            @ApiImplicitParam(name = "maxTime", value = "结束时间", required = true, dataTypeClass = LocalDateTime.class)
    })
    public OperationHomePageResDTO homePage(@RequestParam("minTime") @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") LocalDateTime minTime,
                                            @RequestParam("maxTime") @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") LocalDateTime maxTime) {
        return ordersStatisticsService.homePage(minTime, maxTime);
    }

@Cacheable(value = "JZ_CACHE", cacheManager = "cacheManager30Minutes")
    public OperationHomePageResDTO homePage(LocalDateTime minTime, LocalDateTime maxTime) {
        //校验查询时间
        if (LocalDateTimeUtil.between(minTime, maxTime, ChronoUnit.DAYS) > 365) {
            throw new ForbiddenOperationException("查询时间区间不能超过一年");
        }

        //如果查询日期是同一天，则按小时查询折线图数据
        if (LocalDateTimeUtil.beginOfDay(maxTime).equals(minTime)) {
            return getHourOrdersStatistics(minTime);
        } else {
            //如果查询日期不是同一天，则按日查询折线图数据
            return getDayOrdersStatistics(minTime, maxTime);
        }
    }

private OperationHomePageResDTO getDayOrdersStatistics(LocalDateTime minTime, LocalDateTime maxTime) {
        //定义要返回的对象
        OperationHomePageResDTO operationHomePageResDTO = OperationHomePageResDTO.defaultInstance();

        //日期格式化，格式：yyyyMMdd
        String minTimeDayStr = LocalDateTimeUtil.format(minTime, DatePattern.PURE_DATE_PATTERN);
        String maxTimeDayStr = LocalDateTimeUtil.format(maxTime, DatePattern.PURE_DATE_PATTERN);

        //根据日期区间聚合统计数据
        StatDay statDay = statDayService.aggregationByIdRange(Long.valueOf(minTimeDayStr), Long.valueOf(maxTimeDayStr));

        //将statDay拷贝到operationHomePageResDTO
        operationHomePageResDTO = BeanUtils.copyIgnoreNull(BeanUtil.toBean(statDay, OperationHomePageResDTO.class), operationHomePageResDTO, OperationHomePageResDTO.class);

        //根据日期区间查询按日统计数据
        List<StatDay> statDayList = statDayService.queryListByIdRange(Long.valueOf(minTimeDayStr), Long.valueOf(maxTimeDayStr));
        //将statDayList转为map<趋势图横坐标，订单总数>
        Map<String, Integer> ordersCountMap = statDayList.stream().collect(Collectors.toMap(s -> dateFormatter(s.getId()), StatDay::getTotalOrderNum));
        //趋势图上全部点
        List<OperationHomePageResDTO.OrdersCount> ordersCountsDef = OperationHomePageResDTO.defaultDayOrdersTrend(minTime, maxTime);
        //遍历ordersCountsDef，将统计出来的ordersCountMap覆盖ordersCountsDef中的数据
        ordersCountsDef.stream().forEach(v->{
            if (ObjectUtil.isNotEmpty(ordersCountMap.get(v.getDateTime()))) {
                v.setCount(ordersCountMap.get(v.getDateTime()));
            }
        });
        //将ordersCountsDef放入operationHomePageResDTO
        operationHomePageResDTO.setOrdersTrend(ordersCountsDef);

        return operationHomePageResDTO;
    }

订单导出

web上传、下载

@Controller
@RequestMapping("/excel/test")
public class ExcelController {

/**
     * 文件下载（失败了会返回一个有部分数据的Excel）
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link DownloadData}
     * <p>
     * 2. 设置返回的 参数
     * <p>
     * 3. 直接写，这里注意，finish的时候会自动关闭OutputStream
     */
    @GetMapping("download")
    public void download(HttpServletResponse response) throws IOException {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), DemoData.class).sheet("模板").doWrite(data());
       //设置返回body无需包装标识
       response.setHeader(BODY_PROCESSED, "1");                
    }

    /**
     * 文件上传
     * <p>1. 创建excel对应的实体对象 参照{@link UploadData}
     * <p>2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link UploadDataListener}
     * <p>3. 直接读即可
     */
    @PostMapping("upload")
    @ResponseBody
    public String upload(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), DemoData.class, new DemoDataListener()).sheet().doRead();
        return "success";
    }

@Api(tags = "运营端 - 订单统计相关接口")
@RestController("operationOrdersStatisticsController")
@RequestMapping("/operation/orders-statistics")
public class OrdersStatisticsController {
    @Resource
    private OrdersStatisticsService ordersStatisticsService;


    /**
     * 文件下载并且失败的时候返回json（默认失败了会返回一个有部分数据的Excel）
     *
     * @since 2.1.1
     */
    @GetMapping("downloadStatistics")
    @ApiOperation("导出统计数据")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "minTime", value = "开始时间", required = true, dataTypeClass = LocalDateTime.class),
            @ApiImplicitParam(name = "maxTime", value = "结束时间", required = true, dataTypeClass = LocalDateTime.class)
    })
    public void downloadStatistics(@RequestParam("minTime") @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") LocalDateTime minTime,
                                   @RequestParam("maxTime") @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") LocalDateTime maxTime) throws IOException {
        ordersStatisticsService.downloadStatistics(minTime, maxTime);
    }
}

/**
 * 导出统计数据
 *
 * @param minTime 开始时间
 * @param maxTime 结束时间
 */
@Override
public void downloadStatistics(LocalDateTime minTime, LocalDateTime maxTime) throws IOException {
    //校验查询时间
    if (LocalDateTimeUtil.between(minTime, maxTime, ChronoUnit.DAYS) > 365) {
        throw new ForbiddenOperationException("查询时间区间不能超过一年");
    }

    HttpServletResponse response = ResponseUtils.getResponse();
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    //设置返回body无需包装标识
    response.setHeader(BODY_PROCESSED, "1");
    try {
        //如果查询日期是同一天，则按小时导出数据
        if (LocalDateTimeUtil.beginOfDay(maxTime).equals(minTime)) {
            downloadHourStatisticsData(response, minTime);
        } else {
            //如果查询日期不是同一天，则按日导出数据
            downloadDayStatisticsData(response, minTime, maxTime);
        }

    } catch (Exception e) {
        // 重置response,默认失败了会返回一个有部分数据的Excel
        response.reset();
        response.setContentType("application/json");
        Map<String, String> map = MapUtils.newHashMap();
        map.put("status", "failure");
        map.put("message", "下载文件失败" + e.getMessage());
        response.getWriter().println(JSON.toJSONString(map));
    }
}

private void downloadDayStatisticsData(HttpServletResponse response, LocalDateTime minTime, LocalDateTime maxTime) throws IOException {
        //模板文件路径
        String templateFileName = "static/day_statistics_template.xlsx";

        //转换时间格式，拼接下载文件名称
        String fileNameMinTimeStr = LocalDateTimeUtil.format(minTime, DatePattern.NORM_DATE_PATTERN);
        String fileNameMaxTimeStr = LocalDateTimeUtil.format(maxTime, DatePattern.NORM_DATE_PATTERN);
        String fileName = fileNameMinTimeStr + "~" + fileNameMaxTimeStr + " 全国经营分析统计.xlsx";
        // 这里URLEncoder.encode可以防止中文乱码
        fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8).replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);
        String currentTime = LocalDateTimeUtil.format(LocalDateTime.now(), "yyyy/MM/dd HH:mm:ss");

        //根据id区间查询按天统计数据，并转为map，key为日期，value为日期对应统计数据
        String minTimeDayStr = LocalDateTimeUtil.format(minTime, DatePattern.PURE_DATE_PATTERN);
        String maxTimeDayStr = LocalDateTimeUtil.format(maxTime, DatePattern.PURE_DATE_PATTERN);
        //查询按天统计表
        List<StatDay> statDayList = statDayService.queryListByIdRange(Long.valueOf(minTimeDayStr), Long.valueOf(maxTimeDayStr));
        //转成List<StatisticsData>
        List<StatisticsData> statisticsDataList = BeanUtils.copyToList(statDayList, StatisticsData.class);

        //按月份切分统计数据， 有几个月list中就有几条记录
        List<ExcelMonthData> excelMonthDataList = cutDataListByMonth(statisticsDataList);

        // 生成CellWriteHandler对象，在向单元格写数据时会调用它的afterCellDispose方法
        //  getSpecialHandleDataInfo()方法找到需要格式化处理的行索引，返回CellWriteHandler对象
        EasyExcelUtil easyExcelUtil = getSpecialHandleDataInfo(excelMonthDataList);

        try (ExcelWriter excelWriter = EasyExcel
                //注意，服务器上以jar包运行，只能使用下面第2种方式，第1种方式只能在本地运行成功
//                .write(fileName, StatisticsData.class)
                .write(response.getOutputStream(), StatisticsData.class)
                //注意，服务器上以jar包运行，只能使用下面第3种方式，前2种方式只能在本地运行成功
//                .withTemplate(templateFileName)
//                .withTemplate(FileUtil.getInputStream(templateFileName))
                .withTemplate(FileUtil.class.getClassLoader().getResourceAsStream(templateFileName))
                .autoCloseStream(Boolean.FALSE)
                .registerWriteHandler(easyExcelUtil).build()) {
            // 按天统计，选择第1个sheet，把sheet设置为不需要头
            WriteSheet writeSheet = EasyExcel.writerSheet(0).needHead(Boolean.FALSE).build();

            //构建填充数据,map的key对应模板文件中的{}中的名称
            Map<String, Object> map = MapUtils.newHashMap();
            map.put("startTime", fileNameMinTimeStr);
            map.put("endTime", fileNameMaxTimeStr);
            map.put("currentTime", currentTime);


            //写入填充数据
            excelWriter.fill(map, writeSheet);
            //向单元格式依次写入数据
            for (ExcelMonthData excelMonthData : excelMonthDataList) {
                MonthElement monthElement = new MonthElement(excelMonthData.getMonth());
                excelWriter.write(List.of(monthElement), writeSheet);//月份
                excelWriter.write(excelMonthData.getStatisticsDataList(), writeSheet);//每天的数据
                excelWriter.write(List.of(excelMonthData.getMonthAggregation()), writeSheet);//该天的汇总数据
            }

            excelWriter.finish();
        }
    }