How to extract a week from a date in MySQL using an arbitrary weekday as the start of the week?

Question

I have a data set (say "x" dated "xdate") for which I would like to receive weekly weekly data for the week. I can do this using SQL (for MYSQL) as follows:

SELECT 
    YEAR(xdate), 
    WEEKOFYEAR(xdate), avg(x) 
FROM 
    xtable
GROUP BY 
    YEAR(xdate),
    WEEKOFYEAR(xdate);

However, I would also like to group the weeks using an arbitrary weekday, like "start of the week." Looking at the documentation , it seems you can make a WEEK call using a parameter indicating the start of the week on Sunday or Monday. I would like to call, having the beginning of the week on any day of the week. Thus, average values ​​can be from medium to medium, etc. Is there any way to achieve this?

Answer 1

, .

:

SELECT
  YEAR(xdate),
  WEEKOFYEAR(date_add(xdate, interval 1 day) ),
  avg(x)
FROM
  xtable
GROUP BY
  YEAR(xdate),
  WEEKOFYEAR(date_add(xdate, interval 1 day));

, .