SQL Server Null Rate of all columns

tyu7yeag 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(84)

I have a table (People) with these columns

Gender
Name
Address
Employment
Education

I want a query that will output the percent null rate for all columns while grouping by [Gender] column.

The table I am using has 60 columns so I do not want to hard code the columns. I am using the above just as an example.

I am expecting output similar to this. The figures are percent null rate
| Gender | Name | Address | Employment | Education |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| Male | 0.2365 | 0.5642 | 0.3652 | 0.1563 |
| Female | 0.5263 | 0.3265 | 0.5214 | 0.1563 |

sql-server

来源：https://stackoverflow.com/questions/77138635/null-rate-of-all-columns

1条答案

按热度按时间

vktxenjb1#

Can use the INFORMATION_SCHEMA.COLUMNS table to get the columns, then from there it is just a matter of constructing your dynamic query.

DECLARE @TableName VARCHAR(100) = 'People'
DECLARE @GroupColumn VARCHAR(100) = 'Gender'

DECLARE @DynamicSelect VARCHAR(MAX)
SELECT @DynamicSelect =
    'SELECT ' + @GroupColumn + ', ' + 
    STRING_AGG(CONVERT(VARCHAR(MAX), 'AVG(CASE WHEN [' + COLUMN_NAME +'] IS NULL THEN 1.0 ELSE 0.0 END) AS ' + COLUMN_NAME), ', ') + 
    ' FROM ' + @TableName + ' GROUP BY ' + @GroupColumn
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName AND COLUMN_NAME <> @GroupColumn

EXEC(@DynamicSelect)

This constructs and executes a query similar to this:

SELECT
    Gender,
    AVG(CASE WHEN [Name] IS NULL THEN 1.0 ELSE 0.0 END) AS Name,
    AVG(CASE WHEN [Address] IS NULL THEN 1.0 ELSE 0.0 END) AS Address,
    -- etc
FROM People
GROUP BY Gender

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Null Rate of all columns

1条答案

相关问题

热门标签

最新问答