代码之家 › 专栏 › 技术社区 › imi36

如何动态使用connect by方法

sql-subselect connect-by subquery oracle sql

imi36 · 技术社区 · 8 年前

如何实现以下目标

我有一个问题:

select sr.source_rel_id,
       sr.source_rel_start,
       sr.source_rel_end,
       sr.source_rel_end-sr.source_rel_start+ 1 as daycount
 from (SELECT RELEASES.REL_ID as source_rel_id,
              RELEASES.REL_START_DATE as source_rel_start,
              RELEASES.REL_END_DATE as source_rel_end
         FROM RELEASES) sr

它提供了这个结果:

我试图创建一个额外的列(基于所附的屏幕截图),其中填充了给定开始和结束时间间隔之间递增的上升日期。

以下是我想要实现的目标:

右侧的数字显示了应为每个ID生成多少条记录。

对不起,我的英语很差,我希望这是可以理解的。

2 回复 | 直到 8 年前

krokodilko 8 年前

这基本上是一个生成行的连接查询,例如,要生成从1到10的行,请使用:

select level as x
from dual connect by level <= 10;

|  X |
|----|
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |

为了生成从5到10的行,请使用:

select 5 + level - 1 as x
from dual connect by level <= 10 - 5 + 1;

|  X |
|----|
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |

为了从中生成日期 2017-01-02 到 2017-01-05 使用:

select date '2017-01-02' + level - 1 as x
from dual connect by level <= date '2017-01-05' - date '2017-01-02' + 1;

|                    X |
|----------------------|
| 2017-01-02T00:00:00Z |
| 2017-01-03T00:00:00Z |
| 2017-01-04T00:00:00Z |
| 2017-01-05T00:00:00Z |

如果您使用的是Oracle 12c,则可以使用横向或交叉应用从源子查询或表中的许多开始+结束值运行后一个生成器查询,请考虑以下示例:

create table probe(
   source_rel_id int,
   source_rel_start date,
   source_rel_end date
);

insert into probe values( 1, date '2017-01-02', date '2017-01-05' );
insert into probe values( 2, date '2017-03-01', date '2017-03-15' );
insert into probe values( 3, date '2017-05-05', date '2017-05-30' );
commit;

select * from probe p
cross apply (
    select p.source_rel_start + level - 1 as my_date
    from dual connect by level <= p.source_rel_end - p.source_rel_start + 1
)

SOURCE_REL_ID SOURCE_REL_START SOURCE_REL_END   MY_DATE         
------------- ---------------- ---------------- ----------------
            1 2017/01/02 00:00 2017/01/05 00:00 2017/01/02 00:00
            1 2017/01/02 00:00 2017/01/05 00:00 2017/01/03 00:00
            1 2017/01/02 00:00 2017/01/05 00:00 2017/01/04 00:00
            1 2017/01/02 00:00 2017/01/05 00:00 2017/01/05 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/01 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/02 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/03 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/04 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/05 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/06 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/07 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/08 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/09 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/10 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/11 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/12 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/13 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/14 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/15 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/05 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/06 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/07 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/08 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/09 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/10 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/11 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/12 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/13 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/14 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/15 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/16 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/17 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/18 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/19 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/20 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/21 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/22 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/23 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/24 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/25 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/26 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/27 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/28 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/29 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/30 00:00

编辑

如果我没有12c,还有什么其他选择?我应该检查一下版本,但我记得11。

只需生成一系列从1到N的数字,其中N必须大于您要为一条记录生成的数字的最高计数,如下所示:

  SELECT level as xyz FROM dual
  CONNECT BY LEVEL <= 10000

然后以这种方式将上述查询的结果连接到表中--gt;请看 this demo

SELECT source_rel_id, source_rel_start, source_rel_end,
       source_rel_start + xyz - 1 As days
FROM (
  SELECT level as xyz FROM dual
  CONNECT BY LEVEL <= 10000
) x
JOIN probe p
ON xyz <= source_rel_end - source_rel_start + 1
ORDER BY source_rel_id, days

| SOURCE_REL_ID |     SOURCE_REL_START |       SOURCE_REL_END |                 DAYS |
|---------------|----------------------|----------------------|----------------------|
|             1 | 2017-01-02T00:00:00Z | 2017-01-05T00:00:00Z | 2017-01-02T00:00:00Z |
|             1 | 2017-01-02T00:00:00Z | 2017-01-05T00:00:00Z | 2017-01-03T00:00:00Z |
|             1 | 2017-01-02T00:00:00Z | 2017-01-05T00:00:00Z | 2017-01-04T00:00:00Z |
|             1 | 2017-01-02T00:00:00Z | 2017-01-05T00:00:00Z | 2017-01-05T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-01T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-02T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-03T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-04T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-05T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-06T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-07T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-08T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-09T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-10T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-11T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-12T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-13T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-14T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-15T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-05T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-06T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-07T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-08T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-09T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-10T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-11T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-12T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-13T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-14T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-15T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-16T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-17T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-18T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-19T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-20T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-21T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-22T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-23T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-24T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-25T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-26T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-27T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-28T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-29T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-30T00:00:00Z |

我用了10000天,这应该足够了,因为10000天超过27年,所以我认为你不需要为每个记录生成27年的日期,但如果这仍然太少,那么在实践中它可以是一个介于100万到1000万之间的数字。由于行是在内存中生成的,因此在某个时候(100万到1000万),您会遇到内存不足错误。
您还可以使用子查询精确计算此限制:

SELECT level as xyz FROM dual
CONNECT BY LEVEL <= (
     SELECT max(  source_rel_end - source_rel_start ) + 1
     FROM probe
)

Alex Poole 8 年前

如果您仍在使用Oracle 11g(在评论中建议),可以使用 connect by 分层查询,但由于您要从多个源行获取数据,因此需要包含对非确定性函数的引用(我使用了'dbms\u random.value,但其他可用…)为了防止这种情况:

select rel_id as source_rel_id,
  rel_start_date + level - 1 as days,
  rel_end_date - rel_start_date + 1 as daycount
from releases
connect by rel_id  = prior rel_id
and prior dbms_random.value is not null
and level <= rel_end_date - rel_start_date + 1;

仅使用样本数据的前三行,就可以得到结果集中的92行:

SOURCE_REL_ID DAYS         DAYCOUNT
------------- ---------- ----------
           68 2016-03-01         31
           68 2016-03-02         31
           68 2016-03-03         31
...
           68 2016-03-30         31
           68 2016-03-31         31
           71 2016-06-01         30
           71 2016-06-02         30
...
           71 2016-06-29         30
           71 2016-06-30         30
           73 2016-08-01         31
           73 2016-08-02         31
...
           73 2016-08-30         31
           73 2016-08-31         31

如果你在11gR2(或更高),你也可以用 recursive subquery factoring a、 k.a递归CTE:

with rcte (source_rel_id, rel_end_date, days, daycount) as (
  select rel_id, rel_end_date, rel_start_date, rel_end_date - rel_start_date + 1
  from releases
  union all
  select source_rel_id, rel_end_date, days + 1, daycount
  from rcte
  where days < rel_end_date
)
select source_rel_id, days, daycount
from rcte
order by source_rel_id, days;

得到相同的结果-除了显式排序之外,这可能不是一件坏事。

CTE的锚分支从基表中获取数据,并添加两个额外的列: daycount 与之前一样计算,并且 days 这与第一组行的开始日期相同。这个 rel_end_date 需要对递归分支可用,以便包括在内,但是 rel_start_date 可以跳过。

递归分支然后将一天添加到 天 直到到达结束日期。如果 天 上一个生成行中的值已为 rel\u end\u日期 然后,该条件不满足,因此该源行停止。

最后,只查询希望从CTE中看到的列-在本例中,只排除 rel\u end\u日期 这是用来提供停止条件的,但实际上你不想看到。