Learning window functions over () in Oracle

Learning window functions over () in Oracle
You can use window functions to solve some problems. For example, if we have two tables, one for the instructor and one for the department, and if we want to query the average salary of the teachers in each department, this is of course no problem, however, it would be troublesome to query the instructor information at the same time. We need to regard the average salary of teachers in each department as a temporary table, connect to the table of the original teacher, or query the information of the teacher with the highest salary in each department. Sometimes, grouping, multi-table, and statistical, it often involves querying the specific information of each query, which increases the difficulty of writing. If you use window functions and use them with some other functions, it is very easy to solve this problem.
Sometimes we need to display our results in each record for queries. This window function in oracle can solve this problem. The name of the window function is the over () function. There are two common attributes: partition by and order by. partition by is similar to group by. We usually call group by a group, partition by is called a partition. A group usually returns one row, and a partition by returns multiple rows.
If you use the over () function in Oracle and other functions in combination, it will play a greater role, for example, the most value problem, to obtain data from other rows. 1. and statistical functions (Aggregate functions) can be used with the count (), sum (), avg (), min (), max () functions, so that in the query city, you can also display the information of each record, for example, querying the average salary and teacher information of teachers in each department ,:
2. When the ranking function is used to query the database in the background, the sequence number needs to be displayed in the foreground. oracle provides three functions, which are more useful in combination with the over () function. :
3. The Oracle function and the function of the wrong row have two rows similar to the wrong row. Data of other rows can be obtained in this row. Lead Function and lag function. :
4. The first_value () function with the highest value can be used together with this function to solve the maximum value problem. There are two common attribute values.
Either way, a column of information will be added to the query record. Sometimes this requirement is required. Using this function can also solve the problem of using group, you can only query the field values of a group.