sql - 使用 GROUP BY 条件查找缺失的序列值

Question

我需要找到按年份和部门分类的序列集中缺失的数字。例如，我在表格中有以下一组信息:

╔══════╤══════╤═════╗
║ YEAR │ DEPT │ NUM ║
╠══════╪══════╪═════╣
║ 2016 │ 1    │ 1   ║
╟──────┼──────┼─────╢
║ 2016 │ 1    │ 2   ║
╟──────┼──────┼─────╢
║ 2016 │ 1    │ 4   ║
╟──────┼──────┼─────╢
║ 2016 │ 2    │ 10  ║
╟──────┼──────┼─────╢
║ 2016 │ 2    │ 12  ║
╟──────┼──────┼─────╢
║ 2016 │ 2    │ 13  ║
╟──────┼──────┼─────╢
║ 2015 │ 3    │ 6   ║
╟──────┼──────┼─────╢
║ 2015 │ 3    │ 8   ║
╟──────┼──────┼─────╢
║ 2015 │ 3    │ 9   ║
╟──────┼──────┼─────╢
║ 2015 │ 2    │ 24  ║
╟──────┼──────┼─────╢
║ 2015 │ 2    │ 26  ║
╟──────┼──────┼─────╢
║ 2015 │ 2    │ 27  ║
╚══════╧══════╧═════╝

通常我会LEFT JOIN 到TALLY 表，但我想保留YEAR 和DEPT 缺失值在。我通常会使用如下方法，但我不确定如何循环回缺失值对应的年份和部门，尤其是 MIN 和 MAX 值可以因 YEAR 和 DEPT 而异。

DECLARE @MIN INT = (SELECT MIN(NUM) FROM DOCUMENTS)
DECLARE @MAX INT = (SELECT MAX(NUM) FROM DOCUMENTS)

SELECT
    T.NUM AS 'MISSING'
FROM
    TALLY T
    LEFT JOIN DOCUMENTS D
        ON T.NUM = DOCUMENTS.NUM
WHERE
    D.NUM IS NULL
    AND D.NUM BETWEEN @MIN AND @MAX

我的预期输出如下:

╔══════╤══════╤═════════════╗
║ YEAR │ DEPT │ MISSING_NUM ║
╠══════╪══════╪═════════════╣
║ 2016 │ 1    │ 3           ║
╟──────┼──────┼─────────────╢
║ 2016 │ 2    │ 11          ║
╟──────┼──────┼─────────────╢
║ 2015 │ 3    │ 7           ║
╟──────┼──────┼─────────────╢
║ 2015 │ 2    │ 25          ║
╚══════╧══════╧═════════════╝

我想我可能需要创建一个 TALLY 表，其中包含 YEAR、DEPT 和一个 NUM 列，但那将是数十亿的值，因为我有 1800-2016 年和 15 个不同的部门，其中一些部门的 NUM 范围从 1 到 1 亿。所以我认为这不是最有效/最实用的方法。

Answer 1

如果只有一个值可能缺失，你可以这样做:

select t.year, t.dept, t.num + 1
from t
where t.num < (select max(t2.num) from t t2 where t2.year = t.year and t2.dept = t.dept) and
      not exists (select 1
                  from t t2
                  where t2.year = t.year and t2.dept = t.dept and
                        t.num + 1 = t2.num
                 );

在 SQL Server 2012+ 中，这可以简化为:

select year, dept, num + 1 as num
from (select t.*, lead(num) over (partition by year, dept order by num) as next_num
      from t
     ) t
where next_num <> num + 1;  -- Note:  this handles the final num where `next_num` is `NULL`

这种方法实际上可以推广以找到缺失的范围。假设您使用的是 SQL Server 2012+，那么:

select year, dept, num + 1 as start_missing, next_num - 1 as end_missing
from (select t.*, lead(num) over (partition by year, dept order by num) as next_num
      from t
     ) t
where next_num <> num + 1;  -- Note:  this handles the final num where `next_num` is `NULL`