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 light functions, and the object-oriented query language is difficult to simulate, especially in SQL2003 for OLAP Windows function, etc. Fortunately, Hibernate 3.0 also supports SQL.
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 position
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
Analysis function () over (Partion clause, order by clause, window clause)
It is difficult to talk about, or use examples to speak better.
3.2 Row_Number and Rank, Dense_rank is used to select reports such as 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.
This average and the cumulative sales of the three months before and after the cumulative sales are called Windows Function, which is a new function for the OLAP for the SQL2003. See
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.Ninline View is also used as the most common style.
Then there are three cases of NoncorRelated subquery 1. Return a row of 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)
