如何获取相邻数据
因为项目,所以找到了一些资料并且总结了下关于获取相邻数据的方式。
我只找到了以下的…
/*获取id值与5相减绝对值最近的数据,如果有绝对值相同的,相邻的数据优先输出*/ /*abs(x)其实就是获取绝对值,然后的话order by 后面的1代表着根据select关键字 后的第一个字段进行排序。limit 后面的数字代表着你想查询数据的行数*/ select abs(cid-5),cname from company order by 1 limit 3
/*获取指定id的上一条记录,我这里是以id=5为例的*/ select cid,cname from company order by cid>=5,cid desc limit 1
/*获取指定id的下一条记录,我这里是以id=5为例的*/ 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")
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。
声明:如若本站内容侵犯了原著者的合法权益,可联系我们进行处理。