在不同日期时间范围内查找平均值

Question

我有一张名为 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

如何简化这个？

- arsenal88

Answer 1

您可以使用条件聚合来简化查询，如下所示：

SELECT 
  AVG(CASE WHEN f_datetime < '2020-07-01 01:30:00' THEN f_total_ftg END) AS ft_x,
  AVG(CASE WHEN f_datetime BETWEEN '2020-07-01 01:30:00' - INTERVAL 13 DAY AND '2020-07-01 01:30:00' THEN f_total_ftg END) AS ft_y,
  AVG(CASE WHEN f_datetime < '2020-07-01 01:30:00' THEN f_total_1mg END) AS 1m_x,
  AVG(CASE WHEN f_datetime BETWEEN '2020-07-01 01:30:00' - INTERVAL 13 DAY AND '2020-07-01 01:30:00' THEN f_total_1mg END) AS 1m_y,
  AVG(CASE WHEN f_datetime < '2020-07-01 01:30:00' THEN f_total_htg END) AS ht_total,
  AVG(CASE WHEN f_datetime BETWEEN '2020-07-01 01:30:00' - INTERVAL 13 DAY AND '2020-07-01 01:30:00' THEN f_total_htg END) AS ht_last14d
FROM results

此句话的英文原文已经是汉语翻译，因此我直接将其翻译成中文：此查询可以进一步简化为：

���

SELECT 
  AVG(f_total_ftg) AS ft_x,
  AVG(CASE WHEN f_datetime >= '2020-07-01 01:30:00' - INTERVAL 13 DAY THEN f_total_ftg END) AS ft_y,
  AVG(f_total_1mg) AS 1m_x,
  AVG(CASE WHEN f_datetime >= '2020-07-01 01:30:00' - INTERVAL 13 DAY THEN f_total_1mg END) AS 1m_y,
  AVG(f_total_htg) AS ht_total,
  AVG(CASE WHEN f_datetime >= '2020-07-01 01:30:00' - INTERVAL 13 DAY THEN f_total_htg END) AS ht_last14d
FROM results
WHERE f_datetime <= '2020-07-01 01:30:00'

如果您在CASE表达式的不等式<= '2020-07-01 01:30:00'中包含了=，那么现在您可以从中进行选择并进行计算：

SELECT ft_x, ft_Y, 1m_x, 1m_y, ht_total, ht_last14d, 
       (ht_total + ht_last14d) / 2,
       ...........................
FROM (
  <the query here>
) t

我不确定 (ht_total + ht_last14d) / 2 是否是你需要的，但我认为你理解了这个想法。

- forpas