题解 | #最近7天每天的人均停留时长和次均有效时长#

#真小白思路，按照题意写(PS:虽然题意写的乱七八糟的，但是能看的七七八八)
#1.求每天的播放时长(播放时长/播放人数)。
#2.求每天播放时长大于3的时长的均长(大于3秒的播放时长/大于3秒的人数)
#其实，一解释就好写多了。
select 
date(start_time) as dt,
round(
ifnull(
(sum(timestampdiff(second,start_time,end_time))/count(distinct uid))*1,0),1) as avg_stay_time_len_user,
round(
ifnull(
(
sum(case when timestampdiff(second,start_time,end_time)>=3 then 
timestampdiff(second,start_time,end_time) else 0 end )/
count( case when timestampdiff(second,start_time,end_time)>=3 then uid end))*1,0),1) as avg_stay_time_len_once

from user_play_log_tb
where
datediff((select max(date(start_time)) from user_play_log_tb),date(start_time))<7
group by dt