我有一张名为 results
的数据库表格,其中包含多列数据,显示每分钟进球数量的累加计数。
例如:
f_total_ftg # full time goals
f_total_htg # half time goals
f_total_1mg # 1 minute goals
每次在数据库中插入数据都有一个列
f_datetime
,这是相关的时间戳。我试图得到每个目标列的平均值,然后取总体平均值和最近2周的平均值并除以2。
例子:
f_avg_total_ftg_overall = 3.12
f_avg_total_ftg_last_2_weeks = 2.42
f_avg_ftg = (f_avg_total_ftg_overall + f_avg_total_ftg_last_2_weeks) / 2
我目前的解决方案是将每一列整体/最近2周分开处理,在我的Python代码中返回一个字典,然后进行最终计算,但我认为这应该可以在一个查询中完成。
我目前拥有的:
SELECT AVG((SELECT AVG(f_total_ftg) as x FROM results WHERE f_datetime < '2020-07-01 01:30:00')) AS ft_x,
AVG((SELECT AVG(f_total_ftg) as x FROM results WHERE f_datetime between '2020-07-01 01:30:00' - INTERVAL 13 DAY AND '2020-07-01 01:30:00')) AS ft_y,
AVG((SELECT AVG(f_total_1mg) as x FROM results WHERE f_datetime < '2020-07-01 01:30:00')) AS 1m_x,
AVG((SELECT AVG(f_total_1mg) as x FROM results WHERE f_datetime between '2020-07-01 01:30:00' - INTERVAL 13 DAY AND '2020-07-01 01:30:00')) AS 1m_y,
AVG((SELECT AVG(f_total_htg) as x FROM results WHERE f_datetime < '2020-07-01 01:30:00')) AS ht_total,
AVG((SELECT AVG(f_total_htg) as x FROM results WHERE f_datetime between '2020-07-01 01:30:00' - INTERVAL 13 DAY AND '2020-07-01 01:30:00')) AS ht_last14d
FROM results
如何简化这个?