【StarRocks版本】3.1
请问大家关于使用window_funnel函数计算每天的漏斗转换的sql。
按照官网文档，假设窗口为为 4320s(3天)，筛选模式为 0
计算总体漏斗sql：
SELECT uid,
window_funnel(1800,time,0,[event_type=‘浏览’, event_type=‘点击’, event_type=‘下单’, event_type=‘支付’]) AS level
FROM action
GROUP BY uid;
上述sql在上亿级别的表中，很快就能出结果
但是如果要计算每天的漏斗情况，sql 写法如下：
– 先找到第一个事件的最小时间
with first_event_min_time_table as (select uid,
MIN(time) as first_event_min_time
from action
group by uid),
– 构建漏斗计算时需要的表
daily_funnel_table as (
select l.uid as uid,
l.first_event_min_time as first_event_min_time,
date(l.first_event_min_time) as day,
r.time as time,
r.event_type as event_type
from first_event_min_time_table as l
inner join action as r on
r.uid = l.uid and
r.time >= l.first_event_min_time and
r.time <= (l.first_event_min_time + interval ‘4320’ second)
)
– 漏斗计算
select uid,
day,
window_funnel(4320, time, 0, [event_type=‘浏览’, event_type = ‘点击’, event_type = ‘下单’, event_type = ‘支付’]) AS level
from daily_funnel_table
group by uid, day;
这样性能不如计算总体的快。请问计算每日的还有哪种写法呢。