Oracle:选择缺少的日期 [英] Oracle: select missing dates

问题描述

我在一个字段中有一个表(其中包括日期).

I have a table with (among other things) dates in a field.

我需要获取所有列表的列表，这些列表比最早的日期更新，比最近的日期更早并且在表格中完全丢失.

I need to get a list of all dates that are more recent than the oldest date, older than the most recent date, and are completely missing from the table.

因此，如果表包含:

2012-01-02
2012-01-02
2012-01-03
2012-01-05
2012-01-05
2012-01-07
2012-01-08

我想返回一个查询:

2012-01-04
2012-01-06

推荐答案

这样的事情(假设您的表名为your_table，日期列名为the_date):

Something like this (assuming your table is named your_table and the date column is named the_date):

with date_range as (
      select min(the_date) as oldest, 
             max(the_date) as recent, 
             max(the_date) - min(the_date) as total_days
      from your_table
),
all_dates as (
   select oldest + level - 1 as a_date
   from date_range
   connect by level <= (select total_days from date_range)
)
select ad.a_date
from all_dates ad
  left join your_table yt on ad.a_date = yt.the_date
where yt.the_date is null
order by ad.a_date;  

WITH子句称为公用表表达式"，等效于派生表(内联视图").

the WITH clause is called a "common table expression" and is equivalent to a derived table ("inline view").

类似于

select * 
from ( 
     ..... 
) all_dates
join your_table ...

第二个CTE使用Oracle connect by实现的未记录功能只是简单地即时"创建日期列表.

The second CTE simply creates a list of dates "on-the-fly" using a undocumented feature of Oracle's connect by implementation.

与使用派生表相比，重新使用选择(就像我计算第一个和最后一个日期所做的那样)要容易一些(恕我直言，更具可读性).

Re-using a select (like I did with calculating the first and last date) is a bit easier (and IMHO more readable) than using derived tables.

这也可以通过递归CTE来完成:

This can be done with a recursive CTE as well:

with date_range as (
      select min(the_date) as oldest, 
             max(the_date) as recent, 
             max(the_date) - min(the_date) as total_days
      from your_table
),
all_dates (a_date, lvl) as (
   select oldest as a_date, 1 as lvl
   from date_range 
   union all
   select (select oldest from date_range) + lvl, lvl + 1
   from all_dates 
   where lvl < (select total_days from date_range)
)
select ad.a_date, lvl
from all_dates ad    
  left join your_table yt on ad.a_date = yt.the_date
where yt.the_date is null
order by ad.a_date;  

在所有支持递归CTE的DBMS中都应使用(PostgreSQL和Firebird-更加符合标准-不过确实需要recursive关键字).

Which should work in all DBMS supporting recursive CTEs (PostgreSQL and Firebird - being more standard compliant - do need the recursive keyword though).

请注意递归部分中的破解select (select oldest from date_range) + lvl, lvl + 1.这不是必须的，但是在递归CTE中，Oracle在DATE方面仍然存在一些错误.在PostgreSQL中，以下工作没有问题:

Note the hack select (select oldest from date_range) + lvl, lvl + 1 in the recursive part. This should not be necessary, but Oracle still has some bugs with regards to DATEs in a recursive CTE. In PostgreSQL the following works without problems:

....
all_dates (a_date, lvl) as (
   select oldest as a_date, 0 as lvl
   from date_range 
   union all
   select a_date + 1, lvl + 1
   from all_dates 
   where lvl < (select total_days from date_range)
)
....

这篇关于Oracle:选择缺少的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！