Oracle-some SQL statements required for report Creation

Oracle-some SQL statements required for report Creation

Oracle SQL for report

It was freezing, and I stayed at home and read "Mastering Oracle SQL" 2nd. I found that Oracle is still very powerful, and there are two hundred optical functions. It is difficult to simulate the Object-Oriented Query languages, in particular, windows functions for OLAP in SQL2003.
Fortunately, Hibernate3.0 also supports SQL.

1. Dedicated Rollup functions for total reports

Sales Report
January 20 RMB in Guangzhou
February 25 RMB in Guangzhou
Guangzhou 4500 Yuan
Shenzhen, September 00, January 10
Shenzhen, September 00, February 20
Shenzhen RMB 3000
$7500 for all regions

Previous query SQL:
Select area, month, sum (money) from SaleOrder group by area, month
Then, the sum of Guangzhou and Shenzhen and the sum of all regions must be accumulated in the program.

1. You can actually use the following SQL:

   Select area,month,sum(total_sale) from SaleOrder group by rollup(area,month)

The same record as the report can be generated.

 

2. If you do not want to accumulate year, you can write it

   Select year,month,area,sum(total_sale) from SaleOrder group 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 Cube (area, month) is used instead of RollUp (area, month), the total of each month is obtained in addition to the total of each region, which is displayed at the end of the report.

 

4. Grouping makes the total column better read
When RollUp displays Guangzhou total, the month column is NULL, but it is better to display it as "all months"
Grouping is used to determine whether the current Column is a total Column, 1 is yes, and then convert it to "all months" with Decode"

SelectDecode (Grouping (area), 1, 'all region', area)Area, Decode (Grouping (month), 1, 'all month', month), sum (money) From SaleOrder Group by RollUp (area, month );

 

2. start with... connect
Oracle provides a classic method for personnel organization and product category.

 SELECT LEVEL, name, emp_id,manager_emp_id FROM employee START WITH manager_emp_id is null CONNECT BY PRIOR emp_id = manager_emp_id;

The preceding statement demonstrates all applications. start with indicates where to traverse the tree. If it starts from the root, its manager should be Null. If it starts from a staff member, emp_id = '11'
Connect by indicates the parent-child relationship. Pay attention to the PRIOR location.
There is also a LEVEL column that displays the node hierarchy

 

3. More report/analysis and decision-making functions
3.1 basic structure of the Analysis Function
Analysis Function () over (partion clause, order by clause, window clause)
It is difficult to clarify the concept, but it is better to use examples.

 

3.2 Row_Number and Rank, DENSE_Rank
Used to select reports such as Top 3 sales
When two salesmen may have the same performance, Rank and Dense_Rank must be used.
For example
Amount RowNum Rank Dense_Rank
Zhang San 4000 yuan 1 1 1
Li Si 3000 yuan 2 2 2
5 RMB 2000 RMB 3 3 3
Sun sat 2000 yuan 4 3 3
Ding Qi 1000 yuan 5 5 4

At this time, we should select both Qian Wu and Sun 6th, tied for the third, so the Ranking function is safer than RowNumber. As for Desnse or Ranking, we can see the specific situation.

    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 splits the record equally into Class A, Class B, and Class 4.
For example, if I want to get the first 25% of the records, or treat 25% of the records as equal to the same level, and treat the other 25% as equal to the other Level.

    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) divides the records in the order of SUM (tot_sales) into four parts.

 

3.4 secondary analysis column and Windows Function
In addition to the basic fact data, the report always hopes to have more total annual sales volume next to it. For the cumulative sales volume so far, see the average sales volume of the last three months for reference.
The average sales volume of the last three months and the total sales volume so far are called windows functions. It is a new function of SQL2003 for OLAP. See the following example.

    SELECT month, SUM(tot_sales) monthly_sales,           SUM(SUM(tot_sales)) OVER (ORDER BY month           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 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 the Windows clause's values.
1. A record before PRECEDING
1. One record after FOLLOWING
All records before UNBOUNDED PRECEDING
Current row current record

 

4. SubQuery Summary
SubQuery is used every day. In theory, SubQuery is divided into three types.
1. Noncorrelated subquery is the most common style.
2. Correlated Subqueries pulled the column of the parent Query into the subquery. The first cyt taught me how to understand it for a long time.
3. The Inline View is also used as the most common style.

There are three more Noncorrelated subqueries.
1. Return the where price in a row <(select max (price) from goods)
2. Return where price> = ALL (select price from goods where type = 2) for multiple rows)
Or where NOT price <ANY (select price from goods where type = 2)
The most commonly used IN is actually = ANY ()
3. If multiple rows and columns are returned at a time, the query time is saved.

          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)