oracle 按照日期区间求和

//求两个日期之间的数据之和
//2011/03/16为起始日，2011/09/15为结束日
//没一个月为一个梯度，也就是3.16-4.15,4.16-5.15,...
//数据：
     日期          金额
     2011/03/16     20
     2011/03/17     30
     2011/04/14     50
     2011/04/15     50
     2011/04/16     10
     2011/04/17     20
     2011/05/14     30
     2011/05/15     40
     2011/05/20     10
     2011/06/17     20
     2011/06/18     30
//结果：
     月份          总金额
     2011/04       150
     2011/05       100
     2011/06       10
     2011/07       50
//这个问题的关键是将如何按照日期区间来统计金额
//如果我们这么想，就能够将问题简单化：
//也就是进行分组查询，将日期进行对比，分别进行统计，然后再连接到一起：
//解法一：
select '2011/04' month,sum(val) sum_sal
from t
where dt between to_date('2011/03/16','yyyy/mm/dd')
  and to_date('2011/03/16','yyyy/mm/dd')+30
union all
select '2011/05',sum(val)
from t
where dt between to_date('2011/04/16','yyyy/mm/dd')
  and to_date('2011/04/16','yyyy/mm/dd')+30
union all
select '2011/06',sum(val)
from t
where dt between to_date('2011/05/16','yyyy/mm/dd')
  and to_date('2011/05/16','yyyy/mm/dd')+30
union all
select '2011/07',sum(val)
from t
where dt between to_date('2011/06/16','yyyy/mm/dd')
  and to_date('2011/06/16','yyyy/mm/dd')+30)
/
MONTH     SUM_SAL
------- ----------
2011/04       150
2011/05       100
2011/06        10
2011/07        50
//关键问题是这么写太死板了，如果要统计连续20各月的数据呢？
//我们不是要写20个查询，然后union all呢？
//所以这个方法不可行，我们来看看更优的解法。
//
//解法二
//首先，我们应该得到一个时间区段，也就是将每个时间段分别求出来，
//然后再将于表进行比较，将在这个日期段之间的数据求和：
with ta as(
    select to_date('2011/03/16','yyyy/mm/dd')sdt,to_date('2011/09/15','yyyy/mm/dd') edt from dual)
select add_months(sdt,level-1) sdt,add_months(sdt,level)-1edt
from ta
connect by level<=months_between(edt,sdt)
SDT        EDT
----------- -----------
2011-03-16  2011-04-15
2011-04-16  2011-05-15
2011-05-16  2011-06-15
2011-06-16  2011-07-15
2011-07-16  2011-08-15
//这里还差一条数据，就是8.16-9.15这个日期段的，原因出在什么地方呢？
with ta as(
    select to_date('2011/03/16','yyyy/mm/dd')sdt,to_date('2011/09/15','yyyy/mm/dd') edt from dual)
select months_between(edt,sdt)
from ta
 
MONTHS_BETWEEN(EDT,SDT)
-----------------------
      5.96774193548387
//原来这里只是将两个日期进行了减法，并没有将其取整，
//我们知道，一个date类型与一个number类型值相加，结果为date类型，并且是以天数相加的，
//不足一天的将会转换为hh:mi:ss数据，但是这些我们并不关心，所以应该将这个数据向上取整：
CEIL(MONTHS_BETWEEN(EDT,SDT))
-----------------------------
                           6
/这样我们就得到了全部的日期区间：
SDT        EDT
----------- -----------
2011-03-16  2011-04-15
2011-04-16  2011-05-15
2011-05-16  2011-06-15
2011-06-16  2011-07-15
2011-07-16  2011-08-15
2011-08-16  2011-09-15
//接下来，我们通过日期比较，然后计算求和：
//也就是将test表中的日期与上面我们得到的日期区间进行比较，根据这个比较来求和：
with ta as(
    select to_date('2011/03/16','yyyy/mm/dd')sdt,to_date('2011/09/15','yyyy/mm/dd') edt from dual)
,tb as(
    selectadd_months(sdt,level-1) sdt,add_months(sdt,level)-1 edt
    fromta
    connect bylevel<=ceil(months_between(edt,sdt)))
select to_char(tb.edt,'yyyy/mm') month,
      nvl((select sum(val) from test where dt between tb.sdt andtb.edt),0) sum_sal
from tb
/
MONTH     SUM_SAL
------- ----------
2011/04       150
2011/05       100
2011/06        10
2011/07        50
2011/08         0
2011/09         0
//也可以将没有的数据去掉
select to_char(b.edt,'yyyy/mm') month,sum(val) sum_val
from test a,tb b
where a.dt between b.sdt and b.edt
group by to_char(b.edt,'yyyy/mm')
/
MONTH     SUM_VAL
------- ----------
2011/04       150
2011/05       100
2011/06        10
2011/07        50
//其实这个解法二与解法一是同样的道理，都是进行日期区间的比较；
//解法二的巧妙之处在于，它将起始日期和终止日期之间的月份区间转换为一张表，
//然后再将数据表test与此区间表进行比较，在日期区间的就进行求和val，最后得到结果
//
//解法三：
with t as(
    select to_date('2011/03/16','yyyy/mm/dd') dt,20 val from dual unionall
    select to_date('2011/03/17','yyyy/mm/dd'),30 from dual unionall
    select to_date('2011/04/14','yyyy/mm/dd'),50 from dual unionall
    select to_date('2011/04/15','yyyy/mm/dd'),50 from dual unionall
    select to_date('2011/04/16','yyyy/mm/dd'),10 from dual unionall
    select to_date('2011/04/17','yyyy/mm/dd'),20 from dual unionall
    select to_date('2011/05/14','yyyy/mm/dd'),30 from dual unionall
    select to_date('2011/05/15','yyyy/mm/dd'),40 from dual unionall
    select to_date('2011/05/20','yyyy/mm/dd'),10 from dual unionall
    select to_date('2011/06/17','yyyy/mm/dd'),20 from dual unionall
    select to_date('2011/06/18','yyyy/mm/dd'),30 from dual)
select to_char(add_months(to_date('2011/4/1','yyyy/mm/dd'),
      trunc(months_between(dt,to_date('2011/3/16','yyyy/mm/dd')))),
      'yyyy/mm') month,
      sum(val) sum_val
from t
group bytrunc(months_between(dt,to_date('2011/3/16','yyyy/mm/dd')))
order by month
/
MONTH     SUM_VAL
------- ----------
2011/04       150
2011/05       100
2011/06        10
2011/07        50
//获取日期dt与起始日期相隔的月份，然后将其取整，
//起始日期为(2011/03/16)，这样就保证了每隔一个月统计一次val
//也就是没到一个月的15号为一个月的统计终止日期
SELECT MONTHS_BETWEEN(DT,TO_DATE('2011/3/16','YYYY/MM/DD') FROMT
--------------
            0
0.032258064516
0.935483870967
0.967741935483
            1
1.032258064516
1.935483870967
1.967741935483
2.129032258064
3.032258064516
3.064516129032
//日期之间相隔的月份一般是整数，而我们得到的是小数，所以要将小数转换为整数
//而且我们的查询从2011/04/01开始，
SELECT TRUNC(MONTHS_BETWEEN(DT,TO_DATE('2011/3/16','YYYY/MM/DD')))FROM T
-----------
         0
         0
         0
         0
         1
         1
         1
         1
         2
         3
         3
//使用add_months函数将开始统计的月份4月加上月份相隔，
//并使用to_char函数进行日期格式的转换，这样就能够得到结果中的month部分
add_months(to_date('2011/4/1','yyyy/mm/dd'),
trunc(months_between(dt,to_date('2011/3/16','yyyy/mm/dd'))))
//此解法的关键：
//1.获取月份间隔并取整
//2.查询从04.01开始，足月增加，并截取yyyy/mm作为显示数据