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排序计算。