1. 查询结果

需要根据数据查询出漏斗各层各数量

有表数据：

event_name   evnet_date     user_id
$PageView    2021-12-25      2337204
$PageView    2021-12-26     2337204
$PageLeave   2021-12-28     2337204

先把查询结果给出：

使用的sql：

select
  argMax(event_date_temp, level) as event_date,
  level,
  uniqCombined(user_id) as leven_num
from
  (
    select
      maxIf(event_date, event_name = '$PageView') as event_date_temp,
      windowFunnel(7 * 86400)(event_time, event_name = '$PageView', event_name = '$PageLeave') as level,
      user_id
    from
      events2
    group by
      user_id, event_date with rollup
    having
      event_date_temp > '1970-01-01' and level > 0 and user_id != ''
  )
group by
  level

2. sql解释

1. SQL1:

select
  event_date as event_date_temp,
  windowFunnel(7 * 86400)(event_time,event_name = '$PageView',event_name = '$PageLeave') as level,
  user_id
from
  events2 
group by
  user_id, event_date

2. SQL2:

select
  maxIf(event_date, event_name = '$PageView') as event_date_temp,
  windowFunnel(7 * 86400)(event_time,event_name = '$PageView',event_name = '$PageLeave') as level,
  user_id
from
  events2 
group by
  user_id, event_date

3. SQL3：

select
  maxIf(event_date, event_name = '$PageView') as event_date_temp,
  windowFunnel(7 * 86400)(event_time,event_name = '$PageView',event_name = '$PageLeave') as level,
  user_id
from
  events2 
group by
  user_id, event_date with rollup