Oracle动态范围

玛尔甘

因此，我有以下示例数据：

Department |     InitialDate     |     FinalDate
-------------------------------------------------------
1          | 01/01/2017 01:12:00 | 01/03/2017 00:00:08
1          | 01/03/2017 00:00:08 | 01/04/2017 05:00:01
1          | 01/04/2017 05:00:01 | 01/05/2017 02:00:00
2          | 01/05/2017 10:00:00 | 01/06/2017 11:00:08
2          | 01/06/2017 11:00:08 | 01/07/2017 04:04:00
3          | 01/07/2017 04:00:00 | 01/07/2017 15:00:22
1          | 01/07/2017 14:00:00 | 01/07/2017 18:00:08
1          | 01/07/2017 18:15:00 | 01/08/2017 22:00:00
3          | 01/12/2017 01:30:03 | 01/12/2017 18:00:00
1          | 01/13/2017 23:12:00 | 01/13/2017 23:59:08

并希望像这样分组

Department |     InitialDate     |     FinalDate
-------------------------------------------------------
1          | 01/01/2017 01:12:00 | 01/05/2017 02:00:00
2          | 01/05/2017 10:00:00 | 01/07/2017 04:04:00
3          | 01/07/2017 04:00:00 | 01/07/2017 15:00:22
1          | 01/07/2017 14:00:00 | 01/08/2017 22:00:00
3          | 01/12/2017 01:30:03 | 01/12/2017 18:00:00
1          | 01/13/2017 23:12:00 | 01/13/2017 23:59:08

我需要按部门进行分组，并获取每个分组的开始日期和最后日期，但是部门可以重复进行，并且每次发生时，我都希望该特定窗口的开始日期和最后日期。我已经尝试了解析函数，但是似乎没有任何作用。

MT0

您可以使用LAGanalytic函数将每一行与上一行进行比较：

SELECT department,
       MIN( InitialDate ) AS InitialDate,
       MIN( FinalDate   ) AS FinalDate
FROM   (
  SELECT department,
         InitialDate,
         FinalDate,
         SUM( grp_inc ) OVER ( ORDER BY FinalDate ) AS grp
  FROM   (
    SELECT department,
           InitialDate,
           FinalDate,
           CASE WHEN LAG( department ) OVER ( ORDER BY FinalDate ) = department 
                THEN 0
                ELSE 1
                END AS grp_inc
    FROM   table_name
  )
)
GROUP BY department, grp

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。