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

考察内容：聚合函数、条件函数、日期函数、空值转换函数、排序

思路：

① 先计算每个用户每天的停留时长：timestampdiff(second, start_time, end_time)

② 计算最近7天每天的人均停留时长和次均有效时长：人均停留时长指每个人的总停留时长的平均值，次均有效时长指每次有效停留（假设停留时长小于3秒为无效）的时长平均值，可以用 if 或 case when 筛选有效停留的记录，用 round 保留1位小数。注意：如果当天没有有效的停留，此时数据位 null，需要用空值转换函数转为0，在 MySQL 中可以用 ifnull 函数，在 Hive 中可以用 nvl 函数

• 人均停留时长 = 总停留时长/总人数：sum(stay_time_len) / count(distinct uid)
• 次均有效时长 = 总有效停留时长/总有效停留次数：ifnull(sum(case when stay_time_len >= 3 then stay_time_len else 0 end) / count(case when stay_time_len >= 3 then stay_time_len end), 0)

③ 按照日期 dt 升序排序

select 
	dt,
	round(sum(stay_time_len) / count(distinct uid), 1) as avg_stay_time_len_user,
	round(ifnull(sum(case when stay_time_len >= 3 then stay_time_len else 0 end) / count(case when stay_time_len >= 3 then stay_time_len end), 0), 1) as avg_stay_time_len_once
from
(
	select
		uid,
		date(end_time) as dt,
		timestampdiff(second, start_time, end_time) as stay_time_len
	from
		user_play_log_tb
) a
where
  datediff((select max(date(end_time)) from user_play_log_tb), dt) < 7
group by dt
order by dt
;