字节面试题求累计UV

离线

最近面试字节SQL碰到一面试题：

有一个网站访问记录如下：

user_id	用户id
visit_time	访问时间
dt	分区

每个用户一天可能访问多次，每隔一分钟求当天从0点累计到当前时间的累计uv.

题意很简单，一看这题直接就想起窗口函数，但窗口聚合函数中并没有去重函数，一时卡在那里好久没答出来，面试似乎就是考脑经急转弯题，过后冷静下来后想想其实并不难，特此记录。

窗口函数中没有去重函数，有sum/count之类聚合函数，既然是求uv，只用保留一条记录就行，可以先去重预处理，然后再利用窗口函数count()统计即可。

首先进行去重处理，利用窗口函数row_number()对每个用户访问记录排序后取第一条：

create table first_visit_table as

select user_id, visit_time from

(

select

​ user_id,

​ visit_time,

row_number() over (partition by user_id order by visit_time asc) as rn

from visit_table where dt=${dt}

) tmp

where rn=1

去重之后再求累计uv就很简单了，从第一行到当前行窗口内直接count()即可：

select

visit_time,

count(user_id) over(order by visit_time asc) as uv//按visit_time升序，默认从起点行到当前行做累计count

from first_visit_table

至此即可求出累计uv.

实时

如果要实时求累计uv或者去重求sum之类的场景，思路也同离线进行去重预处理，将用户访问记录按user_id key by, 然后在累计之前使用状态算子存储用户第一次访问记录，比如使用redis或bloomfilter，对后续访问记录直接过滤掉即可。

总结

面试过好多次，大多数SQL面试题都属于脑筋急转弯，比如自身与自身join, 两列计算衍生出一列来(连续访问天数)等。这些都是雕虫小技，只能说数据开发很卷，尤其大公司开发平台封装比较完善，有时不懂代码，只要懂SQL和业务就行了。