在unix时间戳中获取每个特定epoch范围内的值的平均值，在MySQL中的特定条件下返回-1

Question

我有一个MySQL表，它有一些记录，如下所示：

unix_timestamp           value
1001                     2
1003                     3
1012                     1
1025                     5
1040                     0
1101                     3
1105                     4
1130                     0
...

我想计算每10个时期的平均值，以查看以下结果：

unix_timestamp_range         avg_value
1001-1010                    2.5
1011-1020                    1
1021-1030                    5
1031-1040                    0
1041-1050                   -1
1051-1060                   -1
1061-1070                   -1
1071-1080                   -1
1081-1090                   -1
1091-1100                   -1
1101-1110                    3.5
1111-1120                   -1
1121-1130                    0
...

我看到了一些类似的答案，比如在这里输入链接描述，在这里输入链路描述，在那里输入链路描述。但这些答案并不能解决我的特定问题。如何获得上述结果？

Answer 1

最简单的方法是使用日历表。考虑这种方法：

SELECT
CONCAT(CAST(cal.ts AS CHAR(50)), '-', CAST(cal.ts + 9 AS CHAR(50))) AS unix_timestamp_range,
CASE WHEN COUNT(t.value) > 0 THEN AVG(t.value) ELSE -1 END AS avg_value
FROM
(
SELECT 1001 AS ts UNION ALL
SELECT 1011 UNION ALL
SELECT 1021 UNION ALL
...
) cal
LEFT JOIN yourTable t
ON t.unix_timestamp BETWEEN cal.ts AND cal.ts + 9
GROUP BY
cal.ts
ORDER BY
cal.ts;

在实践中，如果您需要经常进行这种查询，而不是上面标记为cal的内联子查询，那么您可能希望有一个完整的专用表来表示所有时间戳范围。