Oracle查询-计算日期和时间差-重叠

bbk 发表于 Dev

步步高

口头查询如何计算重叠时间

ID    startdate                enddate                      hours
a124     10/10/2019 07:30:00    10/10/2019 11:30:00            4
a124     10/10/2019 07:00:00    10/10/2019 15:10:00            8.17
bc24     10/10/2019 07:30:00    10/10/2019 11:30:00            4
bc24     10/10/2019 10:30:00    10/10/2019 15:30:00            5
er67     10/10/2019 09:30:00    10/10/2019 11:30:00            2
er67     10/10/2019 15:30:00    10/10/2019 16:30:00            1

所需输出：

    ID    startdate                enddate                      hours
   a124   10/10/2019 07:00:00     10/10/2019 15:10:00           8.17
   bc24   10/10/2019 07:30:00     10/10/2019 15:30:00            8
   er67   10/10/2019 09:30:00     10/10/2019 11:30:00            2
   er67   10/10/2019 15:30:00     10/10/2019 16:30:00            1

根据以下查询输出

大力水手

更新整个答案。

您需要使用NOT EXISTS来删除开始日期和结束日期在其他日期之间的行（不是重叠的，而是完全包含的），其aggregate功能如下

表创建：

SQL> CREATE TABLE T AS
  2  (SELECT 'a124' ID,     TO_DATE('10/10/2019 07:30:00','DD/MM/YYYY HH24:MI:SS') STARTDATE,    TO_DATE('10/10/2019 11:30:00','DD/MM/YYYY HH24:MI:SS') ENDDATE,            4 AS HOURS FROM DUAL UNION ALL
  3  SELECT 'a124',     TO_DATE('10/10/2019 07:00:00','DD/MM/YYYY HH24:MI:SS'),    TO_DATE('10/10/2019 15:10:00','DD/MM/YYYY HH24:MI:SS'),            8.17 FROM DUAL UNION ALL
  4  SELECT 'bc24',     TO_DATE('10/10/2019 07:30:00','DD/MM/YYYY HH24:MI:SS'),    TO_DATE('10/10/2019 11:30:00','DD/MM/YYYY HH24:MI:SS'),            4 FROM DUAL UNION ALL
  5  SELECT 'bc24',     TO_DATE('10/10/2019 11:30:00','DD/MM/YYYY HH24:MI:SS'),    TO_DATE('10/10/2019 15:30:00','DD/MM/YYYY HH24:MI:SS'),            6 FROM DUAL UNION ALL
  6  SELECT 'er67',     TO_DATE('10/10/2019 09:30:00','DD/MM/YYYY HH24:MI:SS'),    TO_DATE('10/10/2019 11:30:00','DD/MM/YYYY HH24:MI:SS'),            2 FROM DUAL UNION ALL
  7  SELECT 'er67',     TO_DATE('10/10/2019 15:30:00','DD/MM/YYYY HH24:MI:SS'),    TO_DATE('10/10/2019 16:30:00','DD/MM/YYYY HH24:MI:SS'),            1 FROM DUAL)
  8  ;

Table created.

查询以获取所需数据

SQL> SELECT
  2      ID, MIN(STARTDATE), MAX(ENDDTAE),
  3      ROUND(SUM(CASE
  4          WHEN PREV_ENDDATE BETWEEN STARTDATE AND ENDDATE THEN ENDDATE - PREV_ENDDATE
  5          ELSE ENDDATE - STARTDATE
  6      END) * 24, 2) AS HOURS
  7  FROM
  8      (
  9          SELECT
 10              ID,
 11              STARTDATE,
 12              ENDDATE,
 13              LAG(ENDDATE) OVER(
 14                  PARTITION BY ID
 15                  ORDER BY
 16                      STARTDATE, ENDDATE
 17              ) AS PREV_ENDDATE
 18          FROM
 19              T TOUT
 20          WHERE
 21              NOT EXISTS (
 22                  SELECT
 23                      1
 24                  FROM
 25                      T TIN
 26                  WHERE
 27                      TIN.ID = TOUT.ID
 28                      AND TOUT.STARTDATE BETWEEN TIN.STARTDATE AND TIN.ENDDATE
 29                      AND TOUT.ENDDATE BETWEEN TIN.STARTDATE AND TIN.ENDDATE
 30                      AND TOUT.ROWID <> TIN.ROWID
 31              )
 32      )
 33  GROUP BY
 34      ID;

ID        HOURS
---- ----------
a124       8.17
bc24          8
er67          3

SQL>

干杯！！

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

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