Oracle-Some SQL required for reporting

Some Oracle SQL required for reporting

The cold, stay at home and read the mastering Oracle SQL 2nd, found that Oracle is still very powerful, there are 200 light functions, those object-oriented query language is difficult to emulate, especially in SQL2003 for OLAP windows function and so on.
Fortunately, Hibernate3.0 also supports SQL.

1. Rollup function dedicated to report totals

Sales Reports
Guangzhou January 2000 Yuan
Guangzhou February 2500 yuan
Guangzhou 4500 Yuan
Shenzhen January 1000 yuan
Shenzhen February 2000 Yuan
Shenzhen 3000 Yuan
7500 USD in all regions

Previous query sql:
Select area,month,sum from Saleorder GROUP by Area,month
Then the total of Guangzhou, Shenzhen and all regions will need to accumulate in the program itself

1. You can actually use the following sql:

Rollup (Area,month)

To produce the exact same records as the report.

2. If year does not want to accumulate, can write

 Year Rollup (Month,area)

In addition, Oracle 9i 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), you will get totals for each month in addition to the totals for each region, which is displayed at the end of the report.

4.Grouping make the total column read better
Rollup when displaying Guangzhou total, the month is listed as NULL, but a better practice should be shown as "all months"
Grouping is used to determine whether the current column is a total column, 1 is yes, and then it is converted to "all months" with decode

Decode (Grouping), 1, ' All regions ', area areas , Decode (Grouping (month), 1, ' All months ', month), SUM (money) From Saleorder Group by RollUp (Area,month);

2. Start With.....connect by for multi-level queries
such as personnel organization, product category, Oracle provides a very classical method

Leve L, name, emp_id,manager_emp_id from employee  manager_emp_id is null  emp_id = manager_emp_id;

The above statement shows the entire application, start with indicates where to start traversing the tree, if starting from the root, then its manager should be null, if starting from an employee, can be written as emp_id= ' 11 '
CONNECT by is a parent-child relationship, note the prior location
There's also a level column that shows the hierarchy of nodes

3. More Report/Analysis decision function
3.1 Basic structure of the analysis function
Analysis function () over (partion clause, ORDER BY clause, window clause)
It is difficult to speak clearly on the concept, or it is better to speak in an example.

3.2 Row_number and Rank, Dense_rank
Used to select a report such as top 3 sales
When two salesmen may have the same performance, use Rank and Dense_rank
Like what
Amount RowNum Rank Dense_rank
Zhang 34,000 Yuan 1 1 1
Li 43,000 RMB 2 2 2
Money 52,000 RMB 3 3 3
Sun 62,000 RMB 4 3 3
Ding 71,000 RMB 5 5 4

At this time, should be tied to the third of the money five and Magoroku are selected in, so with ranking function than RowNumber insurance. As for desnse or ranking to see the specific situation.

(ORDER by SUM (tot_sales) DESC) Sales_rank from orders GROUP by salesperson_id

3.3 NTILE The record into a B-N-D
For example, I want to get the top 25% record, or 25% of the record as a level equal treatment, the other 25% as another level equal treatment

(Order by SUM (tot_sales) desc) sales_quartile The From Orders GROUP by Cust_nbr ORDER by 3,2 desc;

Ntitle (4) divides the record into 4 parts in SUM (Tot_sales).

3.4 Auxiliary Analysis columns and Windows Function
The report, in addition to the basic factual data, is expected to be next to more than the total annual sales, so far the cumulative sales, the average sales of three months before and after the column reference.
This three-month average and the cumulative sales so far are called Windows functions, which is SQL2003 's new function for OLAP, see the following example

ROWS between unbounded precedingand current ROW) Max_preceeding from Orders GROUP by month 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 a few values of Windows clauses
1 a record before preceding
1 A record after following
All records prior to unbounded preceding
Current ROW Record

4.SubQuery Summary
Subquery used every day, theoretically summed up. Subquery three kinds of
The most common style for 1.Noncorrelated subqueries.
2.Correlated subqueries Pull the parent query column into the subquery, the first time Cyt taught me to understand the half-day.
3.Inline View is also used as the most common style.

  then noncorrelated the subquery in three more cases
  1. Returns a row of columns     where Price < (select Max (price) from goods)
  2. Returns multiple rows a column     where Price>= all (select price from goods where type=2)
  & nbsp;                        or where not price< any (select price from goods where type=2)
  & nbsp;                             most commonly used in is actually =any ()
  3. Returns multiple rows and columns      Returning more than one column at a time, of course, saved query times

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 O RDER_NBR, PART_NBR from Cust_order c)

Oracle-Some SQL required for reporting