Oracle 递归CTE从另一表获取值

我有一个递归的CTE，它可以为多个日期生成开始和结束日期，它是按预期工作的。

它将在超过位置表的计数（*）之前停止创建行。

我正在寻找一种方法，在递归CTE中为每一行指定一个location_id ，而不具有重复的location_id。


开始/结束日期和地点之间没有共同性，所以我觉得加入是不可能的。

在下面的示例代码中，我尝试了一个交叉应用，它为递归代码生成的每个组提供了相同的location_id ，因此它也不起作用，这就是我如何离开测试用例的。

我正在使用管道功能来模拟多个日期，但我不一定会和这个想法联系在一起。如果解决方案要求一次处理一个日期。

我的目标是通过传入一个计划ID、日期范围和一个表示计划开始时间的数字来填充下面的计划表。
在我的测试用例中，它都是硬编码的，因为我希望测试用例尽可能简单。您可以假定Schedule_id =1或任意数字。


一旦我得到这个逻辑工作，我想使用一个. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

主键为：调度ID、位置ID、基日期

我的测试用例在下面，我似乎不能得到工作。提前感谢您的专业技能和耐心。

Current Result 

LOCATION_ID    BASE_DATE    START_DATE    END_DATE
1    08212021 00:00:00    08212021 23:16:00    08212021 23:21:00
1    08212021 00:00:00    08212021 23:26:00    08212021 23:31:00
1    08212021 00:00:00    08212021 23:36:00    08212021 23:41:00
…
…

Expected Result

LOCATION_ID    BASE_DATE    START_DATE    END_DATE
1    08212021 00:00:00    08212021 23:16:00    08212021 23:21:00
2    08212021 00:00:00    08212021 23:26:00    08212021 23:31:00 
3    08212021 00:00:00    08212021 23:36:00    08212021 23:41:00
…
...


ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

CREATE OR REPLACE TYPE nt_date IS TABLE OF DATE;
/

    CREATE OR REPLACE FUNCTION generate_dates_pipelined(
      p_from IN DATE,
      p_to   IN DATE
    )
    RETURN nt_date  PIPELINED   DETERMINISTIC
    IS
      v_start DATE := TRUNC(LEAST(p_from, p_to));
      v_end   DATE := TRUNC(GREATEST(p_from, p_to));
    BEGIN
     LOOP
        PIPE ROW (v_start);
        EXIT WHEN v_start >= v_end;
        v_start := v_start + INTERVAL '1' DAY;
      END LOOP;
      RETURN;
    END       generate_dates_pipelined;
/

CREATE TABLE locations AS
    SELECT level AS location_id,
       'Door ' || level AS location_name,

    CASE round(dbms_random.value(1,3)) 
            WHEN 1 THEN 'A' 
            WHEN 2 THEN 'T' 
            WHEN 3 THEN 'T' 
         END AS location_type

    FROM   dual
    CONNECT BY level <= 20;

create table schedule(
       schedule_id NUMBER(4),
       location_id number(4),
       base_date DATE,
       start_date DATE,
       end_date DATE,
         CONSTRAINT start_min check (start_date=trunc(start_date,'MI')),   
       CONSTRAINT end_min check (end_date=trunc(end_date,'MI')),
 CONSTRAINT end_gt_start CHECK (end_date >= start_date),
CONSTRAINT same_day CHECK (TRUNC(end_date) = TRUNC(start_date))
      );


WITH input  (base_date,start_time) AS (
SELECT 
COLUMN_VALUE,
COLUMN_VALUE+
NUMTODSINTERVAL(83760, 'SECOND')
  FROM   TABLE(generate_dates_pipelined(DATE '2021-08-21',DATE '2021-08-30')) 
)
SELECT
     location_id,
     base_date, start_time + (LEVEL-1) * INTERVAL '10' MINUTE
         AS start_date,
       start_time + (LEVEL-1) * INTERVAL '10' MINUTE + INTERVAL '5' MINUTE
         AS end_date 
FROM   input i
CROSS APPLY  (SELECT location_id from locations)
CONNECT BY (LEVEL-1) * INTERVAL '10' MINUTE < INTERVAL '1' DAY
AND    LEVEL <= (SELECT COUNT(*) FROM locations)
AND    start_time + (LEVEL-1) * INTERVAL '10' MINUTE < TRUNC(start_time) + INTERVAL '1' DAY;

I'm looking for a way to assign a location_id to each row in the recursive CTE without having a duplicate location_id

你在这里到底是什么意思？

每个位置都可以显示only结果中的一次？或者在重新使用同一位置之前，必须显示所有其他位置？

无论哪种方式，您都已使用1..N中的值创建了位置表，并且没有间隙。所以在我看来，你可以给你的日期生成器指定行号，然后把它们连接到各个位置。

如果要循环查看位置，请先取日期行号的模数。使用位置表中的行数作为模数。

当您在19c上时，下面是一个使用SQL宏来生成日期并循环访问位置的示例：

create or replace function generate_days (   
  start_date date, end_date date,  
  day_increment integer default 1  
)   
  return clob sql_macro as  
  stmt clob;  
begin  
    
  stmt := 'select start_date  
           + ( level - 1 ) * day_increment as dt  
  from   dual  
  connect by level <= (  
    ( ( end_date - start_date ) + day_increment 
    ) / day_increment  
  )';  
  
  return stmt;  
    
end generate_days;  
/

select * from (
  select dt, 
         mod ( row_number () over ( order by dt ), 20 ) + 1 rn
  from   generate_days ( date '2021-08-21',date '2021-08-30', 10/1440 )
) join locations
on     rn = location_id
order  by dt
fetch first 23 rows only;

DT                         RN    LOCATION_ID LOCATION_NAME    LOCATION_TYPE   
21-AUG-2021 00:00:00        2              2 Door 2           T                
21-AUG-2021 00:10:00        3              3 Door 3           T                
...  ...
21-AUG-2021 02:50:00       19             19 Door 19          T                
21-AUG-2021 03:00:00       20             20 Door 20          T                
21-AUG-2021 03:10:00        1              1 Door 1           T                
21-AUG-2021 03:20:00        2              2 Door 2           T                
21-AUG-2021 03:30:00        3              3 Door 3           T                
21-AUG-2021 03:40:00        4              4 Door 4           T

有关日期生成和宏的更多信息，请访问https://blogs.oracle.com/sql/how-to-generate-days-weeks-or-months-between-two-dates-in-oracle-database

示例查询只是一个普通的CTE ，它不是递归的。要实现递归，必须有一个联合所有分支，将CTE连接到源表。