http://www.akadia.com/services/ora_analytic_functions.html
The cold is frozen, staying at home and reading "Mastering Oracle SQL" 2nd, found that Oracle's function is still very powerful, there are two hundred optical functions, HSQL is difficult. The next hard bone, it seems to take the hibernate3.0 SQL mapping function, or you will run the JDBC to assemble VO.
1. Sales report for ROLLUP functions for report total report
Guangzhou January 2000 yuan
Guangzhou February 2500 yuan
Guangzhou 4,500 yuan
Shenzhen January 1000 yuan
Shenzhen February 2000 yuan
Shenzhen 3,000 yuan
7500 yuan in all regions
Previous query SQL:
SELECT Area, Month, Sum (Money) from SaleOrder Group by Area, Month
Then Guangzhou, Shenzhen's total and all regions need to be accumulated in the program.
1. In fact, you can use SQL:
Select Area, Month, Sum (Total_sale) from SaleOrder Group By Rollup (Area, Month) can generate an exact same record
2. If Year doesn't want to be accumulated, you can write
Select Year, Month, Area, Sum (Total_Sale) from sales by Year, Rollup (Month, Area) In addition, Oracle 9i also supports the following syntax:
Select Year, Month, Area, Sum (Total_Sale) from Saleorder Group by Rollup (Year, Month), Area
3. If you use Cube (Area, Month) instead of Rollup (Area, Month), in addition to getting the total of each region, it will also get a total of each month, and finally displayed in the report.
4.Grouping makes the column better reading
Rollup is shown as NULL when showing Guangzhou, but the better approach should be displayed as "all months"
Grouping is used to determine if the current column is a total column, 1 is YES, then use decode to "all months"
Select Decode (GROPING (Area), 1, 'All Regions', Area) Area, Decode (Grouping (Month), 1,' All Month, Month, SUM (MONEY) from SaleOrder Group by Rollup (Area, Month) ;
2. START with ..... Connect by multi-level level query .....
Such as personnel organizations, product categories, Oracle provides a very classic method
SELECT Level, Name, EMP_ID, Manager_emp_id from Employee Start with Manager_emp_id is null connection by prior EMP_ID = Manager_Emp_ID;
The above statement Demo has all applications, Start With indicates where to start traversing trees, if it starts from the root, then its manager should be null, if you start from a staff, you can write EMP_ID = '11 '
Connect By is to specify the father and child relationship, pay attention to the PRIOR location
There is also a Level column that displays the hierarchy of nodes.
3. More report / analysis decision function
3.1 Basic structure of analysis function
Analytical function () over (Partion clause, Order By clause, window clause) is very difficult to explain, or use examples to speak.
3.2 Row_Number and Rank, Dense_Rank
Reports for selecting TOP 3 SALES
When two salespersons may have the same performance, they should use Rank and Dense_Rank.
such as
Rownum Rank Dense_rank
Zhang San 4000 yuan 1 1 1
Li Si 3000 yuan 2 2 2
Money 5 2000 yuan 3 3 3
Sun 6 2000 yuan 4 3 3
Ding 7 1000 yuan 5 5 4
At this time, you should choose the third money five and Sun Six, so use the Ranking function than ROWNUMBER insurance. As for DESNSE, it will be apparent.
SELECT SALESPERSON_ID, SUM (Tot_sales) sp_sales, rank () over (ORDER BY SUM (Tot_Sales) DESC) SALES_RANK from ORDERS GROUP BY SALESPERSON_ID
3.3 NTILE to divide the record into a granary base
For example, I want to get the previous 25% record, or treat 25% of the record as the same Level equally, treat another 25% as another Level equality
Select Cust_nbr, Sum (Tot_sales) Cust_Sales, NTILE (4) OVER (ORDER BY SUM (Tot_sales) DESC) SALES_QUARTILE from ORDERS GROUP BY CUST_NBR ORDER BY 3, 2 DESC; NTITLE (4) Sort by SUM (Tot_sales) Part.
3.4 Auxiliary Analysis Levels and Windows Function
In addition to basic fact data, the report always hopes that there are many annual sales, and the cumulative sales so far will be referred to in the average sales of three months before and after.
The average of this three months and the cumulative sales so far are called Windows Function, see the following example
SELECT MONTH, SUM (Tot_sales) Monthly_Sales, Sum (Sum (Tot_sales) over (ORDER BY MONTH ROWS BETWEEN UNBOUNDEDED) MAX_PRECEEDING from ORDERS GROUP BY MONTH ORDER BY MONTH;
SELECT MONTH, SUM (Tot_sales) Monthly_Sales, AVG (Sum (Tot_sales) over (ORDER BY MONTH ROWS BETWEEN 1 Preceding and 1 FOLLOWING) ROLLING_AVG from ORDERS GROUP BY MONTH ORDER BY MONTH;
The key to Windows Function is several values of the Windows clause
1 record before PRECEDING
1 Record after FOLLOWG
All records before unbounded preceding
Current Row Current Record
4.Subquery Summary
SubQuery is used every day, theoretically summarizes. Subquery is divided into three
1.NoncorRelated child query the most common style.
2. Correlated Subqueries inside the Laracle of the Father Query to the subquery, and I understand half a day when CYT taught me.
3.INline View is also used as the most common style. Then NoncorRelated subquery has three situations.
1. Return a row of a row where price <(Select Max (Price) from Goods)
2. Return Multi-Bank WHERE PRICE> = All (Select Price from Goods Where Type = 2)
or where not price The most commonly used IN is actually = any () 3. Return to multi-line multi-column returns multiple columns Of course, save query time UPDATE monthly_orders SET (tot_orders, max_order_amt) = (SELECT COUNT (*), MAX (sale_price) FROM cust_order) DELETE FROM line_item WHERE (order_nbr, part_nbr) IN (SELECT order_nbr, part_nbr FROM cust_order c) ******** *********************************************************** ***************************************************************************************************************************************************************************************************************** Oracle Analysis Function Using Oracle Analysis Functions Use a new concept introduced by Oracle816 to analyze data for our analysis of data A simple and efficient way of handling. Before the analysis function, we must use the self-contained query, child query, or the interlaced statement, even complex stored procedures, now as long as a simple SQL statement can be implemented, Moreover, there is a considerable improvement in terms of performing efficiency. I will do some specific instructions for the analysis function. Today I mainly introduce you to the following functions 1. Auto Summary Function ROLLUP, CUBE, 2. Rank function , Rank, Dense_Rank, Row_Number3. LAG, Lead Function 4. Sum, AVG, Moving, Moving Average 5. Ratio_to_Report Report Processing Function 6. First, Last Take the Basic Data Code: [Copy to CLIPBOARD] 06:34:23 SQL> SELECT * FROM T; BILL_MONTH area_code net_type local_fare ---------------------------------------------------------------------------------------------------------------------------------------- --- 200405 5761 G 7393344.04200405 5761 J 5667089.85200405 5762 G 6315075.96200405 5762 J 6328716.15200405 5763 G 8861742.59200405 5763 J 7788036.32200405 5764 G 6028670.45200405 5764 J 6459121.49200405 5765 G 13156065.77200405 5765 J 11901671.70200406 5761 G 7614587.96200406 5761 J 5704343.05200406 5762 G 6556992.60200406 5762 J 6238068.05200406 5763 G 9130055.46200406 5763 J7990460.25200406 5764 G 6387706.01200406 5764 J 6907481.66200406 5765 G 13562968.81200406 5765 J 12495492.50200407 5761 G 7987050.65200407 5761 J 5723215.28200407 5762 G 6833096.68200407 5762 J 6391201.44200407 5763 G 9410815.91200407 5763 J 8076677.41200407 5764 G 6456433.23200407 5764 J 6987660.53200407 5765 G 14000101.20200407 5765 J 12301780.20200408 5761 G 8085170.84200408 5761 J 6050611.37200408 5762 G 6854584.22200408 5762 J 6521884.50200408 5763 G 9468707.65200408 5763 J 8460049.43200408 5764 G 6587559.23BILL_MONTH AREA_CODE NET_TYPE LOCAL_FARE --------------- ---------- ----- -------------------- 200408 5764 J7342135.86200408 5765 G 14450586.63200408 5765 J 12680052.3840 rows selected.ELAPSED: 00: 00: 00.001. Introduction to ROLLUP functions Quote: Here is an example of collecting data directly using a normal SQL statement 06:41:36 SQL> Set autot ON06: 43: 36 SQL> SELECT Area_code, SUM (local_fare) local_fare06: 43: 50 2 from T06: 43: 51 3 Group by Area_code06: 43: 57 4 Union AlL06: 44: 00 5 Select 'Total' Area_code, SUM (Local_Fare) local_fare06: 44: 06 6 from T06: 44: 08 7 / Area_code local_fare ------- --- -------------- 5761 54,225,413.045762 52,039,619.605763 69,186,545.025764 53,156,768.465765 104,548,719.19 total 333157065.316 rows selected.Elapsed: 00: 00: 00.03Execution Plan ------------- --------------------------------------------- 0 SELECT Statement Optimizer = All_rows (COST = 7 card = 1310 bytes = 24884) 1 0 Union-all 2 1 sort (group by) (COST = 5 card = 1309 bytes = 24871) 3 2 Table access (full) of 't' (COST = 2 Card = 1309 bytes = 248 71) 4 1 Sort (aggregate) 5 4 table access (full) of 't' (COST = 2 card = 1309 BYtes = 170 17) statistics ------------ --------------------------- ------------------ 0 Recursive Calls 0 DB Block Gets 6 Consistent Gets 0 Physical Reads 0 Redo Size 561 Bytes Sent Via SQL * Net To Client 503 BYtes Received VIA SQL * Net from Client 2 SQL * NET ROUNDTRIPS TO / FROM Client 1 Sorts (Memory) 0 Sorts (Disk) 6 Rows Processed The following is an example of summary data that uses the analysis function Rollup 06:44:09 SQL> SELECT NVL (Area_code, 'Total') Area_code, SUM (local_fare) local_fare06: 45: 26 2 from T06: 45: 30 3 Group by Rollup (NVL (Area_code, 'Total) )) 06:45:50 4 / AREA_CODE LOCAL_FARE ---------- -------------- 5761 54225413.045762 52039619.605763 69186545.025764 53156768.465765 104548719.19 333157065.316 rows selected.Elapsed: 00:00 00.00EXECUTION Plan -------------------------------------------------------------------------------------------------------------------------- ------------ 0 Select Statement Optimizer = AllTes = 24871) 1 0 Sort (Group by Rollup) (COST = 5 Card = 1309 BYtes = 24871) 2 1 Table Access (Full) of 'T' (COST = 2 Card = 1309 BYtes = 24871) Statistics ----------------------------------------------------------------------------------------- ---------------------------- 0 Recursive Calls 0 DB Block Gets 4 Consistent Gets 0 Physical Reads 0 Redo Size 557 Bytes Sent Via SQL * Net to Client 503 BYTES RECEIVED VIA SQL * NET from Clom Clism 2 SQL * NET ROUNDTRIPS TO / FROM Client 1 Sorts (Memory) 0 Sorts (Disk) 6 Rows Processed From the above example. We are not difficult to see the ROLLUP function, The system's SQL statement is simpler, and the consumption has less resource, which is reduced to 4 consistent gets from 6 consistent gets. If the base table is large, the result can be imagined. 1. Introduction to the Cube function Quote: To introduce the Cube function Let's take a look at another example of using rollup 06:53:00 SQL> SELECT Area_code, BILL_MONTH, SUM (local_fare) local_fare06: 53: 37 2 from T06: 53: 38 3 Group By Rollup (Area_code, Bill_Month 06:53:49 4 / Area_code bill_month local_fare ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------ 5761200405 13,060,433.895761 13,318,931.015761 200 406 200 407 13,710,265.935761 200408 14,135,782.215761 54,225,413.045762 200405 12,643,792.115762 200 406 200 407 12,795,060.655762 13,224,298.125762 200408 13,376,468.725762 52,039,619.605763 200405 16,649,778.915763 200 406 200 407 17,120,515.715763 17,487,493.325763 200408 17,928,757.085763 69,186,545.025764 13,295,187.675764 200 407 134 440 200 406 200405 12,487,791.945764 93.765764 200408 13929695.095764 53156768.465765 200405 25057737.475765 200406 26058461.315765 200407 26301881.405765 200408 27130639.015765 104548719.19 333157065.3126 rows selected.Elapsed: 00: 00: 00.00 system based only on data from the first parameter area_code rollup of the result set to do a summary of treatment, but did not do a summary of bill_month Analysis, the Cube function is designed for this. Below, let's take a look at the results of using the Cube function 06:58:02 SQL> select area_code, bill_month, sum (local_fare) local_fare06: 58: 30 2 from t06: 58: 32 3 group by cube (area_code, bill_month) 06:58:42 4 order by area_code, bill_month nulls last06: 58: 57 5 / AREA_CODE BILL_MONTH LOCAL_FARE ---------- ------------------------------ 5761 200405 13060.435761 200406 13318.935761 200408 14135.785761 54225.415762 12643.795762 200406 200405 200408 13376.475762 52039.625763 13224.305762 12795.065762 200 407 200 406 200405 16649.785763 17120.525763 17487.495763 200408 200407 200405 12487.795764 17928.765763 69186.545764 13295.195764 200 407 200 406 13444.095764 200408 13929.695764 53156.775765 200405 25057.745765 200406 26058.465765 200407 26301.885765 200408 27130.645765 104548.72 200405 79899.53 200406 82588.15 200407 84168.03 200408 86501.34 333157.0530 rows selected.Elapsed: 00: 00: 00.01 can be seen in the output cube function than with a rollup more than a few lines Statistical data. This is the Cube function, according to Bill_Month, the summary of the summary status 1 Rollup and Cube functions Quote: From the above results, we can easily find that each statistic has a null, how do we distinguish between the summary of that field, at this time, Oracle's grouping function debut. If the current The summary record is to use this field, the grouping function will return 1, otherwise returns 0 1 Select decode (Grouping (Area_code), 1, 'All Area', TO_CHAR (Area_code)) Area_code, 2 decode (Grouping (Bill_Mont) , 1, 'All Month', BILL_MONTH, 3 SUM (local_fare) local_fare 4 from T 5 Group by Cube (Area_code, Bill_Month) 6 * ORDER BY Area_code, BILL_MONTH NULLS Last07: 07: 29 SQL> / Area_code bill_MONTH LOCAL_FARE -------------------------------------- 5761 200405 13060.435761 200406 133710.275761 200408 14135.785761 all month 54225.415762 200405 12643.795762 200406 12795.065762 200407 13224.305762 200408 13376.475762 all month 52039.625763 200405 16649.785763 200406 17120.525763 200407 17487.495763 200408 17928.765763 all month 69186.545764 200405 12487.795764 200406 13295.195764 200407 13444.095764 200408 13929.695764 all month 53156.775765 200405 25057.745765 200406 26058.465765 2004 07 26301.885765 200408 27130.645765 all month 104548.72all area 200405 79899.53all area 200406 82588.15all area 200407 84168.03all area 200408 86501.34all area all month 333157.0530 rows selected.Elapsed: 00: 00: 00.0107: 07: 31 SQL> you can see, all of Null value now makes a good distinction based on the grouping function, so that we can use the Rollup, Cube and Grouping functions, we can easily and more. 2. The introduction of the Rank function introduces the use of Rollup and Cube functions Let's take a look at how the Rank series function is used. Question 2. I want to find out the ranking of total calls in various regions in these months. Quote: In order to display the difference between the Rank, Dense_rank, the Row_Number function, we make some modifications to existing basic data, change 5763 data to the same data as 5761. 1 Update T T1 set local_fare = (2 Select local_fare from t2 3 WHERE T1.BILL_MONTH = T2.BILL_MONTH 4 and T1.NET_TYPE = T2.NET_TYPE 5 and T2.Area_code = '5761' 6 *) Where area_code = '5763'07: 19: 18 SQL> / 8 rows updated.elapsed: 00: 00:00.01 Let's use the Rank function to calculate the ranks ranked by all regions .07: 34: 19 SQL> SELECT Area_code, SUM (Local_Fare) Local_Fare, 07: 35: 25 2 Rank () over (Order By Sum (Local_Fare DESC) FARE_RANK07: 35: 44 3 from T07: 35: 45 4 Group by Area_codee07: 35: 50 507: 35: 52 SQL> SELECT Area_code, SUM (Local_Fare) Local_Fare, 07: 36: 02 2 Rank () over ( ORDER BY SUM (Local_Fare) DESC) FARE_RANK07: 36: 20 3 from T07: 36: 21 4 Group by Area_code07: 36: 25 5 / Area_code local_fare far fare_rank ---------- ------- --------------------------------4225.41 25763 54225.41 2 5764 53156.77 4 5762 52039.62 5ELAPSED: 00: 00: 00: 00:00.01 we can see that the red label appears, hopping, ranking 3 did not appear below let's see the result of Dense_rank queries .07: 36: 26 SQL> Select area_code, sum (local_fare) local_fare, 07: 39: 16 2 dense_rank () over (Order by Sum (Local_Fare) DESC) FARE_RANK07: 39: 39 3 from T07: 39: 42 4 Group by Area_code07: 39: 46 5 / Area_code local_fare far fare_rank ---------- -------------- ---------- 5765 104548.72 15763 54225.41 25764 53156.77 3 This is here The third place 5762 52039.62 4ELAPSED: 00: 00: 00.00 In this example, a third place appeared, this is the difference between rank and dense_rank, if two identical data appears, then the subsequent data will jump directly After this ranking, and Dense_rank will not, the difference is that the row_number even is exactly the same, the ranking will not be the same, this feature is very big when we want to find a unique record corresponding to the condition. use 1 select area_code, sum (local_fare) local_fare, 2 row_number () over (order by sum (local_fare) desc) fare_rank 3 from t 4 * group by area_code07: 44: 50 SQL> / aREA_CODE LOCAL_FARE FARE_RANK ------ ---- -------------- ---------- 5765 104548.72 15761 54225.41 35764 53156.77 45762 52039.62 5 In the row_nubmer function, we found that even SUM (local_fare) is exactly the same, we have got a different ranking, we can use this feature to eliminate repetitive records in the database. Several examples in this post are to illustrate this The basic usage of the three functions. We will introduce some of their usage .2 .2. Introduction to the Rank function a. Remove the N users in the database in the database SELECT User_ID, Tele_Num, User_Name, User_status, create_date from (SELECT) user_id, tele_num, user_name, user_status, create_date, rank () over (order by create_date desc) add_rank from user_info) where add_rank <=: n; b according to repeat recording object_name delete database create table t as select obj #, name. From sys.obj $; reinsert INTO T1 Select * from T1 several times. Delete from T1 Where Rowid in (SELECT ROWID ROW_ID, ROW_NUMBER () over (Partition by Obj # Order by RowID) Where RN < > 1); c. Remove the call income in each region ranked. SQL> select bill_month, area_code, sum (local_fare) local_fare, 2 rank () over (partition by bill_month order by sum (local_fare) desc) area_rank 3 from t 4 group by bill_month, area_code 5 / BILL_MONTH AREA_CODE LOCAL_FARE AREA_RANK ------ ------------------------------------------------ 200405 5765 12,004,055,761 22,004,055,763 25057.74 13060.43 12643.79 13060.43 22,004,055,762 52,004,065,765 42,004,055,764 12487.79 13318.93 26058.46 12,004,065,761 22,004,065,764 22,004,065,763 13318.93 12795.06 13295.19 42,004,065,762 12,004,075,761 52,004,075,765 26301.88 13710.27 2200407 5763 13710.27 22004.09 4200407 5762 13224.30 5200408 5765 27130.64 1200408 5761 14135.78 2200408 5763 14135.78 2200408 5764 13929.69 4200408 5762 13376.47 520 rows selected.sql> 3. LAG and LEAD function introduction to take out each month of last month and next month's Tote Total 1 SELECT Area_code, BILL_MONTH, LOCAL_FARE CUR_LOCAL_FARE, 2 LAG (local_fare, 2,0) over (Partition by Area_code Order Bill_Mont) pre_local_fare , 3 lag (local_fare, 1,0) over (partition by area_code order by bill_month) last_local_fare, 4 lead (local_fare, 1,0) over (partition by area_code order by bill_month) next_local_fare, 5 lead (local_fare, 2,0) OVER (Partition by Area_code Order by Bill_Mont) Post_local_fare 6 from (7 Select Area_code, Bill_Month, Sum (Local_Fare) local_fare 8 from t 9 group by Area_code, BILL_MONTH10 *) SQL> / Area_code bill_MONTH CUR_LOCAL_FARE PRE_LOCAL_FARE LAST_LOCAL_FARE NEXT_LOCAL_FARE POST_LOCAL_FARE --------------------------------------------- ----- ------------------------------------------- 5761200405 13060.433 00 13318.93 13,710.2655761 200406 13318.93 0 13060.433 13710.265 14,135.7815761 200407 13710.265 13060.433 13318.93 14135.781 05761200408 14135.781 13318.93 13710.265 005762200405 12643.791 00 12795.06 13,224.2975762 200406 12795.06 0 12643.791 13224.297 13,376.4685762 200407 13224.297 12643.791 12795.06 13376.468 05762200408 13376.468 12795.06 13224.297 00576320040513060.433 13318.93 13,710.2655763 0 0 0 200 406 13318.93 13060.433 13710.265 13710.265 13060.433 13318.93 14,135.7815763 200407 05763200408 14135.781 14135.781 13318.93 13710.265 12487.791 005764200405 0 0 0 13295.187 13295.187 12487.791 13444.093 13,444.0935764 200 406 200 407 13444.093 12487.791 13295.187 13929.694 13,929.6945764 05764200408 13929.694 13295.187 13444.093 0 05765 200405 25057.736 0 0 26058.46 26301.8815765 200406 26058.46 0 25057.736 26301.881 27130.6385765 200407 26301.881 25057.736 26058.46 27130.638 05765 200408 27130.638 26058.46 26301.881 0 020 rows selected. Using the lag and lead functions, we can show the same row of data in the first n lines, can be displayed The data of the rear N row. 4. SUM, AVG, MAX, MIN mobile computing data Description Average 1 SELECT Area_fore, BILL_MONTH, LOCAL_FARE, 2 SUM (local_fare) 3 over (partition by) area_code 4 order by to_number (bill_month) 5 range between 1 preceding and 1 following) "3month_sum", 6 avg (local_fare) 7 over (partition by area_code 8 order by to_number (bill_month) 9 range between 1 preceding and 1 following) "3month_avg ", 10 max (local_fare) 11 over (Part ition by area_code12 order by to_number (bill_month) 13 range between 1 preceding and 1 following) "3month_max", 14 min (local_fare) 15 over (partition by area_code16 order by to_number (bill_month) 17 range between 1 preceding and 1 following) "3month_min "18 from (19 select area_code, bill_month, sum (local_fare) local_fare20 from t21 group by area_code, bill_month22 *) SQL> / aREA_CODE bILL_MONTH lOCAL_FARE 3month_sum 3month_avg 3month_max 3month_min --------- ------- --- ---------------------------------------------- --------- 5761 200405 13060.433 26379.363 13189.68 15 13318.93 13060.4335761 200406 13318.930 40089.628 13363.2093 13710.265 13060.4335761 200407 13710.265 41164.976 13721.6587 14135.781 13318.9340089.628 = 13060.433 13318.930 13710.26513363.2093 = (13060.433 13318.930 13710.265) / 313710.265 = max (13060.433 13318.930 13710.265) 13060.433 = min (13060.433 13318.930 13710.265) 5761200408 14135.781 27846.046 13923.023 14135.781 13,710.2655762 200405 12643.791 25438.851 12719.4255 12795.06 12,643.7915762 200406 12795.060 38663.148 12887.716 13224.297 12,643.7915762 200407 13224.297 39395.825 13131.9417 13376.468 12795.065762 200408 13376.468 26600.765 13300.3825 13376.468 13,224.2975763 200405 13060.433 26379.363 13189.6815 13318.93 13,060.4335763 200406 13318.930 40089.628 13363.2093 13710.265 13,060.4335763 200407 13710.265 41164.976 1 3721.6587 14135.781 13318.935763 200408 14135.781 27846.046 13923.023 14135.781 13710.2655764 200405 12487.791 25782.978 12891.489 13295.187 12487.7915764 200 406 13295.187 39227.071 13075.6903 13444.093 12487.7915764 200 407 13444.093 40668.974 13556.3247 13929.694 13295.1875764 200408 13929.694 27373.787 13686.8935 13929.694 13444.0935765 200405 25057.736 51116.196 25558.098 26058.46 25057.7365765 200 406 26058.460 77418.077 25806.0257 26301.881 25057.7365765 200 407 26301.881 79490.979 26496.993 27130.638 26058.465765 200408 27130.638 53432.519 26716.2595 27130.638 26301.88120 Rows SELECTED.5. Introduction to the Ratio_to_Report function quote: 1 select bill_month, area_code, sum (local_fare) local_fare, 2 ratio_to_report (sum (local_fare)) over 3 (partition by bill_month) area_pct 4 from t 5 * group by bill_month, area_codeSQL> break on bill_month skip 1SQL> compute sum of local_fare on BILL_MONTHSQL> Compute Sum of Area_pct on Bill_Monthsql> / BILL_MONTH AREA_CODE LOCAL_FARE AREA_PCT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 20043.79 5762 12643.795763 13060.433 5763 13060.433.171149279 5764 12487.79 5764 12487.795763645143 5765 25057.736.328366866 ********* ---------------- --- ------- sum 76310.184 1200406 5761 13318.930 .169050772 5762 12795.060 .162401542 5763 13318.930 .169050772 5764 13295.187 .168749414 5765 26058.460 .330747499 ********** ---------- BA. 327175257 ********* ---------------- ---------- Sum 80390.801 1200408 5761 14135.781.170911147 5762 13376.468.161730539 5763 14135.781 . 178419416 5765 2719416 5765 27130.638.328027751 ********** ---------------- ---------- Sum 82708.362 120 rows SELECTED.6 FIRST, LAST function use introduction Quote:
