老干支部网站建设方案,模板网站什么意思,在哪里看片?,网站建站管理每天一道大厂SQL题【Day12】微众银行真题实战(二)
大家好#xff0c;我是Maynor。相信大家和我一样#xff0c;都有一个大厂梦#xff0c;作为一名资深大数据选手#xff0c;深知SQL重要性#xff0c;接下来我准备用100天时间#xff0c;基于大数据岗面试中的经典SQL题我是Maynor。相信大家和我一样都有一个大厂梦作为一名资深大数据选手深知SQL重要性接下来我准备用100天时间基于大数据岗面试中的经典SQL题以每日1题的形式带你过一遍热门SQL题并给出恰如其分的解答。
一路走来随着问题加深发现不会的也愈来愈多。但底气着实足了不少相信不少朋友和我一样日积月累才是最有效的学习方式
每日语录
人还是要有梦想的即使是咸鱼 也要做最咸的那一条。 第12题贷款产品不良统计
需求列表
笔试题目
说明SQL语法请使用HiveSQL/SparkSQL
基于附录2《借据表》统计下述指标请提供计SOL
产品类型在贷客户数在贷余额不良余额余额不良率不良客户数客户不良率XX贷YY贷ZZ贷汇总
数据准备
链接https://pan.baidu.com/s/1Wiv-LVYziVxm8f0Lbt38Gw?pwds4qc 提取码s4qc
debt.txt文件set spark.sql.shuffle.partitions4;
create database webank_db;
use webank_db;
create or replace temporary view check_view (ds comment 日期分区,
sno comment 流水号, uid comment 用户id,
is_risk_apply comment 是否核额申请,
is_pass_rule comment 是否通过规则,
is_obtain_qutoa comment 是否授信成功, quota comment 授信金额,
update_time comment 更新时间)
as
values (20201101, s000, u000, 1, 1, 1, 700, 2020-11-01 08:12:12),
(20201102, s088, u088, 1, 1, 1, 888, 2020-11-02 08:12:12),
(20201230, s091, u091, 1, 1, 1, 789, 2020-12-30 08:12:12),
(20201230, s092, u092, 1, 0, 0, 0, 2020-12-30 08:12:12),
(20201230, s093, u093, 1, 1, 1, 700, 2020-12-30 08:12:12),
(20201231, s094, u094, 1, 1, 1, 789, 2020-12-31 08:12:12),
(20201231, s095, u095, 1, 1, 1, 600, 2020-12-31 08:12:12),
(20201231, s096, u096, 1, 1, 0, 0, 2020-12-31 08:12:12)
;
--创建核额流水表
drop table if exists check_t;
create table check_t (
sno string comment 流水号, uid string,
is_risk_apply bigint, is_pass_rule bigint, is_obtain_qutoa bigint, quota decimal(30,6), update_time string
) partitioned by (ds string comment 日期分区);
--动态分区需要设置
set hive.exec.dynamic.partitiontrue;
set hive.exec.dynamic.partition.modenonstrict;
insert overwrite table check_t partition (ds) select sno,
uid, is_risk_apply, is_pass_rule, is_obtain_qutoa, quota,
update_time,
ds
from check_view; -- 创 建 借 据 表
create table debt(
duebill_id string comment 借据号,
uid string, prod_type string,
putout_date string,
putout_amt decimal(30, 6),
balance decimal(30, 6),
is_buliang int,
overduedays int
)partitioned by (ds string comment 日期分区);
--资料提供了一个34899条借据数据的文件
--下面补充如何将文件的数据导入到分区表中。需要一个中间普通表过度。
drop table if exists webank_db.debt_temp;
create table webank_db.debt_temp(
duebill_id string comment 借据号, uid string,
prod_type string,
putout_date string, putout_amt decimal(30, 6),
balance decimal(30,6),
is_buliang int, overduedays int,
ds string comment 日期分区
) row format delimited fields terminated by \t;load data local inpath /root/debt.txt overwrite into table webank_db.debt_temp;--动态分区需要设置
set hive.exec.dynamic.partitiontrue;
set hive.exec.dynamic.partition.modenonstrict;
insert overwrite table webank_db.debt partition (ds)
select * from webank_db.debt_temp;--技巧如果查询debt表由于分区数太多导致查询很慢。
-- 开发阶段我们可以事先将表缓存起来并且降低分区数比如为6那么查缓存表大大提升了开发效率。
-- 上线阶段再用实际表替换缓存表。
--首次缓存会耗时慢
cache table cache_debt as select / coalesce(6) / from
debt;
--第二次使用缓存会很快
select count(*) from cache_debt;
select ds,count(1) from cache_debt group by ds;思路分析
--第二问 假设是今天(2021-10-29)的统计并考虑对用户去重select * from debt where ds2021-10-29 ;
drop table if exists prod_type_agg;
create table prod_type_agg(
prod_type string comment 产品,
cnt int,
sum_balance decimal(30, 6),
bad_balance decimal(30, 6),
bad_balance_rate decimal(7, 6),
bad_cnt int,
bad_cnt_rate decimal(7, 6)
) partitioned by (ds string comment 结果分区);在贷客户数指在某一时点有未偿还贷款余额的客户数。在贷余额指在某一时点所有未偿还贷款的总金额。不良余额指在某一时点所有不良贷款即次级、可疑和损失类贷款的总金额。余额不良率指在某一时点不良余额占在贷余额的比例。公式为余额不良率 不良余额 / 在贷余额不良客户数指在某一时点有不良贷款即次级、可疑和损失类贷款的客户数。客户不良率指在某一时点不良客户数占在贷客户数的比例。公式为客户不良率 不良客户数 / 在贷客户数
方案1 使用union all
方案2 使用grouping sets
答案获取
建议你先动脑思考动手写一写再对照看下答案如果实在不懂可以点击下方卡片回复:大厂sql 即可。 参考答案适用HQLSparkSQLFlinkSQL即大数据组件其他SQL需自行修改。
加技术群讨论
点击下方卡片关注 联系我进群
或者直接私信我进群
微众银行源数据表附录
核额流水表
字段名字段意义字段类型ds日期分区样例格式为20200101每个分区有全量流水stringsno每个ds内主键流水号stringuid户idstringis_risk_apply是否核额申请核额漏斗第一步取值0和1bigintis_pass_rule是否通过规则核额漏斗第二步取值0和1bigintis_obtain_qutoa是否授信成功核额漏斗第三步取值0和1bigintquota授信金额decimal(30,6)update_time更新时间样例格式为2020-11-14 08:12:12string
借据表
字段名字段意义字段类型ds日期分区样例格式为20200101每个分区有全量借据stringduebilid借据号每个日期分区内的主键stringuid用户idstringprod_type产品名称仅3个枚举值XX贷YY贷ZZ贷stringputout_date发放日期样例格式为2020-10-10 00:10:30bigintputout_amt发放金额decimal(30,6)balance借据余额decimal(30,6)is_buliang状态-是否不良取值0和1bigintoverduedays逾期天数bigint
模型输出表
字段名字段意义字段类型ds日期分区样例格式为20200101增量表部分流水记录可能有更新stringsno流水号主键stringcreate time创建日期样例格式为2020-10-10 00:10:30与sno唯一绑定不会变更stringuid用户idstringcontentson格式key值名称为V01V06value值取值为0和1stringcreate_time更新日期样例格式为2020-10-1000:10:30string
文末SQL小技巧
提高SQL功底的思路。 1、造数据。因为有数据支撑会方便我们根据数据结果去不断调整SQL的写法。 造数据语法既可以create table再insert into也可以用下面的create temporary view xx as values语句更简单。 其中create temporary view xx as values语句SparkSQL语法支持hive不支持。 2、先将结果表画出来包括结果字段名有哪些数据量也画几条。这是分析他要什么。 从源表到结果表一路可能要走多个步骤其实就是可能需要多个子查询过程多就用with as来重构提高可读性。 3、要由简单过度到复杂不要一下子就写一个很复杂的。 先写简单的select from table…,每个中间步骤都执行打印结果看是否符合预期 根据中间结果进一步调整修饰SQL语句再执行直到接近结果表。 4、数据量要小工具要快如果用hive就设置set hive.exec.mode.local.autotrue;如果是SparkSQL就设置合适的shuffle并行度set spark.sql.shuffle.partitions4;
后记
博客主页https://manor.blog.csdn.net
欢迎点赞 收藏 ⭐留言 如有错误敬请指正 本文由 Maynor 原创首发于 CSDN博客 不能老盯着手机屏幕要不时地抬起头看看老板的位置⭐ 专栏持续更新,欢迎订阅https://blog.csdn.net/xianyu120/category_12182595.html
