时间戳

首页 > 解决方案 > MySQL 过去 14 天的移动平均线

mysql - MySQL 过去 14 天的移动平均线

问题描述

在我的 MySQL 数据库中，我有以下名为 MYDATA 的视图（最初组合了 2 个数据表）。

表 MYDATA

用户 我的约会 项目
17 2020-01-01 1.0
22 2020-01-01 6.0
17 2020-01-02 3.2
17 2020-01-04 4.0
17 2020-01-08 1.0
17 2020-01-09 6.2
22 2020-01-09 4.0
17 2020-01-10 5.3

如您所见，并非所有日期（myDate 列）都包含项目。对于选定的用户（即用户 17），我需要计算过去 14 天（包括“这一天”）所有日期（即包括 2020-01-03 不包括在MYDATA 表）。所以基本上我想获得以下内容：

用户 我的约会 结果
17 2020-01-01 （过去 14 天的平均值）
17 2020-01-02 （过去 14 天的平均值）
17 2020-01-03 （过去 14 天的平均值）
... ... ...
17 2020-12-30 （过去 14 天的平均值）
17 2020-12-31 （过去 14 天的平均值）

随意在 SQLFiddle 中使用它：http ://sqlfiddle.com/#!9/02cc94/1

如果需要，我还有一个包含所有年份日期的表格“日历”。

表日历

我的约会
2020-01-01
2020-01-02
2020-01-03
2020-01-04
2020-01-05
2020-01-06

请问我该如何进行？谢谢你的帮助。我已经在这个问题上停留了几个月。

标签： mysqlmoving-averagemysql-5.6

解决方案

寻找

SELECT user, t1.myDate, SUM(t2.items) / 14 avg_items
FROM calendar t1
JOIN test t2 ON t2.myDate BETWEEN t1.myDate - INTERVAL 13 DAY and t1.myDate
GROUP BY t2.user, t1.myDate
ORDER BY 1,2

fiddle - MySQL8 特定的构造 (CTE) 仅用于calendar表生成。

推荐阅读