Postgres 函数获取表字段填充率

获取表的单个字段填充率

-- 生成测试数据
create schema fill;  
create table fill.tb1(  
    id serial,  
    val text,  
    crt timestamp  
);  
  
truncate fill.tb1;  
insert into fill.tb1(val, crt)  
with t as(  
    select random() v, random()*1000 tm from generate_series(1, 10000)  
)  
select  
case when v > 0 and v <= 0.1 then '-'  
when v > 0.1 and v <= 0.2 then ''  
when v > 0.2 and v <= 0.3 then null  
else '实际值'  
end,  
'2017-1-1'::timestamp +  ((tm::int)::varchar||' day')::interval  
from t;

函数脚本

create or replace function getColFillRate(table_name text, column_name text, time_name text, isText bool,  
except_dafault text[] default format($${'', '-'}$$)::text[])  
returns table(year text, rate numeric(14,2))  
language plpgsql  
as  
/*  
函数用途：按年份获取表字段的填充率，对于字符串字段，附带排除默认值  
  
 */  
    $$  
        declare  
            excepts varchar := concat('(', regexp_replace(except_dafault::varchar, '\{|\}', '', 'g'), ')');  
            sql_when_text varchar :=  
            format($_$  
            select to_char(%s, 'yyyy')::text,            sum(case when %s is null or %s in %s then 0 else 1 end)/1.0/count(*)            from %s group by to_char(%s, 'yyyy')$_$, time_name, column_name, column_name, excepts, table_name, time_name);  
  
            sql_when_not_text varchar :=  
            format($_$  
            select to_char(%s, 'yyyy')::text,            sum(case when %s is null  then 0 else 1 end)/1.0/count(*)            from %s group by to_char(%s, 'yyyy')$_$, time_name, column_name, table_name, time_name);  
  
            sql_text varchar;  
        begin            if isText  
            then sql_text := sql_when_text;  
            else sql_text := sql_when_not_text;  
            end if;            return query execute sql_text ;  
        end;    $$;

函数使用方式

select * from getColFillRate('fill.tb1', 'val', 'crt', true);  
select * from getColFillRate('fill.tb1', 'val', 'crt', false);

获取整个表的所有字段填充率

结合上例测试数据

create function getTableFillRate(tb_name text, time_name text)  
returns table(schema_name text, table_name text, column_name text, year text, rate numeric(14,2))  
language plpgsql  
as  
    $$  
        declare  
            v_schema_name varchar := substring(tb_name, '^[^\.]+');  
            v_table_name varchar := substring(tb_name, '[^\.]+$');  
            column_record record;  
            isText bool;  
            res record;  
        begin            for column_record in select case when columns.column_name ~ 'A-Z' then quote_ident(columns.column_name) else columns.column_name end column_name, columns.data_type from information_schema.columns where columns.table_schema = v_schema_name and columns.table_name = v_table_name and columns.column_name <> time_name  
            loop  
                if column_record.data_type in ('character varying', 'text')  
                    then isText := true;  
                    else isText := false;  
                end if;                return query select v_schema_name::text, v_table_name::text, column_record.column_name::text, getColFillRate.year, getColFillRate.rate from getColFillRate(tb_name, column_record.column_name, time_name, isText);  
            end loop;            return ;        end;    $$;

select * from getTableFillRate('fill.tb1', 'crt')