每日博客
企业发票异常分析
一、数据说明：
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数据仓库中。
2、数据分析：
企业异常的判断标准参考：
（1）、企业增值税发票进项与出项严重不符即出现只出不进或者只进不出的企业；
（2）企业发票数据与详细流水信息不符；
（3）个人上网查阅企业异常信息数据标准；
3、处理结果入库：
将上述异常标准的结果分别汇总统计，并将结果数据导出到mySQL数据库中。
最终结果参考提示：
最终给出的数据情况
企业总数：33,829
非正常企业总数：318
4、数据可视化展示：
利用Echarts将上述统计结果以图形化展示的方式展现出来：饼图、柱状图、地图、折线图等。
三、 测试报告：
1、 按照测试题目顺序，将实验步骤说明和结果截图存储到答题纸上。
过程
1. 导入hive
（其中之一）
create table nsrxx (
nsr_id String,
hydm String,
djzclx_dm String,
kydjrq String,
xgrq String,
label String
) ROW format delimited fields terminated by ',' STORED AS TEXTFILE;
2. 数据去重
（其中之一）
DELETE FROM zzsfp
WHERE fp_nid IN
(SELECT fp_nid FROM
(SELECT fp_nid FROM zzsfp GROUP BY fp_nid HAVING COUNT( * ) > 1) a)
AND id NOT IN(SELECT MIN(id) FROM
(SELECT MIN(id) AS id FROM zzsfp GROUP BY fp_nid HAVING COUNT( * ) > 1) b);
- 3. 去括号
（其中之一）
update zzsfp set fp_nid=replace(fp_nid,'(','') ;
- 4. 导入mysql
(其中之一)
bin/sqoop export --connect jdbc:mysql://localhost:3306/doc_system
--username root
--password fx30001225
--table nsrxx
--num-mappers 1
--export-dir /user/hive/warehouse/nsrxx/nsrxx.txt
--input-fields-terminated-by ","
- 5. 建xf（xf_id,je,se）,gf（gf_id,je,se）表,查各公司总支出收入。
- 6. 分别补充xf和gf表中互相所没有的企业id，je、se默认为0，再建cj（n_id,cj）表查收入支出差价。
（其中之一）
INSERT INTO gf(gf_id)SELECT xf_id FROM xf
WHERE NOT EXISTS(SELECT*FROM gf WHERE gf.gf_id=xf.xf_id)
- 7. 建fp_x（n_id,cs）,fp_g,fp_all查各公司未作废发票总数。
CREATE TABLE fp_x AS
SELECT xf_id as n_id,COUNT(fp_nid) AS cs FROM `zzsfp`
WHERE zfbz='N' GROUP BY xf_id;
CREATE TABLE fp_g AS
SELECT gf_id as n_id,COUNT(fp_nid) AS cs FROM `zzsfp`
WHERE zfbz='N' GROUP BY gf_id;
CREATE TABLE fp_all AS
SELECT fp_x.n_id as n_id,fp_x.cs+fp_g.cs AS cs FROM `fp_g`
JOIN fp_x ON fp_x.n_id=fp_g.n_id
- 8. 建fp_cs（n_id,cs），fp_cs_g，fp_cs_all查各公司发票作废次数。
(fp_cs_all建表语句)
CREATE TABLE fp_cs_all AS
SELECT fp_cs.n_id as n_id,fp_cs.cs+fp_cs_g.cs AS cs FROM fp_cs
JOIN fp_cs_g ON fp_cs.n_id=fp_cs_g.n_id
- 9. 将各公司作废发票数与未作废发票数做比。
CREATE TABLE fpb AS
SELECT fp_cs_all.n_id AS n_id,(fp_cs_all.cs/fp_all.cs) AS bl
FROM fp_cs_all JOIN fp_all
ON fp_cs_all.n_id=fp_all.n_id
GROUP BY fp_cs_all.n_id
- 10. 将各公司收支差价与收支做比，求比率。
若cj >= 0，与
CREATE TABLE cjb AS SELECT
zcj.n_id AS n_id,
IF
(
zcj.cj >= 0,
((zcj.cj * 100 )/ xf.je ),
(((-1) * zcj.cj * 100 )/ gf.je)
) AS bl
FROM `zcj`
JOIN xf ON xf.xf_id = zcj.n_id
JOIN gf ON gf.gf_id = zcj.n_id
GROUP BY n_id
- 11. 根据两个表的比率求问题企业，调整比率，直到问题企业数量接近答案。
SELECT fpb.n_id FROM `fpb` JOIN `cjb` ON fpb.n_id = cjb.n_id
AND fpb.bl >= 0.5 AND cjb.bl >= 0.5
（361条）
各种表
