20210913日报

上午：写上周进度表，查看周报，根据数据分析-访问分析-日趋势图

下午：sql语句测试访问总览页面数据是否正确。

页面访问量：

select count(*) from T_USER_BEHAVIOR_RECORD a inner join T_USER_BEHAVIOR_VERSION_INFO b on a.VERSION_INFO_ID=b.id where CURRENT_TIME between 1631462400000 and 1631550959000

唯一页面访问量：

select count(*) from(select count(*) from T_USER_BEHAVIOR_RECORD a inner join T_USER_BEHAVIOR_VERSION_INFO b on a.VERSION_INFO_ID=b.id where CURRENT_TIME between 1631462400000 and 1631550959000 group by a.PLAY_TIME_PAGE,a.DEVICE_ID)

独立访客数：

select count(*) from(select count(*) from T_USER_BEHAVIOR_RECORD a inner join T_USER_BEHAVIOR_VERSION_INFO b on a.VERSION_INFO_ID=b.id where CURRENT_TIME between 1631462400000 and 1631550959000 group by a.DEVICE_ID)

select count(*) from(select count(*) from T_USER_BEHAVIOR_RECORD a inner join T_USER_BEHAVIOR_VERSION_INFO b on a.VERSION_INFO_ID=b.id where CURRENT_TIME between 1631462400000 and 1631550959000 group by a.IP)

新增独立访客数：

select count(*) from(select count(*) from T_USER_BEHAVIOR_RECORD a innerjoin T_USER_BEHAVIOR_VERSION_INFO b on a.VERSION_INFO_ID=b.id where CURRENT_TIME between 1631462400000 and 1631550959000 and DEVICE_ID not in(select distinct DEVICE_ID from T_USER_BEHAVIOR_RECORD where CURRENT_TIME<1631462400000)
group by a.device_id)

select count(*) from(select count(*) from T_USER_BEHAVIOR_RECORD a inner join T_USER_BEHAVIOR_VERSION_INFO b on a.VERSION_INFO_ID=b.id where CURRENT_TIME between 1631462400000 and 1631550959000 and DEVICE_ID  in(select distinct DEVICE_ID from T_USER_BEHAVIOR_RECORD where CURRENT_TIME<1631462400000)
group by a.device_id)

select avg(play_time) from T_USER_BEHAVIOR_RECORD a innerjoin T_USER_BEHAVIOR_VERSION_INFO b on a.VERSION_INFO_ID=b.id where CURRENT_TIME between 1631462400000 and 1631550959000