hive函数使用（校验日期连续性）

(一）项目需求

     （1）需要校验某张数据表，某个字段是否每天至少有一条数据，查出缺失的数据，已经有的数据，以及期望的数据；

     （2）缺失数据返回。缺失的数据，能够成功定位日期，返回缺失数据结果集。

(二）分析：

查出缺失日期数据量：

    （1）查询这个字段的最大日期，最小日期。datediff(max(zdrq),min(zdrq))+1 就是值就是期望日期数据量

     (2)查询出数据表已经有的，没天不重复数据日期数据个数。  通过group by zdrq,查出count(1)值，这个值就是实际日期数据量

    （3）期望日期数据量-实际日期数据量=缺失的数据量

查出异常日期数据

   （1）生成一张临时表，表中存临时数据,生成从最小日期到最大日期的所有日期数据

    （2）临时数据和原表数据联合查询，找出缺失的日期

  

(三）数据准备

（1）准备测试数据

创建测试数据表

CREATE TABLE `bqtest`.`t_dj_qyxx` ( `djxh` varchar(20) COMMENT '唯一ID',
`qyshxydm` varchar(20) COMMENT '企业社会信用代码',
`qymc` varchar(300) COMMENT '企业名称',
`fddbrxm` varchar(150) COMMENT '法人代表姓名',
`fddbrsfzjhm` varchar(30) COMMENT '法人代表身份证件号码',
`hy_dm` varchar(4) COMMENT '行业代码',
`zcdz` varchar(300) COMMENT '企业注册地址',
`djrq` date COMMENT '登记日期',
`xgrq` varchar(13) COMMENT '修改日期',
`dq_dm` varchar(12) COMMENT '地区代码',
`cjsj` varchar(13) COMMENT 'GFRA',
`czlx` varchar(12) COMMENT 'SGF',
`uuid` varchar(100) COMMENT 'UUID' )COMMENT '大数据量'
row format delimited fields terminated by ',' stored as orcfile TBLPROPERTIES ('transactional'='false');

创建测试数据

INSERT INTO bqtest.t_dj_qyxx(djxh,qyshxydm,qymc,fddbrxm,fddbrsfzjhm,hy_dm,zcdz,djrq,xgrq,dq_dm,cjsj,czlx,uuid) VALUES
('1244140000993855***','***105099053***','土地增值税专用1131111111','奕**辉','4234234***,'7040','凤新街道大***','2020-01-12','2020-01-16','440682',NULL,NULL,'424428a9-fcc6-11ec-98e0-0242ac14000b'),
('1011440682000000***','440682PLDE00003***','批量定额优化测试003***7','mrzha***','we234211***','6210','fdsafdsa','2020-01-17','2020-01-17','440682',NULL,NULL,'424428bf-fcc6-11ec-98e0-024***),
('12441400009972073***','500109745323***','年度汇算清缴***','边建光','330625195410294***','601','重庆市xx云华路170号','2020-01-18','2020-01-18','4406***',NULL,NULL,'424428d5-fcc6-11ec-98e0-0242ac14000b'),
('1011440682000000***','440682PLDE0000***','批量定额优化测试003***','mrzh***','we234211***','6210','fdsafdsa','2020-02-05','2020-02-05','4406***',NULL,NULL,'424428eb-fcc6-11ec-98e0-0242a***')

（四）查询

（1）查出缺失日期数据量，sql如下：

select nvl(b-a,0) ,nvl(a,0) ,nvl(c,0),nvl((b-a)/b, 0)
from((
select count(*) as a from(
SELECT DATE_FORMAT(xgrq, 'yyyy-MM-dd') date1, count(1) num
FROM bqtest.t_dj_qyxx
GROUP BY DATE_FORMAT(xgrq, 'yyyy-MM-dd')
)a)a,
(select datediff(max(DATE_FORMAT(xgrq, 'yyyy-MM-dd')),min(DATE_FORMAT(xgrq, 'yyyy-MM-dd'))) as b from bqtest.t_dj_qyxx) b ,
(select count(1) as c from bqtest.t_dj_qyxx) c
)

序号	表达式	意思
1	nvl(b-a,0)	缺失日期数
2	nvl(a,0)	已经存在日期数
3	nvl(c,0)	数据表总数
4	nvl(b,0)	期望日期总数

（2）查出缺失日期，sql如下：

需要使用函数：repeat、posexplode、dateAdd

1.repeat('ab',num) 

ab：要复制的字符串

num:要复制的次数

select repeat('m', datediff('2020-01-10', '2020-01-01'))

select repeat('m',datediff(max(t.djrq), min(t.djrq))) 
from bqtest.t_dj_qyxx t

2.posexplode()  可以行转列，并把索引取出

select posexplode(split(repeat('m', datediff(max(t.djrq), min(t.djrq))), 'm'))
from bqtest.t_dj_qyxx t

3、生成连续日期数据(从最小日期到最大日期，所有日期数据）

下面sql，是生成从2020-01-01到2020-05-01日期的所有数据。

with dates as(
    select date_add('2020-01-01', a.pos) as d
    from (select posexplode(split(repeat('m', datediff('2020-01-10', '2020-05-01')), 'm'))) a
)
select * from dates;

4、针对测试表，找出缺失日期的sql

生成连续日期的临时表

with dates as(
    select date_add((select min(DATE_FORMAT(djrq, 'yyyy-MM-dd')) from bqtest.t_dj_qyxx), a.pos) as d
    from (select posexplode(split(repeat('m', 5), 'm'))) a
)
select * from dates;

完整的sql

with dates as(
    select date_add((select min(DATE_FORMAT(djrq, 'yyyy-MM-dd')) from bqtest.t_dj_qyxx), a.pos) as d
    from (select posexplode(split(repeat('m', datediff(max(t.djrq), min(t.djrq))), 'm'))
           from bqtest.t_dj_qyxx t) a
)
select * from dates t1
left join bqtest.t_dj_qyxx t2 on t1.d = t2.djrq
where t2.djrq is null