时间段交叉汇总的问题

Question

(#id, #amount, #effective_date, #expiry_date)。
每条记录有自己的生效时间范围。求：同时生效的最大amount之和。例如：
1  50 2008-01-01 2008-12-31
2  60 2008-06-01 2008-12-31
3  40 2008-02-01 2008-05-31
4  30 2008-04-01 2009-03-31则得出1+3+4=120，时间范围为2008-04-01~2008-05-31请给出思路。（个人感觉汇总金额应该一个SQL得出，各位试试看；时间范围可以讨论下思路）。万分感谢~

Answer 1

Sorry, 例子中给出的应该1+2+4....

Answer 2

select d,s,id from(
  select b.d,sum(a.amount)s,wm_concat(a.id)id ,
    row_number()over(order by sum(a.amount)desc)rn from
  tt a ,
    (select (select min(effective_date) from tt)+rownum-1 d from 
      dual connect by rownum<=(select max(expiry_date) from tt)-(select min(effective_date) from tt)+1)b
  where b.d between a.effective_date and a.expiry_date
  group by b.d)
where rn=1
效率不太高，看看有没有效率比较高的办法

Answer 3

with tt as(select 1 id,50 amount ,date'2008-1-1' effective_date,date'2008-12-31' expiry_date from dual
  union all select 2,60,date'2008-6-1',date'2008-12-31'  from dual
  union all select 3,40,date'2008-2-1',date'2008-5-31'  from dual
  union all select 4,30,date'2008-4-1',date'2009-3-31'  from dual)
select id,date_area,s from(
  select b.d,sum(a.amount)s,wm_concat(a.id)id,
    to_char(max(a.effective_date),'yyyy-mm-dd')||'~'||to_char(min(expiry_date),'yyyy-mm-dd') date_area,
    row_number()over(order by sum(a.amount)desc)rn from
  tt a ,
    (select (select min(effective_date) from tt)+rownum-1 d from 
      dual connect by rownum<=(select max(expiry_date) from tt)-(select min(effective_date) from tt)+1)b
  where b.d between a.effective_date and a.expiry_date
  group by b.d)
where rn=1ID DATE_AREA S
1,4,2 2008-06-01~2008-12-31 140

Answer 4

或者
select id,date_area,s from(
  select b.effective_date,sum(a.amount)s,wm_concat(a.id)id,
    to_char(max(a.effective_date),'yyyy-mm-dd')||'~'||to_char(min(a.expiry_date),'yyyy-mm-dd') date_area,
    row_number()over(order by sum(a.amount)desc)rn from
  tt a ,
    (select effective_date,lead(effective_date)over(order by effective_date)expiry_date from(
select effective_date from tt union select expiry_date from tt))b
  where b.effective_date>=a.effective_date and b.expiry_date<=a.expiry_date
  group by b.effective_date)
where rn=1