用户行为数统计

本业务根据每日统计的日志log表按时进行统计，将统计的结果记录到 Analysis 表中

1.实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@TableName("tb_analysis_by_day")
public class Analysis extends BasePojo {

    private Long id;
    /**
     * 日期
     */
    private Date recordDate;
    /**
     * 新注册用户数
     */
    private Integer numRegistered = 0;
    /**
     * 活跃用户数
     */
    private Integer numActive = 0;
    /**
     * 登陆次数
     */
    private Integer numLogin = 0;
    /**
     * 次日留存用户数
     */
    private Integer numRetention1d = 0;

    private Date created;

    private Date updated;
}　

2.定制定时任务

@Component
public class AnalysisTask {

    @Autowired
    private AnalysisService analysisService;

//  每天凌晨0点统计
    @Scheduled(cron = "0 0 0 * * ? ")
    public void  analysis() throws ParseException {
//        日志统计
        System.out.println("开始统计"+(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())));
        analysisService.analysis();
        System.out.println("统计结束");
    }

}

3.每日统计

@Service
public class AnalysisService {

    @Autowired
    private LogMapper logMapper;

    @Autowired
    private AnalysisMapper analysisMapper;

    /**
     * 1.查询统计log表中数据
     * 2.新建或者更新anlysis表中数据
     * 2.1新注册用户数
     * 2.2活跃用户数
     * 2.3登陆次数
     * 2.4次日留存用户数
     */
    public void analysis() throws ParseException {
//        1.定义查询日期
        String today = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
        String yestoday = DateUtil.yesterday().toString("yyyy-MM-dd");
//        2.统计注册数量
        Integer numRegistered = logMapper.queryByTimeAndType(today, "0102");
//        3.查询活跃用户
        Integer numActive = logMapper.queryByTimeAndType(today, "0101");
//        4.查询登录次数
        Integer numLogin = logMapper.queryByTime(today);
//        5.次日留存用户数
        Integer numRetention1d = logMapper.queryNumRetention1d(today, yestoday);
//        查询数据库中今日数据
        LambdaQueryWrapper<Analysis> qw = new LambdaQueryWrapper<>();
        qw.eq(Analysis::getRecordDate, new SimpleDateFormat("yyyy-MM-dd").parse(today));
        Analysis analysis = analysisMapper.selectOne(qw);
//        判断,更新或者新增
        if (analysis!=null){
            analysis.setNumRegistered(numRegistered);
            analysis.setNumActive(numActive);
            analysis.setNumLogin(numLogin);
            analysis.setNumRetention1d(numRetention1d);
            analysisMapper.updateById(analysis);
        }else {
            analysis=new Analysis();
            analysis.setRecordDate(new SimpleDateFormat("yyyy-MM-dd").parse(today));
            analysis.setNumRegistered(numRegistered);
            analysis.setNumActive(numActive);
            analysis.setNumLogin(numLogin);
            analysis.setNumRetention1d(numRetention1d);
            analysis.setCreated(new Date());
            analysis.setUpdated(new Date());
            analysisMapper.insert(analysis);
        }
    }

    public Integer getCumulativeUsers() {
      return analysisMapper.getCumulativeUsers();
    }

    public Analysis getTodayMsg(Date now) {
        LambdaQueryWrapper<Analysis> qw = new LambdaQueryWrapper<>();
        qw.eq(Analysis::getRecordDate, DateUtil.format(now,"yyyy-MM-dd"));
        return analysisMapper.selectOne(qw);
    }

    public Integer getDuringTime(Date now, Date pastDays) {
        return analysisMapper.getDuringTime(DateUtil.format(pastDays,"yyyy-MM-dd"),DateUtil.format(now,"yyyy-MM-dd"));
    }
}

4.sql　　

public interface LogMapper extends BaseMapper<Log> {

    /**
     * 2.1新注册用户数/活跃用户    根据操作类型与时间统计
     * 2.2活跃用户数     根据时间统计
     * 2.3次日留存用户数   从昨天活跃用户中查询今日登录用户
     */

    @Select("SELECT COUNT(DISTINCT user_id) FROM tb_log WHERE type=#{type} AND log_time=#{logTime} ")
    Integer queryByTimeAndType(@Param("logTime") String logTime,@Param("type") String type);

    @Select("SELECT COUNT(DISTINCT user_id) FROM tb_log WHERE  log_time=#{logTime}")
    Integer queryByTime(@Param("logTime") String logTime);


    @Select("SELECT COUNT(DISTINCT user_id) FROM tb_log WHERE  log_time=#{today} AND user_id IN " +
            " (SELECT COUNT(DISTINCT user_id) FROM tb_log WHERE type='01'  AND log_time=#{yestoday})")
    Integer queryNumRetention1d(@Param("today") String today,@Param("yestoday") String yestoday);
}