获取给定模式中每个表的每一列的 NULL 值的绝对数量和百分比

...所有表中每列的空值的绝对数量和百分比

测试设置

CREATE TABLE test (col1 int, col2 text, col3 date);
INSERT INTO test VALUES
  (1, 'text', '2024-1-1')
, (2, 'text', null)
, (3, null  , null)
, (4, 'text', null)
, (5, null  , null)
;

简单、幼稚、重复且缓慢

基本上，您希望任何给定表的所有列都有这种输出：

SELECT 'col1' AS column
     , count(*) FILTER (WHERE col1 IS NULL) AS has_null
     , round(count(*) FILTER (WHERE col1 IS NULL) / (count(*) / 100.0)) AS pct_null
FROM   test t

UNION ALL
SELECT 'col2' AS column
     , count(*) FILTER (WHERE col2 IS NULL) AS has_null
     , round(count(*) FILTER (WHERE col2 IS NULL) / (count(*) / 100.0)) AS pct_null
FROM   test t

UNION ALL
SELECT 'col3' AS column
     , count(*) FILTER (WHERE col3 IS NULL) AS has_null
     , round(count(*) FILTER (WHERE col3 IS NULL) / (count(*) / 100.0)) AS pct_null
FROM   test t;

智能，但还不够动态

除了速度更快之外，这还添加了更多数据并防止被零除：

SELECT c.col AS column
     , c.null_ct AS has_null
     , round(c.null_ct / ct_div) AS pct_null
FROM  (
   SELECT count(*) AS ct
        , NULLIF(count(*), 0) / 100.0 AS ct_div
        , '{"* (total_row_count)","* (rows_with_any_null)",col1,col2,col3}'::text[] AS cols   
        , ARRAY [count(*)
               , count(*) FILTER (WHERE NOT (t.*) IS NOT NULL)
               , count(*) FILTER (WHERE col1 IS NULL)
               , count(*) FILTER (WHERE col2 IS NULL)
               , count(*) FILTER (WHERE col3 IS NULL)
                 -- more?
                ] AS nulls
   FROM   test t
   ) sub, unnest(cols, nulls) AS c(col, null_ct);

关于WHERE NOT (t.*) IS NOT NULL：

• 对一组列的 NOT NULL 约束

有关的：

• 优化每个 ID 具有不同值的列的查询

全自动化

您真正想要的是：执行动态查询的全自动函数。具有附加功能和优化的性能。还可以正确防御可能的 SQL 注入。

该函数接受一个或两个参数：

1. _tbl regclass...表名称为（可选模式限定）字符串文字或 OID 类型regclass
2. _show_table_stats bool...显示附加表统计数据？可选，默认false。

CREATE OR REPLACE FUNCTION public.f_null_ratio(_tbl regclass, _show_table_stats bool = false)
  RETURNS TABLE(column_name text, has_null bigint, pct_null numeric)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _cols text[];
   _nulls_sql text;
   _sql text;
BEGIN
   SELECT INTO _cols, _sql
        CASE WHEN _show_table_stats
               THEN '{* (total_row_count), * (rows_with_any_null)}'::text[] || array_agg(col)
               ELSE array_agg(col)
           END  -- AS cols
        , format(
/* dynamic query string */
$q$SELECT c.col  -- AS column
     , c.null_ct  -- AS has_null
     , round(c.null_ct / ct_div)  -- AS pct_null
FROM  (
   SELECT count(*) AS ct
        , NULLIF(count(*), 0) / 100.0 AS ct_div
        , $1 AS cols   
        , %1$s AS nulls
   FROM   %2$s t
   ) sub, unnest(cols, nulls) AS c(col, null_ct)$q$
/* dynamic query string */
               , CASE WHEN _show_table_stats
                      THEN 'ARRAY [count(*), count(*) FILTER (WHERE NOT (t.*) IS NOT NULL), '
                      ELSE 'ARRAY [' END
              || string_agg('count(*) FILTER (WHERE ' || a.col || ' IS NULL)', ', ') || ']'  -- %1$s 
               , _tbl                                                                        -- %2$s 
                )  -- AS sql
   FROM  (
      SELECT a.attrelid, quote_ident(a.attname) AS col
      FROM   pg_catalog.pg_attribute a
      WHERE  a.attrelid = _tbl
      AND    NOT a.attisdropped  -- no dropped (dead) columns
      AND    a.attnum > 0        -- no system columns
      ORDER  BY a.attnum
      ) a;

   -- RAISE NOTICE E'%\n%', _sql, _cols;  -- debug?
   RETURN QUERY EXECUTE _sql
   USING _cols;
END
$func$;

调用示例：

SELECT * FROM f_null_ratio('public.test'::regclass);
SELECT * FROM f_null_ratio('test');
SELECT * FROM f_null_ratio('_tbl => public.test', _show_table_stats => true);
SELECT * FROM f_null_ratio('test', true);

小提琴

现在您只需为每个感兴趣的表调用此函数即可。
您需要了解 PL/pgSQL 的方法才能正确处理这个问题。

关于使用%s表名作为regclass输入format()：

• 表名作为 PostgreSQL 函数参数

有关的：

• 计算每行的 NULL 值