Counting rows with restrictions in hierarchical data

Question

I have hierarchical data that bind instances of an object using DATE_FROMand DATE_TO.

See sqlfiddle .

Using CONNECT_BY, I can determine the number of continuous instances for each object, i.e. the length of the "islands", which is basically what I want. For example, this gives the expected island lengths for each object since DATE_FROM2014:

-- 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

However, what I would like to do is count the lines on the islands, where DATE_FROMthey DATE_TOtake some minimal number of days. I do not want to destroy the hierarchy of the island when I do this.

So, I tried this, but it is wrong. The results are not always what I am looking for.

-- 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

What gives

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

but i'm looking

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

Oracle. .

Answer 1

WHERE CONNECT 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:

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

Fiddle

Answer 2

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:

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

null;

Answer 3

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 AND Rank = (PRIOR.Rank + 1) . , .

, SubQuery 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