数据质量检查

源表、目标表

源表-->目标表插入

数据抽查

SELECT * FROM T WHERE T_NAME LIKE ‘%名称%’

主键重复

SELECT T_ID,....COUNT(1) FROM T GOUP BY T_ID HAVING COUNT(1)>1

记录数

SELECT COUNT(1) FROM T

代码映射

SELECT T_CODE ,COUNT(1) FROM T GROUP BY T_CODE ORDER BY T_CODE

主外键一致

SELECT COUNT(1) FROM T LEFT JOIN C ON T.C_ID = C.C_ID WHERE C.C_ID IS NULL OR C.C_ID =””

断链交叉链

SELECT * FROM

(SELECT * ,LEAD(start_date) OVER (

PARTITION BY t_id Order by start_date ASC

) AS next_start_date

From T

) TEMP

WHERE next_start_date <>end_date limit 10

Lead() over()，主要作用是从某一个集合的当前cursor位置算起，按照某种顺序将第下第n 个位置的某一个字段值取出来，默认是往下1个位置。

select incid_id,acty_id,

lead(acty_ts) over(partition by incid_id order by acty_ts asc ) as closed_response

from incid_acty_f

where incid_id in (

select incid_id from incid_acty_f group by incid_id having count(acty_id)>1 ) ;

select incid_id,acty_id,

lead(acty_ts) over(partition by null order by incid_id, acty_ts asc ) as closed_response from incid_acty_f

where incid_id in (

select incid_id from incid_acty_f group by incid_id having count(acty_id)>1 ) ;

这两个主要区别，第一个语句，在遇到最后一个相同incid_id的时候，不再往下取数，显示为Null.

第二个语句，在遇到最后一个相同incid_id的时候，继续往下取数，取的是不同incid_id的acty_ts值

字段非空

SELECT COUNT(1) FROM T WHERE T_NAME IS NULL OR T_NAME =””

-- 查询某表是否有重复的语句；

SELECT * FROM tbl_goods_attribute GROUP BY goods_id HAVING COUNT(1) >1;

SELECT * FROM tbl_goods_attribute where goods_id='201701046999';