SQL 窗口函数按空值分区

Question

我有一个数据集:

year     id
NULL     123
NULL     124
NULL     125
1932     126
1932     127
1933     128
1933     129
1934     130

我想创建一个运行计数，其中我将具有 NULL 值的年份组作为一组，另一组具有 non-null 值，即.

year    count
NULL    3
1932    2
1933    4
1934    5

我曾尝试通过两个windows函数数据集的union来做到这一点，即:

select distinct year, 
count(id) over (order by year asc)
from data
where year is null

union

select distinct year, 
count(id) over (order by year asc)
from data
where year is not null;

我想知道是否有更清洁的方法，例如:

select distinct year, 
count(id) over (partition by <whether year is null condition> order by year 
asc)
from data;

我的sql版本是db2。

Answer 1

试试这个:

DECLARE @tab TABLE(year INT, id INT)

INSERT INTO @tab VALUES( NULL,123)
INSERT INTO @tab VALUES(NULL,124)
INSERT INTO @tab VALUES(NULL,125)
INSERT INTO @tab VALUES(1932,126)
INSERT INTO @tab VALUES(1932,127)
INSERT INTO @tab VALUES(1933,128)
INSERT INTO @tab VALUES(1933,129)
INSERT INTO @tab VALUES(1934,130)

SELECT D.year, MAX(D.RN)Count 
FROM(
    SELECT year,SUM(1) OVER(PARTITION BY CASE WHEN year IS NULL THEN 1 ELSE 0 END ORDER BY id) RN FROM @tab
    )D
GROUP BY D.year

输出:

year    Count
NULL    3
1932    2
1933    4
1934    5