我想知道在SQL中如何统计某行中为空的列数,我有一个表Customer,其中包含可空值,我希望查询返回某一行(某个客户)中为空的列数的整数。
我想知道在SQL中如何统计某行中为空的列数,我有一个表Customer,其中包含可空值,我希望查询返回某一行(某个客户)中为空的列数的整数。
这个方法会针对空列分配一个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
我在一个表格中测试了这个模式,看起来它能够正常工作。
IF()
看起来语法更整洁,但CASE
更具普遍性支持。不过,继续这条线,从最通用的解决方案到最干净的解决方案,我建议使用以下语句:SELECT ((col1 IS NULL) + (col2 IS NULL) + ...) AS sum_of_nulls FROM ...
。 - Andriy M我的回答基于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
您没有说明使用的关系型数据库。对于 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
仅适用于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
类似的,但是动态的:
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;
虽然我发现我没有完成函数的参数化。
使用ORACLE:
Number_of_columns - json_value( json_array( comma separated list of columns ), '$.size()' ) from your_table
json_array将构建一个仅包含非空列的数组,而json_query表达式将为您提供数组的大小
根据您想要做什么,如果您忽略了专家的建议,并且使用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
如果您需要执行更多操作而不仅仅是计算非空列的数量,那么这将非常有用,因为它还提供了一种将列作为集合进行操作的方式。
这将给出非空列的数量。您可以适当地应用此方法
SELECT ISNULL(COUNT(col1),'') + ISNULL(COUNT(col2),'') +ISNULL(COUNT(col3),'')
FROM TABLENAME
WHERE ID=1
ISNULL()
不是必需的,因为 COUNT(*)
返回一个值。请注意,ISNULL()
并不在每个系统上都存在,而且 OP 从未指定他使用的 RDBMS,这意味着答案可能对他也不起作用。最后……如果 COUNT()
某种方式返回了空值,你返回一个空白将导致它失败;要么你得到一个字符字符串而不是总数,要么数据库会抱怨类型不匹配。鉴于问题已经接近两年并有一个被接受的答案,-1(基本思路可行)。 - Clockwork-Muse不幸的是,在标准的SQL语句中,您必须输入要测试的每个列,如果要全部以编程方式进行测试,则可以使用T-SQL。但请注意,确保您正在处理真正的NULLS,因为您可能会有存储的空值,数据库将无法识别其为真正的NULL(我知道这听起来很奇怪)。
您可以通过像这样捕获空值和NULLS的语句来避免此问题:
CASE WHEN col1 & '' = '' THEN 1 ELSE 0 END
或者在一些数据库中,例如Oracle(不确定是否有其他数据库),您可以使用以下语句:
CASE WHEN col1 || '' = '' THEN 1 ELSE 0 END
我还没有测试过,但我会尝试使用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