推荐阅读文章列表

大数据开发面经汇总【持续更新...】

我的大数据学习之路

大数据开发面试笔记V6.0

SQL题目

来自蚂蚁数据研发一面

• 有一张用户贷款信息表dwd_trd_loan_tb_dd，包含uid（用户id）、amt（贷款金额）、ovd_days（逾期天数）、dt（时间分区）以及逾期等级配置表dim_ovd_config_dd，包含ovd_days（逾期天数），user_level（用户风险等级）
• 注意：当ovd_days=1且user_level=1，表示用户逾期天数<=1时，用户风险等级都为1；当ovd_days=30且user_level=2，表示用户逾期天数>1同时<=30时，用户风险等级为2；
• 问题：计算20241011日所有贷款用户对应的风险等级

-- 举例如下：
-- 输入
-- 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)
;

思路分析

• 看到多张表，先进行JOIN，但是一眼看去好像只能用逾期天数进行关联，可以又无法直接关联，那么就笛卡尔积（考虑到配置表很小）
• 这时候我们就需要判断每个用户的逾期天数是否小于所有配置的逾期天数，如果小于记为1，这时候会出现一个用户对应多个1，我们要取对应配置逾期天数最小的那一条，怎么办？
• 按照uid进行分组，配置逾期天数进行排序，对标志位进行求和，最后取开窗结果为1的行记录即可

具体代码

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
;