(一)项目需求
(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