PostgreSQL缺失日期识别：查询缺失日期及关联部门ID

我是SQL初学者，创建了一张存在日期缺失记录的Departments表，希望输出显示缺失日期及受影响的关联部门ID，期望输出格式如下：

Date        DepartmentID
2023-11-03  3001
2023-11-03  4001
2023-11-06  1001
2023-11-06  2001
2023-11-07  1001
2023-11-07  2001
2023-11-07  4001
2023-11-09  4001

我的表结构及数据插入语句如下：

Create table Departments (date date,DepartmentID int, Name text);

insert into Departments values
(to_date('1.11.23','DD.MM,YY'),1001,'SRO'),
(to_date('1.11.23','DD.MM,YY'),2001,'Drs'),
(to_date('1.11.23','DD.MM,YY'),3001,'Accounting'),
(to_date('1.11.23','DD.MM,YY'),4001,'ds'),
(to_date('2.11.23','DD.MM,YY'),1001,'SRO'),
(to_date('2.11.23','DD.MM,YY'),2001,'Drs'),
(to_date('2.11.23','DD.MM,YY'),3001,'Accounting'),
(to_date('2.11.23','DD.MM,YY'),4001,'ds'),
(to_date('3.11.23','DD.MM,YY'),1001,'SRO'),
(to_date('3.11.23','DD.MM,YY'),2001,'Drs'),
(to_date('4.11.23','DD.MM,YY'),1001,'SRO'),
(to_date('4.11.23','DD.MM,YY'),2001,'Drs'),
(to_date('4.11.23','DD.MM,YY'),3001,'Accounting'),
(to_date('4.11.23','DD.MM,YY'),4001,'ds'),
(to_date('5.11.23','DD.MM,YY'),1001,'SRO'),
(to_date('5.11.23','DD.MM,YY'),2001,'Drs'),
(to_date('5.11.23','DD.MM,YY'),3001,'Accounting'),
(to_date('5.11.23','DD.MM,YY'),4001,'ds'),
(to_date('6.11.23','DD.MM,YY'),3001,'Accounting'),
(to_date('6.11.23','DD.MM,YY'),4001,'ds'),
(to_date('7.11.23','DD.MM,YY'),3001,'Accounting'),
(to_date('8.11.23','DD.MM,YY'),1001,'SRO'),
(to_date('8.11.23','DD.MM,YY'),2001,'Drs'),
(to_date('8.11.23','DD.MM,YY'),3001,'Accounting'),
(to_date('8.11.23','DD.MM,YY'),4001,'ds'),
(to_date('9.11.23','DD.MM,YY'),1001,'SRO'),
(to_date('9.11.23','DD.MM,YY'),2001,'Drs'),
(to_date('9.11.23','DD.MM,YY'),3001,'Accounting');

我参考相关教程写了如下SQL语句：

with lastdate as
(select max(date) as Maxdate from Departments)

  select date, 
  --lead(date),
  select maxdate from lastdate OVER(partition by date ORDER BY date) as Next_Date 
  from Departments

但执行报错，错误信息如下：

ERROR: syntax error at or near "select" LINE 6: select maxdate from lastdate OVER(partition by date ORDER ... ^

这是在PostgreSQL环境中，我有两个困惑：

1. 如何同时运行CTE并对其使用OVER语句；
2. 解决该缺失日期识别问题的最佳方法。

1. CTE与OVER子句的正确用法 #

你的报错源于语法错误：不能在SELECT列表里嵌套select ... from lastdate后直接跟OVER子句。CTE是临时结果集，可直接在主查询中引用；OVER子句是窗口函数的组成部分，二者无需嵌套使用。

举两个正确用法示例：

• 引用CTE的标量值：

with lastdate as (select max(date) as Maxdate from Departments)
select d.date, ld.Maxdate
from Departments d, lastdate ld;

• 结合窗口函数查询每个部门的后续日期：

select date, DepartmentID,
       lead(date) over (partition by DepartmentID order by date) as next_date
from Departments;

CTE和窗口函数可以共存，但需遵循语法规则——你可以在主查询中对CTE的结果使用窗口函数，或同时使用CTE与窗口函数处理原表数据。

2. 识别缺失日期及对应部门的最佳方法 #

核心思路是：生成所有应存在的(date, DepartmentID)组合，再与原表比对，筛选出不存在的组合即为缺失记录。完整SQL如下：

-- 1. 提取所有唯一部门ID
with all_departments as (
    select distinct DepartmentID from Departments
),
-- 2. 生成从最小到最大日期的完整序列
date_range as (
    select generate_series(
        (select min(date) from Departments),
        (select max(date) from Departments),
        interval '1 day'
    )::date as date
),
-- 3. 生成所有预期的日期-部门组合
expected_combinations as (
    select dr.date, ad.DepartmentID
    from date_range dr
    cross join all_departments ad
)
-- 4. 筛选出原表中不存在的组合，即为缺失记录
select ec.date, ec.DepartmentID
from expected_combinations ec
left join Departments d 
    on ec.date = d.date and ec.DepartmentID = d.DepartmentID
where d.date is null
order by ec.date, ec.DepartmentID;

步骤解释： #

• all_departments：提取所有存在的部门ID，确保覆盖全部部门。
• date_range：用PostgreSQL的generate_series函数生成连续日期序列，填补日期缺口。
• expected_combinations：通过交叉连接将每个日期与每个部门组合，得到所有应存在的记录。
• 最后通过左连接原表，筛选出原表中无匹配的组合，按日期和部门ID排序后即可得到目标结果。

执行该SQL后，输出将与你期望的格式完全一致。

内容的提问来源于stack exchange，提问作者Dan