Counting consequtive NULL values

kirylm · #1 (**permalink**) 06-27-12, 20:55

I came across an interesting problem today. I was not able to find a simple SQL-only solution. So if you guys have that in mind, I would love to see it.

Input data:

Code:

COL1   COL2
abc    NULL
bcd    3.9
cde    1
def    2
efg    3.9
fgh    5
ghi    4.8
hij    NULL
ijk    NULL

I want to count consecutive NULL values, so I want my output to be:

Code:

COL1   COL2   COL3
abc    NULL      1
bcd    3.9
cde    1
def    2
efg    3.9
fgh    5
ghi    4.8
hij    NULL      1
ijk    NULL      2

In my mind, the problem was finding proper partition.

I solved it using a table function, where I basically had logic in a loop.

Just curious if there is a simple SQL solution to that (can be Oracle specific).

shammat · #2 (**permalink**) 06-28-12, 01:34

Quote:

Originally Posted by kirylm

(can be Oracle specific).

So why do you ask this question in the PostgreSQL forum?

The following will work for Postgres and should work for Oracle as well (actually it's standard SQL and should work on most modern DBMS):

Code:

select col1, 
       col2,
       case 
          when col2 is null then group_nr
          else null
       end
from (
    select *,
           sum(group_flag) over (order by col1) as group_nr
    from (
    select *,
           case
              when (col2 is null) or (lag(col2) over (order by col1) is null and col2 is null) then 1
              else null
            end as group_flag
    from t
    ) t1
) t2
order by col1
;

tonkuma · #3 (**permalink**) 06-28-12, 04:15

shammat,
I got different result than expected on DB2.

Note: Added t1. and t., to correspond to DB2 syntax.

Code:

select col1, 
       col2,
       case 
          when col2 is null then group_nr
          else null
       end
from (
    select t1.*,
           sum(group_flag) over (order by col1) as group_nr
    from (
    select t.*,
           case
              when (col2 is null) or (lag(col2) over (order by col1) is null and col2 is null) then 1
              else null
            end as group_flag
    from t
    ) t1
) t2
order by col1
;
------------------------------------------------------------------------------

COL1 COL2           3          
---- -------------- -----------
abc               -           1
bcd             3.9           -
cde             1.0           -
def             2.0           -
efg             3.9           -
fgh             5.0           -
ghi             4.8           -
hij               -           2
ijk               -           3

  9 record(s) selected.

tonkuma · #4 (**permalink**) 06-28-12, 04:22

This worked on DB2 9.7.5 on Windows.

Example 1: include two ways(expressions).

Code:

SELECT col1
     , col2
     , CASE
       WHEN col2 IS NULL THEN
            r_num
          - COALESCE(
               MAX( CASE
                    WHEN col2 IS NOT NULL THEN
                         r_num
                    ELSE NULL
                    END
                  ) /* MAX */
                    OVER( ORDER BY col1 )
             , 0
            ) /* COALESCE */
       END  AS col3_max
     , CASE
       WHEN col2 IS NULL THEN
            r_num
          - LAG( CASE
                 WHEN col2 IS NOT NULL THEN
                      r_num
                 ELSE NULL
                 END
               , 1 , 0
               , 'IGNORE NULLS'
               ) /* LAG */
                 OVER( ORDER BY col1 )
       END  AS col3_lag
 FROM
      (SELECT t.*
            , INT( /* INT was used to narrow width of result col3 column. */
                 ROW_NUMBER()
                    OVER( ORDER BY col1 )
              ) AS r_num
        FROM  sample_data t
      )
 ORDER BY
       col1
;
------------------------------------------------------------------------------

COL1 COL2           COL3_MAX    COL3_LAG   
---- -------------- ----------- -----------
abc               -           1           1
bcd             3.9           -           -
cde             1.0           -           -
def             2.0           -           -
efg             3.9           -           -
fgh             5.0           -           -
ghi             4.8           -           -
hij               -           1           1
ijk               -           2           2

  9 record(s) selected.

shammat · #5 (**permalink**) 06-28-12, 04:58

Quote:

Originally Posted by tonkuma

shammat,
I got different result than expected on DB2.

You are right. It doesn't do what the OP wants even on PostgreSQL. I posted to quickly.

Code:

select t1.col1, 
       t1.col2,
       case  
         when t1.group_flag is null then null 
         when lag(t1.group_flag) over (order by t1.col1) is not null then lag(t1.group_flag) over (order by t1.col1) + 1
         else 1
       end
from (
select t.*,
       case
          when (t.col2 is null) or (lag(t.col2) over (order by t.col1) is null and t.col2 is null) then 1
          else null
        end as group_flag
from t
) t1
order by col1;

kirylm · #6 (**permalink**) 06-29-12, 10:35

Thank you guys,

I posted this question on PostgreSQL forum, because I wanted the answer to work on Postgres primarily, and optionally on Oracle.

Solution by tonkuma works!

Thanks, again.

tonkuma · #7 (**permalink**) 06-29-12, 11:30

A little simplified example.

Example 2:

Code:

SELECT col1
     , col2
     , NULLIF(
          r_num
        - MAX( CASE
               WHEN col2 IS NOT NULL THEN
                    r_num
               ELSE 0
               END
             ) /* MAX */
             OVER( ORDER BY col1 )
        , 0
       ) /* NULLIF */  AS col3_max2
 FROM
      (SELECT t.*
            , INT( /* INT was used to narrow width of result col3 column. */
                 ROW_NUMBER()
                    OVER( ORDER BY col1 )
              ) AS r_num
        FROM  test_count_consecutive_null t
      )
 ORDER BY
       col1
;

Test data:

Code:

CREATE TABLE test_count_consecutive_null
( col1 CHAR(3) NOT NULL PRIMARY KEY
, col2 DECIMAL(3,1)
);

INSERT INTO test_count_consecutive_null
VALUES
  ( 'abc' ,  NULLIF(1.0 , 1.0) )
, ( 'bcd' ,  3.9               )
, ( 'cde' ,  1                 )
, ( 'def' ,  2                 )
, ( 'efg' ,  3.9               )
, ( 'fgh' ,  5                 )
, ( 'ghi' ,  4.8               )
, ( 'hij' ,  NULLIF(1.0 , 1.0) )
, ( 'ijk' ,  NULLIF(1.0 , 1.0) )
, ( 'jkl' ,  NULLIF(1.0 , 1.0) )
, ( 'klm' ,  NULLIF(1.0 , 1.0) )
, ( 'lmn' ,  5.7               )
, ( 'mno' ,  NULLIF(1.0 , 1.0) )
, ( 'nop' ,  NULLIF(1.0 , 1.0) )
, ( 'opq' ,  NULLIF(1.0 , 1.0) )
, ( 'pqr' ,  2.3               )
, ( 'qrs' ,  0.1               )
, ( 'rst' ,  NULLIF(1.0 , 1.0) )
, ( 'stu' ,  7.2               )
, ( 'tuv' ,  NULLIF(1.0 , 1.0) )
, ( 'uvw' ,  NULLIF(1.0 , 1.0) )
;

Result of Example 2 for the test data:

Code:

COL1 COL2  COL3_MAX2  
---- ----- -----------
abc      -           1
bcd    3.9           -
cde    1.0           -
def    2.0           -
efg    3.9           -
fgh    5.0           -
ghi    4.8           -
hij      -           1
ijk      -           2
jkl      -           3
klm      -           4
lmn    5.7           -
mno      -           1
nop      -           2
opq      -           3
pqr    2.3           -
qrs    0.1           -
rst      -           1
stu    7.2           -
tuv      -           1
uvw      -           2

  21 record(s) selected.

Another test: Add WHERE condition to Example 2.

Code:

...
        FROM  test_count_consecutive_null t
        WHERE col1 > 'h'      )
...

Result:

Code:

COL1 COL2  COL3_MAX2  
---- ----- -----------
hij      -           1
ijk      -           2
jkl      -           3
klm      -           4
lmn    5.7           -
mno      -           1
nop      -           2
opq      -           3
pqr    2.3           -
qrs    0.1           -
rst      -           1
stu    7.2           -
tuv      -           1
uvw      -           2

  14 record(s) selected.

Internet Services

Web Development

Digital Marketing

Consumer Tech