Oracle Window Function Basics

1. test environment:
SQL> create table win_order (
Month number (2 ),
Total_sales number );

2. Input data:
Insert into win_order values (1,623141 );
Insert into win_order values (2,423124 );
Insert into win_order values (3,323214 );
Insert into win_order values (4,212314 );
Insert into win_order values (5, 654314 );
Insert into win_order values (6, 12, 4 );
Insert into win_order values (7,859234 );
Insert into win_order values (8, 752314 );
Insert into win_order values (9,365314 );
Insert into win_order values (10, 265314 );
Insert into win_order values (11,563114 );
Insert into win_order values (12, 595314 );

3. Test statement:

We used sum (sal) over (partition by deptno) to calculate the total amount of each department. Now we want to count not only each department, but all partitions. partition by region_id does not work here.

Oracle provides a clause for this situation: rows between... preceding and... following. Literally, I guess it means that all records before and after XXX let us verify through examples:

SQL> select month,
Sum (total_sales) month_sales,
Sum (total_sales) over (order by month
Rows between unbounded preceding and unbounded following) total_sale
From win_order group by month;
 
MONTH MONTH_SALES TOTAL_SALE
--------------------------
1 623141 5758845
2 423124 5758845
3 323214 5758845
4 212314 5758845
5 654314 5758845
6 122134 5758845
7 859234 5758845
8 752314 5758845
9 365314 5758845
10 265314 5758845
11 563114 5758845
12 595314 5758845
 
12 rows selected

The highlighted code plays a key role here, which tells oracle to count the monthly sales from the first record to the last record. This statistics was executed 12 times during the formation of the record set, which is quite time-consuming! But at least we solved the problem.

Unbounded preceding and unbouned following indicate the first and last records of all current records, that is, all records in the table. So what if we directly specify from the first record to the end? Take a look at the following results:

SQL> select month,
Sum (total_sales) month_sales,
Sum (total_sales) over (order by month
Rows between 1 preceding and unbounded following) total_sale
From win_order group by month;

MONTH MONTH_SALES TOTAL_SALE
--------------------------
1 623141 5758845
2 423124 5758845
3 323214 5135704
4 212314 4712580
5 654314 4389366
6 122134 4177052
7 859234 3522738
8 752314 3400604
9 365314 2541370
10 265314 1789056
11 563114 1423742
12 595314 1158428
 
12 rows selected
Obviously, this statement is incorrect. Actually 1 does not start from 1st records, but refers to the previous record of the current record. The modifier before preceding is to tell the number of records referenced during Window Function execution, as if unbounded is to tell oracle no matter how many records are there, are included in the scope of statistics.

1 2 3 Next Page