获取 Teradata 表中每列的缺失值和非缺失值的计数

Question

我在 Teradata 中有一个包含以下类似数据的表，我想计算表中每列的总缺失记录和总非缺失记录。

预期结果：

目前我正在使用以下查询

SELECT 'New_York' Column_Name
, SUM(CASE WHEN New_York <> ''  THEN 1 ELSE 0 END) Nonmissing
,SUM(CASE WHEN New_York = '' THEN 1 ELSE 0 END) Missing
from Table A

SELECT 'London' Column_Name
, SUM(CASE WHEN London <> ''  THEN 1 ELSE 0 END) Nonmissing
,SUM(CASE WHEN London = '' THEN 1 ELSE 0 END) Missing
from Table A

等

任何人都可以提供建议吗？循环遍历所有列以创建单个查询，而不是使用不同的列名多次重复相同的查询。我的表中有大约 430 列，多次重复相同的查询是不可行的。任何建议都将受到高度赞赏。

Answer 1

试试这个。它非常通用的 SQL，它应该适合你。

declare @sql nvarchar(max)
declare @allsql nvarchar(max) = ''

declare db_cursor cursor for
select   
    'SELECT ''' + column_name + ''' AS CityName, (select count(*) from CityNumbers where ' + column_name + ' IS NULL) AS Missing, (select count(*) from CityNumbers where ' + column_name + ' IS NOT NULL) AS NonMissing UNION ' AS sql
from
    INFORMATION_SCHEMA.COLUMNS
    where TABLE_NAME = 'CityNumbers'

open db_cursor
fetch next from db_cursor into @sql

while @@FETCH_STATUS=0
begin
    set @allsql = @allsql + @sql
    fetch next from db_cursor into @sql
end
close db_cursor
deallocate db_cursor

select @allsql = left(@allsql,len(@allsql)-6)
select @allsql

exec (@allsql)

结果如下：

CityName	Missing	NonMissing
Dubai	3	2
London	2	3
NewYork	1	4
Paris	0	5
Singapore	1	4