Oracle Development Report Function _oracle

first, review the previous Oracle Development window function in the section on the full statistics, we use Oracle-provided:

Copy Code code as follows:

SUM (SUM (tot_sales)) over (order by month rows between unbounded preceding and unbounded)

To count the total amount of orders for the whole year, this function is executed once for each record in the Recordset formation, and it executes 12 times in total. This is very time-consuming. In fact, we have a simpler way:

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) Win_sales,
sum (sum (tot_sales)) over () Rpt_sales
From Orders
Group BY Month;

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

12 rows have been selected.

The empty bracket of the over function indicates that all records of the recordset should be included in the range of statistics, and if partition by is used, then the partitions are then counted.

Second, the Ratio_to_report function:

The Report function Special (window function) is especially suitable for situations in a report that require both detailed and statistical data to be displayed. For example, this requirement often occurs in sales reports that list the total sales for each month of the previous year, the year-end sales, and the percentage of sales per month for the total annual sales:

Method ①:

Copy Code code as follows:

Select All_sales.*,
Round (Cust_sales/region_sales, 2) | | '% ' Percent
From (select O.cust_nbr Customer,
O.REGION_ID region,
SUM (o.tot_sales) Cust_sales,
SUM (SUM (o.tot_sales)) over (partition by o.region_id) region_sales
From Orders_tmp o
where O.year = 2001
Group by o.region_id, O.CUST_NBR) all_sales
where All_sales.cust_sales > All_sales.region_sales * 0.2;

This is a stupid method and the easiest way to understand it.

Method ②:

Copy Code code as follows:

Select region_id, salesperson_id,
SUM (tot_sales) Sp_sales,
round (SUM (tot_sales)/SUM (SUM (tot_sales))
Over (partition by region_id), 2) percent_of_region
From Orders
Where year = 2001
Group BY region_id, salesperson_id
Order by region_id, salesperson_id;

Method ③

Copy Code code as follows:

Select region_id, salesperson_id,
SUM (tot_sales) Sp_sales,
round (Ratio_to_report) (SUM (tot_sales))
Over (partition by region_id), 2) Sp_ratio
From Orders
Where year = 2001
Group BY region_id, salesperson_id
Order by region_id, salesperson_id;

The Ratio_to_report function provided by Oracle allows us to calculate the proportion of each record in its corresponding recordset or its child set.

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