数据仓库测试辅助SQL语句
测试中使用的sql 语句
1.重复数据脚本生成语句:
select table_name, 'select ' || '\'' || table_name || '\'' || ',count(1)
from (select ' || group_concat(column_name,',') || ' from ' || table_name || ' group by ' || group_concat(column_name, ',')
|| ' having count(1)>1) ' || char(10) || ' union all'
from system.column_v
where database_name = 'db_name'
and substr(table_name,1,2) = 's_'
group by table_name
--char(10) 回车换行
--可以加特定日期
2. 查询出重复记录
select 重复记录字段 from 数据表 group by 重复记录字段 having count(重复记录字段)>1
或 select count(*) from 数据表 where 具体日期 and 关键唯一性字段 in ( select 关键唯一性字段 from 数据表 where 具体日期 group by 重复记录字段 having count(重复记录字段)>1)
3.参照完整性(来源表数据大于目标表,表示正确)
数据比对:输出差集,无输出即量表数据一致
select "table_name1", count(1) from
(select field1, field2,... from table_name1 where condition --目标表
except
select field1, field2,... from table_name2 where condition --来源表
)
4.拉链表测试
select p1.sdate, lead(sdate, 1) over(partition by xxxxno order by sdate), p1.*
from 拉链表表名 p1
where xxxxno = '123455555';
5.目标表与来源表一致性
select 'actualTableName' table_name, t1.field, t1_total -t1_total as diff_count from
(select 'aimTableName' table_name, field, count(1) t1_total from aimTableName group by field) t1, --目标表记录数
(select 'originalTableName' table_name, field, count(1) t2_total from originalTableName group by field) t2 --来源表记录数
where t1.field = t2.field; --两者无偏差输出 diff_count 为 0;
6.目标表与来源表的数据对比,使用except
校验数据提取正确性及在 ETL加载过程种是否产生被阶段、精度丢失、乱码等问题;
select field1, field2, ... from originalTable where condition
except
select field1, field2, ... from aimTable where condition
7.
************** 检查没有注释的存储过程,并拼接成 填写注释的SQL语句
CREATE OR REPLACE PROCEDURE PR_C_CHK_COMMENTSTRING(P_DATA_DATE IN STRING,P_O_RESULT OUT STRING)
IS
/*********************************************************************************************
主题
目标表名:c_chk_commentstring 目标表中文名:检查字段注释
开发人: 开发日期:
************************************************************************************************/
V_STEP STRING := '0'
V_PROC_NAME STRING := 'PR_C_CHK_COMMENTSTRING'
V_TABLE_NAME STRING := 'c_chk_commentstring '
V_SUCCESS STRING := UTIL.GET_SUCCESS() ---自定义方法
V_FAILED STRING := UTIL.GET_FAILED()
V_END_TIME STRING
V_SQL STRING
V_DATA_DATE STRING := P_DATA_DATE
BEGIN
V_STEP := '1'
P_O_RESULT := V_SUCESS
-- 支持重跑,删除昨日数据
V_STEP := '2'
EXECUTE IMMEDIATE 'DELETE FROM ' || V_TABLE_NAME|| ' WHERE DATE_DT="' || V_DATA_DATE || '"'
-- 执行成功信息记录
V_STEP := '3'
V_SQL := 'INSERT INTO '|| V_TABLE_NAME||'(
DATA_DT,
TABLE_NAME,
COLUMN_NAME,
COMMENTSTRING,
REPAIRE_SQL
)
SELECT
'''|| V_DATA_DATE || ''' AS DATA_DT,
TABLE_NAME, COLUMN_NAME, COMMENTSTRING,
"ALTER TABLE " || UPPER(DATABASE_NAME) || "." || UPPPER(TABLE_NAME) || " CHANGE COLUMN " || UPPER(COLUMN_NAME) || " " || UPPER(COLUMN_TYPE) || " COMMENT \'\'; "
FROM SYSTEM.COLUMNS_V
WHERE LOWER(DATABASE_NAME)='edw'
AND COMMENTSTRING IS NULL
AND (UPPER(SUBSTR(TABLE_NAME,1,2)) IN ("F_","M_","T_","A_") OR UPPER(SUBSTR(TABLE_NAME,1,3)) = "TP_")';
EXECUTE IMMEDIATE V_SQL
COMMIT
---执行成功记录
V_STEP := 'N'
EXCEPTION
WHEN OTHERS THEN
P_O_RESULT := V_FAILED
ETL.WRITE_LOG(V_PROC_NAME,V_STEP, 'EXCEPTION CAUGHT,ERROR CODE = ' ||SQLCODE()||',ERROR MESSAGE=' ||SQLERRM(),P_O_RESULT, V_DATA_DATE)
RAISE_APPLICATION_ERROR(-20001,V_PROC_NAME)
END PR_C_CHK_COMMENTSTRING
8. 调用存储过程
DECLARE
V_OUT STRING --声明变量
BEGIN --执行语句开始
PRO_NAME('入参实参1','入参实参1',V_OUT); --调用存储过程
DBMS_OUT.PUT_LINE("OUT PARAM:" || V_OUT); --输出
END;
9. 函数:
1)concat()函数: 将多个字符串连接成一个字符串,语法: concat(str1, str2,...)
2) group_concat()函数:
a、功能:将group by产生的同一个分组中的值连接起来,返回一个字符串结果。
b、语法:group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
3) over(partition by class order by sroce) 按照sroce排序进行累计,order by是个默认的开窗函数,按照class分区。
4) LEAD函数简单点说,就是把下一行的某列数据提取到当前行来显示
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤