刘一辰的软件工程随笔

石家庄铁道大学2021年秋季

  2019 级课堂测试试卷-企业发票异常分析

课程名称： 大型数据库应用技术  任课教师： 王建民      测试时间：150分钟

一、          数据说明：

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、建表

对三个表建表

-- auto-generated definition

create table zzsfp_hwmx

(

    fp_nid   varchar(256) null,

    date_fey varchar(256) null,

    hwmc     varchar(256) null,

    ggxh     varchar(256) null,

    dw       varchar(256) null,

    sl       varchar(256) null,

    dj       varchar(256) null,

    je       varchar(256) null,

    se       varchar(256) null,

    spbm     varchar(256) null

);

 

-- auto-generated definition

create table zzsfp

(

    fp_nid varchar(256) null,

    xf_id  varchar(256) null,

    gf_id  varchar(256) null,

    je     varchar(256) null,

    se     varchar(256) null,

    jshj   varchar(256) null,

    kpyf   varchar(256) null,

    kprq   varchar(256) null,

    zfbz   varchar(256) null

);

-- auto-generated definition

create table nsrxx

(

    hydm      varchar(256) null,

    nsr_id    varchar(256) null,

    dizclx_dm varchar(256) null,

    kydirq    varchar(256) null,

    xgrq      varchar(256) null,

    label     varchar(256) null

);

得到结果

 

2、开始清洗、筛选

insert overwrite table zzsfp_hwmx select

translate(trim(fp_nid),'%[(]',''),date_key,hwmc,ggxh,dw,sl,dj,je,se,translate(trim(spbm),'%[)]','') from zzsfp_hwmx;

 

insert overwrite table zzsfp select

translate(trim(fp_nid),'%[(]',''),xf_id,gf_id,je,se,jshj,kpyf,kprq,translate(trim(zfbz),'%[)]','') from zzsfp;

 

insert overwrite table nsrxx select

translate(trim(hydm),'%[(]',''),nsr_id,djzclx_dm,kydjrq,xgrq,translate(trim(label),'%[)]','') from nsrxx;

 

导出nsrxx

 

导出zzsfp

 

导出zzsfp_hwmx

 

测试集

 

生成树

 

clf.feature_importances_)

生成树重要性

 

其中思路大体是

抓紧自查！企业这41种行为不要再有了

1. 商贸公司进、销严重背离，如大量购进玉米、销售的却是手机。

2. 企业长期存在增值税留抵异常现象。

3. 企业增值税税负异常偏低。

4. 企业增值税税负偏高异常。

5. 企业常年亏损，导致企业所得税贡献率异常偏低。

6. 公司自开业以来长期零申报。

7. 公司大量存在现金交易，而不通过对公账户交易。

8. 企业的往来账户挂账过大。

9. 企业存货过大。

10. 企业大量取得未填写纳税人识别号或统一社会信用代码的增值税普通发票

11. 企业存在大量无清单的办公用品增值税发票

12. 公司缴纳的增值税与附加税费金额比对异常

13. 企业连续三年以上盈利但从来不向股东分红

14. 企业存在大量发票抬头为个人的不正常费用

15. 企业所得税申报表中的利润数据和报送的财务报表的数据不一致异常

16. 增值税纳税申报表附表——“未开票收入”填写负数异常

17. 增值税申报表申报的销售额与增值税开票系统销售额不一致预警

18. 无免税备案但有免税销售额异常

19. 无简易征收备案但有简易计税销售额的预警风险

20. 开票项目与实际经营范围严重不符异常

21. 增值税纳税申报表附表二“进项税额转出”为负数异常

22. 公司只有销项但是从来没有进项出现异常

23. 公司只有进项但是从来没有销项出现异常

24. 新成立的公司频繁发票增量异常

25. 新成立的公司突然短期内开票额突增异常

26. 工资薪金的个人所得税人均税款偏低异常风险

27. 个人取得两处及两处以上工资薪金所得未合并申报风险

28. 同一单位员工同时存在工资薪金所得与劳务报酬所得的异常

29. 个人所得税和企业所得税申报的工资总额不符出现的异常风险

30. 期间费用率偏高异常

31. 大部分发票顶额开具，发票开具金额满额度明显偏高异常

32. 大量存在农产品抵扣异常

33. 公司账面上没有车辆但是大量存在加油费等异常

34. 外埠进项或者销项税额比重严重过高

35. 增值税专用发票用量变动异常

36. 纳税人销售货物发票价格变动异常

37. 法人户籍非本地，法人设立异常集中

38. 企业大量存在“会务费”“材料一批”“咨询费”“服务费”“培训费”等无证据链的关键词

39. 少交或不交社保。如：试用期不入社保，工资高却按最低基数缴纳社保

40. 企业代别人挂靠社保

41. 员工自愿放弃社保，企业就没有给入

（参考文献https://view.inews.qq.com/a/20210817A04DS400）

其中参考这几条，通过如此语句筛选

2. 公司缴纳的增值税与附加税费金额比对异常

create table xf_data

(xf_id  String,  xf_count String)

ROW format delimited fields terminated by ',' STORED AS TEXTFILE;

 

create table gf_data

(gf_id  String,  gf_count String )

ROW format delimited fields terminated by ',' STORED AS TEXTFILE;

create table xf_gf_data

(xf_gf_id  String,  xf_count String,  gf_count String )

ROW format delimited fields terminated by ',' STORED AS TEXTFILE;

Insert into xf_data(xf_id,xf_count)

Select xf_id , count(xf_id) from zzsfp group by xf_id;

 

Insert into gf_data(gf_id,gf_count)

Select gf_id , count(gf_id) from zzsfp group by gf_id;

 

INSERT INTO xf_gf_data (xf_gf_id)

SELECT

  hydm

FROM

  nsrxx ;

bin/sqoop export \

--connect "jdbc:mysql://hadoop102:3306/dataclean_5?useUnicode=true&characterEncoding=utf-8" \

--username root \

--password 000429 \

--table xf_gf_data \

--num-mappers 1 \

--export-dir /user/hive/warehouse/xf_gf_data \

--input-fields-terminated-by ","

INSERT INTO fp_abs (xf_gf_id, abs_)

SELECT

  xf_gf_id,

  ABS(xf_count - gf_count)

FROM

  xf_gf_data ;

得到结果：导出747条

 

3. 企业增值税税负异常偏低。

create table zfbz_data

(xf_id String , gf_id String , zfcs String)

ROW format delimited fields terminated by ',' STORED AS TEXTFILE

 

Insert into table zfbz_data(xf_id,gf_id,zfcs) (select xf_id, gf_id ,count(zfbz) from zzsfp where zfbz = 'Y'  group by xf_id,gf_id);

 

bin/sqoop export \

--connect "jdbc:mysql://hadoop102:3306/dataclean_5?useUnicode=true&characterEncoding=utf-8" \

--username root \

--password 000429 \

--table zfbz_data \

--num-mappers 1 \

--export-dir /user/hive/warehouse/zfbz_data \

--input-fields-terminated-by ","

bin/sqoop export \

--connect

--password 000429 \

--table xf_gf_data \

--num-mappers 1 \

--export-dir /user/hive/warehouse/xf_gf_data \

--input-fields-terminated-by ","

INSERT INTO fp_abs (xf_gf_id, abs_)

SELECT

  xf_gf_id,

  ABS(xf_count - gf_count)

FROM

  xf_gf_data ;

得到结果：导出1550条

 

4. 企业增值税税负偏高异常

create table xf_data

(xf_id  String,  xf_count String)

ROW format delimited fields terminated by ',' STORED AS TEXTFILE;

 

create table gf_data

(gf_id  String,  gf_count String )

ROW format delimited fields terminated by ',' STORED AS TEXTFILE;

create table xf_gf_data

(xf_gf_id  String,  xf_count String,  gf_count String )

ROW format delimited fields terminated by ',' STORED AS TEXTFILE;

Insert into xf_data(xf_id,xf_count)

Select xf_id , count(xf_id) from zzsfp group by xf_id;

 

Insert into gf_data(gf_id,gf_count)

Select gf_id , count(gf_id) from zzsfp group by gf_id;

 

INSERT INTO xf_gf_data (xf_gf_id)

SELECT

  hydm

FROM

  nsrxx ;

bin/sqoop export \

--connect

--username root \

--password 000429 \

--table xf_gf_data \

--num-mappers 1 \

--export-dir /user/hive/warehouse/xf_gf_data \

--input-fields-terminated-by ","

INSERT INTO fp_abs (xf_gf_id, abs_)

SELECT

  xf_gf_id,

  ABS(xf_count - gf_count)

FROM

  xf_gf_data ;

导处结果989条

 

6. 公司自开业以来长期零申报。

create table xf_data

(xf_id  String,  xf_count String)

ROW format delimited fields terminated by ',' STORED AS TEXTFILE;

 

create table gf_data

(gf_id  String,  gf_count String )

ROW format delimited fields terminated by ',' STORED AS TEXTFILE;

create table xf_gf_data

(xf_gf_id  String,  xf_count String,  gf_count String )

ROW format delimited fields terminated by ',' STORED AS TEXTFILE;

Insert into xf_data(xf_id,xf_count)

Select xf_id , count(xf_id) from zzsfp group by xf_id;

Insert into gf_data(gf_id,gf_count)

Select gf_id , count(gf_id) from zzsfp group by gf_id;

INSERT INTO fp_abs (xf_gf_id, abs_)

SELECT

  xf_gf_id,

  ABS(xf_count - gf_count)

FROM

  xf_gf_data ;

得到结果297条

 

共3,533条，需要进一步清洗重复数据

 

3、得到结果导出到表格里面

测试筛选出来的id

1320

1371

1432

1515

1742

1931

1942

1951

2033

2042

2110

2222

2433

2442

2653

2659

2923

2927

2929

3029

3049

3054

3120

3261

3324

3340

3359

3432

3524

3553

3591

3599

3731

3799

3832

3849

3854

3939

4011

4019

4411

4420

4620

4819

5146

5166

5241

5285

5292

5513

5821

6231

7090

7111

7221

7232

7250

7461

7474

7830

7980

8990

1011

1099

119

1363

1369

1372

1392

1422

1789

230

2414

2432

2434

2612

2614

2625

2643

2681

2720

2812

3011

3024

3035

3057

3073

3110

3264

3413

3857

3859

3952

3990

4012

5134

5174

5222

5231

5244

5286

5297

5419

5611

6232

6311

6599

6740

7129

7310

7514

7990

8119

8740

8919

9125

9422

9510

1331

1340

1353

1362

141

1492

1513

169

1751

1771

1779

1782

1830

190

2130

2239

2419

2422

2444

2644

2664

2730

2770

2822

2921

2925

3012

3041

3229

329

3311

3351

3373

3379

3382

3399

3423

3429

3434

3441

3513

3546

3551

3575

3579

3610

3711

3713

3762

3825

3829

3856

3869

3911

3971

4013

4026

4350

4390

4415

5010

5021

5029

5090

5115

5119

5122

5128

5139

5151

5164

5168

5175

5191

5212

5221

5223

5236

5243

5245

5263

5412

5430

5612

5919

6110

6291

6321

6639

6811

6899

7030

7272

7289

7292

7296

7340

7450

7690

7722

7810

8011

8099

8291

8299

8330

8640

8770

8949

9124

921

1019

1310

1431

1499

1514

1523

1530

1712

1743

1761

1921

1923

1954

2021

2029

2041

2223

2449

2461

2469

2641

2645

2663

2665

2689

2823

2829

2913

2922

3031

3059

3071

3091

3211

3213

3239

3381

3439

3459

3482

3484

3512

3525

3532

3543

3545

3554

3574

3576

3589

3741

3752

3833

3871

3899

3932

4021

4025

4030

4111

412

4342

4414

4690

4700

4830

4920

511

5114

5141

5145

5189

5226

5239

5273

5279

5822

5990

6319

6322

7020

7040

7231

7239

7240

7271

7291

7293

7295

7440

7482

7499

7721

7723

8021

8414

8920

9110

9419

2663

2665

2689

2823

2829

2913

2922

3031

3059

3071

3091

3211

3213

3239

3381

3439

3459

3482

3484

3512

3525

3532

3543

3545

3554

3574

3576

3589

3741

3752

3833

3871

3899

3932

4021

4025

4030

4111

412

4342

4414

4690

4700

4830

4920

511

5114

5141

5145

5189

5226

5239

5273

5279

5822

5990

6319

6322

7020

7040

7231

7239

7240

7271

7291

7293

7295

7440

7482

7499

7721

7723

8021

8414

 

共403个

 

TRANSLATE with x

English

Arabic	Hebrew	Polish
Bulgarian	Hindi	Portuguese
Catalan	Hmong Daw	Romanian
Chinese Simplified	Hungarian	Russian
Chinese Traditional	Indonesian	Slovak
Czech	Italian	Slovenian
Danish	Japanese	Spanish
Dutch	Klingon	Swedish
English	Korean	Thai
Estonian	Latvian	Turkish
Finnish	Lithuanian	Ukrainian
French	Malay	Urdu
German	Maltese	Vietnamese
Greek	Norwegian	Welsh
Haitian Creole	Persian

 

TRANSLATE with

COPY THE URL BELOW

Back

EMBED THE SNIPPET BELOW IN YOUR SITE

Enable collaborative features and customize widget: Bing Webmaster Portal

Back