本文是对智警杯比赛的数据查询题目做出的变种问题,并有对应的解答。

第一题

一、创建loan表;
将本地数据loan.csv导入至对应的表中;
统计表数据,结果写入本地’/root/20200801/01’中;

建表并导入

//建表
create table loan (
LoanStatus string,
BorrowerRate decimal(10,5),
ProsperScore int,
Occupation string,
EmploymentStatus string,
IsBorrowerHomeowner string,
CreditScoreRangeLower int,
CreditScoreRangeUpper int,
IncomeRange string
)
row format delimited
fields terminated by ',';

//导入
wget -P /package http://192.168.0.101:82/package/data/loan.csv
load data local inpath '/package/loan.csv' into table loan;
select * from loan;

//表格标识参考下图

表格标识

    1. 在整体借款数据中,计算信用得分ProsperScore对于借款的影响。(以信用得分为变量,统计借款次数)即了解大多数借款人的信用得分
insert overwrite local directory '/root/20200801/011'
row format delimited fields terminated by '\t'
select ProsperScore,count(*) as sum from loan
group by ProsperScore
order by sum desc;
    1. 找出借款较容易的行业前五及对应对应借款次数
insert overwrite local directory '/root/20200801/012'
row format delimited fields terminated by '\t'
select occupation,count(*) as s from loan
group by occupation
order by s desc limit 5;
    1. 贷款率在哪些范围内贷款成功(按照区间进行统计)
select max(ProsperScore),min(ProsperScore) from loan;
    1. 分析在贷款状态为违约(Defaulted)的就业情况。
insert overwrite local directory '/root/20200801/014'
row format delimited fields terminated by '\t'
select EmploymentStatus,count(*) as s from loan
where LoanStatus='Defaulted'
group by EmploymentStatus;
  • 关联分析算法

关联规则: 用于表示数据内隐含的关联性。 定义一个关联规则: A-B
其中A和B表示的是两个互斥事件,a称为前因(antecedent),b称为后果(consequent),上述关联规则表示a会导致b。
关联规则分析就是为了发掘购物数据背后的商机而诞生的。例如 购买尿布的人往往会买啤酒。
公式参考如下:前项A,后项B
支持度:表示同时包含a和b的事务占所有事物的比例,如果有P(A)表示使用a事务的比例,Support=P(A&B)
置信度:表示使用包含a的事务中同时包含b事务的比例,即同时包含a和b 的事务占包含a事务的比例,Confidence=P(A&B)/P(A)
请根据Apriori关联规则算法的原理找出与违约最多的(借款状态,后项)之间的关联度最强的职业(前项),并计算出其支持度与置信度。

    1. 支持度写到本地/root/college009/ 中(保留5位小数)

解题过程如下

//A是关联度最强的职业,B是违约最多的借款状态
//下面是计算A部分

select Occupation,count(*) as s from loan
where LoanStatus='Defaulted'
group by Occupation
order by s desc limit 1;

//下面是计算B部分

select count(*) as s from loan
where LoanStatus='Defaulted' 

//下面是计算C部分,也就是同时包含A事物和B事物

select count(*) as s from loan
where LoanStatus='Defaulted' and Occupation= 'A求出来的职业'

//下面是计算D部分,也就是全部的数量

select count(*) from loan;

//把以上的综合一下就是下方的内容,其实就是C/D

insert overwrite local directory '/root/college009/'
row format delimited fields terminated by '\t'
select
round(t1.s/t2.s,5)
from ((select count(*) as s from loan) t2 join (select count(*) as s from loan where LoanStatus='Defaulted' and Occupation= 'Other') t1);

    1. 置信度写到本地/root/college010/ 中(保留5位小数)

解题过程如下

//我们在上面的支持度中已经计算了相关的内容,然后我们现在梳理一下。
//按照公式来看,支持度Support=P(A&B),置信度Confidence=P(A&B)/P(A)
//所以我们需要用上方获得的结果,去除以P(A)即可
//P(A)=A部分/D部分(也就是总数)
//P(A&B)=C部分/D部分(也就是总数)
//这个写综合算式实在是太麻烦,所以就用分步计算好啦,然后ABCD上面都有,这里就不复制了。

第二题

二、创建access表;
将本地数据access.txt导入至对应的表中

建表并导入

#下载文件
wget -P /package http://192.168.0.101:82/package/data/access.txt
#建表,现在对日期类型还没有很好的选择,只有data,但是一部分还不支持,所以srting万能,哈哈哈。
create table access (
id string,
data string,
num int
)
row format delimited
fields terminated by ',';
#导入文件
load data local inpath '/package/access.txt' into table access;
#查看文件
select * from access;

  • 1.随机取表并且日期是当天(2015-02)3条数据,结果写入/root/20200801/0201;
insert overwrite local directory '/root/20200801/0201'
row format delimited fields terminated by '\t'
select * from access
where data='2015-02'
order by rand() limit 3;
  • 2.将表随机分成10组,抽取其中的第一个桶的数据 ,结果写入/root/20200801/0202;
insert overwrite local directory '/root/20200801/0202'
row format delimited fields terminated by '\t'
SELECT * FROM access TABLESAMPLE(BUCKET 1 OUT OF 10 ON rand());

//这道题需要特别强调一下,这是一个HIVE的特有功能,MySQL里是没有这个东西的。

第三题

三、创建train表;
将本地数据train_format.csv导入至对应的表中;

建表并导入

#下载文件
wget -P /package http://192.168.0.101:82/package/data/train_format.csv
#查看前五行
head -n 5 train_format.csv
#创建表,加上最后一行,导入的时候就不会导入第一行
create table train (
user_id int,
age_range int,
gender int,
merchant_id int,
label int,
activity_log string
)
row format delimited
fields terminated by ','
tblproperties("skip.header.line.count"="1");
#导入表
load data local inpath '/package/train_format.csv' into table train;
select * from train limit 5;

  • 1.统计每个用户的activity次数(activity_log记录每次的轨迹,多次以#分割)结果写入/root/20200801/0301
insert overwrite local directory '/root/20200801/0301'
row format delimited fields terminated by '\t'
select user_id,sum(size(split(activity_log,'#'))) from train
group by user_id;
  • 2.去掉gender=空白的数据,统计女性(gender=0或gender=2)数量,结果写入/root/20200801/0302,统计男性(gender=1)数量,结果写入/root/20200801/0303
#临时表下面这两种方式都可以
create table test as select * from train where gender is not null;
create temporary table test1 as select * from train where gender is not null;

insert overwrite local directory '/root/20200801/0302'
row format delimited fields terminated by '\t'
select count(gender) from test where gender='2' or gender='0';

insert overwrite local directory '/root/20200801/0303'
row format delimited fields terminated by '\t'
select count(gender) from test where gender='1';

第四题

四、创建anjianInfo表;
将本地数据hongyadata.txt 导入至对应的表中;

建表并导入

#下载数据
wget -P /package http://192.168.0.101:82/package/data/hongyadata.txt
#查看前五行
head -n 5 hongyadata.txt
#建表
create table hongya (
id int,
lian string,
leixign string,
money int,
big string,
xijie string,
chuli string,
time1 string,
time2 string,
location string,
location2 string,
time3 string,
time4 string,
name string
)
row format delimited
fields terminated by '\t';
#导入
load data local inpath '/package/hongyadata2.txt' into table hongya;
#查看文件
select * from hongya limit 5;

  • 1.根据案发地信息,统计网络诈骗最多的5个城市/root/20200801/0401
insert overwrite local directory '/root/20200801/0401'
row format delimited fields terminated by '\t'
select location,count(*) as s from hongya
group by location
order by s desc limit 5;


Last modification:August 11th, 2020 at 11:56 am
如果你觉得我的文章帮到你的话,不要白嫖,一毛两毛也是爱。