网站编辑建设,朝阳网站制作设计,统一用户管理系统,wordpress 返回首页每天一道大厂SQL题【Day15】微众银行真题实战(五)
大家好#xff0c;我是Maynor。相信大家和我一样#xff0c;都有一个大厂梦#xff0c;作为一名资深大数据选手#xff0c;深知SQL重要性#xff0c;接下来我准备用100天时间#xff0c;基于大数据岗面试中的经典SQL题我是Maynor。相信大家和我一样都有一个大厂梦作为一名资深大数据选手深知SQL重要性接下来我准备用100天时间基于大数据岗面试中的经典SQL题以每日1题的形式带你过一遍热门SQL题并给出恰如其分的解答。
一路走来随着问题加深发现不会的也愈来愈多。但底气着实足了不少相信不少朋友和我一样日积月累才是最有效的学习方式
每日语录
第15题逾期率统计
需求列表
基于附录2《借据表》统计下述指标请提供Vintage统计SQLmobX指的是发放后第X月末的不良余额/发放月金额
发放月份发放金额MOB1MOB2MOB3MOB4MOB5MOB6MOB7MOB8MOB9MOB10MOB11MOB122019-102019-112019-122020-012020-022020-032020-042020-052020-062020-072020-082020-092020-10
数据准备
链接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;思路分析 随机观察2个借据的情况
*select* * *from* cache_debt *where* duebill_idu001-1 *order by* ds;*select* * *from* cache_debt *where* duebill_idu005-2 *order by* ds;分3个大步骤
步骤1形成临时表1_每月的总发放金额
发放月份发放金额2019-10aa2019-11bb2019-12cc2020-01dd2020-02ee2020-03ff2020-04gg2020-05hh
步骤2形成临时表2_发放后第几个月末时的不良余额
发放后第几个月末时的不良余额(元)发放月份1月后2月后3月后4月后5月后6月后7月后8月后2019-10a1a2a3a4a5a6a7a82019-11b1b2b3b4b5b6b7b82019-12c1c2c3c4c5c6c7c82020-01d1d2d3d4d5d6d7d82020-02e1e2e3e4e5e6e7e82020-03f1f2f3f4f5f6f7f82020-04g1g2g3g4g5g6g7g82020-05h1h2h3h4h5h6h7h8
步骤3用上面的临时表1关联临时表2用临时表2的每个值除以临时表的总金额。
发放后第几个月末时的不良余额占发放金额的比例发放月份发放金额1月后2月后3月后4月后5月后6月后7月后8月后2019-10aaa1/aaa2/aaa3/aaa4/aaa5/aaa6/aaa7/aaa8/aa2019-11bbb1/bbb2/bbb3/bbb4/bbb5/bbb6/bbb7/bbb8/bb2019-12ccc1/ccc2/ccc3/ccc4/ccc5/ccc6/ccc7/ccc8/cc2020-01ddd1/ddd2/ddd3/ddd4/ddd5/ddd6/ddd7/ddd8/dd2020-02eee1/eee2/eee3/eee4/eee5/eee6/eee7/eee8/ee2020-03fff1/fff2/fff3/fff4/fff5/fff6/fff7/fff8/ff2020-04ggg1/ggg2/ggg3/ggg4/ggg5/ggg6/ggg7/ggg8/gg2020-05hhh1/hhh2/hhh3/hhh4/hhh5/hhh6/hhh7/hhh8/hh
答案获取
建议你先动脑思考动手写一写再对照看下答案如果实在不懂可以点击下方卡片回复:大厂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
