时间段交叉汇总的问题

fool 2009-10-14 02:29:02

(#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得出，各位试试看；时间范围可以讨论下思路）。

万分感谢~

...全文

160 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

fool 2009-10-14

打赏
举报

[Quote=引用 5 楼 wildwave 的回复:]
或者
SQL codeselect id,date_area,sfrom(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(orderbysum(a.amount)desc)rnfrom
tt a ,
(select effective_date,lead(effective_date)over(orderby effective_date)expiry_datefrom(select effective_datefrom ttunionselect expiry_datefrom tt))bwhere b.effective_date>=a.effective_dateand b.expiry_date<=a.expiry_dategroupby b.effective_date)where rn=1
[/Quote]

神人啊~~~~~~~~~超级牛逼~~~~ 大概看懂了~~~~我学着试试

小灰狼W 2009-10-14

打赏
举报

或者

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

小灰狼W 2009-10-14

打赏
举报

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=1



ID	DATE_AREA	S

1,4,2	2008-06-01~2008-12-31	140

小灰狼W 2009-10-14

打赏
举报

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

效率不太高，看看有没有效率比较高的办法

fool 2009-10-14