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

user_13584276 0
Viewed 799

I know I can use this query to get all column names for a given table:

select column_name from all_tab_columns where table_name='my_table';

And I can use this query to calculate the percentage of non-null values for a given column in a table:

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

But I want to combine these two queries to get the percentage of non-null values for all columns in a given table (without having to manually type out the column names for each table)

desired output for a given table:

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

Is it possible to do this with only Select statements (no PL/SQL loops)?

oracle sql
1 Answers

The number of non-null values of a table is already in the data dictionary 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
...

This GATHER_TABLE_STATS analyzes the table and stores the number of null values. If you (or somebody else) inserts/deletes/updates to the table, the statistics snapshot is not exact any more, of course.

To get your "completeness" ratio, you'll need not only num_nulls, but also the number of total rows in the table. To get them, you'll need to join to the view ALL_TABLES, column NUM_ROWS, and substract 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);
user_18640290
Related