[ORACLE] 累加彙總 累加平均

全部累加彙總語法

SELECT ...
     , Sum( column1 ) Over( Order by column2 ) as alias
  FROM ...


 分類累加彙總語法

SELECT ...
     , Sum( column1 ) Over( partition by column2 Order by column3 ) as alias
  FROM ...




 範例
-- 建 Table
CREATE TABLE SUM_TEST (
  MEMBER_ID NUMBER
, DEPT_ID     NUMBER
, MONEY       NUMBER
);

-- 新增 Temp Data
INSERT INTO SUM_TEST VALUES( 1, 100, 10000 );
INSERT INTO SUM_TEST VALUES( 2, 100, 15000 );
INSERT INTO SUM_TEST VALUES( 3, 100, 20000 );
INSERT INTO SUM_TEST VALUES( 4, 200, 32000 );
INSERT INTO SUM_TEST VALUES( 5, 200, 40000 );
INSERT INTO SUM_TEST VALUES( 6, 200, 35000 );
INSERT INTO SUM_TEST VALUES( 7, 300, 30000 );
INSERT INTO SUM_TEST VALUES( 8, 300, 25000 );
INSERT INTO SUM_TEST VALUES( 9, 300, 10000 );
COMMIT;

-- 依 MEMBER_ID 進行 MONEY 全部累加總
select MEMBER_ID     , DEPT_ID     , MONEY
     , sum(MONEY) over(order by MEMBER_ID) as SUM
  from SUM_TEST;


-- 以 DEPT_ID 分類, 依 MEMBER_ID 進行 MONEY 分類累加總
select MEMBER_ID     , DEPT_ID     , MONEY
     , sum(MONEY) over(partition by DEPT_ID order by MEMBER_ID) as SUM
  from SUM_TEST;


-- 依 MEMBER_ID 進行 MONEY 全部累加平均
select MEMBER_ID     , DEPT_ID     , MONEY
     , avg(MONEY) over(order by MEMBER_ID) as AVG
  from SUM_TEST;


-- 以 DEPT_ID 分類, 依 MEMBER_ID 進行 MONEY 分類累加平均
select MEMBER_ID     , DEPT_ID     , MONEY
     , avg(MONEY) over(partition by DEPT_ID order by MEMBER_ID) as AVG
  from SUM_TEST;




http://tomkuo139.blogspot.tw/2009/04/plsql_30.html