Jak grupować według tygodni w MySQL?

Oferty serwerowe tabeli Oracle wbudowany funkcji TRUNC(timestamp,'DY'). Ta funkcja konwertuje dowolny znacznik czasu na północ w poprzednią niedzielę. Jaki jest najlepszy sposób na zrobienie tego w MySQL?

Oracle oferuje również TRUNC(timestamp,'MM')konwersję sygnatury czasowej na północ pierwszego dnia miesiąca, w którym występuje. W MySQL ten jest prosty:

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

Ale ta DATE_FORMATsztuczka nie będzie działać przez tygodnie. Znam tę WEEK(timestamp)funkcję, ale naprawdę nie chcę numeru tygodnia w ciągu roku; te rzeczy są przeznaczone do wieloletniej pracy.

Można używać zarówno YEAR(timestamp)i WEEK(timestamp), i używać obu tych wyrażeń w SELECTi w GROUP BYklauzuli.

Niezbyt eleganckie, ale funkcjonalne ...

I oczywiście możesz połączyć te dwie części daty w jednym wyrażeniu, np

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

Edycja : Jak podkreśla Martin , możesz również użyć tej YEARWEEK(mysqldatefield)funkcji, chociaż jej wyjście nie jest tak przyjazne dla oka, jak powyższa dłuższa formuła.

Edytuj 2 [3 1/2 roku później!]:
YEARWEEK(mysqldatefield)Z opcjonalnym drugim argumentem ( mode) ustawionym na 0 lub 2 jest prawdopodobnie najlepszym sposobem agregacji według pełnych tygodni (tj. Włączając tygodnie, które przypadają na 1 stycznia), jeśli tak jest co jest pożądane. YEAR() / WEEK()Podejście początkowo zaproponowany w tej odpowiedzi ma skutek rozszczepienia dane zagregowane dla takich „okrakiem” tydzień na dwie części: jedną z byłym roku, jeden z nowego roku.
Czyste cięcie każdego roku, kosztem dwóch częściowych tygodni, po jednym na każdym końcu, jest często pożądane w księgowości itp. I dlatego YEAR() / WEEK()podejście jest lepsze.

Przyjęta powyżej odpowiedź nie zadziałała dla mnie, ponieważ uporządkowała tygodnie w kolejności alfabetycznej, a nie chronologicznej:

2012/1
2012/10
2012/11
...
2012/19
2012/2

Oto moje rozwiązanie do liczenia i grupowania według tygodnia:

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

Generuje:

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

Zrozumiałem ... to trochę kłopotliwe, ale oto jest.

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

Jeśli według reguł biznesowych tygodnie rozpoczynają się od poniedziałku, zmień opcję -1na -2.

Edytować

Minęły lata i wreszcie zabrałem się za spisanie tego. http://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/

Połączony rok i numer tygodnia (200945) można uzyskać za pomocą funkcji YEARWEEK () . Jeśli dobrze zrozumiem Twój cel, powinno to umożliwić Ci pogrupowanie danych z wielu lat.

Jeśli potrzebujesz aktualnego sygnatury czasowej początku tygodnia, jest mniej przyjemny:

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

W przypadku zamawiania miesięcznego możesz rozważyć funkcję LAST_DAY () - sortowanie byłoby według ostatniego dnia miesiąca, ale powinno to być równoważne sortowaniu według pierwszego dnia miesiąca ... prawda?

Po prostu dodaj to w zaznaczeniu:

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

I

group_by(week);

Jeśli potrzebujesz daty zakończenia tygodnia, to również zadziała. Spowoduje to policzenie liczby rekordów w każdym tygodniu. Przykład: Jeśli trzy zlecenia zostały utworzone między (włącznie) 1/2/2010 a 1/8/2010, a 5 utworzono między (włącznie) 1/9/2010 a 1/16/2010, zwróci to:

03.08.2010
5 16.01.2010

Musiałem użyć dodatkowej funkcji DATE (), aby obciąć moje pole daty i godziny.

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;