Oracle Development topics: report functions

Reprint: Http://www.blogjava.net/pengpenglin/archive/2008/06/29/211462.html "original" Oracle Development topic: report function

Directory
=========================================
1. Introduction to report functions
2.ratio_to_report function

First, the report function introduction:

Reviewing the section on the full statistics in the previous Oracle Development topic: Window Functions , we used Oracle to provide:

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

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

Sql>SelectMonth,
2Sum(tot_sales) Month_sales,
3Sum(Sum(Tot_sales))Over(OrderByMonth
4RowsBetweenunbounded precedingandUnbounded following) Win_sales,
5Over()Rpt_sales
6FromOrders
7GroupByMonth;

MONTHMonth_sales Window_sales Report_sales
---------- ----------- ------------ ------------
161069763077666307766
242867663077666307766
363703163077666307766
454114663077666307766
559293563077666307766
650148563077666307766
760691463077666307766
846052063077666307766
939289863077666307766
1051011763077666307766
11 532889       6307766       6307766         12      492458      6307766      6307766

has selected 12 rows.

The empty parentheses of the over function indicate that all records of the recordset should be in the range of the statistics, if partition by is used, then the partitions are counted in turn.

Second, Ratio_to_report function:

Report function Special (window function) is particularly suitable for situations in which detailed data and statistics need to be displayed in the report. For example, this is often the case in sales reports: A list of sales totals per month for the previous year, year-end sales, and sales per month as a percentage of total annual sales:

Method ①:

SelectAll_sales.*,
100*Round(Cust_sales/Region_sales,2)||‘%‘Percent
From(SelectO.CUST_NBR Customer,
O.REGION_ID region,
Sum(o.tot_sales) Cust_sales,
Sum(Sum(O.tot_sales))Over(PartitionByO.REGION_ID) Region_sales
FromOrders_tmp o
whereO.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 ②:

Selectregion_id, salesperson_id,
Sum(tot_sales) Sp_sales,
/Sum(Sum(Tot_sales))
Over(PartitionByREGION_ID),2)  percent_of_region
  from orders
where year  = 2001
 group  by region_id, salesperson_id
 order by< Span style= "color: #000000;" > region_id, salesperson_id;

Method ③

Selectregion_id, salesperson_id,
Sum(tot_sales) Sp_sales,
Over(PartitionByREGION_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 sub-set.

Reference: "Mastering Oracle SQL" (by Alan Beaulieu, Sanjay Mishra O ' Reilly June 2004 0-596-00632-2)

Oracle Development topics: report functions