Introduction to the analysis function of Oracle development over usage _oracle

Introduction to Oracle Analysis functions:

In the day-to-day production environment, we have more contact with the OLTP system (ie, online Transaction Process), these systems are characterized by real-time requirements, or at least the length of time to respond to a certain degree of demand, and then the business logic of these systems is generally more complex, You may need to go through multiple operations. For example, we often contact the electronic mall.

In addition to these systems, there is a system called OLAP (ie, online aanalyse Process), which is commonly used in system decision making. It is often associated with concepts such as data warehousing, data analysis, and data mining. These systems are characterized by a large amount of data, the demand for real-time response is not high or do not pay attention to this aspect of the requirements, to query, statistical operations.

Let's take a look at some of the following typical examples:
① find the top 10 employees in each sales area of the previous year
② by region to find the total amount of orders in the previous year accounted for more than 20% of regional orders
③ find the area where the last year's worst sales department is located
④ find the best and worst selling products of the previous year

Let's take a look at the above examples and we can feel that these queries are different from the ones we encounter on a daily basis, specifically:

① need to perform different levels of aggregation on the same data
② need to compare multiple data and the same data multiple times in a table
③ need to perform additional filtering on the sorted result set

Second, Oracle analysis function Simple example:

Here's a practical example: Find the customer with the total order totals of over 20% of the total area by region to see the application of the analysis function.

"1" Test environment:

Copy Code code as follows:

sql> desc orders_tmp;

Name Null? Type
----------------------- -------- ----------------
CUST_NBR not NULL number (5)
region_id not NULL number (5)
salesperson_id not NULL number (5)
Year not NULL number (4)
MONTH not NULL number (2)
Tot_orders not NULL number (7)
Tot_sales not NULL number (11,2)

"2" test data:

Copy Code code as follows:

Sql> select * from Orders_tmp;

CUST_NBR region_id salesperson_id year MONTH tot_orders tot_sales
---------- ---------- -------------- ---------- ---------- ---------- ----------
11 7 11 2001 7 2 12204
4 5 4 2001 10 2 37802
7 6 7 2001 2 3 3750
10 6 8 2001 1 2 21691
10 6 7 2001 2 3 42624
15 7 12 2000 5 6 24
12 7 9 2000 6 2 50658
1 5 2 2000 3 2 44494
1 5 1 2000 9 2 74864
2 5 4 2000 3 2 35060
2 5 4 2000 4 4 6454
2 5 1 2000 10 4 35580
4 5 4 2000 12 2 39190

Rows selected.

"3" test statement:

Copy Code code as follows:

Sql> Select O.CUST_NBR Customer,
O.REGION_ID region,
SUM (o.tot_sales) Cust_sales,
sum (sum (o.tot_sales)) over (partition by o.region_id) region_sales
from Orders_tmp o
where O.year = 2001
GROUP by o.region_id, O.CUST_NBR;

  customer     REGION cust_sales region_sales
------------------------------------- -----
         4               5      37802        37802
         7               6       3750          68065
        10              6      64315        68065
        11              7      12204        12204

Third, analysis function over resolution:

Notice that the green highlights above, group by's intention is obvious: the data by the area ID, the customer group, then over this part of what is the use? If we only need to count the total order of each customer in each area, then we need only group by O.REGION_ID,O.CUST_NBR enough. But we also want to show the total order totals for that customer's area on each line, which is different from the previous one: you need to accumulate by region on the basis of the preceding grouping. Obviously group by and Sum are not able to do this (because the aggregate operation is different in level, the former is for a customer, the latter is for a group of customers).

This is the effect of over function! Its role is to tell the SQL engine to partition the data by region and then accumulate the total order totals (sum (o.tot_sales)) for each customer in each zone.

Now that we know the total order totals for each customer and its respective region for 2001, the following is a list of major customers who have accounted for more than 20% of the total regional orders.

Copy Code code as follows:

Sql> SELECT *
From (select O.cust_nbr Customer,
O.REGION_ID region,
SUM (o.tot_sales) Cust_sales,
SUM (SUM (o.tot_sales)) over (partition by o.region_id) region_sales
From Orders_tmp o
where O.year = 2001
Group by o.region_id, O.CUST_NBR) all_sales
where All_sales.cust_sales > All_sales.region_sales * 0.2;

CUSTOMER REGION cust_sales Region_sales
---------- ---------- ---------- ------------
4 5 37802 37802
10 6 64315 68065
11 7 12204 12204

Sql>

Now we know who these big clients are! Oh, but that's not enough, if we want to know what proportion of each big customer's order? Take a look at the following SQL statement, just a simple round function is done.

Copy Code code as follows:

Sql> Select all_sales.*,
round (cust_sales/region_sales, 2) | | '% ' Percent
from (select O.cust_nbr Customer,
O.REGION_ID region,
SUM (o.tot_sales) Cust_sales,
SUM (SUM (o.tot_sales)) over (partition by o.region_id) region_sales
From Orders_tmp o
where O.year = 2001
Group by o.region_id, O.CUST_NBR) all_sales
where All_sales.cust_sales > All_sales.region_sales * 0.2;

CUSTOMER REGION cust_sales region_sales PERCENT
---------- ---------- ---------- ------------ ----------------------------------------
4 5 37802 37802 100%
10 6 64315 68065 94%
11 7 12204 12204 100%

Sql>

Summarize:

The ①over function indicates that the analysis is done on those fields, where the data is grouped with the partition by representation. Note partition by can have more than one field.

The ②over function can be combined with other aggregate functions and analytic functions to play a different role. For example, here's sum, as well as Rank,dense_rank.