sql - 查找 SQL 中非空行的百分比

Question

哦SQL，你为什么让我沮丧......

大家下午好，我又被困住了......

我有一个数据库，其中保存了表单的信息(废话)，该表单可以“保存”，以便用户稍后可以返回并处理它。我需要的是找到非空字段的百分比。例如；

COL_A |COL_B |COL_C |ETC.. z  %  
-------------------------- z------
  X   |   X  | NULL |      z  66 
  X   |   X  |   X  |      z  100
 NULL | NULL |   X  |      z  33
 NULL | NULL | NULL |      z  0

锦上添花？每行有 47 列需要检查。我可能会以错误的方式处理这个问题。任何帮助/提示将不胜感激。

Answer 1

您可以将CROSS APPLY与VALUES结合使用:

SELECT t.*, FLOOR(100 * (1.0 * sub.col)/sub.counter) AS [percentage]
FROM #tab t
CROSS APPLY (
   SELECT COUNT(c), COUNT(*) 
   FROM (VALUES(t.col1), (t.col2), (t.col3), (t.col4), (t.col5),
               (t.col6), (t.col7), (t.col8), (t.col9), (t.col10)) AS s(c)
 ) AS sub(col, counter)

您需要在 VALUES 子句中添加最多 47 列。

LiveDemo

编辑:

当列具有相似的数据类型或存在隐式转换时，上述解决方案有效。如果存在没有隐式转换的数据类型，它将失败(例如 UNIQUEIDENTIFIER 和 DATETIME。一种快速解决方法是用 CAST(t.colx AS NVARCHAR(100)):

SELECT t.*, FLOOR(100 * (1.0 * sub.col)/sub.counter) AS [percentage]
FROM #tab t
CROSS APPLY (SELECT COUNT(c), COUNT(*) 
             FROM (VALUES 
             (CAST(t.col1 AS NVARCHAR(MAX))), 
             (CAST(t.col2 AS NVARCHAR(MAX))), 
             (CAST(t.col3 AS NVARCHAR(MAX))), 
             (CAST(t.col4 AS NVARCHAR(MAX))), 
             (CAST(t.col5 AS NVARCHAR(MAX))), 
             (CAST(t.col6 AS NVARCHAR(MAX))), 
             (CAST(t.col7 AS NVARCHAR(MAX))), 
             (CAST(t.col8 AS NVARCHAR(MAX))), 
             (CAST(t.col9 AS NVARCHAR(MAX))), 
             (CAST(t.col10 AS NVARCHAR(MAX)))
             ) AS s(c)) AS sub(col, counter)

LiveDemo2

或者可以使用IIF/CASE(不再担心数据类型):

SELECT t.*, FLOOR(100 * (1.0 * sub.col)/sub.counter) AS [percentage]
FROM #tab t
CROSS APPLY (SELECT COUNT(c), COUNT(*) 
             FROM (VALUES 
             (IIF(t.col1 IS NULL, NULL, 1)), 
             (IIF(t.col2 IS NULL, NULL, 1)),
             (IIF(t.col3 IS NULL, NULL, 1)), 
             (IIF(t.col4 IS NULL, NULL, 1)),
             (IIF(t.col5 IS NULL, NULL, 1)), 
             (IIF(t.col6 IS NULL, NULL, 1)),
             (IIF(t.col7 IS NULL, NULL, 1)), 
             (IIF(t.col8 IS NULL, NULL, 1)),
             (IIF(t.col9 IS NULL, NULL, 1)), 
             (IIF(t.col10 IS NULL, NULL, 1))
             ) AS s(c)) AS sub(col, counter);

LiveDemo3