0

I am trying to get the average of all weeks for each month. How can I do this in MySQL? Below is what I have and also what I need:

SELECT                      
  date_part(year, date_of_act) AS CALENDAR_YEAR,                        
  date_part(week, date_of_act) AS CALENDAR_WEEK_OF_YEAR,                        
  (num_active_divs / total_num_divs) AS "Weekly average for each year of week"                      
FROM                        
  Infotable                     
GROUP BY calendar_year, calendar_week_of_year                       
ORDER BY calendar_year, calendar_week_of_year



What I have:        What I have:    What I have:            

Calendar_Year   Days of the year    Calendar_Week_of_year   Weekly average for each year of week            

2018,   Jan 01 - Jan 07,    1,  0.266855            
2018,   Jan 08 - Jan 14,    2,  0.297223            
2018,   Jan 15 - Jan 21,    3,  0.308583            
2018,   Jan 22 - Jan 28,    4,  0.309994            
2018,   Jan 29 - Feb 04,    5,  0.317419            
2018,   Feb 05 - Feb 11,    6,  0.316454            
2018,   Feb 12 - Feb 18,    7,  0.313929            
2018,   Feb 19 - Feb 25,    8,  0.315489            
2018,   Feb 26 - Mar 04,    9,  0.3218          
2018,   Mar 05 - Mar 11,    10, 0.308509            
2018,   Mar 12 - Mar 18,    11, 0.302866            
2018,   Mar 19 - Mar 25,    12, 0.31586         

What I need:                What I need:    What I need:    What I need:

Calendar_Year       Calendar_Week_of_year   Weekly average for each year of week    Calendar_Month_of_year  Week_of_month   Average for all weeks in that month

2018,   Jan 01 - Jan 07,    1,  0.266855,   1,  1   
2018,   Jan 08 - Jan 14,    2,  0.297223,   1,  2   
2018,   Jan 15 - Jan 21,    3,  0.308583,   1,  3   
2018,   Jan 22 - Jan 28,    4,  0.309994,   1,  4, (0.266855+0.297223+0.308583+0.309994)/4

2018,   Jan 29 - Feb 04,    5,  0.317419,   2,  1   
2018,   Feb 05 - Feb 11,    6,  0.316454,   2,  2   
2018,   Feb 12 - Feb 18,    7,  0.313929,   2,  3   
2018,   Feb 19 - Feb 25,    8,  0.315489,   2,  4,  (0.317419+0.316454+0.313929+0.315489)/4

2018,   Feb 26 - Mar 04,    9,  0.32180,    3,  1   
2018,   Mar 05 - Mar 11,    10, 0.308509,   3,  2   
2018,   Mar 12 - Mar 18,    11, 0.302866,   3,  3   
2018,   Mar 19 - Mar 25,    12, 0.31586,    3,  4,  (0.3218+0.308509+0.302866+0.31586)/4
PravinS
  • 2,640
  • 3
  • 21
  • 25
user10040592
  • 1

1 Answers1

0

One option here would be to join to a subquery which finds the monthly averages:

SELECT
    t1.*,
    t2.avg_weekly_average
FROM yourTable t1
INNER JOIN
(
    SELECT Calendar_Year, (Calendar_Week_of_year - 1) / 4 AS month,
        AVG(weekly_average) AS avg_weekly_average
    FROM yourTable
    GROUP BYCalendar_Year, (Calendar_Week_of_year - 1) / 4
) t2
    ON t1.Calendar_Year = t2.Calendar_Year AND
       (t1.Calendar_Week_of_year - 1) / 4 = t2.month;

If you are using MySQL 8+, then you might be able to use AVG as an analytic function here. In this case, the query becomes a bit less terse:

SELECT *,
    AVG(weekly_average) OVER (PARTITION BY Calendar_Year,
        (Calendar_Week_of_year - 1) / 4) avg_weekly_average
FROM yourTable t1;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Hi Time, thanks for your reply. I tried the 'AVG' option, but it did not work as expected. Please see the output below, which is different from the expected values (last column), for some reason - may be the select statement needs to be edited in some fashion? – user10040592 Jul 06 '18 at 18:23
  • `Calendar_Year Calendar_Week_of_year Weekly average for each year of week Average for all weeks in that month Expected value 2018 1 0.266637 0.204148429 0.29542225 2018 2 0.29698 0.23631892 0.29542225 2018 3 0.308331 0.238056842 0.29542225 2018 4 0.309741 0.246393239 0.29542225 2018 5 0.31716 0.259385717 0.315565 2018 6 0.316196 0.259694425 0.315565 2018 7 0.313673 0.261424683 0.315565 2018 8 0.315231 0.260448818 0.315565 2018 9 0.321537 0.262954257 0.31200375 2018 10 0.308257 0.263811621 0.31200375 2018 11 0.302619 0.25810876 0.31200375 2018 12 0.315602 0.264338671 0.312003` – user10040592 Jul 06 '18 at 18:24
  • Calendar_Year Calendar_Week_of_year Weekly average for each year of week Average for all weeks in that month Expected value 2018 1 0.266637 0.204148429 0.29542225 2018 2 0.29698 0.23631892 0.29542225 2018 3 0.308331 0.238056842 0.29542225 2018 4 0.309741 0.246393239 0.29542225 2018 5 0.31716 0.259385717 0.315565 2018 6 0.316196 0.259694425 0.315565 2018 7 0.313673 0.261424683 0.315565 2018 8 0.315231 0.260448818 0.315565 2018 9 0.321537 0.262954257 0.31200375 2018 10 0.308257 0.263811621 0.31200375 2018 11 0.302619 0.25810876 0.31200375 2018 12 0.315602 0.264338671 0.31200375 – user10040592 Jul 06 '18 at 18:24