Oracle分析函数实例

嵇娅尊

sum() over(...)...
1:

WITH t AS(
SELECT DATE'2010-01-03' 时间, 1 新增数量 FROM dual UNION ALL
SELECT DATE'2010-01-04' 时间, 3 新增数量 FROM dual UNION ALL
SELECT DATE'2010-01-05' 时间, 5 新增数量 FROM dual UNION ALL
SELECT DATE'2010-01-06' 时间, 7 新增数量 FROM dual UNION ALL
SELECT DATE'2010-01-07' 时间, 8 新增数量 FROM dual
)
SELECT * FROM t;
WITH t AS(
SELECT DATE'2010-01-03' 时间, 1 新增数量 FROM dual UNION ALL
SELECT DATE'2010-01-04' 时间, 3 新增数量 FROM dual UNION ALL
SELECT DATE'2010-01-05' 时间, 5 新增数量 FROM dual UNION ALL
SELECT DATE'2010-01-06' 时间, 7 新增数量 FROM dual UNION ALL
SELECT DATE'2010-01-07' 时间, 8 新增数量 FROM dual
)
SELECT t.时间,SUM(新增数量) over(ORDER BY 时间) 累计和 FROM t
WHERE t.时间 BETWEEN DATE'2010-01-03' AND DATE'2010-02-03';

2:

WITH t as(
SELECT 200405 BILL_MONTH,          '5761' AREA_CODE,       'G' NET_TYPE,              7393344.04 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200405 BILL_MONTH,          '5761' AREA_CODE,       'J' NET_TYPE,              5667089.85 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200405 BILL_MONTH,          '5762' AREA_CODE,       'G' NET_TYPE,              6315075.96 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200405 BILL_MONTH,          '5762' AREA_CODE,       'J' NET_TYPE,              6328716.15 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200405 BILL_MONTH,          '5763' AREA_CODE,       'G' NET_TYPE,              8861742.59 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200405 BILL_MONTH,          '5763' AREA_CODE,       'J' NET_TYPE,              7788036.32 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200405 BILL_MONTH,          '5764' AREA_CODE,       'G' NET_TYPE,              6028670.45 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200405 BILL_MONTH,          '5764' AREA_CODE,       'J' NET_TYPE,              6459121.49 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200405 BILL_MONTH,          '5765' AREA_CODE,       'G' NET_TYPE,             13156065.77 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200405 BILL_MONTH,          '5765' AREA_CODE,       'J' NET_TYPE,             11901671.70 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200406 BILL_MONTH,          '5761' AREA_CODE,       'G' NET_TYPE,              7614587.96 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200406 BILL_MONTH,          '5761' AREA_CODE,       'J' NET_TYPE,              5704343.05 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200406 BILL_MONTH,          '5762' AREA_CODE,       'G' NET_TYPE,              6556992.60 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200406 BILL_MONTH,          '5762' AREA_CODE,       'J' NET_TYPE,              6238068.05 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200406 BILL_MONTH,          '5763' AREA_CODE,       'G' NET_TYPE,              9130055.46 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200406 BILL_MONTH,          '5763' AREA_CODE,       'J' NET_TYPE,              7990460.25 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200406 BILL_MONTH,          '5764' AREA_CODE,       'G' NET_TYPE,              6387706.01 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200406 BILL_MONTH,          '5764' AREA_CODE,       'J' NET_TYPE,              6907481.66 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200406 BILL_MONTH,          '5765' AREA_CODE,       'G' NET_TYPE,             13562968.81 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200406 BILL_MONTH,          '5765' AREA_CODE,       'J' NET_TYPE,             12495492.50 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200407 BILL_MONTH,          '5761' AREA_CODE,       'G' NET_TYPE,              7987050.65 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200407 BILL_MONTH,          '5761' AREA_CODE,       'J' NET_TYPE,              5723215.28 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200407 BILL_MONTH,          '5762' AREA_CODE,       'G' NET_TYPE,              6833096.68 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200407 BILL_MONTH,          '5762' AREA_CODE,       'J' NET_TYPE,              6391201.44 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200407 BILL_MONTH,          '5763' AREA_CODE,       'G' NET_TYPE,              9410815.91 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200407 BILL_MONTH,          '5763' AREA_CODE,       'J' NET_TYPE,              8076677.41 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200407 BILL_MONTH,          '5764' AREA_CODE,       'G' NET_TYPE,              6456433.23 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200407 BILL_MONTH,          '5764' AREA_CODE,       'J' NET_TYPE,              6987660.53 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200407 BILL_MONTH,          '5765' AREA_CODE,       'G' NET_TYPE,             14000101.20 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200407 BILL_MONTH,          '5765' AREA_CODE,       'J' NET_TYPE,             12301780.20 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200408 BILL_MONTH,          '5761' AREA_CODE,       'G' NET_TYPE,              8085170.84 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200408 BILL_MONTH,          '5761' AREA_CODE,       'J' NET_TYPE,              6050611.37 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200408 BILL_MONTH,          '5762' AREA_CODE,       'G' NET_TYPE,              6854584.22 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200408 BILL_MONTH,          '5762' AREA_CODE,       'J' NET_TYPE,              6521884.50 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200408 BILL_MONTH,          '5763' AREA_CODE,       'G' NET_TYPE,              9468707.65 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200408 BILL_MONTH,          '5763' AREA_CODE,       'J' NET_TYPE,              8460049.43 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200408 BILL_MONTH,          '5764' AREA_CODE,       'G' NET_TYPE,              6587559.23 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200408 BILL_MONTH,          '5764' AREA_CODE,       'J' NET_TYPE,              7342135.86 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200408 BILL_MONTH,          '5765' AREA_CODE,       'G' NET_TYPE,             14450586.63 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200408 BILL_MONTH,          '5765' AREA_CODE,       'J' NET_TYPE,             12680052.38 LOCAL_FARE FROM DUAL
)
SELECT nvl(area_code,'合计') area_code ,SUM(local_fare) local_fare FROM t
GROUP BY rollup(nvl(area_code,'合计'));
SELECT area_code,SUM(local_fare) local_fare FROM t
GROUP BY area_code
UNION ALL
SELECT '合计' area_code,SUM(local_fare) local_fare FROM t;

WITH t AS (
SELECT 1 aa FROM dual UNION ALL
SELECT 2 aa FROM dual UNION ALL
SELECT 2 aa FROM dual UNION ALL
SELECT 2 aa FROM dual UNION ALL
SELECT 3 aa FROM dual UNION ALL
SELECT 4 aa FROM dual UNION ALL
SELECT 5 aa FROM dual UNION ALL
SELECT 6 aa FROM dual UNION ALL
SELECT 7 aa FROM dual UNION ALL
SELECT 9 aa FROM dual
)
SELECT aa,SUM(aa) over(ORDER BY aa) FROM t;
--SELECT aa,SUM(aa) over(ORDER BY aa RANGE BETWEEN 2 preceding AND 2 following) FROM t