1

I want a generic query to get fill rate of all columns in table .Query should work irrespective of the column number.I have to implement this using presto sql.I have tried searching for a method but nothing seems to working.

Input

A B C D
1 null null 1
2 2 3 4
Null Null Null 5

Output

A B C D
0.66 0.33 0.33 1.0

Explanation: A Col contains 3 rows with 2 non null values so 2/3 B and C Cols contain 2 null value and one non null value so 1/3 D col there is no null values so 3/3

Thanks in advance

aju_dev
  • 25
  • 5

1 Answers1

0

AFAIK Presto/Trino does not provide dynamic query execution capabilities (i.e. something like EXEC in T-SQL) so the only option (unless you are ready to go down user defined function road) to write a query which will enumerate all needed columns (if you are using client from another language - you can build the query dynamically leveraging information_schema.columns info):

with dataset(A, B, C, D) as (
    values (1, null, null, 1),
        (2, 2, 3, 4),
        (Null, Null, Null, 5)
)

select 1.00 * count_if(a is not null) / count(*) a,
    1.00 * count_if(b is not null) / count(*) b,
    1.00 * count_if(c is not null) / count(*) c,
    1.00 * count_if(d is not null) / count(*) d
from dataset;

Output:

a b c d
0.67 0.33 0.33 1.00
Guru Stron
  • 102,774
  • 10
  • 95
  • 132
  • Hi, Thanks again,Is there any way to loop through the column we get from information schema ? – aju_dev Jan 28 '23 at 03:45
  • @aju_dev AFAIK Presto does not have PL/SQL like capabilities so you will need either to execute one query which will generate another one using information_schema and then manually execute the generated query or use some external client scripting to fully automate it. – Guru Stron Jan 28 '23 at 09:12