大数据入门练习笔记

石家庄铁道大学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’代表作废

fp_nid  xf_id     gf_id     je    se    jshj        kpyf       kprq       zfbz      (9个字段)

 

启动 hive

hive --service metastore

 

zzsfp建表语句：

 

create table zzsfp

(fp_nid  String,  xf_id String, gf_id  String, je String, se String, jshj String, kpyf String, kprq String, zfbz String)

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

 

导入hive数据库：

load data local inpath '/opt/module/data/zzsfp.csv' into table zzsfp; 

select * from  zzsfp  limit 7;

 

 

 

 

 

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

 

                           

字段fp_nid  date_key      hwmc    ggxh      dw  sl    dj    je    se    spbm  （共10个字段）

zzsfp_hwmx建表语句：

create table zzsfp_hwmx

(fp_nid  String,  date_key String, hwmc  String, ggxh String, dw String, sl String, dj String ,je   String, se  String, spbm  String)

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

 

 

 

导入hive数据库：

load data local inpath '/opt/module/data/zzsfp_hwmx.csv' into table zzsfp_hwmx;  

select  * from zzsfp_hwmx  limit 7;

 

 

 

zzsfp_hwmx表内容（$ less zzsfp_hwmx）

 

（3）nsrxx表

字段名称

字段含义

数据类型

备注

hydm

行业代码

String

 

nsr_id

纳税人id

String

企业唯一身份标识

djzclx_dm

登记注册类型代码

String

网上可查阅相关代码含义

kydjrq

开业登记日期

String

 

xgrq

修改日期

String

给企业打标签的时间

label

标签

String

‘0’代表正常企业

‘1’代表问题企业

 

字段：hydm      nsr_id    djzclx_dm    kydjrq   xgrq       label   (共6个字段)

nsrxx建表语句：

create table nsrxx

(hydm  String,  nsr_id String, djzclx_dm  String, kydjrq String, xgrq String, label String)

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

 

导入hive数据库：

load data local inpath '/opt/module/data/nsrxx.csv' into table nsrxx;  

select * from  nsrxx  limit 7;

 

nsrxx表内容（$ less nsrxx）

 

 

 

Sqoop导出

一、nsrxx表

bin/sqoop export \

--connect jdbc:mysql://hadoop102:3306/dataclean_5 \

--username root \

--password 000429 \

--table nsrxx \

--num-mappers 1 \

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

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

二、zzsfp表

bin/sqoop export \

--connect jdbc:mysql://hadoop102:3306/dataclean_5 \

--username root \

--password 000429 \

--table zzsfp \

--num-mappers 1 \

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

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

三、zzsfp_hwxx表

bin/sqoop export \
--connect "jdbc:mysql://hadoop102:3306/dataclean_5?useUnicode=true&characterEncoding=utf-8" \
--username root \
--password 000429 \
--table zzsfp_hwmx \
--num-mappers 1 \
--export-dir /user/hive/warehouse/zzsfp_hwmx \
--input-fields-terminated-by ","

 

 

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、     按照测试题目顺序，将实验步骤说明和结果截图存储到答题纸上。

Sql语句：

 

（1）发票交易次数异常，三个表，结果集在xf_gf_data表。xf_count和gf_count字段然后计算差值

建表：

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 ","

求出买卖发票差值后存到fp_abs表

INSERT INTO fp_abs (xf_gf_id, abs_)

SELECT

  xf_gf_id,

  ABS(xf_count - gf_count)

FROM

  xf_gf_data ;

 

销方11041空值只出不进

SELECT

  COUNT(xf_count)

FROM

  xf_gf_data

WHERE xf_count = 0 ;

 

 

购方空值4656 只进不出

SELECT

  COUNT(gf_count)

FROM

  xf_gf_data

WHERE gf_count = 0 ;

 

 

 

表数据有33829条，中位数在16915行

差值中位数 15

SELECT * FROM fp_abs ORDER BY abs_ DESC LIMIT 16915;

 

 

 

待定标准：600

 

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

 

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

查找企业发票作废次数存入zfbz_data

 

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 ","

 

待定标准： 17

中位数：

select *from zfbz_data  order  by zfcs desc limit 63017;

63016,63017的作废次数和除以2；======== 1

SELECT COUNT(*) FROM zfbz_data WHERE zfcs>17;

 

平均数2

 

 

 

不同的作废次数对应的企业ID数：

 

 

 

合并第一个和第三个标准

一、 取fp_abs表和zfbz_data表的企业id交集。

（1）      xf_id交集

fp_abs表标准：字段abs_>600共709企业

zfbz_data表标准：字段zfcs>17 共684企业

（2）      gf_id交集

（1）          插入销方和购方的重复id 27个

INSERT INTO id_unfit (id)   

SELECT

  xx.xf_gf_id

FROM

  (SELECT

    a.xf_gf_id

  FROM

    (SELECT

      xf_gf_id

    FROM

      fp_abs

    WHERE abs_ > 400) AS a

    JOIN

      (SELECT

        xf_id

      FROM

        zfbz_data

      WHERE zfcs > 15) AS b

      ON a.xf_gf_id = b.xf_id GROUP BY a.xf_gf_id) AS xx

  JOIN

    (SELECT

      aa.xf_gf_id

    FROM

      (SELECT

        xf_gf_id

      FROM

        fp_abs

      WHERE abs_ > 400) AS aa

      JOIN

        (SELECT

          gf_id

        FROM

          zfbz_data

        WHERE zfcs > 15) AS bb

        ON aa.xf_gf_id = bb.gf_id GROUP BY aa.xf_gf_id) AS yy

    ON xx.xf_gf_id = yy.xf_gf_id ;

（2）          插入不重复的id

xf_id交集142

xf_id交集196

最终表311条id

INSERT INTO id_unfit(id)    

 SELECT xx.xf_gf_id FROM (SELECT

    a.xf_gf_id

  FROM

    (SELECT

      xf_gf_id

    FROM

      fp_abs

    WHERE abs_ > 400) AS a

    JOIN

      (SELECT

        xf_id

      FROM

        zfbz_data

      WHERE zfcs > 15) AS b

      ON a.xf_gf_id = b.xf_id GROUP BY a.xf_gf_id) AS xx WHERE xx.xf_gf_id  NOT IN (SELECT id FROM id_unfit);

     

 INSERT INTO id_unfit(id)    

 SELECT xx.xf_gf_id FROM (SELECT

    a.xf_gf_id

  FROM

    (SELECT

      xf_gf_id

    FROM

      fp_abs

    WHERE abs_ > 400) AS a

    JOIN

      (SELECT

        gf_id

      FROM

        zfbz_data

      WHERE zfcs > 15) AS b

      ON a.xf_gf_id = b.gf_id GROUP BY a.xf_gf_id) AS xx WHERE xx.xf_gf_id  NOT IN (SELECT id FROM id_unfit); 

 

SELECT

  COUNT(*)

FROM

  id_unfit a

  JOIN

    (SELECT

      hydm

    FROM

      nsrxx

    WHERE label = 1) b

ON a.`id` = b.`hydm`

 

posted @ 2021-10-20 21:22  靠谱杨  阅读(61)  评论(0)  编辑  收藏  举报