select artical_id, max(current_max) as max_uv
from (
select artical_id, at_time,
sum(uv) over(partition by artical_id order by at_time, uv desc) as current_max
from (
select artical_id, in_time as at_time, 1 as uv from tb_user_log
union all
select artical_id, out_time as at_time, -1 as uv from tb_user_log
order by at_time
) as t_uv_at_time
where artical_id != 0
) as t_artical_cur_max
group by artical_id
order by max_uv desc;
内层子查询 (
t_uv_at_time
):这个子查询通过UNION ALL
将两个表连接起来。第一个表包含用户进入页面的时间(in_time
)和UV值为1,第二个表包含用户离开页面的时间(out_time
)和UV值为-1。这样,每次用户进入页面时,UV值增加1,每次用户离开页面时,UV值减少1。这个子查询的结果按照at_time
排序。中间子查询 (
t_artical_cur_max
):这个子查询使用窗口函数SUM() OVER()
来计算每个artical_id
的当前最大UV值。PARTITION BY artical_id
表示窗口函数在每个artical_id
内部进行计算,ORDER BY at_time, uv DESC
表示窗口函数按照at_time
和UV值(降序)进行排序。这样,对于每个artical_id
,我们可以得到一个随着时间变化的UV值序列。current_max
列表示在每个时间点之前的最大UV值。外层查询:这个查询对中间子查询的结果进行分组,并计算每个
artical_id
的最大UV值。GROUP BY artical_id
表示按照artical_id
进行分组,MAX(current_max)
表示取每个分组中的最大UV值。最后,结果按照最大UV值降序排序。整个查询的逻辑是先计算出每个
artical_id
在每个时间点的UV值,然后计算出每个artical_id
的最大UV值,并按照这个值进行排序。