SQL/PLSQL查询获取开始日期和结束日期之间的差额

Question

我有一个表，它有以下数据

Date_from     Date_to
20-NOV-19     22-NOV-19
10-NOV-19     21-NOV-19
14-NOV-19     26-NOV-19

我需要一个查询来查找date_from和date_to之间的差异之和。

示例20-11月19日和22-11月-19之间的差异是2天，10-11月19日和21-11月19日之间的差异是11天，但是查询必须将其视为9天，因为第一行已经考虑了第20天-11月5日、21-11月5日。14-11月19日和26-11月19日之间的区别是12天，但查询必须将其视为4天，因为在上面的行中已经考虑了14-11月至22-11月。

查询结果应为15天(2+9+4)。

任何帮助都将不胜感激。

Answer 1

还有一个备选案文：

with t as (
select to_date('20-11-2019','DD.MM.YYYY') Date_from,to_date('22-11-2019','DD.MM.YYYY') Date_to from dual
union all
select to_date('10-11-2019','DD.MM.YYYY') Date_from,to_date('21-11-2019','DD.MM.YYYY') Date_to from dual
union all
select to_date('14-11-2019','DD.MM.YYYY') Date_from,to_date('26-11-2019','DD.MM.YYYY') Date_to from dual
),
alldays as
(select (select min(t.Date_from) from t)+level-1 dt  from dual connect by level<(select max(t.Date_to)+2 from t)-(select min(t.Date_from) from t))
select count(distinct a.dt)-1 from t
join alldays a on a.dt>=t.Date_from  and a.dt<t.Date_to
order by a.dt    

Answer 2

这里有一个选项:内联视图获取CFROM和CTO之间的所有日期，并返回不同日期的数量。

SQL> WITH test (cfrom, cto)
  2       -- sample data
  3       AS (SELECT DATE '2019-11-20', DATE '2019-11-22' FROM DUAL
  4           UNION ALL
  5           SELECT DATE '2019-11-10', DATE '2019-11-21' FROM DUAL
  6           UNION ALL
  7           SELECT DATE '2019-11-14', DATE '2019-11-26' FROM DUAL)
  8  -- this is useful code
  9  SELECT COUNT (DISTINCT datum) - 1 diff
 10    FROM (SELECT cfrom + COLUMN_VALUE - 1 datum
 11            FROM test
 12                 CROSS JOIN
 13                 TABLE (
 14                    CAST (
 15                       MULTISET (    SELECT LEVEL
 16                                       FROM DUAL
 17                                 CONNECT BY LEVEL <= cto - cfrom) AS SYS.odcinumberlist)));

      DIFF
----------
        15

SQL>

Answer 3

这是空隙和岛屿问题的变体。

以下是一种方法：

starts.

• Aggregate

• 使用lag()或累积最大值标识新周期的起始位置(“岛屿”)。

• 使用起始点来标识岛屿，使用累积和表示总体上的lag()。

。

所以：

select sum(date_to - date_from)
from (select min(date_from) as date_from, max(date_to) as date_to
      from (select t.*,
                   sum(case when prev_dt >= date_from then 0 else 1 end) over (order by date_from) as grp
            from (select t.*,
                         lag(date_to) over (order by date_from) as prev_dt
                  from t
                 ) t
           ) t
      group by grp
     ) t;