Oracle SQL is still irreplaceable-mastering Oracle SQL

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
Previous query SQL statements of 7500 yuan in all regions:
Select area, month, sum (money) from saleorder group by area, month
Then, the sum of Guangzhou and Shenzhen and the sum of all regions need to 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 hard to clearly understand the concept. It is better to use the example to speak. 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 moment, we should choose both the third-party money 5 and Sun 6, 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. Then there are three scenarios for 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)