对单行中非空列进行计数和取平均值

Question

我想计算并取每行中非空列的平均值例如，我有一张这样的表

Name | a  | b  | c  | d  | e   | f |
     |    |    |    |    |     |   |
umar | 2  |null| 3  | 5  | null| 4 |
ali  |null|null| 3  |null| 1   | 4 |
ali  |null|null| 3  |null| null| 4 |

结果应该是

    Name | a  | b  | c  | d  | e   | f | average
         |    |    |    |    |     |   |
    umar | 2  |null| 3  | 5  | null| 4 |  3.5
    ali  |null|null| 3  |null| 1   | 4 |  2.66
    ali  |null|null| 3  |null| null| 4 | 3.5

Answer 1

var query = from x in table
            select new 
            {
                CountNotNull = (x.a ?? 0) + (x.b ?? 0) + (x.c ?? 0) + (x.d ?? 0) + (x.e ?? 0) + (x.f ?? 0) 
            };

如果您不想计算它但计算平均值（您已更改问题）：

var query = 
     from x in table
     let NotNullColCount = (x.a == null ? 0 : 1) + (x.b == null ? 0 : 1) + (x.c == null ? 0 : 1) + (x.d == null ? 0 : 1) + (x.e == null ? 0 : 1) + (x.f == null ? 0 : 1)
     let NotNullColSum = (x.a ?? 0) + (x.b ?? 0) + (x.c ?? 0) + (x.d ?? 0) + (x.e ?? 0) + (x.f ?? 0)
     select new 
     {
        AverageNotNull = NotNullColCount == 0 
                         ? 0.0 
                         : 1.0 * NotNullColSum / NotNullColCount 
     };

Answer 2

您可以使用case表达式：

select ((case when a is not null then 1 else 0 end) +
        (case when b is not null then 1 else 0 end) +
        (case when c is not null then 1 else 0 end) +
        (case when d is not null then 1 else 0 end) +
        (case when e is not null then 1 else 0 end) +
        (case when f is not null then 1 else 0 end)
       ) as NumNotNull

Answer 3

您可以使用

CASE EXPRESSION：

SELECT t.name,
       (CASE WHEN t.a is not null then 1 else 0 END +
       CASE WHEN t.b is not null then 1 else 0 END +
       CASE WHEN t.c is not null then 1 else 0 END +
       CASE WHEN t.d is not null then 1 else 0 END +
       CASE WHEN t.e is not null then 1 else 0 END +
       CASE WHEN t.f is not null then 1 else 0 END) as not_null_count
FROM YourTable t

Answer 4

你也可以使用动态查询，如果你的列数很大

声明@SQL NVARCHAR（最大）;
SET @SQL = '选择表名.列名 ， ' + 东西（（选择 '+ 案例当 ' + 引号（COLUMN_NAME） + ' 为空，则 1 个其他 0 结束 从INFORMATION_SCHEMA。列 其中 TABLE_NAME = XML 路径 （''）） 的 '表名' ， 1 ， 1 ， ''） + "来自表名";
执行（@SQL）;

Answer 5

下面的SQL可能会帮助你：

SELECT  Name
      , a
      , b
      , c
      , d
      , e
      , f
      , CONVERT(FLOAT, ( tab.a1 + tab.b1 + tab.c1 + tab.d1 + tab.e1 + tab.f1 )) / CONVERT(FLOAT, ( tab.a2 + tab.b2
                                                                                                   + tab.c2 + tab.d2
                                                                                                   + tab.e2 + tab.f2 )) AS [average]
FROM    ( SELECT    Name
                  , a
                  , b
                  , c
                  , d
                  , e
                  , f
                  , CASE WHEN a IS NULL THEN 0
                         ELSE a
                    END AS a1
                  , CASE WHEN b IS NULL THEN 0
                         ELSE b
                    END AS b1
                  , CASE WHEN c IS NULL THEN 0
                         ELSE c
                    END AS c1
                  , CASE WHEN d IS NULL THEN 0
                         ELSE d
                    END AS d1
                  , CASE WHEN e IS NULL THEN 0
                         ELSE e
                    END AS e1
                  , CASE WHEN f IS NULL THEN 0
                         ELSE f
                    END AS f1
                  , CASE WHEN a IS NULL THEN 0
                         ELSE 1
                    END AS a2
                  , CASE WHEN b IS NULL THEN 0
                         ELSE 1
                    END AS b2
                  , CASE WHEN c IS NULL THEN 0
                         ELSE 1
                    END AS c2
                  , CASE WHEN d IS NULL THEN 0
                         ELSE 1
                    END AS d2
                  , CASE WHEN e IS NULL THEN 0
                         ELSE 1
                    END AS e2
                  , CASE WHEN f IS NULL THEN 0
                         ELSE 1
                    END AS f2
          FROM      YourTable
        ) tab