基于巧妙的常见查询的SQL移动平均线是行不通的

Question

无法使移动平均线在我的桌子上工作。 它只是吐回每一行的行数据，而不是产生我寻求的平均值。

基于 ACQ 的移动平均线 SQL：（http://www.artfulsoftware.com/infotree/qrytip.php?id=445）

SELECT t1.item,t1.dt, AVG(t2.qty) AS 5DayAvg 
FROM t t1 
JOIN t t2 ON t1.item=t2.item AND DATEDIFF(t1.dt, t2.dt) BETWEEN 0 AND 4 
GROUP BY t1.item,t1.dt; 

我改编的代码：

SELECT t1.id, t1.t, t1.atm_p, AVG(t2.atm_p) as 20sec_atm_p_avg
FROM test_data t1
JOIN test_data t2 ON t1.id = t2.id AND TIME_TO_SEC(TIMEDIFF(t1.t, t2.t)) BETWEEN 0 AND 19
GROUP BY t1.t;

我的test_data表：

id,t,atm_p
'1', '08:01:25', '12.9'
'2', '08:01:35', '12.9'
'3', '08:01:45', '13.2'
'4', '08:01:55', '14.0'
'5', '08:02:05', '12.0'
'6', '08:02:15', '13.9'
'7', '08:02:20', '12.9'
'8', '08:02:25', '14.0'
'9', '08:02:30', '14.0'
'10', '08:02:35', '12.0'
'11', '08:02:40', '11.0'
'12', '08:02:45', '3.0'
'13', '08:02:50', '14.0'
'14', '08:02:55', '12.0'
'15', '08:03:00', '12.9'

我的输出错误：

id  t       atm_p   20sec_atm_p_avg
1   08:01:25    12.9    12.90000
2   08:01:35    12.9    12.90000
3   08:01:45    13.2    13.20000
4   08:01:55    14.0    14.00000
5   08:02:05    12.0    12.00000
6   08:02:15    13.9    13.90000
7   08:02:20    12.9    12.90000
8   08:02:25    14.0    14.00000
9   08:02:30    14.0    14.00000
10  08:02:35    12.0    12.00000
11  08:02:40    11.0    11.00000
12  08:02:45    3.0 3.00000
13  08:02:50    14.0    14.00000
14  08:02:55    12.0    12.00000
15  08:03:00    12.9    12.90000

如有任何帮助，将不胜感激！

Answer 1

我发现从相关子查询的角度考虑移动平均线要容易得多，而不是用聚合连接。

select t1.*,
       (select avg(t2.qty)
        from t t2
        where TIME_TO_SEC(TIMEDIFF(t1.t, t2.t)) BETWEEN 0 AND 19
       ) as `5DayAvg`
from t t1;

但是，查询的问题在于 id 上的联接。 只需删除它：

SELECT t1.id, t1.t, t1.atm_p, AVG(t2.atm_p) as 20sec_atm_p_avg
FROM test_data t1 JOIN
     test_data t2
     ON TIME_TO_SEC(TIMEDIFF(t1.t, t2.t)) BETWEEN 0 AND 19
GROUP BY t1.id, t1.t;

Answer 2

您是否尝试过根据 20 秒Date_Add加入？

SELECT 
      t1.id, 
      t1.t, 
      t1.atm_p, 
      AVG(t2.atm_p) as 20sec_atm_p_avg
   FROM 
      test_data t1
         JOIN test_data t2 
            ON t1.id = t2.id 
            AND t2.t >= t1.t
            AND t2.t <= t1.t + INTERVAL 20 SECONDS
   GROUP BY 
      t1.t;