mysql 查询相邻两行数据的时间间隔 且 小于指定时间间隔的增加状态标记
1. 业务场景
监管公司下的商家的售出记录,若某个商家、某业务类型、售出的订单时间间隔太小,说明可能存在人为的操作问题
2. 解决方案
mysql 先按商家、业务、时间排序,然后实现计算相邻订单时间间隔,且将小于指定时间的记录做状态标记
SQL语句如下:
select
TIMESTAMPDIFF(SECOND, lag(a.lrsj) over (order by a.czdw,a.ywlx,a.lrsj asc), a.lrsj) diff,
case
when
a.czdw = lag(a.czdw) over (order by a.czdw,a.ywlx,a.lrsj asc)
and a.ywlx = lag(a.ywlx) over (order by a.czdw,a.ywlx,a.lrsj asc)
and TIMESTAMPDIFF(SECOND, lag(a.lrsj) over (order by a.czdw, a.ywlx, a.lrsj asc), a.lrsj) <![CDATA[ <= ]]> #{sec}
then 1
else 0
end as yczt,
lag(a.lsh) over (order by a.czdw, a.ywlx, a.lrsj asc) laglsh
from c_flow a
字段说明:
lsh:流水号
lrsj : 录入时间
czdw:单位编号
ywlx:业务类型
关键语法:
lag(a.ywlx) over (order by a.czdw,a.ywlx,a.lrsj asc)
语法说明:上前记录(上条记录的字段)按什么排序规则得到上条记录(排序的方式)
sql语法说明:
1)、两条记录间隔时间:
TIMESTAMPDIFF(SECOND, lag(a.lrsj) over (order by a.czdw,a.ywlx,a.lrsj asc), a.lrsj) diff
2)、增加记录是否异常字段:小于等于指定时间为1,大于指定时间为0
case
when
a.czdw = lag(a.czdw) over (order by a.czdw,a.ywlx,a.lrsj asc)
and a.ywlx = lag(a.ywlx) over (order by a.czdw,a.ywlx,a.lrsj asc)
and TIMESTAMPDIFF(SECOND, lag(a.lrsj) over (order by a.czdw, a.ywlx, a.lrsj asc), a.lrsj) <![CDATA[ <= ]]> 指定时间间隔
then 1
else 0
end as yczt,
3)、对比的上条记录的流水号(主键)
lag(a.lsh) over (order by a.czdw, a.ywlx, a.lrsj asc) laglsh