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屋!