Oracle SQL: CONNECT BY LEVEL возвращает много строк

Я впервые использую команду CONNECT BY и понимаю, что с ее помощью можно создавать циклы. Я пытался создать запрос, который генерирует строку для каждой метки времени между временем начала и временем окончания с переменным интервалом. Когда я запускаю этот запрос для каждого «маршрута» в отдельности, он работает отлично. Но когда я пытаюсь запустить его для обоих маршрутов одновременно, цикл продолжается.

Что я делаю неправильно?

SELECT ROUTE_NAME, START_TIME + (LEVEL - 1) * TIME_PERIOD OUTPUT_MOMENT
  FROM (SELECT *
          FROM (SELECT 1 / 24 AS TIME_PERIOD,
                       SYSDATE - 8 / 24 AS START_TIME,
                       SYSDATE + 3 / 24 AS END_TIME,
                       'ROUTE A' ROUTE_NAME
                  FROM DUAL
                UNION ALL
                SELECT 1 / 48 AS TIME_PERIOD,
                       SYSDATE - 8 / 24 AS START_TIME,
                       SYSDATE + 3 / 24 AS END_TIME,
                       'ROUTE B' ROUTE_NAME
                  FROM DUAL)
         WHERE ROUTE_NAME IN ('ROUTE A')
         --WHERE ROUTE_NAME IN ('ROUTE B')
         --WHERE ROUTE_NAME IN ('ROUTE A', 'ROUTE B')
       )
CONNECT BY (LEVEL - 1) <= (END_TIME - START_TIME) / TIME_PERIOD

Результат на маршруте А:

* ROUTE_NAME    OUTPUT_MOMENT
* ROUTE A   9/3/2018 5:41:01
* ROUTE A   9/3/2018 6:41:01
* ROUTE A   9/3/2018 7:41:01
* ROUTE A   9/3/2018 8:41:01
* ROUTE A   9/3/2018 9:41:01
* ROUTE A   9/3/2018 10:41:01
* ROUTE A   9/3/2018 11:41:01
* ROUTE A   9/3/2018 12:41:01
* ROUTE A   9/3/2018 13:41:01
* ROUTE A   9/3/2018 14:41:01
* ROUTE A   9/3/2018 15:41:01

Результаты на маршруте B:

* ROUTE_NAME    OUTPUT_MOMENT
* ROUTE B   9/3/2018 5:42:34
* ROUTE B   9/3/2018 6:12:34
* ROUTE B   9/3/2018 6:42:34
* ROUTE B   9/3/2018 7:12:34
* ROUTE B   9/3/2018 7:42:34
* ROUTE B   9/3/2018 8:12:34
* ROUTE B   9/3/2018 8:42:34
* ROUTE B   9/3/2018 9:12:34
* ROUTE B   9/3/2018 9:42:34
* ROUTE B   9/3/2018 10:12:34
* ROUTE B   9/3/2018 10:42:34
* ROUTE B   9/3/2018 11:12:34
* ROUTE B   9/3/2018 11:42:34
* ROUTE B   9/3/2018 12:12:34
* ROUTE B   9/3/2018 12:42:34
* ROUTE B   9/3/2018 13:12:34
* ROUTE B   9/3/2018 13:42:34
* ROUTE B   9/3/2018 14:12:34
* ROUTE B   9/3/2018 14:42:34
* ROUTE B   9/3/2018 15:12:34
* ROUTE B   9/3/2018 15:42:34
* ROUTE B   9/3/2018 16:12:34
* ROUTE B   9/3/2018 16:42:34

Результаты по обоим (существует больше строк):

* ROUTE_NAME    OUTPUT_MOMENT
* ROUTE A   9/3/2018 5:43:21
* ROUTE A   9/3/2018 6:43:21
* ROUTE A   9/3/2018 7:43:21
* ROUTE A   9/3/2018 8:43:21
* ROUTE A   9/3/2018 9:43:21
* ROUTE A   9/3/2018 10:43:21
* ROUTE A   9/3/2018 11:43:21
* ROUTE A   9/3/2018 12:43:21
* ROUTE A   9/3/2018 13:43:21
* ROUTE A   9/3/2018 14:43:21
* ROUTE A   9/3/2018 15:43:21
* ROUTE B   9/3/2018 11:13:21
* ROUTE B   9/3/2018 11:43:21
* ROUTE B   9/3/2018 12:13:21
* ROUTE B   9/3/2018 12:43:21
* ROUTE B   9/3/2018 13:13:21
* ROUTE B   9/3/2018 13:43:21
* ROUTE B   9/3/2018 14:13:21
* ROUTE B   9/3/2018 14:43:21
* ROUTE B   9/3/2018 15:13:21
* ROUTE B   9/3/2018 15:43:21
* ROUTE B   9/3/2018 16:13:21
* ROUTE B   9/3/2018 16:43:21
* ROUTE B   9/3/2018 10:43:21
* ROUTE B   9/3/2018 11:13:21
* ROUTE B   9/3/2018 11:43:21
* ROUTE B   9/3/2018 12:13:21
* ROUTE B   9/3/2018 12:43:21
* ROUTE B   9/3/2018 13:13:21
* ROUTE B   9/3/2018 13:43:21
* ROUTE B   9/3/2018 14:13:21
* ROUTE B   9/3/2018 14:43:21
* ROUTE B   9/3/2018 15:13:21
* ROUTE B   9/3/2018 15:43:21
* ROUTE B   9/3/2018 16:13:21
* ROUTE B   9/3/2018 16:43:21
* ROUTE B   9/3/2018 10:13:21
* ROUTE A   9/3/2018 15:43:21
* ROUTE B   9/3/2018 11:13:21
* ROUTE B   9/3/2018 11:43:21
* ROUTE B   9/3/2018 12:13:21
* ROUTE B   9/3/2018 12:43:21
* ROUTE B   9/3/2018 13:13:21
* ROUTE B   9/3/2018 13:43:21
* ROUTE B   9/3/2018 14:13:21
* ROUTE B   9/3/2018 14:43:21
* ROUTE B   9/3/2018 15:13:21
* ROUTE B   9/3/2018 15:43:21
* ROUTE B   9/3/2018 16:13:21
* ROUTE B   9/3/2018 16:43:21
* ROUTE B   9/3/2018 10:43:21
* ROUTE B   9/3/2018 11:13:21
* ROUTE B   9/3/2018 11:43:21
* ROUTE B   9/3/2018 12:13:21
* ROUTE B   9/3/2018 12:43:21
* ROUTE B   9/3/2018 13:13:21
* ROUTE B   9/3/2018 13:43:21
* ROUTE B   9/3/2018 14:13:21
* ROUTE B   9/3/2018 14:43:21
* ROUTE B   9/3/2018 15:13:21
* ROUTE B   9/3/2018 15:43:21
* ROUTE B   9/3/2018 16:13:21
* ROUTE B   9/3/2018 16:43:21
* ROUTE B   9/3/2018 9:43:21
* ROUTE A   9/3/2018 14:43:21
* ROUTE A   9/3/2018 15:43:21
* ROUTE B   9/3/2018 11:13:21
* ROUTE B   9/3/2018 11:43:21
* ROUTE B   9/3/2018 12:13:21
* ROUTE B   9/3/2018 12:43:21
* ROUTE B   9/3/2018 13:13:21
* ROUTE B   9/3/2018 13:43:21
* ROUTE B   9/3/2018 14:13:21
* ROUTE B   9/3/2018 14:43:21
* ROUTE B   9/3/2018 15:13:21
* ROUTE B   9/3/2018 15:43:21
* ROUTE B   9/3/2018 16:13:21
* ROUTE B   9/3/2018 16:43:21
* ROUTE B   9/3/2018 10:43:21
* ROUTE B   9/3/2018 11:13:21
* ROUTE B   9/3/2018 11:43:21
* ROUTE B   9/3/2018 12:13:21
* ROUTE B   9/3/2018 12:43:21

sql oracle connect-by
person Ronald    schedule 09.03.2018    source источник

Ответы (2)


arrow_upward
2
arrow_downward

connect by зависит только от времени, поэтому вы каждый раз соединяетесь по маршруту A с маршрутом B и наоборот.

Простое исправление, кажется, состоит в том, чтобы сделать это:

CONNECT BY (LEVEL - 1) <= (END_TIME - START_TIME) / TIME_PERIOD
AND ROUTE_NAME = PRIOR ROUTE_NAME

ограничить его одним маршрутом за раз; но это затем образует цикл, поэтому вам нужно добавить вызов недетерминированной функции, чтобы предотвратить это; Например:

CONNECT BY (LEVEL - 1) <= (END_TIME - START_TIME) / TIME_PERIOD
AND ROUTE_NAME = PRIOR ROUTE_NAME
AND PRIOR DBMS_RANDOM.VALUE() IS NOT NULL

который получает:

ROUTE_NAME OUTPUT_MOMENT      
---------- -------------------
ROUTE A    2018-03-09 05:00:08
ROUTE A    2018-03-09 06:00:08
ROUTE A    2018-03-09 07:00:08
ROUTE A    2018-03-09 08:00:08
ROUTE A    2018-03-09 09:00:08
ROUTE A    2018-03-09 10:00:08
ROUTE A    2018-03-09 11:00:08
ROUTE A    2018-03-09 12:00:08
ROUTE A    2018-03-09 13:00:08
ROUTE A    2018-03-09 14:00:08
ROUTE A    2018-03-09 15:00:08
ROUTE B    2018-03-09 05:00:08
ROUTE B    2018-03-09 05:30:08
ROUTE B    2018-03-09 06:00:08
ROUTE B    2018-03-09 06:30:08
ROUTE B    2018-03-09 07:00:08
ROUTE B    2018-03-09 07:30:08
ROUTE B    2018-03-09 08:00:08
ROUTE B    2018-03-09 08:30:08
ROUTE B    2018-03-09 09:00:08
ROUTE B    2018-03-09 09:30:08
ROUTE B    2018-03-09 10:00:08
ROUTE B    2018-03-09 10:30:08
ROUTE B    2018-03-09 11:00:08
ROUTE B    2018-03-09 11:30:08
ROUTE B    2018-03-09 12:00:08
ROUTE B    2018-03-09 12:30:08
ROUTE B    2018-03-09 13:00:08
ROUTE B    2018-03-09 13:30:08
ROUTE B    2018-03-09 14:00:08
ROUTE B    2018-03-09 14:30:08
ROUTE B    2018-03-09 15:00:08
ROUTE B    2018-03-09 15:30:08
ROUTE B    2018-03-09 16:00:08

34 rows selected.

Вы также можете выполнить два запроса connect by и объединить результаты вместе, возможно, перетащив временной диапазон в CTE, чтобы избежать дублирования:

WITH START_END AS (
  SELECT SYSDATE - 8 / 24 AS START_TIME,
         SYSDATE + 3 / 24 AS END_TIME
  FROM DUAL
)
SELECT 'ROUTE A' ROUTE_NAME,
       START_TIME + (LEVEL - 1) / 24 AS OUTPUT_MOMENT
  FROM START_END
CONNECT BY (LEVEL - 1) <= (END_TIME - START_TIME) / (1 / 24)
UNION ALL
SELECT 'ROUTE B' ROUTE_NAME,
       START_TIME + (LEVEL - 1) / 48 AS OUTPUT_MOMENT
FROM START_END
CONNECT BY (LEVEL - 1) <= (END_TIME - START_TIME) / (1 / 48)

Использование / ( 1 / 24) выглядит странно, когда вместо этого можно было бы использовать * 24, но на самом деле вы получаете немного другой результат из-за ошибок округления; с последним вы получаете дополнительную строку для маршрута A. Однако вы можете изменить логику, чтобы избежать этой путаницы.

person Alex Poole    schedule 09.03.2018
comment
Ага, вот оно! Большое спасибо за такой быстрый ответ. Это дает мне больше функций для Google :) - person Ronald; 09.03.2018

arrow_upward
1
arrow_downward

Здесь вы получаете удвоение строк на каждом уровне иерархического запроса.
Рассмотрим простой пример:

WITH dbl AS (
    SELECT * FROM dual  UNION ALL SELECT *FROM dual
)
SELECT *FROM dbl CONNECT BY LEVEL <= N

Для N=2 запрос возвращает 6 строк, для N=6 — 126 строк, для N=10 — 2046 строк. Итак, мы видим, что количество строк растет экспоненциально.

Ваш запрос работает аналогичным образом. Чтобы исправить это, вы можете либо переместить union all на внешний уровень, выполнив отдельные иерархические запросы для каждого маршрута:

SELECT ROUTE_NAME, START_TIME + (lvl - 1) * TIME_PERIOD
FROM (
    SELECT LEVEL AS lvl, ROUTE_NAME AS "ROUTE A"... 
    ...
    FROM DUAL CONNECT BY (LEVEL - 1) <= (END_TIME - START_TIME) / TIME_PERIOD
)
UNION ALL 
SELECT ROUTE_NAME, START_TIME + (lvl - 1) * TIME_PERIOD
FROM (
    SELECT LEVEL AS lvl, ROUTE_NAME AS "ROUTE B", ... 
    ...
    FROM DUAL CONNECT BY (LEVEL - 1) <= (END_TIME - START_TIME) / TIME_PERIOD
)

Или запретите одному маршруту следовать за другим с помощью ROUTE_NAME = PRIOR ROUTE_NAME, как было предложено в другом ответе.

person wolfrevokcats    schedule 09.03.2018
comment
Спасибо за объяснение! - person Ronald; 09.03.2018