sum(sal) over (partition by deptno) --按照不同的部門分組，對部門自身的員工工資連續求和
partition by 是起著分組的作用
二、row_number over
row_number() over ([partition by col1] order by col2) ) as 別名
表示根據col1分組，在分組內部根據 col2排序
而這個“別名”的值就表示每組內部排序後的順序編號（組內連續的唯一的）,[partition by col1] 可省略
row_number() over 按照partion by 分組，然後在每組內部按照 open_date 排序 得到返回的是內部排序的順序編號
SELECT t.family_id,
t.package_id,
t.phone,
t.reason,
t.open_date,
ROW_NUMBER ()
OVER (PARTITION BY family_id, package_id, phone
ORDER BY open_date DESC)
rn
FROM zj_transaction_log t
WHERE TO_CHAR (t.open_date, 'yyyy-mm') >= '2011-03';
SELECT t.family_id,
t.package_id,
t.phone,
t.reason,
t.open_date,
ROW_NUMBER ()
OVER (PARTITION BY family_id, package_id, phone
ORDER BY package_id DESC)
rn
FROM zj_transaction_log t
WHERE TO_CHAR (t.open_date, 'yyyy-mm') >= '2011-03';
SELECT t.family_id,
t.package_id,
t.phone,
t.open_date,
t.reason,
COUNT( * )
OVER (PARTITION BY family_id, package_id, phone)
rn
FROM zj_transaction_log t
WHERE TO_CHAR (t.open_date, 'yyyy-mm') >= '2011-03';
SELECT t.family_id,
t.package_id,
t.phone,
t.open_date,
t.reason,
COUNT( * )
OVER (PARTITION BY family_id, package_id, phone
order by open_date)
rn
FROM zj_transaction_log t
WHERE TO_CHAR (t.open_date, 'yyyy-mm') >= '2011-03';
SELECT t.family_id,
t.package_id,
t.phone,
t.open_date,
t.reason,
COUNT( * )
OVER (PARTITION BY family_id, package_id, phone
order by package_id)
rn
FROM zj_transaction_log t
WHERE TO_CHAR (t.open_date, 'yyyy-mm') >= '2011-03';
SELECT t.family_id,
t.package_id,
t.phone,
t.reason,
COUNT( * )
OVER (PARTITION BY family_id, package_id, phone
ORDER BY open_date DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
rn
FROM zj_transaction_log t
WHERE TO_CHAR (t.open_date, 'yyyy-mm') >= '2011-03';
SELECT t.family_id,
t.package_id,
t.phone,
t.reason,
COUNT( * )
OVER (PARTITION BY family_id, package_id, phone
ORDER BY package_id DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
rn
FROM zj_transaction_log t
WHERE TO_CHAR (t.open_date, 'yyyy-mm') >= '2011-03';
rank 的分析功能文法：
RANK ( ) OVER ( [query_partition_clause] order_by_clause )
rank 的合計功能文法：
RANK ( expr [, expr]... ) WITHIN GROUP
( ORDER BY
expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
[, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...
)
對於分析功能，
SELECT a.*,RANK() OVER(PARTITION BY col2 ORDER BY col1) "Rank" FROM table a;
已col2分組col1排序，rank與dense_rank用法相當，區別如下：
例如：當rank時為：
SELECT f.phone, f.student_name,
RANK () OVER (PARTITION BY f.phone ORDER BY f.student_name) cnt
FROM zs_family f
WHERE NVL (f.is_test, 0) <> 1
AND LENGTH (f.phone) = 11
AND f.phone NOT LIKE '%0%'
而如果用dense_rank時為：
SELECT f.phone, f.student_name,
dense_RANK () OVER (PARTITION BY f.phone ORDER BY f.student_name) cnt
FROM zs_family f
WHERE NVL (f.is_test, 0) <> 1
AND LENGTH (f.phone) = 11
AND f.phone NOT LIKE '%0%'
SELECT RANK ('********188','陳雅琳') within group (ORDER BY f.phone,f.student_name) cnt
FROM zs_family f
WHERE NVL (f.is_test, 0) <> 1
AND LENGTH (f.phone) = 11
AND f.phone NOT LIKE '%0%';
cnt
258
感覺就是查詢定位該記錄的在表中位置
五、max\min over
功能：產生2列獲得欄位最大值、最小值
例如：
查詢員工資訊與其中工資最高最低員工 select ename,sal,max(sal) over(), min(sal) over() from emp;
六、lag\lead over
功能：
得到當前行上一行或者下一行的資料
例子：
要求用一個SQL語句，統計每個月及上月和下月的訂單量
要求列表輸出為
月份 當月訂單量 上月訂單量 下月訂單量
select to_char(d.update_time, 'yyyymm'),
count(code),
lag(count(code)) over(order by count(code)) 上月訂單量,
lead(count(code)) over(order by count(code)) 下有訂單量
from V_NWRZ_dwxx_TEST2 d
group by to_char(d.update_time, 'yyyymm');
