CODEWITHSUNDEEP
sqlsql-servert-sqlwindow-functions
Bob Australia
Bob Australia

Reputation: 33

T-SQL windowing functions - counting the gaps

I want to count the gaps (columns filled with NULL) in a window v, but I don't know how.

Screenshot

IF OBJECT_ID('tempdb..#X') IS NOT NULL DROP TABLE #X;

CREATE TABLE #X
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    v INT
);

INSERT INTO #X
    SELECT 121 UNION ALL SELECT NULL UNION ALL SELECT NULL 
    UNION ALL SELECT 312 UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL
    UNION ALL SELECT 123 UNION ALL SELECT NULL UNION ALL SELECT NULL 
    UNION ALL SELECT 415 UNION ALL SELECT 416 UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL
    UNION ALL SELECT 200;


SELECT 
    ID, v, s, n, m, x, c
FROM
    (SELECT 
         ID, v, 
         MAX(v) OVER (PARTITION BY c) s,
         ROW_NUMBER() OVER (PARTITION BY c ORDER BY ID DESC) n,
         ROW_NUMBER() OVER (PARTITION BY c ORDER BY ID) - 1 m,
         COUNT(CASE WHEN v IS NULL THEN 1 END) OVER (PARTITION BY c) x,
         c
     FROM
         (SELECT 
              ID, v,
              -- c =  COUNT(CASE WHEN v IS NULL THEN 1 END) OVER (ORDER BY ID)
              c = COUNT(v) OVER (ORDER BY ID)
          FROM 
              #X) a
    ) a
ORDER BY 
    ID;

Calculation c is close, but it doesn't work on two filled columns in a row.

Could anyone give a hint please?

Thank you guys, this works for me now.

Screenshot solved solution

Upvotes: 3

Views: 122

Answers (3)

David דודו Markovitz
David דודו Markovitz

Reputation: 44941

select *, case when v is null then count(null_grp_start) over (order by id) end null_grp_id
from   (select *,case when v is null and lag(v,1,1) over (order by id) is not null then 1 end as null_grp_start
        from   t
        ) t

-

+----+--------+----------+-------------+
| ID |   v    | null_grp | null_grp_id |
+----+--------+----------+-------------+
|  1 | 121    | (null)   | (null)      |
|  2 | (null) | 1        | 1           |
|  3 | (null) | (null)   | 1           |
|  4 | 312    | (null)   | (null)      |
|  5 | (null) | 1        | 2           |
|  6 | (null) | (null)   | 2           |
|  7 | (null) | (null)   | 2           |
|  8 | 123    | (null)   | (null)      |
|  9 | (null) | 1        | 3           |
| 10 | (null) | (null)   | 3           |
| 11 | 415    | (null)   | (null)      |
| 12 | 416    | (null)   | (null)      |
| 13 | (null) | 1        | 4           |
| 14 | (null) | (null)   | 4           |
| 15 | (null) | (null)   | 4           |
| 16 | 200    | (null)   | (null)      |
+----+--------+----------+-------------+

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269743

If you want to enumerate the periods of NULL-ness, you can use a cumulative count the get the non-null values and then enumerate them:

select t.*,
       (case when v is null then dense_rank() over (partition by v order by null_grp)
        end) as newcolumn
from (select t.*,
             count(v) over (order by id) as null_grp
      from t
     ) t;

Here is a db<>fiddle.

Upvotes: 2

lptr
lptr

Reputation: 6788

declare @t table
(
    id int identity primary key clustered,
    val int
);


insert into @t(val)
values (null), (2), (3), (null), (5), (null), (null), (null), (9), (10), (null), (null), (null), (null), (15);


select *, case when first_value(val) over(order by id) is null then 1 else 0 end + case when val is null then sum(addme) over(order by id) end as null_group_ordinal
from
(
select *, case when lag(val) over(order by id) is null and val is not null then 1 else 0 end as addme
from @t
) as src;

Upvotes: 1

Related Questions