oracle：计算一行中空字段的数量

Question

我有一个有 5 个“可选”字段的表。我想知道有多少行全部 5 个为空，有多少行有 1 个字段非空，等等。

我尝试了一些方法，例如：

select 
 count(*),
 ( (if field1 is null then 1 else 0) +
   (if field2 is null then 1 else 0) +
 etc.

但这当然行不通。

理想情况下，我正在寻找类似的输出

Nulls   Cnt
0        200
1        345
...
5        40

是否有一个优雅的解决方案？

Answer 1

关键字不是 if，而是 case，并且必须使用 end 结束 case 语句。

这是一个适合您的查询：

WITH subQuery AS
(
  SELECT My_Table.*, (CASE WHEN My_Table.field1 IS NULL THEN 1 ELSE 0 END +
                      CASE WHEN My_Table.field2 IS NULL THEN 1 ELSE 0 END +
                      CASE WHEN My_Table.field3 IS NULL THEN 1 ELSE 0 END +
                      CASE WHEN My_Table.field4 IS NULL THEN 1 ELSE 0 END +
                      CASE WHEN My_Table.field5 IS NULL THEN 1 ELSE 0 END ) NumberOfNullFields
    FROM My_Table
)
SELECT NumberOfNullFields, COUNT(*)
  FROM subQuery
 GROUP BY NumberOfNullFields;

Answer 2

虽然计数时的情况没有任何问题，但我只是想看看是否还有其他方法。

WITH SAMPLEDATA AS(--just generate some data with nulls for 5 cols
    select  level ,
            (case when mod(level,2) = 0 then 1 else null end) colA,
            (case when mod(level,3) = 0 then 1 else null end) colB,
            (case when mod(level,5) = 0 then 1 else null end) colC,
            (case when mod(level,7) = 0 then 1 else null end) colD,
            (case when mod(level,11) = 0 then 1 else null end) colE

      from dual
      connect by level < 1000
    ), --utilize the count(Aggregate)'s avoidance of nulls to our summation advantage
    nullCols as(
    SELECT COUNT(COLA) aNotNull
           ,cOUNT(*)-COUNT(COLA) aNull
           ,count(colB) bNotNull
           ,cOUNT(*)-count(colB) bNull
           ,count(colc) cNotNull
           ,cOUNT(*)-count(colc) cNull
           ,count(cold) dNotNull
           ,cOUNT(*)-count(cold) dNull
           ,count(cole) eNotNull
           ,cOUNT(*)-count(cole) eNull
           , cOUNT(*) TotalCountOfRows
     from SAMPLEDATA  )
    SELECT (select count(*) from sampledata where cola is null and colb is null and colc is null and cold is null and cole is null) allIsNull     
           ,nullCols.*
    FROM nullCols;

ALLISNULL              ANOTNULL               ANULL                  BNOTNULL               BNULL                  CNOTNULL               CNULL                  DNOTNULL               DNULL                  ENOTNULL               ENULL                  TOTALCOUNTOFROWS       
---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- 
207                    499                    500                    333                    666                    199                    800                    142                    857                    90                     909                    999                    

这利用了

如果计数中的表达式（表达式）
计算结果为 null，则不计算在内：
正如此处

所述

，这种方法，正如上面显而易见的那样，不能“雄辩地”很好地总结所有空列。只是想看看如果没有 CASE 逻辑这是否可行。