Sql 缺少日期标识

在PostgreSQL中，anti-joins比except:demo at db<>fiddle快

with required_dept_date as (
  select distinct DepartmentID, 
         generate_series('2023.11.01'::date,'2023.11.10','1 day')::date AS date
  from Departments)
select required_dept_date.*
from required_dept_date natural left join Departments
where Departments.DepartmentID is null order by 1,2;

它需要0.14秒.您可以通过将start和stop日期设置为generate_series()来 Select 要放大的时段.当在 Select 列表中使用时，它将为所有可能的部门生成所有必需的日期，您可以将这些日期与您的表连接起来，并挑选出您的表不匹配的日期和部门(Departments is null).

同样，使用except将是few times slower:

select distinct DepartmentID, 
       generate_series('2023.11.01'::date,'2023.11.10','1 day')::date AS date
from Departments
except
select DepartmentID, date
from Departments;

这是一个实现细节，因此它可以在future 更改--毕竟，这两种形式表达了相同的概念.反联接的替代形式包括not exists, <>all(), not in，而PostgreSQL planner从at least version 8.4开始就有这个"preference"(在当前的16.1中仍然有).其他RDBMS不一定会有这种偏见.

要获取所有日期而不是特定期间，可以分别基于min()和max()生成它们，或者直接在需要这些值的地方插入不相关的scalar subqueries:

with required_dept_date as (
  select distinct DepartmentID, 
         generate_series((select min(date) from Departments),
                         (select max(date) from Departments),
                         '1 day')::date AS date
  from Departments)
select required_dept_date.*
from required_dept_date left join Departments using (DepartmentID,date)
where Departments.DepartmentID is null;

这些值非常灵活和直观，但最好确保不会查询太多、太多次，因此try 在一次扫描中获得这些值可能会更好.

如果您想忽略无人报告的日期(在您的小提琴中为11月3日和7日):不是生成完整的日历，而是提取您拥有的所有不同的日期，并将它们与所有不同的部门合并:

with required_dept as (
  select distinct DepartmentID from Departments)
,required_dept_date as (
  select distinct date,rd.DepartmentID from Departments, required_dept AS rd)
select required_dept_date.*
from Departments natural right join required_dept_date
where Departments is null;

注意a natural join b和a join b using (c1,c2)--它们是a join b on a.c1=b.c1 and a.c2=b.c2的替代形式.Natural自动检测所有匹配的列名并使用它们连接，而using匹配列表中圆括号中的列.务必确保为该目的(CTE、子查询、别名、模式设置)准备和命名列-否则，您可能会意外地联接到您不打算使用的列，但恰好有匹配的名称.

在任何情况下，您都可能希望 for each 日期获取所有缺失部门中的array个:

with required_dept_date as (
  select distinct DepartmentID, 
         generate_series((select min(date) from Departments),
                         (select max(date) from Departments),
                         '1 day')::date AS date
  from Departments)
select required_dept_date.date,
       array_agg(required_dept_date.departmentid 
                 order by required_dept_date.departmentid) AS missing
from required_dept_date left join Departments using (DepartmentID,date)
where Departments.DepartmentID is null
group by 1 order by 1;