sql - 在 Oracle SQL 中对一段(有时是非连续的)天数进行分组

Question

我正在寻找一个查询，它大部分时间都是连续的几天，并根据它们是否真的连续以及属于该日期的值将它们组合在一起。我正在使用 Oracle 版本 11g。

这是一些示例数据:

date          value
2012-01-01    2000
2012-01-02    2000 //(there is no data for Jan 03 for example)
2012-01-04    2000
2012-01-05    5000
2012-01-06    5000
2012-01-07    5000
2012-01-08    2000
2012-01-09    2000
2012-01-10    2000

(这是一个相当大的查询的结果)

我正在寻找的是将这些日子按这样的时期组合在一起:

from_date   to_date     value
2012-01-01  2012-01-02  2000
2012-01-04  2012-01-04  2000
2012-01-05  2012-01-07  5000
2012-01-08  2012-01-10  2000

我们确实设法制定了一个符合我要求的查询，但这不是一种非常有效的方式，而且我很确定存在更好/更优雅的东西。这是我现在使用的:

with temp_table as (
select a.pk_date DATE1, c.pk_date DATE2, a.volume VOL1
from dm_2203 a, dm_2203 c
where a.volume = c.volume
  and a.pk_date <= c.pk_date
  and not exists (select 1 from dm_2203 b
                  where a.volume = b.volume
                    and a.pk_date = b.pk_date+1)
  and not exists (select 1 from dm_2203 d
                  where c.volume = d.volume
                    and c.pk_date = d.pk_date-1)  )
select * from temp_table y
where date2-date1+1 = (select count(*)
                       from dm_2203 z
                       where z.pk_date between y.date1 and y.date2
                         and y.vol1 = z.volume)
order by 1;

有没有人知道如何在没有所有连接的情况下更快地做到这一点？谢谢!

Answer 1

我认为这应该有效并且效率也合理(它应该只上 table 一次)

create table t ( d date, v number);

insert into t values (trunc(sysdate), 100);
insert into t values (trunc(sysdate+2), 100);
insert into t values (trunc(sysdate+3), 100);
insert into t values (trunc(sysdate+4), 100);
insert into t values (trunc(sysdate+5), 200);
insert into t values (trunc(sysdate+6), 200);
insert into t values (trunc(sysdate+7), 200);
insert into t values (trunc(sysdate+8), 100);

select min(d), max(d), v
from (
    select d, v, 
        sum( gc) over (partition by v order by d) g
    from (
        select d, v, 
            (case (d - lag(d) over ( partition by v order by d) )
                when 1 then 0
                else 1
             end) gc
        from t
    )
) group by v, g
order by min(d), v

请注意，如果您想在数据的子集上以有效的方式运行逻辑，则应在最内部的选择中添加 where 子句。否则oracle 使用任何索引都会有问题。