查询以查找总ID的计数、任意列中为null的ID以及两者的百分比(即ID为null的计数/总ID计数)

Question

有两个表。表A具有以下结构

ID	标志	名称
1X	1	Y
2Y	0	空
3Z	1	空
4A	1	Y

使用条件聚合：

SELECT COUNT(*) Total_Count,    
COUNT(CASE WHEN t1.Name IS NULL OR t2.City IS NULL OR t2.State IS NULL THEN 1 END) Ids_having_null,  
AVG(CASE WHEN COALESCE(t1.Name, t2.City, t2.State) IS NOT NULL THEN 1 ELSE 0 END) Percentage_missing
FROM Table1 t1 INNER JOIN Table2 t2
ON t2.B_ID = t1.ID;

请参阅演示

如果您不知道其中一个表是否具有所有ID
然后，我建议在子查询中使用具有一些条件聚合的full join。

例如：

select 
Total as "Total_Count"  
, TotalMissing as "Ids having null" 
, (TotalMissing / Total)*100||'%' as "Percentage missing" 
from
(
select 
count(distinct coalesce(a.ID, b.B_ID)) as Total
, count(distinct case
when a.name is null
or b.city is null
or b.state is null
then coalesce(a.ID, b.B_ID) 
end) as TotalMissing
from TableA a
full outer join TableB b 
on a.ID = b.B_ID
) q

Total_Count | Id为null |缺少百分比----------：|----------------：|：-----------------4|3|75%

db<gt；小提琴这里

试试这个->

select total_count, (total_count - cn) as ids_having_null, 
(total_count - cn) *100 / total_count as Percentage_missing 
FROM
(select count(t1.ID) as cn , t1.total_count 
FROM ( select ID,Name, sum(tmp_col) over ( order by tmp_col) as total_count from  (select ID,Name, 1 as tmp_col  from tableA ) ) t1
JOIN TableB t2
ON t1.ID = t2.B_ID
WHERE t1.Name is not null and t2.City is not null and t2.State is not null );

根据您对百分比或比率的要求，您可以更改percentage_missing列的逻辑