统计今天和今天之前的数量

问题：更具所给数据，统计当天的qty和当天之前的qty

解法：

with a as(
     select 'A' id, to_date('20110301','yyyymmdd') misdte, 2 qty from dual union all
     select 'A' id, to_date('20110302','yyyymmdd'), 1 from dual union all
     select 'A' id, to_date('20110303','yyyymmdd'), 4 from dual union all
     select 'A' id, to_date('20110302','yyyymmdd'), 1 from dual union all
     select 'A' id, to_date('20110304','yyyymmdd'), 2 from dual union all
     select 'A' id, to_date('20110305','yyyymmdd'), 7 from dual union all
     select 'A' id, to_date('20110302','yyyymmdd'), 1 from dual union all
     select 'A' id, to_date('20110305','yyyymmdd'), 2 from dual union all
     select 'A' id, to_date('20110305','yyyymmdd'), 2 from dual union all
     select 'B' id, to_date('20110301','yyyymmdd'), 3 from dual union all
     select 'B' id, to_date('20110301','yyyymmdd'), 9 from dual union all
     select 'B' id, to_date('20110301','yyyymmdd'), 3 from dual union all
     select 'B' id, to_date('20110303','yyyymmdd'), 17 from dual)
select id,
       misdte,
       sum_qty,
       lag(sum_qty1, 1, 0) over(partition by id order by id, misdte) sum_qty1
from (select id,
             misdte,
             sum_qty,
             sum(sum_qty) over(partition by id order by id, misdte) sum_qty1
      from (select id, misdte, sum(qty) sum_qty
            from a
            group by id, misdte
            order by 1, 2));
ID MISDTE         SUM_QTY   SUM_QTY1
-- ----------- ---------- ----------
A  2011-3-1             2          0
A  2011-3-2             3          2
A  2011-3-3             4          5
A  2011-3-4             2          9
A  2011-3-5            11         11
B  2011-3-1            15          0
B  2011-3-3            17         15

原帖：http://topic.csdn.net/u/20110327/15/6c3a2f34-6a5b-4e38-bb27-132b78d2ad31.html?88830