Note: the sql used in this article provides data and tables for HR users. HR user introduction view the following links.
https://blog.csdn.net/weixin_45842494/article/details/122528264

1. Top-N analysis

1.1 what is Top-N analysis

The Top-N query is useful when you need to display the first record or the last n records from the table based on a condition. This result can be used for further analysis. For example, with Top-N analysis, you can execute the following query types:

• The three who make the most money in
• The latest four in the company
• The two sales agents who sell the most products
• Top 3 products sold in the past 6 months

To put it simply, it is ranking.

1.2 perform Top-N analysis

The Top-N query uses a consistent nested query structure with the elements described below:

• A subquery or built-in view generates a sorted list of data. The subquery or built-in view contains an order by clause to ensure that the sorting is arranged in the desired order. In order to retrieve the maximum value, you need to use the DESC parameter.
• Limit the number of rows in the final result set with external queries. External query includes the following components:
  • ROWNUM pseudo column, which specifies a continuous value starting from 1 for each row returned from the subquery
  • A WHERE clause that specifies the n rows to be returned. The outer WHERE clause must use a < or < = operation.

select rownum ,last_name from employees;
return:
    ROWNUM LAST_NAME
---------- -------------------------
         1 Abel
         2 Ande
         3 Atkinson
		...
       109 Whalen
       110 Zlotkey
110 rows selected

Note: rownum is not a column in the table, but we add rownum for sorting. We will start from 1 and sort the data we query.

Example 1:

Show the names and salaries of the three people who earn the most money from the EMPLOYEES table.

select rownum,last_name,salary from (select salary,last_name from employees order by salary desc) where rownum <= 3;
return:
    ROWNUM LAST_NAME                     SALARY
---------- ------------------------- ----------
         1 King                        24000.00
         2 Kochhar                     17000.00
         3 De Haan                     17000.00

Example 2:

Display the four oldest qualified employees in the company, and display their employment time and name.

select rownum,e.last_name,e.hire_date from (select last_name, hire_date from employees order by hire_date) e where rownum <= 4;
return:
    ROWNUM LAST_NAME                 HIRE_DATE
---------- ------------------------- -----------
         1 De Haan                   2001/1/13
         2 Mavris                    2002/6/7
         3 Higgins                   2002/6/7
         4 Baer                      2002/6/7

Analysis: the sub query (built-in view) helps us find the employment time and name we need, and arrange them side by side. The main query is how many entries are taken from the ordered results.

2. Paging query of Oracle Database

When the amount of data in the query result set is too large, it may lead to various problems, such as server resource depletion, processing timeout due to excessive data transmission, and so on. Eventually, the query cannot be completed. One strategy to solve this problem is "paging query", that is, don't query all the data at one time, and only query part of the data at a time. In this way, processing in batches can present a good user experience and consume little server resources.

Paging query principle:

In the built-in view, the quantity of data obtained is specified by judging the pseudo column value of rownum.

Example:

Query the data in the employee table and only return 10 items at a time, including name and salary.

-- Top 10
select em.rn, em.last_name,em.salary from (select rownum rn,e.* from employees e) em where em.rn >= 1 and em.rn <= 10;
return:
        RN LAST_NAME                     SALARY
---------- ------------------------- ----------
         1 Lu                           1000.00
         2 Lu                           1000.00
         ......
        10 Pataballa                    4800.00
10 rows selected
-- Next 10
select em.rn, em.last_name,em.salary from (select rownum rn,e.* from employees e) em where em.rn >= 11 and em.rn <= 20;
return:
        RN LAST_NAME                     SALARY
---------- ------------------------- ----------
        11 Lorentz                      4200.00
        12 Greenberg                   12008.00
        ......
        20 Baida                        2900.00
10 rows selected

Note: compared with top-N, paged query puts rownum into sub query (built-in view), because all columns can be returned through rownum. Top-N is only used to get the first few or the last few, and cannot get the middle data.

For > = and < =, you can also use between... And

Difference: Oracle database uses rownum pseudo column for paging. Mysql database is paged by limit(m,n).

For views, refer to the following links:

https://blog.csdn.net/weixin_45842494/article/details/122675205