SQL语句求数据的突变值

标签： Sql 分类：数据库创建时间：2020-07-02 10:41:21 更新时间：2023-10-20 11:23:26

如题，我想在一系列数据中，求数据的突变值。比如从1变成0,或者从零变成1,如下：

比如id为3时，数据为1，id为4时，数据为零，这就是一个突变值，我想要的是取出3和4，以下依次类推，要取出id号为5、6、7的值。

1.创建数据表

1	create table test(id int, status int)

2.插入数据

insert into test 
select 1, 1 union all
select 2, 1 union all
select 3, 1 union all
select 4, 0 union all
select 5, 0 union all
select 6, 1 union all
select 7, 0 union all
select 8, 0 union all
select 9, 0 union all
select 10, 0 union all
select 11, 1 union all
select 12, 0 union all
select 13, 0 union all
select 14, 0 union all
select 15, 0 union all
select 16, 0

3.求波动值

1 2	select a.* from test a left join test b on a.id=b.id-1 left join test c on a.id=c.id+1 where a.status<>b.status or a.status<>c.status

结果基本满意：

参考文章：
1.从一系列波动的数据中找到转折点
2.Sql Server 2012 的新方法以及 T-SQL 与其他数据库不同的用法
3.SQL NULL 函数
4.SQL UNION 和 UNION ALL 操作符

4.更进一步要求

如果我的数据里面没有id号怎么办呢？

创建一个临时表，然后给一个id号

1	select row_number() over (order by RecordTime ) as id, RecordTime ,velocity from secHis t where sectionId =1 and (RecordTime between '2020-07-01 15:17:55' and '2020-07-02 15:17:55')

参考文章：
1.SQL 把查询结果当作”表”来使用
2.mysql使用查询结果作为临时表
3.SQL Server创建和使用临时表（转)
4.sql 查询,把查出的数据作为临时表在进行查询 (没有用)
5.SQL 这种是什么运算 <> , <=>

5.复杂的sql语句

数据库中有一系列的按时间排列的数据

(1) 暴力遍历法
给定一个标准值，标准值以上的为正常值，标准值以下的为异常值，写一个sql语句，要找出所有从正常值变为异常值，以及从异常值变为正常值的记录，并计算两者之间的时间差。简单的画一个示意图：

就像图中文字描述一样，求一定时间范围内所有异常时间的总和以及异常的开始和结束时间（可能有多个异常的开始和结束时间）。

最笨的方法就是，把给定时间范围内所有的记录都取出来，然后遍历，比较每一个值，当数值小于等于标准值时，标记开始位置，继续往下搜索，当数值大于标准值时，标记异常结束位置，将开始和结束时间相减，得到第一个异常的时间范围，然后继续往下搜索，再次标记开始和结束位置，然后计算第二个时间范围，以此类推，直到将全部的记录搜索完毕。

(2) sql语句加乘积小于零

with test as (select row_number() over (order by RecordTime ) as id, RecordTime ,velocity from secHis t where sectionId =1 and (RecordTime between '2020-07-01 15:17:55' and '2020-07-02 15:17:55') )
select a.* from test a 
left join 
test b on a.id=b.id-1
left join 
test c on a.id=c.id+1
where (b.velocity-0)*(c.velocity-0) <= 0

sql语句的基本思想，先选出符合条件的记录，然后做三个left join表，根据数学上的公式，f(x)*f(y)<0,则说明x和y之间的函数值，一定会跨过坐标轴，取出满足条件的记录。

(3) sql语句之罗列全部符合要求值法
上面的sql语句有一个缺点，就是无法应付数值在两条记录之间直接切换的，比如下面的169数据，它的(b.velocity-0)*(c.velocity-0)大于零，但是也属于一个突变值，应该被记录。

而且还会有大量的异常值没有去除，但是我只需要的是异常值的开始和结束位置，如果要找到开始和结束位置，还是需要进行遍历。

于是鉴于上面的问题，修改sql语句如下：

with test as (select row_number() over (order by RecordTime ) as id, RecordTime ,velocity from secHis t where sectionId =1 and (RecordTime between '2020-07-01 15:17:55' and '2020-07-02 15:17:55') )
select * from test a 
left join 
test b on a.id=b.id-1
left join 
test c on a.id=c.id+1
where (a.velocity<=0 and b.velocity >0 and c.velocity <=0) or (a.velocity<=0 and b.velocity <=0 and c.velocity >0) or (a.velocity<=0 and b.velocity >0 and c.velocity >0)

where条件的思想就是找出全部符合条件的记录，符合条件的记录满足如下条件：
第一种：

记录的值小于或者等于临界值
记录的上一条记录值大于临界值
记录的下一条记录值小于等于临界值

第二种：

记录的值小于或者等于临界值
记录的上一条记录值小于等于临界值
记录的下一条记录值大于临界值

第三种：

记录的值小于或者等于临界值
记录的上一条记录值大于临界值
记录的下一条记录值大于临界值

(4) sql语句获取最大和最小记录
获取到了所有的变化的节点(无论是从正常值变化到异常值，还是从异常值变化到正常值),还有一个工作就是获取第一条和最后一条记录，这样才能直到从第一条记录，到第一个数值变化的记录，这一段时间内的数据是不是异常值，如果是异常值，则需要将时间段累计。

with test as (select row_number() over (order by RecordTime ) as id, RecordTime ,velocity from secHis t where sectionId =1 and (RecordTime between '2020-07-01 15:17:55' and '2020-07-02 15:17:55') )
select * from test a 
left join 
test b on a.id=b.id-1
left join 
test c on a.id=c.id+1
where (a.velocity<=0 and b.velocity >0 and c.velocity <=0) or (a.velocity<=0 and b.velocity <=0 and c.velocity >0) or (a.velocity<=0 and b.velocity >0 and c.velocity >0)
or a.id=(SELECT max(id) from test)
or a.id=(SELECT min(id) from test)

这个sql语句的最大缺点，就是需要的时间会非常的长

参考文章：
1.sql去重复操作详解SQL中distinct的用法
2.一张表被同一个sql多次调用，使用with as 优化
3.SQL LEFT JOIN 关键字
4.实现前后行相减的SQL该如何写？有两种方法！
5.SQL查询一个表中类别字段中Max()最大值对应的记录 (获取max某个属性最大值并获取其属性)

往期推荐

文章目录

微信公众号

广告位

诚心邀请广大金主爸爸洽谈合作

每日一省

isNaN 和 Number.isNaN 函数的区别？

1.函数 isNaN 接收参数后，会尝试将这个参数转换为数值，任何不能被转换为数值的的值都会返回 true，因此非数字值传入也会返回 true ，会影响 NaN 的判断。

2.函数 Number.isNaN 会首先判断传入参数是否为数字，如果是数字再继续判断是否为 NaN ，不会进行数据类型的转换，这种方法对于 NaN 的判断更为准确。

每日二省

为什么0.1+0.2 ! == 0.3，如何让其相等?

一个直接的解决方法就是设置一个误差范围，通常称为“机器精度”。对JavaScript来说，这个值通常为2-52，在ES6中，提供了Number.EPSILON属性，而它的值就是2-52，只要判断0.1+0.2-0.3是否小于Number.EPSILON，如果小于，就可以判断为0.1+0.2 ===0.3。

每日三省

== 操作符的强制类型转换规则？

1.首先会判断两者类型是否**相同，**相同的话就比较两者的大小。

2.类型不相同的话，就会进行类型转换。

3.会先判断是否在对比 null 和 undefined，是的话就会返回 true。

4.判断两者类型是否为 string 和 number，是的话就会将字符串转换为 number。

5.判断其中一方是否为 boolean，是的话就会把 boolean 转为 number 再进行判断。

6.判断其中一方是否为 object 且另一方为 string、number 或者 symbol，是的话就会把 object 转为原始类型再进行判断。

每日英语

Happiness is time precipitation, smile is the lonely sad.

幸福是年华的沉淀，微笑是寂寞的悲伤。