package cn.lili.modules.member.mapper;
|
|
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;
|
|
/**
|
* 浏览历史数据处理层
|
*
|
* @author Chopper
|
* @since 2020-02-25 14:10:16
|
*/
|
public interface FootprintMapper extends BaseMapper<FootPrint> {
|
/**
|
* 删除超过100条后的记录
|
*
|
* @param memberId 会员ID
|
*/
|
@Delete("DELETE li_foot_print " +
|
"FROM li_foot_print " +
|
"LEFT JOIN ( " +
|
" SELECT id " +
|
" FROM ( " +
|
" SELECT id " +
|
" FROM li_foot_print " +
|
" WHERE member_id = ${memberId} " +
|
" ORDER BY create_time DESC " +
|
" LIMIT 100 " +
|
" ) AS keep " +
|
") AS latest_footprints " +
|
"ON li_foot_print.id = latest_footprints.id " +
|
"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
|
);
|
|
}
|
