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

1. Basic usage of window functions

Window functions have only been supported since MySQL8

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

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)

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

Query results:

##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;

The results are as follows:

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

Query results:

##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;

Query results:

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;

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!