Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2012
    Posts
    2

    Unanswered: Counting consequtive NULL values

    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).

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by kirylm View Post
    (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
    ;
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by tonkuma View Post
    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;
    Last edited by shammat; 06-28-12 at 06:03.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  6. #6
    Join Date
    Feb 2012
    Posts
    2
    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.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 06-29-12 at 12:34.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •