Oracle Development window Function _oracle

Introduction of Window Function:

So far, the analytic functions we've learned are particularly useful for calculating/counting data over time, but what if the calculation/statistics needs to be done with every record of the recordset? For some examples:

① lists the total monthly orders and total orders for the whole year
② lists the total monthly orders and the total orders up to the current month
③ lists total orders for last month, month, next January, and total orders for the year
④ lists the daily turnover and total turnover in a week.
⑤ lists the daily turnover and average turnover per day in a week.

Looking back at the analytic function we introduced earlier, we will find that these requirements are different from the previous ones: the analysis function we described earlier is used to compute/count an explicit stage/Recordset, and there are some requirements, such as 2, that need to be counted as each record of the recordset is traversed.

That is to say: statistics not only happen once, but happen many times. Statistics do not occur after a recordset is formed, but rather occur during the formation of a recordset.

This is the application of the window function we are going to introduce this time. It applies to the following situations:

① by specifying a batch of records: For example, from the current record to the end of a part of the last record
② by specifying a time interval: for example, the first 30 days before the trading day
③ by specifying a range value: For example, all records that account for 5% of the current total volume

Second, window function examples-all statistics:

Here's what we need: Take a look at the application of the window function by listing the total orders for each month and the total order totals for the whole year.

"1" Test environment:

Copy Code code as follows:

sql> desc orders;

is the name empty? Type
----------------------- -------- ----------------
MONTH Number (2)
Tot_sales number

Sql>

"2" test data:

Copy Code code as follows:

Sql> select * from Orders;

MONTH Tot_sales
---------- ----------
1 610697
2 428676
3 637031
4 541146
5 592935
6 501485
7 606914
8 460520
9 392898
10 510117
11 532889
12 492458

12 rows have been selected.

"3" test statement:

Recalling the previous article on Oracle development topics: Analytic functions (over), we used sum (tot_sales) over (partition by region_id) to count the total orders for each partition. Now we're going to count not just every partition, but all partitions, partition by region_id doesn't work here.

Oracle provides a clause for this situation: rows between ... preceding and ... following. Literally guess what it means: all the records before XXX and after xxx, the actual situation lets us verify by example:

Copy Code code as follows:

Sql> Select Month,
SUM (tot_sales) Month_sales,
SUM (SUM (tot_sales)) over (order by month
rows between unbounded preceding and unbounded following) Total_sales
From Orders
Group BY Month;

MONTH Month_sales Total_sales
---------- ----------- -----------
1 610697 6307766
2 428676 6307766
3 637031 6307766
4 541146 6307766
5 592935 6307766
6 501485 6307766
7 606914 6307766
8 460520 6307766
9 392898 6307766
10 510117 6307766
11 532889 6307766
12 492458 6307766

12 rows have been selected.

The green highlighting code plays a key role here, telling Oracle to count the monthly sales from the first record to the last record. This statistic was executed 12 times during the formation of the recordset, which is quite time-consuming! But at least we solved the problem.

The meaning of unbounded preceding and unbouned following is for the previous and last records of all current records, that is, all records in the table. So what if we just specify from the first record to the end? Look at the following results:

Copy Code code as follows:

Sql> Select Month,
SUM (tot_sales) Month_sales,
SUM (SUM (tot_sales)) over (order by month
Rows between 1 preceding and unbounded following) All_sales
From Orders
Group BY Month;

MONTH Month_sales All_sales
---------- ----------- ----------
1 610697 6307766
2 428676 6307766
3 637031 5697069
4 541146 5268393
5 592935 4631362
6 501485 4090216
7 606914 3497281
8 460520 2995796
9 392898 2388882
10 510117 1928362
11 532889 1535464
12 492458 1025347

12 rows have been selected.

It is obvious that the statement was wrong. The actual 1 here is not the beginning of the 1th record, but the previous record of the current record. The modifier before the preceding is the number of records to tell the window function to execute, as unbounded tells Oracle that regardless of the current record is the first few, as long as the number of previous records, are included in the scope of the statistics.

Third, the window function advanced-scrolling statistics (cumulative/mean):

Consider the 2nd requirement mentioned earlier: list the total monthly orders and the total orders up to the current month. That means the February record will show the sum of the total orders for the month and the sum of 1, February orders. March to show the sum of the total orders for the month and the sum of 1, 2, March orders, and so on.

It is clear that this requirement needs to be counted on the sum of the total orders for the nth month. Think of the above statement, if only we could replace and unbounded following with the logic representing the current month! Luckily Oracle took our needs into account, so we just need to change the statement a little: curreent row is ok.

Copy Code code as follows:

Sql> Select Month,
SUM (tot_sales) Month_sales,
SUM (SUM (tot_sales)) over (order by month
rows between unbounded precedingand current row) Current_total_sales
From Orders
Group BY Month;

MONTH Month_sales Current_total_sales
---------- ----------- -------------------
1 610697 610697
2 428676 1039373
3 637031 1676404
4 541146 2217550
5 592935 2810485
6 501485 3311970
7 606914 3918884
8 460520 4379404
9 392898 4772302
10 510117 5282419
11 532889 5815308
12 492458 6307766

12 rows have been selected.

Now we can get the total amount of rolling sales! The following statistic looks more perfect, and it shows all the data we need:

Copy Code code as follows:

Sql> Select Month,
SUM (tot_sales) Month_sales,
SUM (SUM (tot_sales)) over (order by month
Rows between unbounded preceding and current row) Current_total_sales,
SUM (SUM (tot_sales)) over (order by month
Rows between unbounded preceding and unbounded following) total_sales
From Orders
Group BY Month;

MONTH month_sales current_total_sales Total_sales
---------- ----------- ------------------- -----------
1 610697 610697 6307766
2 428676 1039373 6307766
3 637031 1676404 6307766
4 541146 2217550 6307766
5 592935 2810485 6307766
6 501485 3311970 6307766
7 606914 3918884 6307766
8 460520 4379404 6307766
9 392898 4772302 6307766
10 510117 5282419 6307766
11 532889 5815308 6307766
12 492458 6307766 6307766

12 rows have been selected.

In some sales reports we often see the need for averages, sometimes for the year's data, and sometimes for all data up to the present. It's simple, just want to:
SUM (SUM (tot_sales)) is replaced by AVG (SUM (tot_sales)).

Four, the window function advanced-according to the time range statistics:

As we said earlier, window functions not only apply to specifying a recordset for statistics, but also apply to a specified range of statistics, such as the following SQL statement that counts the day's sales and evaluates sales within five days:

Copy Code code as follows:

Select Trunc (ORDER_DT) Day,
SUM (sale_price) Daily_sales,
AVG (SUM (sale_price)) over (order by trunc (ORDER_DT)
range between interval ' 2 ' day preceding
and interval ' 2 ' day following) Five_day_avg
From Cust_order
Where Sale_price is not null
and Order_dt between To_date (' 01-jul-2001 ', ' dd-mon-yyyy ')
and To_date (' 31-jul-2001 ', ' dd-mon-yyyy ')

To count the specified range, Oracle uses the keyword range, interval to specify a range. The above example tells Oracle to find the first 2 days of the current date, the 2-day range of records, and to count their sales averages.

Five, the window function Advanced-first_value/last_value:

Oracle provides 2 additional functions: First_value, Last_value, for finding the first and last records in a window recordset. Let's say our report needs to show sales for the current month, one months, one months, and average sales every 3 months, and these two functions can be useful.

Copy Code code as follows:

Select Month,
First_value (SUM (tot_sales)) over (order by month
Rows between 1 preceding and 1 following) Prev_month,
SUM (tot_sales) Monthly_sales,
Last_value (SUM (tot_sales)) over (order by month
Rows between 1 preceding and 1 following) Next_month,
AVG (SUM (tot_sales)) over (order by month
rows between 1 preceding and 1 following) Rolling_avg
From Orders
Where year = 2001
and region_id = 6
GROUP BY month
Order BY month;

First, let's look at the following: rows between 1 preceding and 1 following tell Oracle to look up and count in the previous and last sections of the current record, and First_value and Last_value in the 3 records to find the first one, The third record, so we can easily get the next three months of sales records and averages!

Six, window function advanced-compare adjacent records:

Through the fifth part of the study, we know how to use the window function to display adjacent records, now if we want to show each month's sales and last month's sales, what should be done?

From the fifth part of the introduction we can know that the use of First_value (Tot_sales) over (order by month rows between 1 preceding and 0 following)) can be done, in fact oracl E There's a simpler way to compare 2 records, which is the lag function.

The LEG function is similar to the preceding and following clauses, which can be applied to the relative position of the current record and is particularly useful when comparing two contiguous records in the same contiguous recordset.

Copy Code code as follows:

Select Month,
SUM (tot_sales) Monthly_sales,
Lag (sum (tot_sales), 1) over (order by month) Prev_month_sales
From Orders
Where year = 2001
and region_id = 6
GROUP BY month
Order BY month;

1 of Lag (sum (tot_sales), 1) is based on January.

The above is the Oracle window function usage All content, hoped can give everybody a reference, also hoped that everybody supports the cloud habitat community.