我需要计算一个表中有多少字段为null、为空和已填充。其中每列都是一个字段,例如:
select COUNT(1),
case when table.name is null
then 'Null'
when table.name = ''
then 'Empty'
else 'Filled' end
from table
(这是一个示例(
FIELD NULL EMPTY FILLED
name 0 2 98
age 10 10 80
heigh 0 50 50
有人知道我该怎么做吗?这个表大约有30列。
您可以将每一行转换为JSON值,然后按这些JSON值的键(即列名(分组:
select d.col,
count(*) filter (where value is null) as null_count,
count(*) filter (where value is not null) as not_null_count,
count(*) filter (where value = '') as empty
from the_table t
cross join jsonb_each_text(to_jsonb(t)) as d(col, value)
group by d.col;
请注意,这将比像您那样手动列出每一列慢很多。