漏斗分析代码记录
SELECT media_source AS media_source, funnel_sort AS funnel_sort, count(1) AS total_count FROM (SELECT media_source, arraySort(x -> -x, groupArray(count)) AS funnel_sort FROM (SELECT media_source, level_index, count(1) AS count FROM (SELECT media_source, user_id AS user_id, arrayJoin(arrayEnumerate(levels)) AS level_index, arrayWithConstant(level, 1) AS levels FROM (SELECT media_source, user_id, windowFunnel(86400)(event_time, event_name = 'bf_enter_success', event_name = 'RewardVideoShowSuccess') AS level FROM (SELECT date AS date, country AS country, app_name AS app_name, app_version AS app_version, media_source AS media_source, user_id AS user_id, event_name AS event_name, app_id AS app_id, toUInt64(toUnixTimestamp64Milli(toDateTime64(splitByString('+', event_time)[1], 3, 'Asia/Shanghai'))) AS event_time FROM ods.ods_appsflyer_event WHERE 1 = 1 AND date BETWEEN '2020-10-10' AND '2020-10-10' AND app_id = ('APAFXD4SDC4WE')) WHERE 1 = 1 GROUP BY media_source, user_id) WHERE 1 = 1) WHERE 1 = 1 GROUP BY media_source, level_index) WHERE 1 = 1 GROUP BY media_source) WHERE 1 = 1 GROUP BY media_source, funnel_sort ORDER BY media_source ASC; SELECT date AS date, count(user_id) AS count, SUM(data_0) AS data_0, SUM(data_1) AS data_1 FROM (SELECT date AS date, user_id AS user_id, groupArray(event) AS events, toString(events) AS events_uniq_str, arrayCount(x-> x = 1, events) AS data_0, length(extractAll(events_uniq_str, '1.*?2')) AS data_1 FROM (SELECT date AS date, country AS country, app_name AS app_name, app_version AS app_version, media_source AS media_source, user_id AS user_id, case when event_name = 'bf_enter_success' then 1 when event_name = 'RewardVideoShowSuccess' then 2 else 0 end AS event, app_id AS app_id, toUInt64(toUnixTimestamp64Milli( toDateTime64(splitByString('+', event_time)[1], 3, 'Asia/Shanghai'))) AS event_time FROM ods.ods_appsflyer_event WHERE 1 = 1 AND date BETWEEN '2020-10-10' AND '2020-10-10' AND app_id = ('APAFXD4SDC4WE') AND event_name in ('bf_enter_success', 'RewardVideoShowSuccess') ORDER BY event_time ASC) WHERE 1 = 1 GROUP BY date, user_id) WHERE 1 = 1 GROUP BY date ORDER BY date ASC