Блог

У меня есть иерархические данные, которые связывают экземпляры объекта с помощью DATE_FROM и DATE_TO .

Пожалуйста, смотрите sqlfiddle.

Используя CONNECT_BY , я могу определить количество смежных экземпляров для каждого объекта, т. Е. Длину «островов», что в основном то, что я хочу. Например, это дает ожидаемые длины островов для каждого объекта с DATE_FROM в 2014 году:

 -- QUERY 1
SELECT 
  T.ENTITY_ID,
  MAX(LEVEL) MAX_LEVEL
FROM TEST T
WHERE EXTRACT(YEAR FROM T.DATE_FROM) = 2014
CONNECT BY 
  T.ENTITY_ID = PRIOR T.ENTITY_ID
  AND T.DATE_FROM = PRIOR T.DATE_TO
GROUP BY T.ENTITY_ID
  

Однако то, что я хотел бы сделать, это подсчитать строки на островах, где DATE_FROM и DATE_TO занимают некоторое минимальное количество дней. Я не хочу нарушать иерархию острова, когда делаю это.

Итак, я попробовал это, но это неправильно. Результаты не всегда соответствуют тому, что я ищу.

 -- QUERY 2
SELECT 
  T.ENTITY_ID,
  MAX(LEVEL) MAX_LEVEL,
  SUM(
    CASE WHEN PRIOR T.DATE_TO - PRIOR T.DATE_FROM > 183 
    THEN 1 
    ELSE 0 
    END
  ) LONG_TERM_COUNT
FROM TEST T
WHERE EXTRACT(YEAR FROM T.DATE_FROM) = 2014
CONNECT BY 
  T.ENTITY_ID = PRIOR T.ENTITY_ID
  AND T.DATE_FROM = PRIOR T.DATE_TO
GROUP BY T.ENTITY_ID
  

Что дает

  ----------- ----------- ----------------- 
| ENTITY_ID | MAX_LEVEL | LONG_TERM_COUNT |
 ----------- ----------- ----------------- 
|         1 |         4 |               3 |
|         2 |         5 |               4 |
 ----------- ----------- ----------------- 
  

но я ищу

  ----------- ----------- ----------------- 
| ENTITY_ID | MAX_LEVEL | LONG_TERM_COUNT |
 ----------- ----------- ----------------- 
|         1 |         4 |               4 |
|         2 |         5 |               4 |
 ----------- ----------- ----------------- 
  

Мне нужно решение Oracle. Спасибо за чтение.

Условие WHERE вычисляется после ПОДКЛЮЧЕНИЯ BY, поэтому ваш запрос не начинается со строк из 2014. Он создает иерархию для каждой строки в вашей таблице, вы можете легко увидеть это, удалив WHERE и агрегацию:

 SELECT 
  T.ENTITY_ID,
  LEVEL,
  T.DATE_TO,  
  T.DATE_FROM,
  prior T.DATE_TO,
  prior T.DATE_FROM
FROM TEST T
CONNECT BY 
  T.ENTITY_ID = PRIOR T.ENTITY_ID
  AND T.DATE_TO = PRIOR T.DATE_FROM
order by 1,2
  

Вам нужно использовать START WITH вместо WHERE-condition:

 SELECT 
  T.ENTITY_ID,
  LEVEL,
  T.DATE_TO,  
  T.DATE_FROM,
  prior T.DATE_TO,
  prior T.DATE_FROM
FROM TEST T
START WITH EXTRACT(YEAR FROM T.DATE_FROM) = 2014
CONNECT BY 
   T.ENTITY_ID = PRIOR T.ENTITY_ID
   AND T.DATE_TO = PRIOR T.DATE_FROM
  

Итак, наконец, это:

 SELECT 
  T.ENTITY_ID,
  MAX(LEVEL) MAX_LEVEL, -- or COUNT(*)
  SUM(
    CASE WHEN  T.DATE_TO -  T.DATE_FROM > 183 
    THEN 1 
    ELSE 0 
    END
  ) LONG_TERM_COUNT
FROM TEST T
CONNECT BY 
  T.ENTITY_ID = PRIOR T.ENTITY_ID
  AND T.DATE_TO = PRIOR T.DATE_FROM
START WITH EXTRACT(YEAR FROM T.DATE_FROM) = 2014
GROUP BY T.ENTITY_ID
  

И вы можете получить неверный результат, если существуют две строки в 2014 году, поэтому вам нужно начать с последней строки в 2014 году:

 SELECT 
  T.ENTITY_ID,
  MAX(LEVEL) MAX_LEVEL,
  SUM(
    CASE WHEN  T.DATE_TO -  T.DATE_FROM > 183 
    THEN 1 
    ELSE 0 
    END
  ) LONG_TERM_COUNT
FROM TEST T
CONNECT BY 
  T.ENTITY_ID = PRIOR T.ENTITY_ID
  AND T.DATE_TO = PRIOR T.DATE_FROM
START WITH T.DATE_FROM = 
  (
    SELECT MAX(T2.DATE_FROM) 
    FROM TEST T2 
    WHERE T.ENTITY_ID = T2.ENTITY_ID
      AND T2.DATE_FROM >= DATE '2014-01-01'
      AND T2.DATE_FROM <= DATE '2014-12-31'
  )
GROUP BY T.ENTITY_ID
  

Скрипка

Ваш оператор sql верен. Но один сценарий, который необходимо учитывать, когда CASE WHEN T.DATE_TO - PRIOR T.DATE_FROM > 183 оператор становится null , который не будет учитываться.

 INSERT INTO TEST 
 VALUES (1,TO_DATE('20130101','YYYYMMDD'),TO_DATE('20140101','YYYYMMDD'));
INSERT INTO TEST 
 VALUES (1,TO_DATE('20140101','YYYYMMDD'),TO_DATE('20150101','YYYYMMDD'));
  

Из вашего примера данных, эквивалентного случаю:

 CASE WHEN 
      TO_DATE('20140101','YYYYMMDD') - PRIOR TO_DATE('20140101','YYYYMMDD') > 183
  

Это дает null значение;

Я не очень хорошо знаком с Oracle, но хорошим подходом могло бы быть использование RANK Aggregate. Например:

 SELECT 
 T.ENTITY_ID,
 T.DATE_FROM,
 RANK() OVER (PARTITION BY ENTITY_ID
 ORDER BY T.DATE_TO DESC) "Rank"
FROM TEST T
WHERE EXTRACT(YEAR FROM T.DATE_FROM) <= 2014 
  

Объединение T.ENTITY_ID = Prior T.ENTITY_ID И Rank = (PRIOR.Rank 1) может привести к решению. Как я уже сказал, это всего лишь предложение, как подойти.

Я попробовал немного больше, и это мое решение с подзапросом SQL Fiddle

 SELECT 
 T.ENTITY_ID,
 MAX(LEVEL) MAX_LEVEL,
 (Select MAX("Rank") FROM
  (
    SELECT T2.ENTITY_ID AS ID, RANK() OVER (PARTITION BY T2.ENTITY_ID
    ORDER BY T2.DATE_TO DESC) "Rank"
    FROM TEST T2
    WHERE EXTRACT(YEAR FROM T2.DATE_FROM) < 2014 
  ) SubQ
  WHERE ID = T.ENTITY_ID
 ) "LONG_TERM_COUNT"
FROM TEST T
WHERE EXTRACT(YEAR FROM T.DATE_FROM) = 2014
CONNECT BY 
  T.ENTITY_ID = PRIOR T.ENTITY_ID
  AND T.DATE_FROM = PRIOR T.DATE_TO
GROUP BY T.ENTITY_ID