专栏首页猴子数据分析金融行业实战项目:如何理解业务?
原创

金融行业实战项目:如何理解业务?

【面试题】

下表是某金融App的数据,请结合Excel数据集中的数据完成下列问题(某金融公司面试题,文末有数据下载 )

原始数据如下所示:

为方便后期分析,我们对数据进行分类。数据通常分为3类:用户数据、行为数据、产品数据。

用户数据指用户基本情况,行为数据指用户做过什么,产品数据是指平台卖的东西。

原始数据中年龄、性别、手机省份、手机城市、注册时间、用户注册终端、用户注册渠道、会员级别等信息为用户数据,其余数据为行为数据,无产品数据。

因为行为数据过多不利于分析,我们把行为数据中与最近一次登陆相关的信息提取出来,列为最近登录数据。

把是否...7天内...类的问题提取出来,作为活跃数据。

行为数据如下:

问题:

1.理解数据

如果将该数据建立一个数据库,有哪些表,各表之间的关系是什么?

2.数据整理

假设该Excel数据是一个数据表(Datatable),请用SQL写出:

(1)最近一次登录城市各有多少用户在表中?

(2)“用户ID”之间的可能关系?(比如家庭成员,情侣等)

(3)请找出数据表中的异常值,并以此阐述你如何控制数据质量并以本数据为例设计数据质量报表。

【解题思路】

1.理解数据

如果将该数据建立一个数据,有哪些表,各表之间的关系是什么?

和面试官沟通该金融App后,了解了该产品的业务流程,画出下图:

拆分为4个表(也就是Excel对应的各个sheet表):

(1)“用户信息”表中包含用户id,年龄,性别等基本信息。

其中用户id已进行加密,手机省份和手机城市是注册所在地,用户注册终端是注册时的终端(电脑端web、苹果手机、安卓手机),用户注册渠道展示的是渠道ID。

(2)“最近登录”表中是最近一次登陆ip,展示的是已加密的ip。

(3)“行为数据”表中,投资金额经过等比例放大。

表中字段含义,首投距今时间(天)是固收(含债转)_首次投资时间距今天的时间。

最近一次投资距今时间(天)是固收(含债转)_最近一次投资时间 距今天的时间。

首投距注册时长(天)是首次投资距离注册时间,以天为单位。

用户浏览产品期限倾向是指固收含转让区(产品期限:1-3,4-6,7-12,12以上(PC+H5+APP))。

用户浏览产品利率倾向是指固收含转让区(利率:(0,4%],(4%,5.5%],(5.5%,6%],(6%,7.5%],7.5%以上(PC+H5+APP))。

(4)“活跃数据”表中,本月是否有大额回款指本月回款额度1万及以上。

表中字段含义,

是否访问7天内未注册:首次访问到注册时间>7天,或有访问已超过7天但未注册。

是否注册7天内未充值:注册到首次充值时间>7天,或有注册已超过7天但未充值。

是否注册7天内未投资:注册到首次投资时间>7天,或有注册已超过7天但未投资。

是否托管7天内未充值:托管到首次充值时间>7天,或有托管已超过7天但未充值。

是否托管7天内未投资:托管到首次投资时间>7天,或有托管已超过7天但未投资。

是否充值7天内未投资:最近一次充值到最近一次投资时间>7天,或最近一次充值已超过7天但未投资。

根据上面的业务分析,建立数据库,建立4个表有用户数据、行为数据、最近登录数据、活跃数据表,各表之间以用户id为主键联结,关系如下图。

2.数据整理

(1)最近一次登录城市各有多少用户在表中?

“最近一次登录”在“最近登陆数据“表中。

“各有”,翻译过来就是“每个城市”。涉及到“每个”到业务问题,要想到《猴子 从零学会SQL》里讲过的用“分组汇总”来实现。

这里分组按用户id,汇总使用count函数进行计数。

输出结果(数据过多仅展示前8行数据):

(2)“用户ID”之间的可能关系?(比如家庭成员,情侣等)

使用多维度拆解分析方法来拆解问题:当最近登录ip相同时,用户之间可能会存在关系。因此,分两步解决该业务问题。

(1)找出ip重复的数据;

(2)找出重复ip对应的用户信息。也就是输出用户id、性别、年龄,最近一次登陆ip等信息,并对最近一次登陆ip进行升序排列。

第1步:找出ip重复的数据

“最近一次登录ip”在“最近登陆数据表“中。

该问题是“找重复数据”类问题,按“最近一次登陆ip”分组(group by),然后使用having来筛选出每组里面次数>1次的就是重复数据。

第2步:找出重复ip对应的用户信息

输出用户id、性别,年龄,最近一次登陆ip等信息。

第3步:联结用户数据表,输出信息

把用户数据作为临时表a,第二步得出的结果作为临时表b,并对所有数据进行升序排列。

查询结果:

可以看出用户id(1、2)具有相同ip地址,都为女性,年龄相差20岁,可以推测是母女关系。

用户id(3、4)具有相同ip地址,性别一男一女,年龄相差6岁,可以推测是夫妻关系。

用户id(5、6)具有相同ip地址,性别一男一女,年龄相差8岁,可以推测是夫妻关系。

用户id(7、8)具有相同ip地址,性别一男一女,年龄相差3岁,可以推测是夫妻关系。

(3)请找出数据表中的异常值,并以此阐述你如何控制数据质量并以本数据为例设计数据质量报表。

根据《描述统计分析》里讲过的异常值方法,我们可以使用Tukey's test方法找出宜昌至的范围。

我们以该案例中的“年龄”为例,展示如何找出异常值。

第一步:计算出上四分位数

首先我们增加一列行号,使用“@”自定义“行号”这一变量,并用赋值运算符::=对“@行号”赋值为0。

结果如下:

然后,我们使用内联结把原表格和新增的一列联结在一起,形成新的表格,记为临时表r。

结果如下:

然后,用赋值运算符“:=”赋值行号为“@行号:=@行号+1”,并该列名为行号,进行升序排列,把该表结果记为临时表t。

最后,使用sql的floor函数来计算下四分位数,floor函数:向下舍入为指定小数位数。

同理,使用3*(n+1)/4可以用来计算上四分位数。查询结果如下

第二步,用Tukey's test方法中k=1.5来找出中度异常值。

可以使用where函数找出异常值——在最大估算值和最小估算值之外的数据。

查询结果如下:

用同样的方法我们可以计算出投资金额的异常值。结果如下(因数据较多只选取部分数据呈现):

第三步:制作数据质量报表,以年龄数据为例。

把第二步中得到的结果作为临时表e,用sum函数计算出年龄异常值的总人数与总人数占比。

结果如下:

按照上述步骤可得投资金额异常值的总人数与总人数占比,结果如下:

数据质量报表如下:

异常值的发生原因可以分为两类:人为异常值和自然异常值。

自然异常值不是由于错误产生的,无需避免。

人为异常值可能由于数据输入错误,故意填写错误,数据处理错误等原因产生,这些情况是可以尽量避免的,这就要求我们做到以下几点:

  1. 认真细心。避免数据输入错误。
  2. 做好用户宣传,尽量填写真实信息。
  3. 提高提取数据的正确率,确保数据来源准确。

【本题考点】

1.考察对业务的理解能力,拿到数据以后,不是立马开始分析,而是要先理解数据,对数据分类。

2.“每个问题”要想到用分组汇总来实现。

3.查找出重复数据。对数据项进行分组,找出数量大于2的数据即为重复值。

4.利用sql计算四分位数,找出异常值。增加一列行号并升序排列,利用公式取出上四分位数和下四分位数,找出最小和最大估计值,在此范围外的即为异常值。

5.excel观察数据特征:平均数、中位数、众数。

6.掌握常用的分析方法解决问题的能力。

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 销售数据发生波动,怎么办?

    以下是一家B2C电子商务网站一周销售数据,该网站主要用户是办公室女性,销售额主要集中在5款产品上,如果你是分析师:

    猴子数据分析
  • 如何用Power BI获取数据?

    前面我们介绍了Power BI 是什么,今天介绍如何用Power BI 获取数据。

    猴子数据分析
  • 学会这个,领导要的结果立马就有

    某公司是金融第三方支付公司,为商家生产硬件商机,该种机器可以帮助商家收款,例如我们在商场里见到的收款pos机、收款机等。

    猴子数据分析
  • 基于CentOS搭建微信小程序--响应错误502

    在腾讯云开发者实验室的搭建微信小程序中  ,遇到了在完成实验二会话部署失败后,实验一http访问测试也显示失败并且表示响应错误-错误码:502。(域名是可以正常...

    可别是只猪
  • 卓伟:腾讯云人脸融合技术构建

    4月13日结束的计算机视觉沙龙圆满落幕。本期沙龙从构建图像识别系统的方法切入,讲述腾讯云人脸识别、文字识别、人脸核身等技术能力原理与行业应用,为各位开发者带来了...

    云加社区技术沙龙
  • 5G催化沉浸感升级,视频将像空气和水一样无处不在

    大家好,我是来自快手的于冰。首先感谢来自世界各地的音视频技术专家们来到本次大会,很多人在这个会上相识、相知,还有不少志同道合的同学走到了一起创业,一起去做一些有...

    LiveVideoStack
  • Deep visual domain adaptation: A survey

    深度视觉域适配作为一个解决大量标注数据缺失的新的学习技巧而出现。与传统的学习共享特征子空间或使用浅层表示重用重要源实例的方法相比,深度域适应方法通过将域适应嵌入...

    于小勇
  • Python有趣时刻,这些代码让你大呼"

    不知道大家第一眼看了这个代码,什么感受?我第一眼的感受是密密麻麻一大堆,读都不想读

    py3study
  • day81-<权限组件>-基本流程以及部分代码

    少年包青菜
  • Python爬虫之BeautifulSoup库的入门与使用Beautiful Soup库的理解Beautiful Soup库的引用BeautifulSoup类的基本元素BeautifulSoup解析实

    上篇文章中,Python爬虫之requests库网络爬取简单实战 我们学习了如何利用requets库快速获取页面的源代码信息。我们在具体的爬虫实践的时候,第一...

    desperate633

扫码关注云+社区

领取腾讯云代金券