在SQL中计算一行中的空列数

Question

我想知道在SQL中如何统计某行中为空的列数，我有一个表Customer，其中包含可空值，我希望查询返回某一行（某个客户）中为空的列数的整数。

- Islam

---

这不是你在SQL中想要做的事情。 - zerkms

你的意思是说，针对那种情况的任何解决方案都是丑陋的解决方案？ - Islam

@SpectralGhost 问题是我想为客户构建一个进度条来填写他的信息，因此我需要获取总列数和空列数，以便计算填充数据的百分比。 - Islam

啊，一个有趣且合乎逻辑的原因想要这样做。+1 - UnhandledExcepSean

@zerkms - 为什么不呢？你想知道自己对那个 SQL 行的了解程度如何。 - andowero

Answer 1

这个方法会针对空列分配一个1或0，并将它们全部加在一起。希望你这里没有太多可空列需要添加...

SELECT 
  ((CASE WHEN col1 IS NULL THEN 1 ELSE 0 END)
  + (CASE WHEN col2 IS NULL THEN 1 ELSE 0 END)
  + (CASE WHEN col3 IS NULL THEN 1 ELSE 0 END)
  ...
  ...
  + (CASE WHEN col10 IS NULL THEN 1 ELSE 0 END)) AS sum_of_nulls
FROM table
WHERE Customer=some_cust_id

请注意，如果您的 RDBMS 支持它，您也可以使用 IF() 进行更加语法清晰的操作。

SELECT 
  (IF(col1 IS NULL, 1, 0)
  + IF(col2 IS NULL, 1, 0)
  + IF(col3 IS NULL, 1, 0)
  ...
  ...
  + IF(col10 IS NULL, 1, 0)) AS sum_of_nulls
FROM table
WHERE Customer=some_cust_id

我在一个表格中测试了这个模式，看起来它能够正常工作。

- Michael Berkowski

---

IF()看起来语法更整洁，但CASE更具普遍性支持。不过，继续这条线，从最通用的解决方案到最干净的解决方案，我建议使用以下语句：SELECT ((col1 IS NULL) + (col2 IS NULL) + ...) AS sum_of_nulls FROM ...。 - Andriy M

@ImanMarashi 我不确定你的问题是什么。此查询的结果是一个单整数列，表示每行中 NULL 列的计数。 - Michael Berkowski

Answer 2

我的回答基于Michael Berkowski的回答，但为了避免输入数百个列名，我做了以下操作：

步骤1：获取表中所有列的列表

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTable';

步骤二：将列表粘贴到Notepad++（任何支持正则表达式替换的编辑器都可以）。然后使用此替换模式。

• Search:

  ^(.*)$
  

• Replace:

  \(CASE WHEN \1 IS NULL THEN 1 ELSE 0 END\) +
  

步骤三：在最前面加上SELECT identityColumnName，并将最后一个+改为AS NullCount FROM myTable，可选地添加一个ORDER BY...

SELECT 
    identityColumnName, 
    (CASE WHEN column001 IS NULL THEN 1 ELSE 0 END) +
    -- ...
    (CASE WHEN column200 IS NULL THEN 1 ELSE 0 END) AS NullCount
FROM
    myTable
ORDER BY 
    NullCount DESC

- Drew Chapin

---

你也可以利用sys.columns或类似的东西来连接所有这些，而不需要像记事本那样的工具。 - mateoc15

Answer 3

您没有说明使用的关系型数据库。对于 SQL Server 2008...

SELECT CustomerId,
       (SELECT COUNT(*) - COUNT(C)
        FROM   (VALUES(CAST(Col1 AS SQL_VARIANT)),
                      (Col2),
                      /*....*/
                      (Col9),
                      (Col10)) T(C)) AS NumberOfNulls
FROM   Customer  

- Martin Smith

Answer 4

仅适用于ORACLE-DBMS。

您可以使用NVL2函数：

NVL2( string1, value_if_not_null, value_if_null )

这里有一个选择器，其方法与Michael Berkowski建议的类似：

SELECT (NVL2(col1, 0, 1) 
        + NVL2(col2, 0, 1)
        + NVL2(col3, 0, 1)
        ...
        ...
        + NVL2(col10, 0, 1)
        ) AS sum_of_nulls
FROM table
WHERE Customer=some_cust_id

---

一个更通用的方法是编写一个PL/SQL块并使用动态SQL。您需要为特定表中的每个列构建一个SELECT字符串，使用上面的NVL2方法来处理all_tab_columns中的每一列。

- Stephan Schielke

Answer 5

类似的，但是动态的：

drop table if exists myschema.table_with_nulls;

create table myschema.table_with_nulls as

select 
   n1::integer,
   n2::integer,
   n3::integer,
   n4::integer,
   c1::character varying,
   c2::character varying,
   c3::character varying,
   c4::character varying
from 
   (
      values 
         (1,2,3,4,'a','b','c','d'),
         (1,2,3,null,'a','b','c',null),
         (1,2,null,null,'a','b',null,null),
         (1,null,null,null,'a',null,null,null)
   ) as test_records(n1, n2, n3, n4, c1, c2, c3, c4);


drop function if exists myschema.count_nulls(varchar,varchar);

create function myschema.count_nulls(schemaname varchar, tablename varchar) returns void as 
$BODY$
   declare
      calc varchar;
      sqlstring varchar;
begin
   select
      array_to_string(array_agg('(' || trim(column_name) || ' is null)::integer'),' + ') 
   into
      calc
   from
      information_schema.columns
   where 
      table_schema in ('myschema') 
         and table_name in ('table_with_nulls'); 

   sqlstring = 'create temp view count_nulls as select *, ' || calc || '::integer as count_nulls from myschema.table_with_nulls';

   execute sqlstring;

   return;
end;
$BODY$ LANGUAGE plpgsql STRICT;

select * from myschema.count_nulls('myschema'::varchar,'table_with_nulls'::varchar);


select
   *
from 
   count_nulls;

虽然我发现我没有完成函数的参数化。

- Mark Andrew

Answer 6

使用ORACLE：

Number_of_columns - json_value( json_array( comma separated list of columns ), '$.size()' ) from your_table  

json_array将构建一个仅包含非空列的数组，而json_query表达式将为您提供数组的大小

- p3consulting

Answer 7

根据您想要做什么，如果您忽略了专家的建议，并且使用SQL Server 2012，您可以用另一种方式来完成它。

必须知道候选列（“插槽”）的总数。 1. 逐列选择所有已知的“插槽”列（它们是已知的）。 2. 对结果进行解构以获得每个原始列的一行表格。这有效，因为空列不会解构，而且您知道所有列名。 3. 计算(*)结果以获取非空值的数量；从中减去即可得到答案。

例如，对于汽车中的4个“座位”：

select 'empty seats' = 4 - count(*)
from 
(
    select carId, seat1,seat2,seat3,seat4 from cars where carId = @carId
) carSpec
unpivot (FieldValue FOR seat in ([seat1],[seat2],[seat3],[seat4])) AS results

如果您需要执行更多操作而不仅仅是计算非空列的数量，那么这将非常有用，因为它还提供了一种将列作为集合进行操作的方式。

- philw

Answer 8

这将给出非空列的数量。您可以适当地应用此方法

SELECT   ISNULL(COUNT(col1),'') + ISNULL(COUNT(col2),'') +ISNULL(COUNT(col3),'') 
   FROM TABLENAME
   WHERE  ID=1

- user2893188

---

严格来说，ISNULL() 不是必需的，因为 COUNT(*) 返回一个值。请注意，ISNULL() 并不在每个系统上都存在，而且 OP 从未指定他使用的 RDBMS，这意味着答案可能对他也不起作用。最后……如果 COUNT() 某种方式返回了空值，你返回一个空白将导致它失败；要么你得到一个字符字符串而不是总数，要么数据库会抱怨类型不匹配。鉴于问题已经接近两年并有一个被接受的答案，-1（基本思路可行）。 - Clockwork-Muse

Answer 9

不幸的是，在标准的SQL语句中，您必须输入要测试的每个列，如果要全部以编程方式进行测试，则可以使用T-SQL。但请注意，确保您正在处理真正的NULLS，因为您可能会有存储的空值，数据库将无法识别其为真正的NULL（我知道这听起来很奇怪）。

您可以通过像这样捕获空值和NULLS的语句来避免此问题：

CASE WHEN col1 & '' = '' THEN 1 ELSE 0 END

或者在一些数据库中，例如Oracle（不确定是否有其他数据库），您可以使用以下语句：

CASE WHEN col1 || '' = '' THEN 1 ELSE 0 END

- Matt Donnan

Answer 10

我还没有测试过，但我会尝试使用PL\SQL函数来实现它

CREATE OR REPLACE TYPE ANYARRAY AS TABLE OF ANYDATA
;

CREATE OR REPLACE Function COUNT_NULL
( ARR IN ANYARRAY )
RETURN number
IS
   cnumber number ;
BEGIN

   for i in 1 .. ARR.count loop
     if ARR(i).column_value is null then
       cnumber := cnumber + 1;
     end if;
   end loop;

RETURN cnumber;

EXCEPTION
WHEN OTHERS THEN
   raise_application_error
   (-20001,'An error was encountered - '
   ||SQLCODE||' -ERROR- '||SQLERRM);
END
;

然后像这样在选择查询中使用它

CREATE TABLE TEST (A NUMBER, B NUMBER, C NUMBER);

INSERT INTO TEST (NULL,NULL,NULL);
INSERT INTO TEST (1   ,NULL,NULL);
INSERT INTO TEST (1   ,2   ,NULL);
INSERT INTO TEST (1   ,2   ,3   );

SELECT ROWNUM,COUNT_NULL(A,B,C) AS NULL_COUNT FROM TEST;

期望输出

ROWNUM | NULL_COUNT
-------+-----------
     1 | 3
     2 | 2
     3 | 1
     4 | 0

- Khaled.K