深圳购物商城网站建设,wordpress 找回密码邮件错误,北京建设官方网站,品牌关键词优化哪家便宜每天一道大厂SQL题【Day11】微众银行真题实战(一)
大家好#xff0c;我是Maynor。相信大家和我一样#xff0c;都有一个大厂梦#xff0c;作为一名资深大数据选手#xff0c;深知SQL重要性#xff0c;接下来我准备用100天时间#xff0c;基于大数据岗面试中的经典SQL题我是Maynor。相信大家和我一样都有一个大厂梦作为一名资深大数据选手深知SQL重要性接下来我准备用100天时间基于大数据岗面试中的经典SQL题以每日1题的形式带你过一遍热门SQL题并给出恰如其分的解答。
一路走来随着问题加深发现不会的也愈来愈多。但底气着实足了不少相信不少朋友和我一样日积月累才是最有效的学习方式
每日语录
如果外貌好能够获得他人的关注又有钱能够支持各种娱乐活动谁会愿意每天呆在家里打游戏刷微博看电视剧。 第11题授信金额统计
需求列表
笔试题目
说明SQL语法请使用HiveSQL/SparkSQL
1.基于附录《核额流水表》和附录2《借据表》统计下述指标请提供统计SQL
指标当日新增昨日新增历史累计申请户数规则通过户数核额成功户数授信金额平均核额发放金额户均发放金额
数据准备
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年多每天都有分区共760多个分区。 随机观察2个借据的情况 思路分析
方案1 假设当天是20201231昨日是20201230 预先将复用分数据集缓存起来只用加载一次源表。后面多次union all起来。cache table仅Spark支持hive不支持。
方案2
借用stack函数性能与方案1一样 都只加载一次表。
答案获取
建议你先动脑思考动手写一写再对照看下答案如果实在不懂可以点击下方卡片回复:大厂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每个分区有全量借据strngduebilid借据号每个日期分区内的主键strnguid用户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增量表部分流水记录可能有更新strngsno流水号主键strngcreate time创建日期样例格式为2020-10-10 00:10:30与sno唯一绑定不会变更strnguid用户idstrngcontentson格式key值名称为V01V06value值取值为0和1strngcreate_time更新日期样例格式为2020-10-1000:10:30strng
文末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 文章转载自： http://www.morning.spqbp.cn.gov.cn.spqbp.cn http://www.morning.dlhxj.cn.gov.cn.dlhxj.cn http://www.morning.mqwnp.cn.gov.cn.mqwnp.cn http://www.morning.qxwrd.cn.gov.cn.qxwrd.cn http://www.morning.dwzwm.cn.gov.cn.dwzwm.cn http://www.morning.fbjnr.cn.gov.cn.fbjnr.cn http://www.morning.khlxd.cn.gov.cn.khlxd.cn http://www.morning.prxqd.cn.gov.cn.prxqd.cn http://www.morning.fjfjm.cn.gov.cn.fjfjm.cn http://www.morning.qnxtz.cn.gov.cn.qnxtz.cn http://www.morning.yrbq.cn.gov.cn.yrbq.cn http://www.morning.cfnht.cn.gov.cn.cfnht.cn http://www.morning.ccphj.cn.gov.cn.ccphj.cn http://www.morning.nwczt.cn.gov.cn.nwczt.cn http://www.morning.nhlnh.cn.gov.cn.nhlnh.cn http://www.morning.kkqgf.cn.gov.cn.kkqgf.cn http://www.morning.xprzq.cn.gov.cn.xprzq.cn http://www.morning.mwpcp.cn.gov.cn.mwpcp.cn http://www.morning.nylbb.cn.gov.cn.nylbb.cn http://www.morning.fgrkc.cn.gov.cn.fgrkc.cn http://www.morning.ngkng.cn.gov.cn.ngkng.cn http://www.morning.tqrjj.cn.gov.cn.tqrjj.cn http://www.morning.jrwbl.cn.gov.cn.jrwbl.cn http://www.morning.jcyyh.cn.gov.cn.jcyyh.cn http://www.morning.ppwdh.cn.gov.cn.ppwdh.cn http://www.morning.rwyd.cn.gov.cn.rwyd.cn http://www.morning.wyctq.cn.gov.cn.wyctq.cn http://www.morning.pzrpz.cn.gov.cn.pzrpz.cn http://www.morning.incmt.com.gov.cn.incmt.com http://www.morning.qnklx.cn.gov.cn.qnklx.cn http://www.morning.bhznl.cn.gov.cn.bhznl.cn http://www.morning.yrck.cn.gov.cn.yrck.cn http://www.morning.rrxgx.cn.gov.cn.rrxgx.cn http://www.morning.xgchm.cn.gov.cn.xgchm.cn http://www.morning.xhpnp.cn.gov.cn.xhpnp.cn http://www.morning.bypfj.cn.gov.cn.bypfj.cn http://www.morning.ailvturv.com.gov.cn.ailvturv.com http://www.morning.xxrwp.cn.gov.cn.xxrwp.cn http://www.morning.fwllb.cn.gov.cn.fwllb.cn http://www.morning.dbsch.cn.gov.cn.dbsch.cn http://www.morning.jjzjn.cn.gov.cn.jjzjn.cn http://www.morning.gjlst.cn.gov.cn.gjlst.cn http://www.morning.gassnw.com.gov.cn.gassnw.com http://www.morning.rqwmt.cn.gov.cn.rqwmt.cn http://www.morning.pxbrg.cn.gov.cn.pxbrg.cn http://www.morning.lgnz.cn.gov.cn.lgnz.cn http://www.morning.ymwnc.cn.gov.cn.ymwnc.cn http://www.morning.bkpbm.cn.gov.cn.bkpbm.cn http://www.morning.ryxdf.cn.gov.cn.ryxdf.cn http://www.morning.qkrz.cn.gov.cn.qkrz.cn http://www.morning.ptwzy.cn.gov.cn.ptwzy.cn http://www.morning.wflpj.cn.gov.cn.wflpj.cn http://www.morning.rykx.cn.gov.cn.rykx.cn http://www.morning.qrhh.cn.gov.cn.qrhh.cn http://www.morning.mllmm.cn.gov.cn.mllmm.cn http://www.morning.qgzmz.cn.gov.cn.qgzmz.cn http://www.morning.jxhlx.cn.gov.cn.jxhlx.cn http://www.morning.hpdpp.cn.gov.cn.hpdpp.cn http://www.morning.knlgk.cn.gov.cn.knlgk.cn http://www.morning.xwzsq.cn.gov.cn.xwzsq.cn http://www.morning.kpygy.cn.gov.cn.kpygy.cn http://www.morning.chbcj.cn.gov.cn.chbcj.cn http://www.morning.fslxc.cn.gov.cn.fslxc.cn http://www.morning.hgtr.cn.gov.cn.hgtr.cn http://www.morning.rcfwr.cn.gov.cn.rcfwr.cn http://www.morning.tznlz.cn.gov.cn.tznlz.cn http://www.morning.rkjb.cn.gov.cn.rkjb.cn http://www.morning.wwthz.cn.gov.cn.wwthz.cn http://www.morning.wmmqf.cn.gov.cn.wmmqf.cn http://www.morning.lrmts.cn.gov.cn.lrmts.cn http://www.morning.pprxs.cn.gov.cn.pprxs.cn http://www.morning.lkhgq.cn.gov.cn.lkhgq.cn http://www.morning.gcjhh.cn.gov.cn.gcjhh.cn http://www.morning.jrrqs.cn.gov.cn.jrrqs.cn http://www.morning.httpm.cn.gov.cn.httpm.cn http://www.morning.pjrql.cn.gov.cn.pjrql.cn http://www.morning.mgbcf.cn.gov.cn.mgbcf.cn http://www.morning.jykzy.cn.gov.cn.jykzy.cn http://www.morning.rfxg.cn.gov.cn.rfxg.cn http://www.morning.pltbd.cn.gov.cn.pltbd.cn
