大数据开发面经汇总【持续更新...】
我的大数据学习之路
大数据开发面试笔记V6.0
来自蚂蚁数据研发一面
-- 举例如下: -- 输入 -- dwd_trd_loan_tb_dd uid amt ovd_days dt 1001 1000 0 20241011 1002 1000 33 20241011 1003 1000 12 20241011 1004 1000 68 20241011 -- dim_ovd_config_dd ovd_days user_level 1 1 30 2 60 3 180 4 -- 输出 uid user_level 1001 1 1002 3 1003 2 1004 4
create table dwd_trd_loan_tb_dd ( uid varchar(20), amt bigint, ovd_days bigint, dt varchar(20) ); create table dim_ovd_config_dd ( ovd_days bigint, user_level bigint ); INSERT INTO dwd_trd_loan_tb_dd VALUES ('1001',1000,0,'20241011'), ('1002',1000,33,'20241011'), ('1003',1000,12,'20241011'), ('1004',1000,68,'20241011') ; INSERT INTO dim_ovd_config_dd VALUES (1,1), (30,2), (60,3), (180,4) ;
select uid, user_level from ( select t1.uid, t1.ovd_days, t2.ovd_days as ovd_days_config, t2.user_level, sum(if(t1.ovd_days < t2.ovd_days, 1, 0)) over(partition by t1.uid order by t2.ovd_days) as total_cnt from ( select * from dwd_trd_loan_tb_dd where dt = '20241011' ) t1 join ( select * from dim_ovd_config_dd ) t2 on 1 = 1 ) t where total_cnt = 1 ;#数据人的面试交流地##大数据开发##SQL面试#