Shulou(Shulou.com)05/31 Report--

Editor to share with you how to help MySQL to achieve Oracle advanced analysis function, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Oracle supports some unique syntax and functions, which is more or less troublesome to programmers when porting to MySQL. Here are some examples of special uses of Oracle and how to use aggregators to accomplish the same function. Of course, these methods are not limited to MySQL, but are also supported for all other databases.

1. Recursive statement

A) select employee_id,first_name,last_name,manager_id

From hr.employees

Start with employee_id=102

Connect by prior employee_id = manager_id

(1) A3 sets the key of sequence table A2

(2) A4 selection of starting employees

(3) A5 converts the MANAGER_ID value in A2 into a record for recursion

(4) A6 acquires all child nodes of the initial employee

B) select employee_id, first_name,last_name,manager_id

From hr.employees

Start with employee_id=104

Connect by prior manager_id = employee_id

(1) A6 acquires all parent nodes of the initial employee

C) select employee_id,last_name,manager_id,sys_connect_by_path (last_name,'/') path from hr.employees

Start with employee_id=102

Connect by prior employee_id = manager_id

(1) since the parent node of each record in A7 is in front of this node, A8 can modify the PATH value of each record in A7 in turn.

2. Nested aggregation function

Select avg (max (salary)) avg_max, avg (min (salary)) avg_min

From hr.employees

Group by department_id

(1) A1.query in A2 can also be changed to A1.cursor.

3. Aggregation analysis functions FIRST and LAST

SELECT department_id

MIN (salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) worst

MAX (salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) best

FROM hr.employees

GROUP BY department_id

ORDER BY department_id

(1) if A2 is sorted by DEPARTMENT_ID, then A3 can be grouped with group@o.

(2) FIRST/LAST takes the first / last group sorted, while null ranks last when Oracle is sorted, so the last group that LAST will get is the group in which the null value is located. Null is excluded when maxp/minp calculates all rows with maximum / minimum values, so it is maximum when A4 uses ifn (COMMISSION_PCT,2) to guarantee null values.

(3) in A5, DEPARTMENT_ID=null uses power larger than all DEPARTMENT_ID (2pj32) to ensure that this line is at the end of the row.

If the amount of data is large, you can also use cursors.

(1) in A3, min ([if (COMMISSION_PCT,2), SALARY]) calculates the minimum SALARY value of COMMISSION_PCT, that is, the minimum value of SALARY when COMMISSION_PCT ranks first, and max is similar.

4. Proportion function ratio_to_report

A) SELECT last_name, salary, RATIO_TO_REPORT (salary) OVER () AS rr

FROM hr.employees

WHERE job_id = 'PU_CLERK'

ORDER BY last_name

B) SELECT department_id,last_name, salary, RATIO_TO_REPORT (salary) OVER (partition by department_id) AS rr

FROM hr.employees

WHERE department_id in (20pl 60)

ORDER BY department_id,last_name

(1) if A2 is sorted by DEPARTMENT_ID, then A3 can be grouped and aggregated with groups@o.

5. Multiple grouping

SELECT department_id, job_id, sum (salary) total

FROM hr.employees

WHERE department_id in (30,50)

GROUP BY grouping sets (department_id, job_id), department_id)

(1) because A3 and A4 are ordered to DEPARTMENT_ID, A5 can be merge,ifn (JOB_ID,fill ("z", 10)) to ensure that JOB_ID is null.

You can also use cursors.

(1) A2.group in A3 requires A2 to order DEPARTMENT_ID.

(2) A4 sums each group of A3 and inserts the result at the end of the group.

It can also be piped.

(1) A3 creates pipes and appends grouping summation

(2) A4 pushes the data in A2 to A3. Note that this action is performed only if the data is actually fetched in A2.

(3) A5 creates pipes and appends grouping summation

(4) A6 pushes A3 results to A5, and the data in A2 can also be pushed to A5 directly, but it will increase the time complexity.

(5) A7 retains A3 data

(6) read A2 in a loop, taking only 1000 entries at a time to reduce memory footprint

(7) A10 sorts the data in A3 and A5. Because the algorithm is stable, the data whose JOB_ID is null ranks last.

The above is all the contents of the article "how to assist MySQL in implementing Oracle advanced analysis functions". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.