总单元数=列数* count(*)有数据单元为 对每列count(*) where colname is not null (或者根据实际问题背景的没有数据标记) 相加
--表名:HI_PROPERTIES为我自己库内的表,请自行替换 declare counted int := 0; getCounted int := 0; total int := 0;--总列数 nullCounted int := 0;--无值列数 strSql varchar2(1000) := '';
getRate number := 0.0; nullRate number := 0.0; begin select count(1) into counted from hi_properties;--取指定表的总记录数
for colList in (select cname from col where tname='HI_PROPERTIES') loop strSql := 'select sum(decode(' || colList.Cname || ',null,0,1)) from HI_PROPERTIES'; execute immediate strSql into getCounted; if (getCounted = 0) then nullCounted := nullCounted + 1; end if; total := total + 1; --延伸:如果字段内记录数并不是所有的都为NULL值,只有部分为NULL strSql := 'select trunc(sum(decode(' || colList.Cname || ',null,0,1))/' || to_char(counted) || '*100,2) from HI_PROPERTIES'; execute immediate strSql into getRate; if (getRate < 50 ) then nullRate := nullRate + 1; end if;
谢谢各位:大体上,2,3楼所给的思路一样,谢谢3楼敲的代码。以下是我自己的存储过程: create or replace procedure test3(tableName in varchar2,v_res out number) is v_int number; v_num number:=0; v_col number; v_rol number; begin for m in(select COLUMN_NAME from user_tab_cols where Table_name=tableName) loop execute immediate 'select count('||m.column_name||') from '||tableName into v_int; v_num:=v_num+v_int; end loop; execute immediate 'select count(*) from '||tableName into v_rol; execute immediate 'select count(COLUMN_NAME) from USER_TAB_COLUMNS where Table_name = '''||tableName||''' 'into v_col;
if v_rol<>0 then v_res:=v_num/(v_col*v_rol); else v_res:=0; end if;end test3; 测试了:13904935条数据,时间117s 3楼的代码没测试,觉得时间应该和我的差不多。师兄表示,需要改进,目前没有发现其他更快的方案。
经师兄指点,1千万条数据,从2分钟优化到10秒钟,泪崩啊! create or replace procedure test5(tableName in varchar2,v_res out number) isv_num number:=0;--非空个数 v_rol number:=0;--行数 v_col number:=0;--列数 strSQL varchar2(1000):='';begin for m in(select COLUMN_NAME from user_tab_cols where Table_name=tableName) loop strSQL := strSQL || 'count(' || m.column_name || ')+'; end loop; strSQL:= strSQL || 'count(*)'; execute immediate 'select '||strSql||' from '||tableName into v_num; execute immediate 'select count(*) from '||tableName into v_rol; execute immediate 'select count(COLUMN_NAME) from USER_TAB_COLUMNS where Table_name = '''||tableName||''' 'into v_col;if v_rol<>0 then v_res:=(v_num-v_rol)/( v_col *v_rol); else v_res:=0; end if;end test5;
declare
counted int := 0;
getCounted int := 0;
total int := 0;--总列数
nullCounted int := 0;--无值列数
strSql varchar2(1000) := '';
getRate number := 0.0;
nullRate number := 0.0;
begin
select count(1) into counted from hi_properties;--取指定表的总记录数
for colList in (select cname from col where tname='HI_PROPERTIES') loop
strSql := 'select sum(decode(' || colList.Cname || ',null,0,1)) from HI_PROPERTIES';
execute immediate strSql into getCounted;
if (getCounted = 0) then
nullCounted := nullCounted + 1;
end if;
total := total + 1;
--延伸:如果字段内记录数并不是所有的都为NULL值,只有部分为NULL
strSql := 'select trunc(sum(decode(' || colList.Cname || ',null,0,1))/' || to_char(counted) || '*100,2) from HI_PROPERTIES';
execute immediate strSql into getRate;
if (getRate < 50 ) then
nullRate := nullRate + 1;
end if;
end loop;
dbms_output.put_line('全空字段占所有字段的比率:' || trunc((nullCounted/total)*100,2) || '%');
dbms_output.put_line('NULL值记录为记录总数50%以下的字段占所有字段的比率:' || trunc((nullRate/total)*100,2) || '%');
end;--结果
全空字段占所有字段的比率:0%
NULL值记录为记录总数50%以下的字段占所有字段的比率:27.27%
create or replace procedure test3(tableName in varchar2,v_res out number) is
v_int number;
v_num number:=0;
v_col number;
v_rol number;
begin for m in(select COLUMN_NAME from user_tab_cols where Table_name=tableName)
loop
execute immediate 'select count('||m.column_name||') from '||tableName into v_int;
v_num:=v_num+v_int;
end loop; execute immediate 'select count(*) from '||tableName into v_rol;
execute immediate 'select count(COLUMN_NAME) from USER_TAB_COLUMNS where Table_name = '''||tableName||''' 'into v_col;
if v_rol<>0 then
v_res:=v_num/(v_col*v_rol);
else
v_res:=0;
end if;end test3;
测试了:13904935条数据,时间117s
3楼的代码没测试,觉得时间应该和我的差不多。师兄表示,需要改进,目前没有发现其他更快的方案。
create or replace procedure test5(tableName in varchar2,v_res out number) isv_num number:=0;--非空个数
v_rol number:=0;--行数
v_col number:=0;--列数
strSQL varchar2(1000):='';begin for m in(select COLUMN_NAME from user_tab_cols where Table_name=tableName)
loop
strSQL := strSQL || 'count(' || m.column_name || ')+';
end loop;
strSQL:= strSQL || 'count(*)';
execute immediate 'select '||strSql||' from '||tableName into v_num; execute immediate 'select count(*) from '||tableName into v_rol;
execute immediate 'select count(COLUMN_NAME) from USER_TAB_COLUMNS where Table_name = '''||tableName||''' 'into v_col;if v_rol<>0 then
v_res:=(v_num-v_rol)/( v_col *v_rol);
else
v_res:=0;
end if;end test5;