A case study of efficient paging query with SQL window function

Time:2021-7-25

分页

If you don’t smell it, if you don’t smell it, if you smell it, if you don’t see it, if you see it, if you know it, if you know it, if you don’t do it. Learn as much as you do—— a pre-Qin philosopher

hello everyone! I’m a teacher Tony who only talks about technology and doesn’t cut his hair.

When using SQL statements to implement paging queries, we need to know some additional parameter information, such as the total number of rows returned by the query, the current number of pages, the number of pages on the last page, etc. In the traditional implementation method, we need to execute additional query statements to obtain these information. This paper introduces a method that only one query statement can return all data, that is, to realize the efficient paging query function through SQL window function.

The sample tables and data used in this article canDownload here

Traditional methods to realize paging query

The traditional way to implement paging query in SQL is to use standard offset… Fetch statements or limit… Offset statements supported by many databases, such as:


-- Oracle、SQL Server、PostgreSQL
SELECT emp_name, sex, email 
FROM employee
ORDER BY emp_id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

-- MySQL、PostgreSQL、SQLite
SELECT emp_name, sex, email 
FROM employee
ORDER BY emp_id
LIMIT 10 OFFSET 10;

The above statement is very easy to understand, and the 10 records on page 2 are returned. However, the problem is how to know the total number of pages of data (or the total number of records). Obviously, before that, we need to execute another query:


SELECT COUNT(*)
FROM employee;

COUNT(*)|
--------+
      25|

With the total number of records 25, we can calculate that there are 3 pages of data, 10 entries per page.

This method requires us to execute two query statements every time we perform paging query, which is not very convenient to use. Next, we introduce a more efficient window function paging query.

As for the implementation of paging query, offset paging may have performance problems for paging a large amount of data. Another method is to use keyset paging.

Window function to realize paging query

First, let’s consider what parameters are required when using offset paging query:

  • TOTAL_ Rows, total records;
  • CURRENT_ Page, the current page number;
  • MAX_ PAGE_ Size, the maximum number of records displayed on each page, such as 10, 20 and 50;
  • ACTUAL_ PAGE_ Size, the number of records actually contained in the current page;
  • ROW_ NBR, actual offset of each record;
  • LAST_ Page, whether the current page is the last page.

Maximum number of records displayed per page (max)_ PAGE_ Size) is the parameter we pass to the database, and others are the results returned by the query. We can realize all functions through the following query statements:

-- Oracle、SQL Server、PostgreSQL
With E as (- - initial query
  SELECT emp_id, emp_name, sex, email
  FROM employee
),
t AS (
  SELECT emp_id, emp_name, sex, email, 
         COUNT(*) OVER () AS total_ Rows, -- total records
         ROW_ NUMBER () OVER (ORDER BY e.emp_ id) AS row_ NBR -- offset, order by is the same as the initial query
  FROM e
  ORDER BY e.emp_ ID -- sort
  Offset 10 rows -- paging
  FETCH NEXT 10 ROWS ONLY
)
SELECT
  emp_id, emp_name, sex, email,
  COUNT(*) OVER () AS actual_ page_ Size, -- actual number of records on the current page
  CASE MAX(row_nbr) OVER () 
    WHEN total_rows THEN 'Y' 
    ELSE 'N' 
  END AS last_ Page, -- last page
  total_ Rows, -- total records
  row_ NBR, -- offset of each piece of data
  ((row_ nbr - 1) / 10) + 1 AS current_ Page -- current page number
FROM t
ORDER BY emp_id;


-- MySQL、PostgreSQL、SQLite
With E as (- - initial query
  SELECT emp_id, emp_name, sex, email
  FROM employee
),
t AS (
  SELECT emp_id, emp_name, sex, email, 
         COUNT(*) OVER () AS total_ Rows, -- total records
         ROW_ NUMBER () OVER (ORDER BY e.emp_ id) AS row_ NBR -- offset, order by is the same as the initial query
  FROM e
  ORDER BY e.emp_ ID -- sort
  LIMIT 10
  Offset 10 rows -- paging
)
SELECT
  emp_id, emp_name, sex, email,
  COUNT(*) OVER () AS actual_ page_ Size, -- actual number of records on the current page
  CASE MAX(row_nbr) OVER () 
    WHEN total_rows THEN 'Y' 
    ELSE 'N' 
  END AS last_ Page, -- last page
  total_ Rows, -- total records
  row_ NBR, -- offset of each piece of data
  ((row_ nbr - 1) / 10) + 1 AS current_ Page -- current page number
FROM t
ORDER BY emp_id;

Firstly, we define the general table expression e, which is the initial query to return data, and other filtering conditions can be added.

Then, we define another general table expression T based on e, sort and page in the definition, calculate the total number of records using the window function count (*), and use the window function row_ Number() calculates the offset (line number) for each piece of data.

Next, we return more parameters based on T, and use the window function count (*) to return the actual number of records on the current page through the window function max (row)_ NBR) compare the maximum offset of the current page returned with the total number of records to determine whether it is the last page and the current page number.

emp_id|emp_name|sex|email              |actual_page_size|last_page|total_rows|row_nbr|current_page|
------+--------+---+-------------------+----------------+---------+----------+-------+------------+
    11 | Guan Ping | male| [email protected] |              10|N        |        27|     11|           2|
    12 | Zhao | female| [email protected]  |              10|N        |        27|     12|           2|
    13 | Guan Xing | male| [email protected] |              10|N        |        27|     13|           2|
    14 | bud | male| [email protected] |              10|N        |        27|     14|           2|
    15 | Zhao Tong | male| [email protected] |              10|N        |        27|     15|           2|
    16 | Zhou Cang | male| [email protected] |              10|N        |        27|     16|           2|
    17 | Ma Dai | male| [email protected]    |              10|N        |        27|     17|           2|
    18 | Fazheng | male| [email protected]  |              10|N        |        27|     18|           2|
    19 | Pang Tong | male| [email protected] |              10|N        |        27|     19|           2|
    20 | Jiang Wan | male| [email protected] |              10|N        |        27|     20|           2|

Introduction to window functionsRefer to this article

summary

This paper introduces how to use the window function to return the results of paging query and all required parameters in one statement. This method is more concise and efficient than the traditional paging query.