sql - Oracle SQL : query to calculate percentage of non-null values for each column in a table

Question

我知道我可以使用这个查询来获取给定表的所有列名:

select column_name from all_tab_columns where table_name='my_table';

我可以使用这个查询来计算表中给定列的非空值的百分比:

select count(col_1) / count(*), count(col_2) / count(*)
from my_table

但我想结合这两个查询来获取给定表中所有列的非空值的百分比(无需手动输入每个表的列名)

给定表的所需输出:

column_name, completeness
col_1,  0.8
col_2, 1.0
col_3, 0.0

是否可以仅使用 Select 语句(无 PL/SQL 循环)来执行此操作？

Answer 1

数据字典 View all_tab_columns 中已经存在表的非空值的数量:

CREATE TABLE t AS SELECT * FROM all_objects;

EXECUTE DBMS_STATS.GATHER_TABLE_STATS(NULL, 'T');

SELECT column_name, num_nulls FROM all_tab_columns WHERE table_name='T';

COLUMN_NAME      NUM_NULLS
OWNER                    0
OBJECT_NAME              0
SUBOBJECT_NAME       66502
OBJECT_ID                0
DATA_OBJECT_ID       62642
...

此 GATHER_TABLE_STATS分析表并存储空值的数量。当然，如果您(或其他人)插入/删除/更新表，则统计快照不再准确。

要获得“完整性”比率，您不仅需要 num_nulls，还需要表中的总行数。要获得它们，您需要加入 View ALL_TABLES , 栏目 NUM_ROWS ，然后减去 NUM_ROWS - NUM_NULLS :

SELECT table_name, column_name, 
       t.num_rows - c.num_nulls / NULLIF(t.num_rows, 0) AS completeness 
  FROM all_tables t
  JOIN all_tab_columns c 
 USING (owner, table_name);