framework/src/main/java/cn/lili/modules/lmk/domain/vo/VideoViewCompletionRateVO.java
New file @@ -0,0 +1,22 @@ package cn.lili.modules.lmk.domain.vo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; /** * lmk-shop-java * * @author : zxl * @date : 2025-09-25 15:36 **/ @Data @NoArgsConstructor @AllArgsConstructor public class VideoViewCompletionRateVO { private String complete_rate; private Long total_views; } framework/src/main/java/cn/lili/modules/member/mapper/FootprintMapper.java
@@ -3,6 +3,12 @@ import cn.lili.modules.member.entity.dos.FootPrint; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import org.apache.ibatis.annotations.Delete; import org.apache.ibatis.annotations.Param; import org.apache.ibatis.annotations.Select; import java.util.Date; import java.util.List; import java.util.Map; /** * 浏览历史数据处理层 @@ -32,4 +38,65 @@ "WHERE li_foot_print.member_id = ${memberId} AND latest_footprints.id IS NULL; ") void deleteLastFootPrint(String memberId); @Select("SELECT " + "lfp.ref_id AS GoodsId, " + "lg.goods_name AS GoodsName, "+ "COUNT(*) AS view_count " + "FROM li_foot_print lfp " + "LEFT JOIN li_goods lg ON lfp.ref_id = lg.id " + "WHERE lfp.delete_flag = 0 " + "AND lfp.create_time BETWEEN #{startTime} AND #{endTime} " + "AND lfp.view_type = 'goods' " + "AND lg.goods_name is not null " + "GROUP BY lfp.ref_id " + // 按商品ID分组 "ORDER BY view_count DESC " + "LIMIT #{currentLimit}" ) List<Map<String,Object>> selectViewAndCompletionRateCountByDay(Date startTime, Date endTime, Integer currentLimit); /** * 按视频维度统计：每个视频的总浏览数和完播率 * @param startTime 开始时间 * @param endTime 结束时间 * @return 包含视频ID、名称、总浏览数、完播率的列表 */ @Select({ "SELECT", " lfp.ref_id AS video_id, " + " lmk.title, " + " COUNT(*) AS total_views, " + " ROUND(" + " IF(COUNT(*) = 0, 0," + " (SUM(" + " CASE WHEN ( " + " CAST(lfp.play_at AS DECIMAL(10,3)) >= CAST(lmk.video_duration AS DECIMAL(10,3)) " + " OR " + " CAST(lfp.play_at AS DECIMAL(10,3)) / CAST(lmk.video_duration AS DECIMAL(10,3)) > 0.9 " + " ) THEN 1 " + " ELSE 0 " + " END " + " ) / COUNT(*)) * 100 " + " ), 2 " + " ) AS complete_rate " + "FROM li_foot_print lfp" + " LEFT JOIN lmk_video lmk ON lfp.ref_id = lmk.id " + // 按实际关联字段调整 "WHERE " + " lfp.delete_flag = 0", " AND lfp.view_type = 'video' " +// 只统计视频类型 " AND lfp.create_time BETWEEN #{startTime} AND #{endTime} " + " AND lmk.video_duration > 0 " + " AND ref_id IS NOT NULL AND lmk.title IS NOT NULL "+ "GROUP BY lfp.ref_id "+ "ORDER BY total_views DESC " + "LIMIT #{currentLimit}" }) List<Map<String, Object>> selectEachVideoStats( Date startTime, Date endTime, Integer currentLimit ); } framework/src/main/java/cn/lili/modules/member/service/FootprintService.java
@@ -8,6 +8,7 @@ import cn.lili.modules.member.entity.dos.FootPrint; import cn.lili.modules.member.entity.dto.FootPrintQueryParams; import cn.lili.modules.search.entity.dos.EsGoodsIndex; import cn.lili.modules.statistics.entity.dto.StatisticsQueryParam; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.service.IService; @@ -75,4 +76,5 @@ */ long getFootprintNum(); Result getViewAndCompletionRateCount(StatisticsQueryParam queryParam); } framework/src/main/java/cn/lili/modules/member/serviceimpl/FootprintServiceImpl.java
@@ -2,19 +2,18 @@ import cn.lili.base.Result; import cn.lili.common.security.context.UserContext; import cn.lili.common.utils.StringUtils; import cn.lili.modules.goods.entity.dos.GoodsSku; import cn.lili.modules.goods.service.GoodsSkuService; import cn.lili.modules.lmk.domain.query.FootPrintQuery; import cn.lili.modules.lmk.domain.vo.SimpleVideoTagVO; import cn.lili.modules.lmk.domain.vo.VideoFootInfoVo; import cn.lili.modules.lmk.domain.vo.VideoFootVO; import cn.lili.modules.lmk.domain.vo.VideoVO; import cn.lili.modules.lmk.domain.vo.*; import cn.lili.modules.lmk.mapper.VideoMapper; import cn.lili.modules.member.entity.dos.FootPrint; import cn.lili.modules.member.entity.dto.FootPrintQueryParams; import cn.lili.modules.member.mapper.FootprintMapper; import cn.lili.modules.member.service.FootprintService; import cn.lili.modules.search.entity.dos.EsGoodsIndex; import cn.lili.modules.statistics.entity.dto.StatisticsQueryParam; import cn.lili.mybatis.util.PageUtil; import cn.lili.utils.COSUtil; import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper; @@ -28,6 +27,7 @@ import java.math.BigDecimal; import java.math.RoundingMode; import java.text.SimpleDateFormat; import java.util.*; import java.util.stream.Collectors; import java.util.stream.IntStream; @@ -168,7 +168,151 @@ return this.count(lambdaQueryWrapper); } @Override public Result getViewAndCompletionRateCount(StatisticsQueryParam queryParam) { Date startTime = null; Date endTime = new Date(); // 结束时间默认是当前时间 List<String> dateList = new ArrayList<>(); Calendar calendar = Calendar.getInstance(); // 用于日期计算的日历实例 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); int days = 0; switch (queryParam.getSearchType()) { case "TODAY": // 今天：从今天0点到现在 calendar.setTime(new Date()); // 重置为当前时间 calendar.set(Calendar.HOUR_OF_DAY, 0); // 小时设为0（24小时制） calendar.set(Calendar.MINUTE, 0); // 分钟设为0 calendar.set(Calendar.SECOND, 0); // 秒设为0 calendar.set(Calendar.MILLISECOND, 0); // 毫秒设为0 startTime = calendar.getTime(); // 得到今天0点的Date对象 dateList.add(sdf.format(startTime)); break; case "YESTERDAY": // 昨天：从昨天0点到昨天23:59:59.999 calendar.setTime(new Date()); calendar.add(Calendar.DATE, -1); // 日期减1天（变为昨天） // 设置昨天0点 calendar.set(Calendar.HOUR_OF_DAY, 0); calendar.set(Calendar.MINUTE, 0); calendar.set(Calendar.SECOND, 0); calendar.set(Calendar.MILLISECOND, 0); startTime = calendar.getTime(); // 设置昨天23:59:59.999 calendar.set(Calendar.HOUR_OF_DAY, 23); calendar.set(Calendar.MINUTE, 59); calendar.set(Calendar.SECOND, 59); calendar.set(Calendar.MILLISECOND, 999); endTime = calendar.getTime(); dateList.add(sdf.format(startTime)); break; case "LAST_SEVEN": // 过去七天：从7天前0点到现在（含今天共7天） calendar.setTime(new Date()); calendar.add(Calendar.DATE, -6); // 日期减6天（7天前的今天） // 设置7天前0点 calendar.set(Calendar.HOUR_OF_DAY, 0); calendar.set(Calendar.MINUTE, 0); calendar.set(Calendar.SECOND, 0); calendar.set(Calendar.MILLISECOND, 0); startTime = calendar.getTime(); days = 7; // 循环生成7天的日期字符串 Calendar tempCalendar = Calendar.getInstance(); tempCalendar.setTime(startTime); for (int i = 0; i < days; i++) { dateList.add(sdf.format(tempCalendar.getTime())); tempCalendar.add(Calendar.DATE, 1); // 每天累加1天 } break; case "LAST_THIRTY": // 过去30天：从30天前0点到现在（含今天共30天） calendar.setTime(new Date()); calendar.add(Calendar.DATE, -29); // 日期减29天（30天前的今天） // 设置30天前0点 calendar.set(Calendar.HOUR_OF_DAY, 0); calendar.set(Calendar.MINUTE, 0); calendar.set(Calendar.SECOND, 0); calendar.set(Calendar.MILLISECOND, 0); startTime = calendar.getTime(); days = 30; // 循环生成30天的日期字符串 Calendar tempCalendar30 = Calendar.getInstance(); tempCalendar30.setTime(startTime); for (int i = 0; i < days; i++) { dateList.add(sdf.format(tempCalendar30.getTime())); tempCalendar30.add(Calendar.DATE, 1); // 每天累加1天 } break; default: return Result.error("不支持的时间范围类型"); } if ("goods".equals(queryParam.getCurrentType())){ List<Map<String, Object>> maps = baseMapper.selectViewAndCompletionRateCountByDay(startTime, endTime,queryParam.getCurrentLimit()); Map<String,Long> viewPrintMap = maps.stream() .collect(Collectors.toMap( map -> { String goodsId = Objects.toString(map.get("GoodsId"), ""); // 空值处理 String goodsName = Objects.toString(map.get("GoodsName"), ""); // 空值处理 return goodsName + " | " + goodsId; // 用|分隔，方便后续拆分 }, map-> Long.valueOf(map.get("view_count").toString()), (existing, replacement) -> existing, LinkedHashMap::new // 指定使用 LinkedHashMap，保留插入顺序 )); List<String> yData = new ArrayList<>(); List<Long> xData = new ArrayList<>(); for (Map.Entry<String, Long> entry : viewPrintMap.entrySet()){ yData.add(entry.getKey()); xData.add(entry.getValue()); } Map<String,Object> data = new HashMap<>(); data.put("yData", yData); data.put("xData", xData); return Result.ok().data(data); }else{ List<Map<String, Object>> maps = baseMapper.selectEachVideoStats(startTime, endTime,queryParam.getCurrentLimit()); Map<String, VideoViewCompletionRateVO> viewPrintMap = maps.stream() .collect(Collectors.toMap( map -> { String videoId = Objects.toString(map.get("video_id"), ""); // 空值处理 String videoName = Objects.toString(map.get("title"), ""); // 空值处理 return videoName + " | " + videoId; // 用|分隔，方便后续拆分 }, map-> { VideoViewCompletionRateVO vo = new VideoViewCompletionRateVO(); vo.setTotal_views(Long.valueOf(map.get("total_views").toString())); vo.setComplete_rate(map.get("complete_rate").toString()); return vo; }, (existing, replacement) -> existing, LinkedHashMap::new // 指定使用 LinkedHashMap，保留插入顺序 )); List<String> yData = new ArrayList<>(); List<Long> xData = new ArrayList<>(); List<String> rateData = new ArrayList<>(); for (Map.Entry<String, VideoViewCompletionRateVO> entry : viewPrintMap.entrySet()){ yData.add(entry.getKey()); xData.add(entry.getValue().getTotal_views()); rateData.add(entry.getValue().getComplete_rate()); } Map<String,Object> data = new HashMap<>(); data.put("yData", yData); data.put("xData", xData); data.put("rateData", rateData); return Result.ok().data(data); } } } framework/src/main/java/cn/lili/modules/order/order/mapper/OrderMapper.java
@@ -13,7 +13,9 @@ import org.apache.ibatis.annotations.Select; import org.apache.ibatis.annotations.Update; import java.util.Date; import java.util.List; import java.util.Map; /** * 订单数据处理层 @@ -192,4 +194,30 @@ List<Order> queryListByParams(@Param(Constants.WRAPPER) Wrapper<Order> queryWrapper); @Select("SELECT " + " DATE(create_time) AS day, " + " COUNT(*) AS count " + " FROM" + " li_order o" + " WHERE " + " o.create_time BETWEEN #{startTime} AND #{endTime} " + " GROUP BY" + " day " + " ORDER BY " + " day ASC") List<Map<String, Object>> selectOrderCountByDay(Date startTime, Date endTime); @Select("SELECT " + " DATE(create_time) AS day, " + " HOUR(create_time) AS hour," + " COUNT(*) AS count " + " FROM" + " li_order o" + " WHERE " + " o.create_time BETWEEN #{startTime} AND #{endTime} " + " GROUP BY" + " day " + " ORDER BY " + " day , hour ASC") List<Map<String, Object>> selectOrderTimePeriod(Date startTime, Date endTime); } framework/src/main/java/cn/lili/modules/order/order/service/OrderService.java
@@ -360,4 +360,6 @@ * @return */ Result getOrderCount(StatisticsQueryParam queryParam); Result getOrderTimePeriod(StatisticsQueryParam queryParam); } framework/src/main/java/cn/lili/modules/order/order/serviceimpl/OrderServiceImpl.java
@@ -90,6 +90,7 @@ import com.baomidou.mybatisplus.core.toolkit.Wrappers; import com.baomidou.mybatisplus.extension.conditions.query.LambdaQueryChainWrapper; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import io.reactivex.rxjava3.core.Maybe; import lombok.extern.slf4j.Slf4j; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; @@ -112,6 +113,7 @@ import java.io.InputStream; import java.math.BigDecimal; import java.net.URLEncoder; import java.text.SimpleDateFormat; import java.time.LocalDate; import java.time.LocalDateTime; import java.time.LocalTime; @@ -1275,61 +1277,193 @@ @Override public Result getOrderCount(StatisticsQueryParam queryParam) { LocalDateTime startTime = null; LocalDateTime endTime = LocalDateTime.now(); // 结束时间默认是当前时间 int days = 0; Date startTime = null; Date endTime = new Date(); // 结束时间默认是当前时间 List<String> dateList = new ArrayList<>(); Calendar calendar = Calendar.getInstance(); // 用于日期计算的日历实例 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); int days = 0; switch (queryParam.getSearchType()) { case "TODAY": // 今天：从今天0点到现在 startTime = LocalDateTime.of(LocalDate.now(), LocalTime.MIN); days = 1; calendar.setTime(new Date()); // 重置为当前时间 calendar.set(Calendar.HOUR_OF_DAY, 0); // 小时设为0（24小时制） calendar.set(Calendar.MINUTE, 0); // 分钟设为0 calendar.set(Calendar.SECOND, 0); // 秒设为0 calendar.set(Calendar.MILLISECOND, 0); // 毫秒设为0 startTime = calendar.getTime(); // 得到今天0点的Date对象 dateList.add(sdf.format(startTime)); break; case "YESTERDAY": // 昨天：从昨天0点到昨天23:59:59 LocalDate yesterday = LocalDate.now().minusDays(1); startTime = LocalDateTime.of(yesterday, LocalTime.MIN); endTime = LocalDateTime.of(yesterday, LocalTime.MAX); days = 1; // 昨天：从昨天0点到昨天23:59:59.999 calendar.setTime(new Date()); calendar.add(Calendar.DATE, -1); // 日期减1天（变为昨天） // 设置昨天0点 calendar.set(Calendar.HOUR_OF_DAY, 0); calendar.set(Calendar.MINUTE, 0); calendar.set(Calendar.SECOND, 0); calendar.set(Calendar.MILLISECOND, 0); startTime = calendar.getTime(); // 设置昨天23:59:59.999 calendar.set(Calendar.HOUR_OF_DAY, 23); calendar.set(Calendar.MINUTE, 59); calendar.set(Calendar.SECOND, 59); calendar.set(Calendar.MILLISECOND, 999); endTime = calendar.getTime(); dateList.add(sdf.format(startTime)); break; case "LAST_SEVEN": // 过去七天：从7天前0点到现在 startTime = LocalDateTime.of(LocalDate.now().minusDays(6), LocalTime.MIN); // 过去七天：从7天前0点到现在（含今天共7天） calendar.setTime(new Date()); calendar.add(Calendar.DATE, -6); // 日期减6天（7天前的今天） // 设置7天前0点 calendar.set(Calendar.HOUR_OF_DAY, 0); calendar.set(Calendar.MINUTE, 0); calendar.set(Calendar.SECOND, 0); calendar.set(Calendar.MILLISECOND, 0); startTime = calendar.getTime(); days = 7; // 循环生成7天的日期字符串 Calendar tempCalendar = Calendar.getInstance(); tempCalendar.setTime(startTime); for (int i = 0; i < days; i++) { dateList.add(sdf.format(tempCalendar.getTime())); tempCalendar.add(Calendar.DATE, 1); // 每天累加1天 } break; case "LAST_THIRTY": // 过去30天：从30天前0点到现在 startTime = LocalDateTime.of(LocalDate.now().minusDays(29), LocalTime.MIN); // 过去30天：从30天前0点到现在（含今天共30天） calendar.setTime(new Date()); calendar.add(Calendar.DATE, -29); // 日期减29天（30天前的今天） // 设置30天前0点 calendar.set(Calendar.HOUR_OF_DAY, 0); calendar.set(Calendar.MINUTE, 0); calendar.set(Calendar.SECOND, 0); calendar.set(Calendar.MILLISECOND, 0); startTime = calendar.getTime(); days = 30; // 循环生成30天的日期字符串 Calendar tempCalendar30 = Calendar.getInstance(); tempCalendar30.setTime(startTime); for (int i = 0; i < days; i++) { dateList.add(sdf.format(tempCalendar30.getTime())); tempCalendar30.add(Calendar.DATE, 1); // 每天累加1天 } break; default: return Result.error("不支持的时间范围类型"); } List<Map<String, Object>> maps = baseMapper.selectOrderCountByDay(startTime,endTime); // 2. 查询该时间范围内的每日订单数量（按日期分组） // LambdaQueryWrapper<Order> queryWrapper = Wrappers.lambdaQuery(); // queryWrapper.between(Order::getCreateTime, startTime, endTime); // // 按日期分组（提取日期部分） // // // 执行查询，返回日期和数量的映射（day -> count） // List<Map<String, Object>> maps = baseMapper.selectMaps(queryWrapper); // Map<String, Long> dayCountMap = maps.stream() // .collect(Collectors.toMap( // map -> map.get("day").toString(), // 日期字符串（如2023-09-19） // map -> Long.valueOf(map.get("count").toString()) // 订单数量 // )); // // // 3. 生成完整的日期列表（确保即使某天没有订单也会返回0） // List<Long> orderCounts = new ArrayList<>(days); // for (int i = 0; i < days; i++) { // // 计算当前循环对应的日期（从startTime开始的第i天） // LocalDate currentDate = startTime.toLocalDate().plusDays(i); // String dateStr = currentDate.toString(); // 转为yyyy-MM-dd格式 // // 从映射中获取数量，没有则为0 // orderCounts.add(dayCountMap.getOrDefault(dateStr, 0L)); // } Map<String, Long> dayCountMap = maps.stream() .collect(Collectors.toMap( map -> map.get("day").toString(), // 日期字符串（如2023-09-19） map -> Long.valueOf(map.get("count").toString()) // 订单数量 )); return null; List<Long> orderCounts = new ArrayList<>(); for (String date : dateList) { // 核心：存在则取查询结果，不存在则补0 orderCounts.add(dayCountMap.getOrDefault(date, 0L)); } return Result.ok().data(orderCounts); } @Override public Result getOrderTimePeriod(StatisticsQueryParam queryParam) { Date startTime = null; Date endTime = new Date(); // 结束时间默认是当前时间 List<String> dateList = new ArrayList<>(); Calendar calendar = Calendar.getInstance(); // 用于日期计算的日历实例 for (int i =0;i<=24;i++){ dateList.add(i + "点"); } switch (queryParam.getSearchType()) { case "TODAY": // 今天：从今天0点到现在 calendar.setTime(new Date()); // 重置为当前时间 calendar.set(Calendar.HOUR_OF_DAY, 0); // 小时设为0（24小时制） calendar.set(Calendar.MINUTE, 0); // 分钟设为0 calendar.set(Calendar.SECOND, 0); // 秒设为0 calendar.set(Calendar.MILLISECOND, 0); // 毫秒设为0 startTime = calendar.getTime(); // 得到今天0点的Date对象 break; case "YESTERDAY": // 昨天：从昨天0点到昨天23:59:59.999 calendar.setTime(new Date()); calendar.add(Calendar.DATE, -1); // 日期减1天（变为昨天） // 设置昨天0点 calendar.set(Calendar.HOUR_OF_DAY, 0); calendar.set(Calendar.MINUTE, 0); calendar.set(Calendar.SECOND, 0); calendar.set(Calendar.MILLISECOND, 0); startTime = calendar.getTime(); // 设置昨天23:59:59.999 calendar.set(Calendar.HOUR_OF_DAY, 23); calendar.set(Calendar.MINUTE, 59); calendar.set(Calendar.SECOND, 59); calendar.set(Calendar.MILLISECOND, 999); endTime = calendar.getTime(); break; case "LAST_SEVEN": // 过去七天：从7天前0点到现在（含今天共7天） calendar.setTime(new Date()); calendar.add(Calendar.DATE, -6); // 日期减6天（7天前的今天） // 设置7天前0点 calendar.set(Calendar.HOUR_OF_DAY, 0); calendar.set(Calendar.MINUTE, 0); calendar.set(Calendar.SECOND, 0); calendar.set(Calendar.MILLISECOND, 0); startTime = calendar.getTime(); break; case "LAST_THIRTY": // 过去30天：从30天前0点到现在（含今天共30天） calendar.setTime(new Date()); calendar.add(Calendar.DATE, -29); // 日期减29天（30天前的今天） // 设置30天前0点 calendar.set(Calendar.HOUR_OF_DAY, 0); calendar.set(Calendar.MINUTE, 0); calendar.set(Calendar.SECOND, 0); calendar.set(Calendar.MILLISECOND, 0); startTime = calendar.getTime(); break; default: return Result.error("不支持的时间范围类型"); } List<Map<String, Object>> maps = baseMapper.selectOrderTimePeriod(startTime,endTime); Map<String, Long> dayCountMap = maps.stream() .collect(Collectors.toMap( map -> map.get("day").toString(), // 日期字符串（如2023-09-19） map -> Long.valueOf(map.get("count").toString()) // 订单数量 )); List<Long> orderCounts = new ArrayList<>(); for (String date : dateList) { // 核心：存在则取查询结果，不存在则补0 orderCounts.add(dayCountMap.getOrDefault(date, 0L)); } return Result.ok().data(orderCounts); } /** framework/src/main/java/cn/lili/modules/statistics/entity/dto/StatisticsQueryParam.java
@@ -31,4 +31,10 @@ @ApiModelProperty(value = "店铺ID") private String storeId; @ApiModelProperty(value = "类型：商品goods 、视频video") private String currentType; @ApiModelProperty(value = "显示排名：10,20,30") private Integer currentLimit; } manager-api/src/main/java/cn/lili/controller/lmk/StatisticsController.java
@@ -6,6 +6,7 @@ import cn.lili.common.vo.ResultMessage; import cn.lili.modules.lmk.domain.vo.PvUvVO; import cn.lili.modules.lmk.enums.general.StatisticsSearchTypeEnum; import cn.lili.modules.member.service.FootprintService; import cn.lili.modules.order.order.service.OrderService; import cn.lili.modules.statistics.entity.dto.StatisticsQueryParam; import cn.lili.modules.statistics.entity.vo.PlatformViewVO; @@ -31,6 +32,8 @@ @RequestMapping("/manager/lmk/statistics") public class StatisticsController { private final OrderService orderService; private final FootprintService footprintService; @ApiOperation(value = "获取pv、uv流量数据 表单获取") @GetMapping("/pvUv") @@ -81,4 +84,20 @@ } /** * 视频/商品 完播率和浏览量统计 * @param queryParam * @return */ @GetMapping("/viewAndCompletionRateCount") public Result getViewAndCompletionRateCount(StatisticsQueryParam queryParam) { return footprintService.getViewAndCompletionRateCount(queryParam); } @GetMapping("/orderTimePeriod") public Result getOrderTimePeriod(StatisticsQueryParam queryParam) { return orderService.getOrderTimePeriod(queryParam); } }
