SQL中@变量与:=操作符的用法及相邻会话平均时差计算问题

核心基础概念 #

• @var 是MySQL的用户自定义会话变量，整个会话连接期间有效
• := 是MySQL专用的赋值操作符：无论在什么语句场景下，只要出现:=就执行赋值操作，不会被判定为相等比较，这是这类查询的核心逻辑点，区别于普通的=（=在SELECT语句中默认是比较逻辑，只有在SET/UPDATE语句中才是赋值）

第一个SQL（相邻会话平均时间差）逻辑拆解 #

原SQL是三层嵌套结构，我们从最内层往外层捋：

第一层：变量初始化 #

SELECT @date_posted := NULL

这一步是给自定义变量@date_posted赋初始值NULL，别名vars，是MySQL中给用户变量预声明的通用写法。

第二层：遍历计算相邻差值 #

SELECT  
  TIME_TO_SEC(TIMEDIFF(@date_posted, date_posted)) AS period,
  @date_posted := date_posted
FROM  
  vars, messages
ORDER BY date_posted

执行逻辑严格按时间正序遍历messages表每一行：

1. 先拿当前行的date_posted和变量@date_posted里存的上一行的时间做差值，转成秒数存为period
2. 再把当前行的date_posted赋值给@date_posted，供下一行计算差值用
   第一行遍历时@date_posted是初始NULL值，所以第一行的period为NULL，后续行都会正常计算和上一行的时间差。

第三层：求平均时间差 #

SELECT AVG(period)
FROM 中间层结果集q

把中间层计算出的所有相邻时间差求平均值，NULL值会被AVG函数自动忽略，刚好排除了第一行的无效值。

第二个SQL（连续事件差值计算）逻辑拆解 #

逻辑和上面完全一致，只是计算的是温度差值：

1. 最内层初始化@r变量为0
2. 按time字段正序遍历temperatures表每一行：先算当前温度和@r里存的上一行温度的差值，再把当前温度赋值给@r
3. 最终得到每一行和上一行的温度差值

业务适配修改思路 #

如果你要计算自己业务表的相邻会话时间差，只需要修改几个对应参数即可：

• 把messages替换为你自己的业务表名
• 把date_posted替换为你表中存储会话时间的字段名
• 如果需要加过滤条件，在中间层FROM语句后加WHERE子句即可，比如计算某个用户的会话差，就加WHERE user_id = 对应ID

内容的提问来源于stack exchange，提问作者Sagar Moghe