MySQL同表相邻数据比对查询

需求

我们将会比对相邻的数据，其中value是递增的，但也会存在清零的情况。我们的需求是计算当天的分钟递增量。

SQL

语句中需要解释一下参数

• ** ctc_etl.1_1_1907/sum.out_2021 ：表名**
• ** WHERE
  item_timestamp > “2021-12-01 00:00:00”
  AND item_timestamp < “2021-12-02 00:00:00” 是我自己加的筛选条件**

SELECT b.id,( b.item_value - a.item_value ) AS value,
	b.item_timestamp AS time 
FROM
	(
	SELECT id,item_value,
		@num := @num + 1 AS row_number 
	FROM
		( SELECT @num := 0 ) r,
			ctc_etl.`1_1_1907/sum.out_2021` 
	WHERE
		item_timestamp > "2021-12-01 00:00:00" 
		AND item_timestamp < "2021-12-02 00:00:00" 
	ORDER BY
		id 
	) a,
	(
	SELECT
		id,
		item_value,
		item_timestamp,
		@num2 := @num2 + 1 AS row_number 
	FROM
		( SELECT @num2 := 0 ) r2,
			ctc_etl.`1_1_1907/sum.out_2021` 
	WHERE
		item_timestamp > "2021-12-01 00:00:00" 
		AND item_timestamp < "2021-12-02 00:00:00" 
	ORDER BY
		id 
	) b 
WHERE
	a.row_number + 1 = b.row_number;

解析

目前计算的只是每秒的增加量，其次就是存在负数的情况，也就是清零了后一个数比前一个小就造成了负数

最终SQL

SELECT
	c.id,
	SUM( c.VALUE ) AS value,
	DATE_FORMAT( c.time, "%Y-%m-%d %H:%i:00" ) AS time 
FROM
	(
	SELECT
		b.id,
		(b.item_value - a.item_value ) AS value,
		b.item_timestamp AS time 
	FROM
		(
		SELECT
			id,
			item_value,
			@num := @num + 1 AS row_number 
		FROM
			( SELECT @num := 0 ) r,
			ctc_etl.`1_1_1907/sum.out_2021` 
		WHERE
			item_timestamp > "2021-12-01 00:00:00" 
			AND item_timestamp < "2021-12-02 00:00:00" 
		ORDER BY
			id 
		) a,
		(
		SELECT
			id,
			item_value,
			item_timestamp,
			@num2 := @num2 + 1 AS row_number 
		FROM
			( SELECT @num2 := 0 ) r2,
			ctc_etl.`1_1_1907/sum.out_2021` 
		WHERE
			item_timestamp > "2021-12-01 00:00:00" 
		AND item_timestamp < "2021-12-02 00:00:00" ORDER BY id ) b WHERE a.row_number + 1 = b.row_number ) c WHERE c.VALUE > 0 
GROUP BY
	DATE_FORMAT(
	c.time,
	"%Y-%m-%d %H:%i:00")