ORACLE-over()函数用法
over()
SQL> select annual_rate, invest_amount, self_amt from busi_order_bak;
ANNUAL_RATE INVEST_AMOUNT SELF_AMT
----------- ----------------- -----------------
15.00 1000.00 200.00
15.00 1000.00 200.00
15.00 2000.00 800.00
5.00 200.00 500.00
5.00 100.00 10.00
5.00 100.00 20.00
5.00 100.00 200.00
7 rows selected
SQL> select sum(self_amt) from busi_order_bak;
SUM(SELF_AMT)
-------------
1930
SQL> select annual_rate, invest_amount, self_amt, sum(self_amt) over() from busi_order_bak;
ANNUAL_RATE INVEST_AMOUNT SELF_AMT SUM(SELF_AMT)OVER()
----------- ----------------- ----------------- -------------------
15.00 1000.00 200.00 1930
15.00 1000.00 200.00 1930
15.00 2000.00 800.00 1930
5.00 200.00 500.00 1930
5.00 100.00 10.00 1930
5.00 100.00 20.00 1930
5.00 100.00 200.00 1930
7 rows selected
以上,sum(self_amt) 等同于SUM(SELF_AMT)OVER(),不连续累加,求的是总和。
SQL> select annual_rate, invest_amount, self_amt, sum(self_amt) over(order by annual_rate,invest_amount,self_amt) from busi_order_bak;
ANNUAL_RATE INVEST_AMOUNT SELF_AMT SUM(SELF_AMT)OVER(ORDERBYANNUA
----------- ----------------- ----------------- ------------------------------
5.00 100.00 10.00 10
5.00 100.00 20.00 30
5.00 100.00 200.00 230
5.00 200.00 500.00 730
15.00 1000.00 200.00 1130
15.00 1000.00 200.00 1130
15.00 2000.00 800.00 1930
7 rows selected
以上按order by annual_rate,invest_amount,self_amt排序,over(order by annual_rate,invest_amount,self_amt)连续累加。
SQL> select annual_rate, invest_amount, self_amt, sum(self_amt) over(partition by annual_rate,invest_amount order by annual_rate,invest_amount) from busi_order_bak;
ANNUAL_RATE INVEST_AMOUNT SELF_AMT SUM(SELF_AMT)OVER(PARTITIONBYA
----------- ----------------- ----------------- ------------------------------
5.00 100.00 20.00 230
5.00 100.00 10.00 230
5.00 100.00 200.00 230
5.00 200.00 500.00 500
15.00 1000.00 200.00 400
15.00 1000.00 200.00 400
15.00 2000.00 800.00 800
7 rows selected
以上按partition by annual_rate,invest_amount分组,再按order by annual_rate,invest_amount排序计算。