SQL查询以查找更多记录处于活动状态的日期

Question

prod StartDate   ENDdate
----------------------------
a    1789-04-01  1799-12-14
b    1797-03-04  1826-07-04
c    1801-03-04  1826-07-04
d    1809-03-04  1836-06-28
e    1817-03-04  1831-07-04

我试图为上述场景找到一个解决方案，但无法获得逻辑。你能帮我做这件事吗。

问题是找到大多数产品处于活动状态的开始日期和结束日期。

例如：在上述样本数据中，在1817-03-04至1826-07-04期间，几乎有4种产品(b、c、d、e(处于活性(3409天(，1789-04-01至1799-12-14期间有1种产品(a(处于活性。

结果应该像一样

prod_count StartDate       ENDdate
-----------------------------------
4          1817-03-04  1826-07-04
1          1789-04-01  1799-12-14   

Answer 1

测试表和数据：

create table startend ( prod, startdate, enddate )
as
select 'a', date'1789-04-01', date'1799-12-14' from dual union all
select 'b', date'1797-03-04', date'1826-07-04' from dual union all
select 'c', date'1801-03-04', date'1826-07-04' from dual union all
select 'd', date'1809-03-04', date'1836-06-28' from dual union all
select 'e', date'1817-03-04', date'1831-07-04' from dual ; 
SQL> select * from startend;
PROD  STARTDATE  ENDDATE    
a     01-APR-89  14-DEC-99  
b     04-MAR-97  04-JUL-26  
c     04-MAR-01  04-JUL-26  
d     04-MAR-09  28-JUN-36  
e     04-MAR-17  04-JUL-31

让我们假设我们需要找到/检查STARTDATE和ENDDATE的每一个可能的组合。我们可以使用类似于下面内联视图中的JOIN。在此查询中，rownum值已重命名为：ERA(稍后将用于GROUP BY(。

select 
to_char( startdate, 'YYYY-MM-DD') start_
, to_char( enddate, 'YYYY-MM-DD')   end_
, enddate - startdate as duration
, rownum as era
from ( 
select distinct
S1.startdate
, S2.enddate
from startend S1 
join startend S2 on S1.startdate < S2.enddate
) 
;
-- result
START_     END_         DURATION        ERA
---------- ---------- ---------- ----------
1789-04-01 1836-06-28      17254          1
1789-04-01 1826-07-04      13607          2
1801-03-04 1831-07-04      11079          3
1809-03-04 1836-06-28       9978          4
1817-03-04 1836-06-28       7056          5
1817-03-04 1831-07-04       5235          6
1801-03-04 1826-07-04       9253          7
1809-03-04 1826-07-04       6331          8
1789-04-01 1831-07-04      15433          9
1797-03-04 1799-12-14       1015         10
1797-03-04 1826-07-04      10713         11
1797-03-04 1831-07-04      12539         12
1817-03-04 1826-07-04       3409         13
1789-04-01 1799-12-14       3909         14
1797-03-04 1836-06-28      14360         15
1801-03-04 1836-06-28      12900         16
1809-03-04 1831-07-04       8157         17
17 rows selected. 

您需要的条件如下(请参阅WHERE子句(：

-- test dates: from your question
select prod
from startend
where startdate <= date'1817-03-04' and startdate < date'1826-07-04'
and enddate   > date'1817-03-04' and enddate   >= date'1826-07-04'
;
-- result
b
c
d
e

最后一步：结合前两个查询背后的想法，类似于(Oracle11g(：

select count(*)                        as "prod_count"
, to_char( E.startdate, 'YYYY-MM-DD' ) as "StartDate"
, to_char( E.enddate, 'YYYY-MM-DD' )   as "EndDate"
from 
(
select startdate, enddate, rownum as era
from 
(
select distinct
S1.startdate
, S2.enddate
from startend S1 join startend S2 on S1.startdate < S2.enddate
)
) E 
join 
(
select distinct prod, startdate, enddate from startend
) P  
on    
( P.startdate <= E.startdate and P.startdate < E.enddate )
and ( P.enddate   >  E.startdate and P.enddate   >= E.enddate )
--
group by era, E.startdate, E.enddate
order by 2, 3
;

结果

prod_count StartDate  EndDate   
---------- ---------- ----------
1 1789-04-01 1799-12-14
2 1797-03-04 1799-12-14
1 1797-03-04 1826-07-04
2 1801-03-04 1826-07-04
3 1809-03-04 1826-07-04
1 1809-03-04 1831-07-04
1 1809-03-04 1836-06-28
4 1817-03-04 1826-07-04
2 1817-03-04 1831-07-04
1 1817-03-04 1836-06-28
10 rows selected.

另请参阅此处的dbfiddle。当使用Oracle12c(或18c(时，您可以使用CROSSAPPLY(而不是JOIN…ON…(

Answer 2

1(

SELECT COUNT(*) AS prod_count, StartDate, ENDdate
FROM XXX
GROUP BY StartDate, ENDdate;

--它使用相等的StartDate和ENDdate值。

2( 另一个想法可以是：

SELECT COUNT(*) AS prod_count, StartDate, ENDdate
(
SELECT StartDate, ENDdate, (StartDate - ENDdate) AS TimePeriod 
FROM XXX
) AS X2
GROUP BY TimePeriod;

--它在相同的时间段内工作。

很抱歉，我没有Oracle实例访问权限，所以它是在没有之前测试的情况下进行的，所以可能会有一些不准确的地方。