统计

zxl

2025-10-16 79b026c753c25f998776d8a6210fc6afab97e647

 framework/src/main/java/cn/lili/modules/member/mapper/FootprintMapper.java

@@ -39,7 +39,8 @@
    void deleteLastFootPrint(String memberId);


    @Select("SELECT " +
    @Select("<script>" +
            "SELECT " +
            "lfp.ref_id AS GoodsId, " +
            "lg.goods_name AS GoodsName, "+
            "COUNT(*) AS view_count " +
@@ -48,14 +49,19 @@
            "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分组
            "AND lg.goods_name IS NOT NULL " +
            "<if test='storeId != null and storeId != \"\"'>" +
            "AND lfp.store_id = #{storeId} " +
            "</if>" +
            "GROUP BY lfp.ref_id " +
            "ORDER BY view_count DESC " +
            "LIMIT #{currentLimit}"
            "LIMIT #{currentLimit}" +
            "</script>"
            )
    List<Map<String,Object>> selectViewAndCompletionRateCountByDay(Date startTime,
                                                                   Date endTime,
                                                                   Integer currentLimit);
                                                                   Integer currentLimit,
    String storeId);

    /**
     * 按视频维度统计：每个视频的总浏览数和完播率
@@ -63,8 +69,8 @@
     * @param endTime 结束时间
     * @return 包含视频ID、名称、总浏览数、完播率的列表
     */
    @Select({
            "SELECT",
    @Select("<script>" +
            "SELECT" +
            "  lfp.ref_id AS video_id, " +
            "  lmk.title, " +
            "  COUNT(*) AS total_views, " +
@@ -84,19 +90,24 @@
            "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' " +// 只统计视频类型
            "  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 "+
            "<if test='storeId != null and storeId != \"\"'>" +
            " AND lfp.store_id = #{storeId} " +
            "</if>" +
            "GROUP BY lfp.ref_id "+
            "ORDER BY total_views DESC " +
            "LIMIT #{currentLimit}"
    })
            "LIMIT #{currentLimit}"+
            "</script>"
    )
    List<Map<String, Object>> selectEachVideoStats(
             Date startTime,
            Date endTime,
            Integer currentLimit
            Integer currentLimit,
             String storeId
    );

}