统计

zxl

2025-10-15 7aa3389ed873054a07a963ac010190d12e7ce89b

统计

 framework/src/main/java/cn/lili/modules/lmk/mapper/LmkOrderSelectMapper.java

New file
@@ -0,0 +1,24 @@
package cn.lili.modules.lmk.mapper;

import cn.lili.modules.order.order.entity.dos.Order;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;
import java.util.Map;

@Mapper
public interface LmkOrderSelectMapper extends BaseMapper<Order> {

    List<Map<String, Object>> selectOrderCountByDay(Date startTime, Date endTime);

    List<Map<String, Object>>  selectOrderTimePeriod(Date startTime, Date endTime);

    List<Map<String,Object>> selectViewDataCount(Date startTime, Date endTime);

    List<Map<String,Object>> selectProductRepurchase(Date startTime, Date endTime,Integer currentLimit);


}

 framework/src/main/java/cn/lili/modules/order/order/mapper/OrderMapper.java

@@ -193,32 +193,4 @@
    @Select("select o.* " +
            " FROM li_order o INNER JOIN li_order_item AS oi on o.sn = oi.order_sn ${ew.customSqlSegment} ")
    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

@@ -362,4 +362,6 @@
    Result getOrderCount(StatisticsQueryParam queryParam);

    Result getOrderTimePeriod(StatisticsQueryParam queryParam);

    Result gerProductRepurchase(StatisticsQueryParam queryParam);
}

 framework/src/main/java/cn/lili/modules/order/order/serviceimpl/OrderServiceImpl.java

@@ -35,6 +35,7 @@
import cn.lili.modules.lmk.domain.vo.OrderCountVO;
import cn.lili.modules.lmk.enums.general.AdminRoleEnum;
import cn.lili.modules.lmk.enums.general.VirtualGoodsTypeEnum;
import cn.lili.modules.lmk.mapper.LmkOrderSelectMapper;
import cn.lili.modules.lmk.service.CouponVirtualService;
import cn.lili.modules.member.entity.dos.Member;
import cn.lili.modules.member.entity.dto.MemberAddressDTO;
@@ -113,6 +114,7 @@
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
@@ -227,6 +229,11 @@

    @Autowired
    private GoodsSkuService goodsSkuService;

    @Autowired
    private LmkOrderSelectMapper lmkOrderSelectMapper;


    private final static  String LOCK_ORDER_NO_MQ="lock_order_no_mq:";
    private final static  String LOCK_EDIT_ORDER_ADDRESS="lock_edit_order_address:";
    private final static  String LOCK_COUPON_CARD="lock_coupon_card:";
@@ -1399,7 +1406,7 @@
            }
        }

        List<Map<String, Object>> maps = baseMapper.selectOrderCountByDay(startTime,endTime);
        List<Map<String, Object>> maps = lmkOrderSelectMapper.selectOrderCountByDay(startTime,endTime);

        Map<String, Long> dayCountMap = maps.stream()
                .collect(Collectors.toMap(
@@ -1494,15 +1501,17 @@



        List<Map<String, Object>> maps = baseMapper.selectOrderTimePeriod(startTime,endTime);

        List<Map<String, Object>> maps = lmkOrderSelectMapper.selectOrderTimePeriod(startTime,endTime);
        List<Map<String,Object>> countMap = lmkOrderSelectMapper.selectViewDataCount(startTime,endTime);
        Map<String, Long> countByDayHour = maps.stream()
                .collect(Collectors.toMap(
                        map -> map.get("hour").toString(),
                        map -> Long.valueOf(map.get("count").toString()),
                        Long::sum  // 如果有重复，累加count
                ));

        System.out.println("打印记录---------------------------------------");
        System.out.println(countMap);
        System.out.println("---------------------------------------");
        // 遍历查看结果
        List<Long> countList = new ArrayList<>();
        for (int i = 0; i < 24; i++) {
@@ -1520,6 +1529,103 @@
        return Result.ok().data(map);
    }

    @Override
    public Result gerProductRepurchase(StatisticsQueryParam queryParam) {
        Date startTime = null;
        Date endTime = new Date(); // 结束时间默认是当前时间

        Calendar calendar = Calendar.getInstance(); // 用于日期计算的日历实例

        if (queryParam.getYear() != null && queryParam.getMonth() != null) {
            Date[] dates =CommonUtil.getMonthStartAndEnd(queryParam.getYear(),queryParam.getMonth());
            startTime = dates[0];
            endTime = dates[1];
        }else{
            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 = lmkOrderSelectMapper.selectProductRepurchase(startTime,endTime,queryParam.getCurrentLimit());
        List<String> goodsData = new ArrayList<>();
        List<BigDecimal> rateData = new ArrayList<>();

        // 遍历结果集，提取数据到集合
        for (Map<String, Object> map : maps) {
            // 提取商品名（注意：键是resultMap中定义的property值"goodsName"）
            if (Objects.nonNull(map.get("goodsName"))) {
                String goodsName = map.get("goodsName").toString();
                goodsData.add(goodsName);
            }

            // 提取复购率（复购率通常是数字类型，这里用BigDecimal接收）
            if (Objects.nonNull(map.get("repurchaseRate"))) {
                // 转换为BigDecimal（根据实际类型调整，也可能是Double等）
                BigDecimal rate = new BigDecimal(map.get("repurchaseRate").toString()).setScale(2, RoundingMode.HALF_UP);
                rateData.add(rate);
            }
        }
        Map<String,Object> map = new HashMap<>();
        map.put("goodsData",goodsData);
        map.put("rateData",rateData);

        return Result.ok().data(map);
    }

    /**
     * 虚拟成团
     *

 framework/src/main/resources/mapper/lmk/orderMapper.xml

New file
@@ -0,0 +1,105 @@
<?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="cn.lili.modules.lmk.mapper.LmkOrderSelectMapper">
    <resultMap id="OrderCountByDayMap" type="java.util.Map">
        <result column="day" property="day" />
        <result column="count" property="count"/>
    </resultMap>

    <resultMap id="orderTimePeriod" type="java.util.Map">
        <result column="day" property="day" />
        <result column="hour" property="hour"/>
    </resultMap>

    <resultMap id="productRepurchase" type="java.util.Map">
        <result column="goods_name" property="goodsName"/>
        <result column="repurchase_rate_percent" property="repurchaseRate"/>
    </resultMap>

    <resultMap id="viewDataCount" type="java.util.Map">
        <result column="day" property="day" />
        <result column="hour" property="hour"/>
        <result column="count" property="count"/>
    </resultMap>

    <select id="selectOrderCountByDay"  resultMap="OrderCountByDayMap">
        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
    </select>

    <select id="selectOrderTimePeriod" resultMap="orderTimePeriod">
        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
    </select>
    <select id="selectViewDataCount" resultMap="">
        SELECT
            DATE(LAR.create_time) AS day,
            HOUR(LAR.create_time) AS hour,
            COUNT(LAR.id) AS count
        FROM lmk_action_record LAR
        where LAR.delete_flag = 0
          AND LAR.page_status = 'JOIN'
        GROUP BY day, hour
        ORDER BY day, hour;
    </select>
<!--buyer_count购买人数  buy_times总购买次数  repurchase_buyer_count 复购人数-->
    <select id="selectProductRepurchase"  resultMap="productRepurchase">
        SELECT
            t.goods_name,
            t.total_users AS buyer_count,
            t.total_buy_times AS buy_times,
            t.repurchase_users AS repurchase_buyer_count,
            ROUND(
                IFNULL(t.repurchase_users / t.total_users, 0) * 100,
                2
            ) AS repurchase_rate_percent
        FROM (
                 SELECT
                     goods_id,
                     goods_name,
                     COUNT(DISTINCT member_id) AS total_users,
                     SUM(user_buy_counts.buy_count) AS total_buy_times,
                     SUM(CASE WHEN buy_count >= 2 THEN 1 ELSE 0 END) AS repurchase_users
                 FROM (
                          SELECT
                              i.goods_id,
                              i.goods_name,
                              o.member_id,
                              COUNT(DISTINCT o.sn) AS buy_count
                          FROM li_order_item i
                              INNER JOIN li_order o ON i.order_sn = o.sn
                          WHERE
                              o.order_status = 'COMPLETED'
                            AND o.delete_flag = 0
                            AND i.delete_flag = 0
                            AND o.member_id IS NOT NULL
                            AND o.create_time >= #{startTime}
                            AND o.create_time &lt;= #{endTime}
                          GROUP BY i.goods_id, i.goods_name, o.member_id
                      ) AS user_buy_counts
                 GROUP BY goods_id, goods_name
             ) AS t
        ORDER BY repurchase_rate_percent DESC
        LIMIT #{currentLimit};
    </select>

</mapper>

 manager-api/src/main/java/cn/lili/controller/lmk/StatisticsController.java

@@ -106,4 +106,14 @@
        return orderService.getOrderTimePeriod(queryParam);

    }

    /**
     * 商品复购率
     * @param queryParam
     * @return
     */
    @GetMapping("/productRepurchase")
    public Result gerProductRepurchase(StatisticsQueryParam queryParam){
        return orderService.gerProductRepurchase(queryParam);
    }
}