sql - 在分层数据中计算有约束的行数

Question

我有使用 DATE_FROM 和 DATE_TO 链接实体实例的分层数据。

请参阅sqlfiddle .

使用CONNECT_BY我可以确定每个实体的连续实例的数量，即“岛屿”的长度，这主要是我想要的。例如，这给出了 2014 年 DATE_FROM 的每个实体的预期岛屿长度:

-- 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 解决方案。感谢您的阅读。

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