【苍穹外卖】项目日记(超详细) day10

最新推荐文章于 2025-08-02 12:01:09 发布

海梨花

最新推荐文章于 2025-08-02 12:01:09 发布

阅读量410

点赞数 4

CC 4.0 BY-SA版权

文章标签： java 数据库后端

本文链接：https://blog.youkuaiyun.com/2301_79903357/article/details/149842775

D10

数据统计图形报表

这里借助Apache Echarts，一个前端数据可视化平台

营业额统计

表现层

设置传入数据格式

@RestController
@Api("数据相关统计接口")
@RequestMapping("/admin/report")
@Slf4j
public class ReportController {

    @Autowired
    private ReportService reportService;

    @GetMapping("/turnoverStatistics")
    @ApiOperation("营业额统计")
    public Result<TurnoverReportVO> turnoverStatistics(
        @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin,
        @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end
    ){
        log.info("营业额统计：{}，{}",begin, end);
        TurnoverReportVO turnoverStatistics = reportService.getTurnoverStatistics(begin, end);
        return Result.success(turnoverStatistics);
    }
}

实现类

将指定时间段内的日期放到List中，遍历日期，对每天进行查询营业额

@Service
@Slf4j
public class ReportServiceImpl implements ReportService {

    @Autowired
    private OrderMapper orderMapper;
    /**
     * 统计指定时间区间内的营业额数据
     * @param begin
     * @param end
     * @return
     */
    public TurnoverReportVO getTurnoverStatistics(LocalDate begin, LocalDate end) {
        //当前集合用于存放从begin到end范围内的每天的日期
        List<LocalDate> dateList = new ArrayList<>();

        dateList.add(begin);
        while(!begin.equals(end)){
            //日期计算，计算指定日期的后一天对应的日期
            begin = begin.plusDays(1);
            dateList.add(begin);
        }
        List<Double> turnoverList = new ArrayList<>();
        for (LocalDate date : dateList) {
            //查询date日期对应的营业额数据，营业额是指，状态为“已完成”的订单金额合计
            LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);//表示某天的00:00
            LocalDateTime endTime = LocalDateTime.of(date, LocalTime.MAX);

            // select * from order where status = ? and order_time > beginTime and orderTime < endTime
            Map map = new HashMap();
            map.put("begin", beginTime);
            map.put("end", endTime);
            map.put("status", Orders.COMPLETED);
            Double turnover = orderMapper.sumByMap(map);
            turnover = turnover == null ? 0.0 : turnover;
            turnoverList.add(turnover);
        }

        //封装返回结果
        return TurnoverReportVO
        .builder()
        .dateList(StringUtils.join(dateList,","))
        .turnoverList(StringUtils.join(turnoverList, ","))
        .build();

    }
}

映射文件

<select id="sumByMap" resultType="java.lang.Double">
  select sum(amount) from orders
  <where>
    <if test="begin != null">
      and order_time &gt; #{begin}
    </if>
    <if test="end != null">
      and order_time &lt; #{end}
    </if>
    <if test="status != null">
      and status = #{status}
    </if>
  </where>
</select>

用户统计

表现层

/**
 * 用户统计
 * @param begin
 * @param end
 * @return
 */
@GetMapping("/userStatistics")
@ApiOperation("用户统计")
public Result<UserReportVO> userStatistics(
    @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin,
    @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end
){
    log.info("用户数据统计：{}, {}", begin, end);
    return Result.success(reportService.getUserStatistics(begin, end));
}

实现类

这块重点是动态SQL的编写，查询用户数量主要涉及select count from user where create_time > ? and create_time < ?，问号处分别为一天的起始时间和结束时间。

比如：

如果是查询新增用户，那么问号处不为空，表示在某天新增的注册用户。

如果是查询用户总数，只需要endTime，表示查询在这之前的所有用户。

/**
 * 统计指定时间内的用户数据
 * @param begin
 * @param end
 * @return
 */
public UserReportVO getUserStatistics(LocalDate begin, LocalDate end) {
    //当前集合用于存放从begin到end的范围的每天日期
    List<LocalDate> dateList = new ArrayList<>();

    while(!begin.equals(end)){
        begin = begin.plusDays(1);
        dateList.add(begin);
    }

    // 存放每天的新增用户 select count(id) from user where create_time > ? and create_time < ?
    List<Integer> newUserList = new ArrayList<>();
    // 存放每天的总用户数量 select count(id) from user where create_time < ?
    List<Integer> totalUserList = new ArrayList<>();

    for (LocalDate date : dateList) {
        LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);
        LocalDateTime endTime = LocalDateTime.of(date, LocalTime.MAX);

        Map map = new HashMap();
        map.put("end", endTime);

        //计算每天的总用户数量
        Integer userTotal = userMapper.countByMap(map);

        //计算每天的新增用户数量
        map.put("begin", beginTime);
        Integer newUser = userMapper.countByMap(map);

        newUserList.add(newUser);
        totalUserList.add(userTotal);

    }
    //封装结果数据
    return UserReportVO.builder()
    .dateList(StringUtils.join(dateList, ","))
    .newUserList(StringUtils.join(newUserList, ","))
    .totalUserList(StringUtils.join(totalUserList, ","))
    .build();


}

<select id="countByMap" resultType="java.lang.Integer">
  select count(id) from user
  <where>
    <if test="begin != null">
      and create_time &gt; #{begin}
    </if>
    <if test="end != null">
      and create_time &lt; #{end}
    </if>
  </where>
</select>

订单统计

表现层

/**
 * 订单数据统计
 * @param begin
 * @param end
 * @return
 */
@GetMapping("/ordersStatistics")
@ApiOperation("订单数据统计")
public Result<OrderReportVO> ordersStatistics(
    @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin,
    @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end
){
    log.info("订单数据统计：{}, {}", begin, end);
    return Result.success(reportService.getOrderStatistics(begin, end));
}

实现类

实现类代码比较长，因为本业务要求返回orderReportVo对象，具有6个属性，具体实现见如下注释，总的来说这三类业务逻辑实现是相近的。

/**
 * 统计指定时间内的订单数据统计
 * @param begin
 * @param end
 * @return
 */
public OrderReportVO getOrderStatistics(LocalDate begin, LocalDate end) {
    // 获取当前时间段所有日期
    List<LocalDate> dateList = new ArrayList<>();

    dateList.add(begin);

    while(!begin.equals(end)){
        begin = begin.plusDays(1);
        dateList.add(begin);
    }
    //存放每天的订单数
    List<Integer> orderCountList = new ArrayList<>();
    //存放每天的有效订单数
    List<Integer> validOrderCountList = new ArrayList<>();

    //遍历datelist，查询每天的订单数和有效订单数
    for (LocalDate date : dateList) {
        LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);
        LocalDateTime endTime = LocalDateTime.of(date, LocalTime.MAX);

        //查询每天的订单总数 select count(id) from orders where order_time > ? and order_time < ?
        Map map = new HashMap();
        map.put("begin", beginTime);
        map.put("end", endTime);
        Integer orderCount = orderMapper.countByOrders(map);

        //查询每天有效订单数 select count(id) from orders where order_time > ? and order_time < ? and status = 5
        map.put("status", Orders.COMPLETED);
        Integer validOrderCount = orderMapper.countByOrders(map);

        orderCountList.add(orderCount);
        validOrderCountList.add(validOrderCount);

    }

    //计算时间区间内的订单总数量
    Integer totalOrderCount = orderCountList.stream().reduce(Integer::sum).get();

    //计算时间区间内的有效订单数量
    Integer validOrderCount = validOrderCountList.stream().reduce(Integer::sum).get();

    //计算订单完成率
    Double orderCompletionRate = 0.0;
    if(totalOrderCount != 0){
        orderCompletionRate = validOrderCount.doubleValue() / totalOrderCount;
    }

    return OrderReportVO.builder()
    .dateList(StringUtils.join(dateList, ","))
    .validOrderCountList(StringUtils.join(validOrderCountList, ","))
    .orderCountList(StringUtils.join(orderCountList, ","))
    .validOrderCount(validOrderCount)
    .orderCompletionRate(orderCompletionRate)
    .totalOrderCount(totalOrderCount)
    .build();
}

销量排名统计

需求分析

表现层

/**
 * 销量排名top10
 * @param begin
 * @param end
 * @return
 */
@GetMapping("/top10")
@ApiOperation("销量排名top10")
public Result<SalesTop10ReportVO> top10(
    @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin,
    @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end
){
    log.info("销量排名top10：{}, {}", begin, end);
    return Result.success(reportService.getSalesTop10(begin, end));
}

实现类

因为涉及多表查询，所以这部分SQL会略显复杂，但整体功能实现还是比较简单的，要注意这里的stream流的用法，需要保持熟悉。

/**
 * 统计指定时间内的销量排名前10
 * @param begin
 * @param end
 * @return
 */
public SalesTop10ReportVO getSalesTop10(LocalDate begin, LocalDate end) {
    // select od.name, sum(od.number) number from order_detail od, orders o
    // where od.order_id = o.id and o.status = 5 and order_time > ? and order_time < ?
    // group by od.name
    // order by number desc
    // limit 0,10

    LocalDateTime beginTime = LocalDateTime.of(begin, LocalTime.MIN);
    LocalDateTime endTime = LocalDateTime.of(begin, LocalTime.MAX);

    List<GoodsSalesDTO> salesTop10 = orderMapper.getSalesTop10(beginTime, endTime);

    List<String> names = salesTop10.stream().map(GoodsSalesDTO::getName).collect(Collectors.toList());
    String nameList = StringUtils.join(names, ",");

    List<String> number = salesTop10.stream().map(GoodsSalesDTO::getName).collect(Collectors.toList());
    String numberList = StringUtils.join(number, ",");

    //封装返回结果
    return SalesTop10ReportVO.builder()
    .nameList(nameList)
    .numberList(numberList)
    .build();
}

<select id="getSalesTop10" resultType="com.sky.dto.GoodsSalesDTO">
  select od.name, sum(od.number) number from order_detail od, orders o
  where od.order_id = o.id and o.status = 5
  <if test="begin != null">
    and order_time &gt; #{begin}
  </if>
  <if test="end != null">
    and order_time &lt; #{end}
  </if>
  group by od.name
  order by number desc
  limit 0,10
</select>