Find averages across different date time ranges

user_7908852 0
Viewed 42

I have a database table results with a number of columns, showing a running count of how many goals have been scored at each minute.

e.g.

f_total_ftg    # full time goals
f_total_htg    # half time goals
f_total_1mg    # 1 minute goals

Each insert in the database has a column f_datetime, which is the associated timestamp.

I'm trying to get an average of each goal column and then take the overall avg and last 2 weeks average and divide by 2.

Example:

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

My current solution is to take each column overall/last 2 weeks separately, returning a dict in my python code where I do the end calculation, but this should be doable in one query I think.

What I currently have:

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

How can I simplify this?

mysql
1 Answers

Your query can be simplified with conditional aggregation like this:

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

Also the query could be further simplified to:

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'

if you included the = in the inequality <= '2020-07-01 01:30:00' of your CASE expressions.

Now you can select from it and do your calculations:

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

I'm not sure that (ht_total + ht_last14d) / 2 is what you need but I think you get the idea.

user_104988280
Related