How to use the LAG() function and LEAD() function in MySQL-Mysql Tutorial-php.cn

1. Basic usage of window functions

Window functions have only been supported since MySQL8

<窗口函数> OVER ([PARTITION BY <用于分组的列>] ORDER BY <用于排序的列>)

Copy after login

2. Introduction to LAG() and LEAD() functions

lag and lead mean forward and backward respectively
There are three parameters.expression: Column name;offset: Offset;default_value: Default value beyond the recording window (default is null, can be set to 0)

3. Data preparation (create table sql at the end)

How to use the LAG() function and LEAD() function in MySQL

1. LAG() function: Statistics of temperature changes compared with the previous day For the high date ID

, we first sort by date, and then find the ID with a higher temperature today than the previous day; use the lag() function to push the temperature back one day.

select id, date, temperature, LAG(temperature, 1, 0) OVER (order by date) as temp FROM weather

Copy after login

Query results:

How to use the LAG() function and LEAD() function in MySQL

##Then select the data with temperature greater than temp and temp not equal to 0

select id from (select id, date, temperature, LAG(temperature, 1, 0) OVER (order by date) as temp FROM weather) tmp where temperature>temp and temp != 0;

Copy after login

The results are as follows:

How to use the LAG() function and LEAD() function in MySQL

2. LEAD() function: Count the date IDs with higher temperatures than the next day

First, we sort the dates, and then use lead The () function pushes the temperature back one day and finds the ID that has a higher temperature on that day than the next day.

select id, date, temperature, LEAD(temperature, 1, 0) OVER (order by date) as temp FROM weather

Copy after login

Query results:

How to use the LAG() function and LEAD() function in MySQL ##Then select the data with temperature greater than temp and temp not equal to 0

select id from (select id, date, temperature, LEAD(temperature, 1, 0) OVER (order by date) as temp FROM weather) tmp where temperature>temp and temp != 0;

Copy after login

Query results:

How to use the LAG() function and LEAD() function in MySQL 4. Create table data sql

DROP TABLE IF EXISTS `weather`; CREATE TABLE `weather` ( `id` int(11) NOT NULL, `date` date NULL DEFAULT NULL, `temperature` int(11) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of weather -- ---------------------------- INSERT INTO `weather` VALUES (1, '2022-08-01', 20); INSERT INTO `weather` VALUES (2, '2022-08-02', 25); INSERT INTO `weather` VALUES (3, '2022-08-03', 22); INSERT INTO `weather` VALUES (4, '2022-08-04', 22); INSERT INTO `weather` VALUES (5, '2022-08-05', 26); INSERT INTO `weather` VALUES (6, '2022-08-06', 28); INSERT INTO `weather` VALUES (7, '2022-08-07', 20); SET FOREIGN_KEY_CHECKS = 1;

Copy after login

The above is the detailed content of How to use the LAG() function and LEAD() function in MySQL. For more information, please follow other related articles on the PHP Chinese website!