lgq19991225 commented on issue #23554:
URL: https://github.com/apache/doris/issues/23554#issuecomment-1695548875
可以用EXPLODE和ARRAY_RANGE函数实现了这个功能，要是有开箱即用的函数更好
```sql
select
funnel.event_date,
sum(if(step >= 1 , 1, 0)) as step1,
sum(if(step >= 2 , 1, 0)) as step2,
sum(if(step >= 3 , 1, 0)) as step3
from
(
SELECT
user_id,FROM_DAYS(tmp.event_date) as event_date,
window_funnel(3600 * 24 * 3,'defalut',event_time,
event_key = 'App_Quanmaidian_Page_Exp' and (p__user_type = 1 or p__user_type
= 2),
event_key = 'Passenger_Xunjiaye_Gouquanrukou_Popbanner_Exp',
event_key = 'Passenger_Trip_Youhuiquantuijiantanchuang_Popbanner_Exp'
) as step
FROM ruqi_realtime.dwd_user_event_track_normal
LATERAL VIEW EXPLODE(
ARRAY_RANGE(
IF(
event_key = 'App_Quanmaidian_Page_Exp' and (p__user_type
= 1 or p__user_type = 2),
TO_DAYS(event_time),
TO_DAYS(SECONDS_SUB(event_time, 3600 * 24 * 3))
),
TO_DAYS(e.event_time) + 1
)
) tmp AS event_date
WHERE event_time>='2023-08-01 00:00:00' and event_time<='2023-08-24
00:00:00' and user_id is not null
GROUP BY user_id,event_date
) as funnel
group by funnel.event_date
order by funnel.event_date
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
