全部累加彙總語法
SELECT ...
, Sum( column1 ) Over( Order by column2 ) as alias
FROM ...
分類累加彙總語法
SELECT ...
, Sum( column1 ) Over( partition by column2 Order by column3 ) as alias
FROM ...
範例
-- 建立 Temp Table
CREATE TABLE TOM_TEST (
EMPLOYEE_ID NUMBER
, DEPT_ID NUMBER
, MONEY NUMBER
);
-- 新增 Temp Data
INSERT INTO TOM_TEST VALUES( 1, 100, 10000 );
INSERT INTO TOM_TEST VALUES( 2, 100, 15000 );
INSERT INTO TOM_TEST VALUES( 3, 100, 20000 );
INSERT INTO TOM_TEST VALUES( 4, 200, 32000 );
INSERT INTO TOM_TEST VALUES( 5, 200, 40000 );
INSERT INTO TOM_TEST VALUES( 6, 200, 35000 );
INSERT INTO TOM_TEST VALUES( 7, 300, 30000 );
INSERT INTO TOM_TEST VALUES( 8, 300, 25000 );
INSERT INTO TOM_TEST VALUES( 9, 300, 10000 );
COMMIT;
-- 按 EMPLOYEE_ID 進行 MONEY 全部累加彙總
select EMPLOYEE_ID
, DEPT_ID
, MONEY
, sum(MONEY) over(order by EMPLOYEE_ID) as SUM
from TOM_TEST;
-- 以 DEPT_ID 分類, 按 EMPLOYEE_ID 進行 MONEY 分類累加彙總
select EMPLOYEE_ID
, DEPT_ID
, MONEY
, sum(MONEY) over(partition by DEPT_ID order by EMPLOYEE_ID) as SUM
from TOM_TEST;
-- 按 EMPLOYEE_ID 進行 MONEY 全部累加平均
select EMPLOYEE_ID
, DEPT_ID
, MONEY
, avg(MONEY) over(order by EMPLOYEE_ID) as AVG
from TOM_TEST;
-- 以 DEPT_ID 分類, 按 EMPLOYEE_ID 進行 MONEY 分類累加平均
select EMPLOYEE_ID
, DEPT_ID
, MONEY
, avg(MONEY) over(partition by DEPT_ID order by EMPLOYEE_ID) as AVG
from TOM_TEST;
-- 刪除 Temp Table
DROP TABLE TOM_TEST;
source: http://tomkuo139.blogspot.com
沒有留言:
張貼留言