企业发票异常分析---查找作废次数

 

今天做了企业发票异常分析的查找作废次数，因为作废次数越多就越有问题

建表zfbz_data 并将 zzsfp表中zfbz='Y'的数据查找出来

建表语句之前发表的博客当中有，这里就不再写了

插入语句：

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); 

这样就可以将作废次数的记录全都导入到zfbz_data表中了

 

 

 

导出bin/sqoop export \

--connect jdbc:mysql://192.168.10.102:3306/user \

--username root \

--password 123456 \

--table zfbz_data \

--num-mappers 1 \

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

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

查找作废次数多的企业并一步步往318上靠

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

最终发现在作废次数为25的时候最接近318，是305条