select t1.stats_date_period
,t1.loan_account_id
,t1.user_id
,t1.active_os_list
,t1.active_platform_name_list
,t1.active_sdk_type_list
,t1.active_device_id_list
,t1.latest_active_os
,t1.latest_active_platform_name
,t1.latest_active_sdk_type
,t1.latest_active_device_id
,t1.latest_active_city
,t1.latest_active_province
,t1.latest_active_country
,t1.login_cnt
,t1.repayment_plan_exposure_cnt
,t1.living_confirmed_photo_click_cnt
,t1.app_launch_cnt
,t1.credit_increase_click_cnt
,t1.contact_from_contacts_cnt
,t1.app_exit_cnt
,t1.coupon_pageview_cnt
,t1.change_bank_card_click_cnt
,t1.delete_account_succ_cnt
,t2.active_os_cnt
,t2.active_platform_name_cnt
,t2.active_sdk_type_list
,t2.active_ip_cnt
,t2.active_device_cnt
,t2.active_city_cnt
,t2.active_province_cnt
,t2.active_country_cnt
,t3.login_method_distribution
,t4.app_launch_hour_distribution
,t5.app_consecutive_launch_days
,t6.bankcard_valid_cnt
,t6.bankcard_invalid_cnt
from
(
select stats_date_period
,loan_account_id
,user_id
,active_os_list
,active_platform_name_list
,active_sdk_type_list
,active_device_id_list
,count(case when event_code = 'login' then 1 end) as login_cnt
,count(case when event_code = 'repayment_plan_exposure' then 1 end) as repayment_plan_exposure_cnt
,count(case when event_code = 'living_confirmed_photo_click' then 1 end) as living_confirmed_photo_click_cnt
,count(case when event_code = 'app_start' then 1 end) as app_launch_cnt
,count(case when event_code = 'credit_increase_click' then 1 end) as credit_increase_click_cnt
,count(case when event_code = 'contact_from_contacts' then 1 end) as contact_from_contacts_cnt
,count(case when event_code = 'app_end' then 1 end) as app_exit_cnt
,count(case when event_code = 'coupon_pageview' then 1 end) as coupon_pageview_cnt
,count(case when event_code = 'change_bank_card_click' then 1 end) as change_bank_card_click_cnt
,count(case when event_code = 'delete_account_succ' then 1 end) as delete_account_succ_cnt
,max(latest_active_os) as latest_active_os
,max(latest_active_platform_name) as latest_active_platform_name
,max(latest_active_sdk_type) as latest_active_sdk_type
,max(latest_active_device_id) as latest_active_device_id
,max(latest_active_city) as latest_active_city
,max(latest_active_province) as latest_active_province
,max(latest_active_country) as latest_active_country
from
(
select stats_date_period
,loan_account_id
,user_id
,active_os_list
,active_platform_name_list
,active_sdk_type_list
,active_device_id_list
,event_code
,ip
,created_ts
,created_hour
,city
,province
,country
,event_code
,login_type
,last_value(os) over(partition by user_id order by created_ts asc IGNORE NULLS) as latest_active_os -- 过滤空值
,last_value(active_platform_name_list) over(partition by user_id order by created_ts asc IGNORE NULLS) as latest_active_platform_name
,last_value(active_sdk_type_list) over(partition by user_id order by created_ts asc IGNORE NULLS) as latest_active_sdk_type
,last_value(active_device_id_list) over(partition by user_id order by created_ts asc IGNORE NULLS) as latest_active_device_id
,last_value(city) over(partition by user_id order by created_ts asc IGNORE NULLS) as latest_active_city
,last_value(province) over(partition by user_id order by created_ts asc IGNORE NULLS) as latest_active_province
,last_value(country) over(partition by user_id order by created_ts asc IGNORE NULLS) as latest_active_country
from user_detail
) a
group by stats_date_period
,loan_account_id
,user_id
,active_os_list
,active_platform_name_list
,active_sdk_type_list
,active_device_id_list
) t1
left join
(
select stats_date_period
,loan_account_id
,user_id
,count(distinct active_os_list) as active_os_cnt
,count(distinct active_platform_name_list) as active_platform_name_cnt
,count(distinct active_sdk_type_list) as active_sdk_type_list
,count(distinct ip) as active_ip_cnt
,count(distinct active_device_id_list) as active_device_cnt
,count(distinct case when city is not null then city end) as active_city_cnt
,count(distinct case when province is not null then province end) as active_province_cnt
,count(distinct case when country is not null then country end) as active_country_cnt
from user_detail
group by stats_date_period
,loan_account_id
,user_id
) t2 on t1.stats_date_period = t2.stats_date_period and t1.user_id = t2.user_id and t1.loan_account_id = t2.loan_account_id
left join
-- 登录方式分布
(
select stats_date_period
,loan_account_id
,user_id
,concat(login_type, '', cast(login_type_cnt as string)) as login_method_distribution
from
(
select stats_date_period
,loan_account_id
,user_id
,login_type
,count(1) as login_type_cnt
from user_detail
where login_type is not null
group by stats_date_period
,loan_account_id
,user_id
,login_type
) a
) t3 on t1.stats_date_period = t3.stats_date_period and t1.user_id = t3.user_id and t1.loan_account_id = t3.loan_account_id
left join
-- APP启动时间段分布
(
select stats_date_period
,loan_account_id
,user_id
,concat(created_hour, '', cast(login_hour_cnt as string)) as app_launch_hour_distribution
from
(
select stats_date_period
,loan_account_id
,user_id
,created_hour
,count(1) as login_hour_cnt
from user_detail
where event_code = 'app_start'
group by stats_date_period
,loan_account_id
,user_id
,created_hour
) a
) t4 on t1.stats_date_period = t4.stats_date_period and t1.user_id = t4.user_id and t1.loan_account_id = t4.loan_account_id
left join
-- 用户最后一次连续登陆天数
(
select user_id
,loan_account_id
,stats_date_period
,consecutive_days as app_consecutive_launch_days
from
(
select user_id
,loan_account_id
,stats_date_period
,min(dt) as session_start_date -- 连续登陆起始日
,max(dt) as session_end_date -- 连续会话结束日
,count(distinct dt) as consecutive_days -- 连续登陆天数
,max(last_login_dt) as last_login_dt -- 用户在该区间最后一次登录日期
from
(
select dt
,stats_date_period
,user_id
,loan_account_id
,cast(dt as int) - row_number() over (partition by user_id, b_stats_date_period order by dt) as session_start -- 连续登陆标识
,max(dt) over (partition by user_id, b_stats_date_period) as last_login_dt -- 用户在该区间最后一次登录日期
from
(
select dt
,user_id
,loan_account_id
,stats_date_period
from user_detail
where event_code = 'app_start'
group by dt
,user_id
,loan_account_id
,stats_date_period
) a
) b
group by user_id
,loan_account_id
,stats_date_period
) c
where last_login_dt = session_end_date -- 限定最后登陆日期=某次连续登陆最后日期，即可取得最后一次连续登陆的天数(含1天)
) t5 on t1.stats_date_period = t5.stats_date_period and t1.user_id = t5.user_id and t1.loan_account_id = t5.loan_account_id
left join
-- 银行卡不同状态绑定数
(
select b_stats_date_period as stats_date_period
,user_id
,count(distinct if(available_status = 'A', account_number, null)) as bankcard_valid_cnt
,count(distinct if(available_status = 'U', account_number, null)) as bankcard_invalid_cnt
from
(
select case
when dt = '20251204' then array('T1','T3','T7','T30')
when dt between '20251202' and '20251204' then array('T3','T7','T30')
when dt between '20251128' and '20251204' then array('T7','T30')
when dt between '20251105' and '20251204' then array('T30')
end as stats_date_period
,user_id
,account_number
,available_status
from
(
select dt
,user_id
,account_number
,available_status
,row_number() over (partition by account_number order by updated_ts desc) as rn
from ec_dim.dim_ec_df_loan_bank_account -- 只有最近六天
where dt between '20251105' and '20251204'
) a
where rn = 1 -- 取最新状态
) b
LATERAL VIEW explode(stats_date_period) b AS b_stats_date_period
group by b_stats_date_period
,user_id
) t6
on t1.stats_date_period = t6.stats_date_period and t1.user_id = t6.user_id 哪里缺少右括号了。帮我看一下
本文深入探讨了SQL中GROUP BY与COUNT(DISTINCT)的区别，解析了这两种方法在处理海量数据时的不同策略及优劣，帮助读者理解如何在时间和空间效率之间做出选择。
扫一扫
620
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
