From 076c7f1df538ed9c04701ebda3f6fe11c47e91e4 Mon Sep 17 00:00:00 2001
From: zxl <763096477@qq.com>
Date: 星期四, 25 九月 2025 17:16:11 +0800
Subject: [PATCH] 订单统计
---
framework/src/main/java/cn/lili/modules/member/mapper/FootprintMapper.java | 67 +++++++++++++++++++++++++++++++++
1 files changed, 67 insertions(+), 0 deletions(-)
diff --git a/framework/src/main/java/cn/lili/modules/member/mapper/FootprintMapper.java b/framework/src/main/java/cn/lili/modules/member/mapper/FootprintMapper.java
index f1897f4..d8af27e 100644
--- a/framework/src/main/java/cn/lili/modules/member/mapper/FootprintMapper.java
+++ b/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 " + // 鎸夊晢鍝両D鍒嗙粍
+ "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
+ );
+
}
\ No newline at end of file
--
Gitblit v1.8.0