如何获取相邻数据
因为项目，所以找到了一些资料并且总结了下关于获取相邻数据的方式。
我只找到了以下的...
SELECT ABS(cid-5),cname FROM company ORDER BY 1 LIMIT 3
select cid,cname from company order by cid>=5,cid desc limit 1
SELECT cid,cname from company order by cid<=5,cid asc limit 1记录一下，以免以后遇到又不会
同表相邻数据比对查询
需求
我们将会比对相邻的数据，其中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")
以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程网。
