企业发票异常分析

一、          数据说明：

1、数据组成

（1）增值税发票数据，文件名zzsfp

（2）发票对应货物明细数据，文件名zzsfp_hwmx

（3）企业信息，文件名nsrxx

2、数据字段说明

（1）zzsfp表字典

字段名称	字段含义	数据类型	备注
fp_nid	发票id	String	发票唯一标识
xf_id	销方识别号	String	企业唯一身份标识
gf_id	购方识别号	String	企业唯一身份标识
je	金额	Double
se	税额	Double
jshj	价税合计	Double
kpyf	开票月份	String
kprq	开票日期	String
zfbz	作废标志	String	‘Y’代表作废

 

 

zzsfp表内容（$ less zzsfp）

 

 

 

（2）zzsfp_hwmx表

字段名称	字段含义	数据类型	备注
fp_nid	发票id	String	发票唯一标识
date_key	开票月份	String
hwmc	货物名称	String
ggxh	规格型号	String
dw	单位	String
sl	数量	Double
dj	单价	Double
je	金额	Double
se	税额	Double
spbm	商品编码	String

zzsfp_hwmx表内容（$ less zzsfp_hwmx）

 

 

 

（3）nsrxx表

字段名称	字段含义	数据类型	备注
hydm	行业代码	String
nsr_id	纳税人id	String	企业唯一身份标识
djzclx_dm	登记注册类型代码	String	网上可查阅相关代码含义
kydjrq	开业登记日期	String
xgrq	修改日期	String	给企业打标签的时间
label	标签	String	‘0’代表正常企业 ‘1’代表问题企业

nsrxx表内容（$ less nsrxx）

 

 

 

 

3、关联数据的必要说明

（1）zzsfp表可通过fp_nid进行关联

（2）zzsfp表可通过xf_id或者gf_id与nsrxx中的nsr_id进行关联，分离出销项发票表和进项发票表

二、     测试要求：

1、数据导入：

   要求将三个样表文件中的数据导入HIVE数据仓库中。

建表

create table zzsfp(fp_nid varchar(30),xf_id varchar(30),gf_id varchar(30),je varchar(30),se varchar(30),jshj varchar(30),kpyf varchar(30),kprq varchar(30),zfbz varchar(30)) row format delimited fields terminated by ',' ;

 

create table zzsfp_hwmx(fp_nid varchar(30),date_key  varchar(30),hwmc  varchar(30),ggxh  varchar(30),dw  varchar(30),sl  varchar(30),dj  varchar(30),je  varchar(30),se  varchar(30),spbm  varchar(30)) row format delimited fields terminated by ',' ;

 

create table nsrxx(hydm varchar(30),nsr_id varchar(30),djzclx_dm varchar(30),kydjrq varchar(30),xgrq varchar(30),label varchar(30)) row format delimited fields terminated by ',' ;

 

 

 

导数据

load data local inpath '/linmob/data/zzsfp.txt' overwrite into table zzsfp;

 

load data local inpath '/linmob/data/zzsfp_hwmx.txt' overwrite into table zzsfp_hwmx;

 

load data local inpath '/linmob/data/nsrxx.txt' overwrite into table nsrxx;

 

2、数据分析：

   企业异常的判断标准参考：

    （1）、企业增值税发票进项与出项严重不符即出现只出不进或者只进不出的企业；

create table jin(nsr_id varchar(30),num varchar(30),money varchar(30),shui  varchar(30))row format delimited fields terminated by ',' ;

 

insert into table jin select gf_id as nsr_id,count(*) as num,sum(je) as money,sum(se) as shui from zzsfp group by gf_id;

 

create table chu(nsr_id varchar(30),num varchar(30),money varchar(30),shui  varchar(30))row format delimited fields terminated by ',' ;

 

insert into table chu select xf_id as nsr_id,count(*) as num,sum(je) as money,sum(se) as shui   from zzsfp group by xf_id;

 

create table zjbc(nsr_id  varchar(30),num varchar(30),money varchar(30),shui  varchar(30)) row format delimited fields terminated by ',' ;

 

insert into table zjbc select nsr_id as nsr_id,num as num,money as money,shui as shui  from jin where nsr_id not in (select nsr_id from chu) and money>10000000;

 

create table zcbj(nsr_id  varchar(30),num varchar(30),money varchar(30),shui  varchar(30)) row format delimited fields terminated by ',' ;

 

insert into table zcbj select nsr_id as nsr_id,num as num,money as money,shui as shui  from chu where nsr_id not in (select nsr_id from jin) and money>10000000;

 

结果

 

 

 

    （2）企业发票数据与详细流水信息不符；

create table zzsfp_hwmx_je(fp_nid varchar(30),je  varchar(30),se  varchar(30)) row format delimited fields terminated by ',' ;

 

insert into table zzsfp_hwmx_je select fp_nid as fp_nid,sum(je) as je,sum(se) as se from zzsfp_hwmx group by fp_nid;

 

create table jecw(fp_nid varchar(30),xf_id  varchar(30),gf_id  varchar(30),je  varchar(30),mxje   varchar(30)) row format delimited fields terminated by ',' ;

 

insert into table jecw select zzsfp.fp_nid as fp_nid,xf_id as xf_id,gf_id as gf_id,zzsfp.je as je,zzsfp_hwmx_je.je as mxje from zzsfp join zzsfp_hwmx_je on zzsfp.fp_nid = zzsfp_hwmx_je.fp_nid;

 

create table qymd(nsr_id varchar(30)) row format delimited fields terminated by ',' ;

 

insert into table qymd select nsr_id as nsr_id from zjbc where nsr_id in ( select gf_id from jecw where je-mxje>10000);

 

insert into table qymd select nsr_id as nsr_id from zcbj where nsr_id in ( select xf_id from jecw where je-mxje>10000);

 

结果：

select count(*) from qymd;

 

 

 

    （3）个人上网查阅企业异常信息数据标准；

 

 

 

3、处理结果入库：

   将上述异常标准的结果分别汇总统计，并将结果数据导出到mySQL数据库中。

create table qymd(nsr_id varchar(30))charset utf8 collate utf8_general_ci;

 

 

 

./sqoop export --connect "jdbc:mysql://192.168.111.100:3306/tab?characterEncoding=UTF-8"  --username root --password root --table qymd --export-dir /user/hive/warehouse/qymd --input-null-string "\\\\N" --input-null-non-string "\\\\N" --input-fields-terminated-by "," --input-lines-terminated-by "\\n" -m 1

 

 

 

 

 

   最终结果参考提示：

          最终给出的数据情况

企业总数：33,829

非正常企业总数：1314

 

4、数据可视化展示：

   利用Echarts将上述统计结果以图形化展示的方式展现出来：饼图、柱状图、地图、折线图等。

 

 

 

三、     测试报告：

1、按照测试题目顺序，将实验步骤说明和结果截图存储到答题纸上。