Использование функции LAG в MySQL

MySQL версии 8.0 представил оконные функции MySQL, позволяющие выполнять запросы более простым и организованным методом. Тем самым увеличивая обработку и производительность. К таким функциям относятся: RANK(), ROW_RANK(), LAST_VALUE() и многие другие.

В этом руководстве мы сосредоточимся на использовании одной из функций MySQL: LAG(). Это оконная функция, которая позволяет вам получать доступ и извлекать значения предыдущих строк из текущей строки в том же наборе результатов.

Базовый синтаксис

Общий синтаксис использования функции MySQL LAG():

LAG(Expression, OffSetValue, DefaultVar) OVER (
PARTITION BY [Expression]
ORDER BY Expression [ASC|DESC]
);

Давайте уделим время объяснению некоторых параметров синтаксиса функции LAG().

Вот они:

Expression: это значение, возвращаемое функцией из строки, которая опережает текущую строку на указанное значение смещения.

OffSetValue: это значение представляет количество строк, предшествующих текущей строке, из которой следует получить значение. Это значение должно быть 0 или больше 0.

[gn_box title=»Примечание» box_color=»#fff08c» title_color=»#000000″]Значение 0 представляет текущую строку.[/gn_box]

DefaultVar: это значение возвращается функцией как значение по умолчанию, если предыдущая строка не существует. Если значение по умолчанию в параметре функции не определено и предыдущая строка не существует, функция возвращает значение NULL.

PARTITION BY: Предложение PARTITION BY разделяет строки в наборе логических разделов. Затем к разделенным разделам применяется функция LAG.

ORDER BY: как обычно, это значение определяет порядок строк в доступных разделах.

Примеры использования

Давайте посмотрим на примеры использования функции LAG(), чтобы понять, как она работает. Начните с создания образца базы данных с именем sample_db;

DROP DATABASE IF EXISTS sample_database;
CREATE DATABASE sample_database;
USE sample_database;
DROP TABLE IF EXISTS users;
CREATE TABLE users
(
id          INT PRIMARY KEY AUTO_INCREMENT,
name        VARCHAR(255),
Score       INT,
Enroll_Date DATE
);
INSERT INTO users(id, name, score, enroll_date)
VALUES (1, "Alexandra", 99, '2021-01-10'),
(2, "Jacob", 81, '2021-05-20'),
(3, "Leonard", 67, '2020-01-02'),
(4, "Peter", 88, '2021-03-03'),
(5, "Amy", 100, '2021-05-05');

SELECT * FROM users;

Теперь, когда у нас есть образец базы данных для работы, мы можем продолжить и проиллюстрировать, как работать с функцией MySQL LAG.

Пример 1. Функция задержки без значения по умолчанию.

Рассмотрим приведенный ниже пример, в котором функция задержки применяется к Enroll_Date со значением смещения 1.

SELECT *, LAG(Enroll_Date, 1) OVER ( ORDER BY id ASC) as previous_date FROM sample_database.users;

После выполнения вышеуказанного запроса мы получаем новый столбец previous_date, содержащий предыдущее значение строки, как указано со значением смещения 1. Поскольку в первой строке нет предыдущего значения, значение равно нулю.

[gn_box title=»Примечание» box_color=»#fff08c» title_color=»#000000″]Вы можете указать значение по умолчанию, если строка не имеет предыдущего значения.[/gn_box]

Пример 2: Функция задержки со значением по умолчанию

Вы также можете указать значение по умолчанию для строки, в которой предыдущее значение не существует. В нашем примере мы установим значение по умолчанию для текущей даты.

[gn_box title=»Примечание» box_color=»#fff08c» title_color=»#000000″]В этом примере мы также установим значение смещения как 2 вместо 1.[/gn_box]

Рассмотрим запрос ниже:

SELECT *, LAG(Enroll_Date, 2, CURDATE()) OVER ( ORDER BY id ASC) as previous_date FROM sample_database.users;

После выполнения вышеуказанного запроса мы получим значения со значением смещения, равным двум, и текущей датой по умолчанию для нулевых значений.

Пример 3: Функция задержки с помощью Partition by

Мы можем использовать функцию LAG() с разделом по условию. Это предложение сначала группирует данные в различные логические подмножества, а затем применяет функцию задержки к разделам.

Прежде чем продолжить, давайте посмотрим данные в таблице пользователя. Рассмотрим следующий запрос:

INSERT INTO users(id, name, score, enroll_date)
VALUES (1, "Alexandra", 99, '2021-01-10'),
(2, "Jacob", 81, '2021-05-20'),
(3, "Leonard", 67, '2020-01-02'),
(4, "Peter", 88, '2021-03-03'),
(5, "Amy", 100, '2021-05-05'),
(6, "Tobias", 100, '2020-06-06'),
(7, "Kurtzman", 67, '2020-07-10'),
(8, "Immortal", 50, '2021-03-01'),
(9, "Anthony", 81, '2021-01-01'),
(10, "James", 77, '2021-02-03');

Теперь, когда у нас есть таблица с 10 значениями, мы можем разделить данные по счету, а затем применить функцию задержки.

Вышеупомянутая операция проиллюстрирована в запросе ниже:

SELECT *, LAG(Enroll_date, 1, CURDATE()) OVER (PARTITION BY score ORDER BY id ASC) AS previous_date FROM sample_database.users;

В приведенном выше запросе мы начинаем с разделения данных на основе оценки, а затем применяем функцию задержки со значением смещения 1. Мы также устанавливаем значение по умолчанию как текущую дату.

[gn_box title=»Примечание» box_color=»#fff08c» title_color=»#000000″]Вы также можете заметить, что первая строка каждого раздела содержит текущую дату, что означает, что в заданной строке нет предыдущего значения.[/gn_box]

Заключение

В этой статье обсуждалось, как функция LAG() работает для получения значений предыдущих строк в текущей строке.

Резюмируем:

• Функция MySQL — это оконная функция, которая получает значение из предыдущей строки на основе указанного значения смещения. Это означает, что если значение смещения равно 1, оно получает значение прямо над ним.
• По умолчанию функция LAG() использует значение смещения 1, если явно не указано иное.
• Если данные находятся вне диапазона (в смещении не указано предыдущее значение), устанавливается значение NULL.
• Функция LAG() также принимает предложение PARTITION BY, которое группирует данные в различные логические разделы на основе указанного столбца или условия.

Читайте также

Изменить столбец в MySQL

4 года назад

Как использовать функцию mysqli_real_escape_string в MySQL

3 года назад

Создание таблиц в MySQL с помощью Perl

5 лет назад