Как группировать по неделям в MySQL?

Сервер Oracle table предлагает встроенную функцию TRUNC(timestamp,'DY'). Эта функция преобразует любую временную метку в полночь в предыдущее воскресенье. Какой лучший способ сделать это в MySQL?

Oracle также предлагает TRUNC(timestamp,'MM') преобразовать временную метку в полночь в первый день месяца, в котором она происходит. В MySQL это просто:

TIMESTAMP(DATE_FORMAT(timestamp, '%Y-%m-01'))

Но этот трюк DATE_FORMAT не будет работать неделями. Я знаю о функции WEEK(timestamp), но мне действительно не нужен номер недели в течение года; этот материал предназначен для многолетней работы.

Ответ 1

Разобрался... немного громоздко, но вот оно.

FROM_DAYS(TO_DAYS(TIMESTAMP) -MOD(TO_DAYS(TIMESTAMP) -1, 7))

И, если ваши бизнес-правила говорят, что ваши недели начинаются по понедельникам, измените -1 на -2.

редактировать

Прошли годы, и я наконец-то нашел время написать это. http://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/

Ответ 2

Вы можете использовать как YEAR(timestamp) и WEEK(timestamp), и использовать оба этих выражения в предложении SELECT и GROUP BY.

Не слишком элегантно, но функционально...

И, конечно, вы можете объединить эти две части даты в одном выражении, то есть что-то вроде

SELECT CONCAT(YEAR(timestamp), '/', WEEK(timestamp)), etc...
FROM ...
WHERE ..
GROUP BY CONCAT(YEAR(timestamp), '/', WEEK(timestamp))

Редактировать: Как указывает Мартин , вы также можете использовать YEARWEEK(mysqldatefield), хотя ее вывод не так YEARWEEK(mysqldatefield) глаз, как более длинная формула выше.

Отредактируйте 2 [3,5 года спустя!]:
YEARWEEK(mysqldatefield) с необязательным вторым аргументом ( mode), установленным в 0 или 2, вероятно, является наилучшим способом агрегирования по целым неделям (т.е. включая недели, которые колеблются после 1 января), если это то, что нужно. Подход YEAR()/WEEK() первоначально предложенный в этом ответе, позволяет разделить агрегированные данные для таких "колеблющихся" недель на две части: одну с предыдущим годом, одну с новым годом.
Ежегодно необходимо проводить чистую срезку за счет наличия до двух неполных недель, по одной на каждом конце, часто в бухгалтерском учете и т.д., И в этом случае подход YEAR()/WEEK() лучше.

Ответ 3

Принятый ответ выше не работал у меня, потому что он заказывал недели по алфавиту, а не по хронологическому порядку:

Здесь мое решение для подсчета и группировки по неделям:

SELECT CONCAT(YEAR(date), '/', WEEK(date)) AS week_name, 
       YEAR(date), WEEK(date), COUNT(*)
FROM column_name
GROUP BY week_name
ORDER BY YEAR(DATE) ASC, WEEK(date) ASC

Формирует:

YEAR/WEEK   YEAR   WEEK   COUNT
2011/51     2011    51      15
2011/52     2011    52      14
2012/1      2012    1       20
2012/2      2012    2       14
2012/3      2012    3       19
2012/4      2012    4       19

Ответ 4

Вы можете получить сцепленный номер года и недели (200945) с помощью функции YEARWEEK(). Если я правильно понимаю вашу цель, это должно позволить вам группировать многолетние данные.

Если вам нужна фактическая отметка времени для начала недели, она менее приятная:

DATE_SUB( field, INTERVAL DAYOFWEEK( field ) - 1 DAY )

Для ежемесячного заказа вы можете рассмотреть функцию LAST_DAY() - сортировка будет в последний день месяца, но это должно быть эквивалентно к сортировке в первый день месяца... не так ли?

Ответ 5

Просто выберите это в списке:

DATE_FORMAT($yourDate, \'%X %V\') as week

group_by(week);

Ответ 6

Если вам нужна дата окончания недели, это также сработает. Это будет подсчитывать количество записей за каждую неделю. Пример. Если между (включительно) были созданы три рабочих заказа (включительно) 1/2/2010 и 1/8/2010 и 5 между (включительно) 1/9/2010 и 1/16/2010, это вернет:

3 1/8/2010
5 1/16/2010

Мне пришлось использовать дополнительную функцию DATE(), чтобы усечь мое поле datetime.

SELECT COUNT(*), DATE_ADD( DATE(wo.date_created), INTERVAL (7 - DAYOFWEEK( wo.date_created )) DAY) week_ending FROM work_order wo GROUP BY week_ending;