ORACLE-over()函数用法

来源：互联网发布：东莞plc编程培训机构编辑：程序博客网时间：2024/05/21 07:08

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.007 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                19307 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                           19307 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                            8007 rows selected

以上按partition by annual_rate,invest_amount分组，再按order by annual_rate,invest_amount排序计算。

阅读全文

0 0