Wie gruppiere ich nach Woche in MySQL?

Question

Oracles Tabellenserver bietet eine integrierte Funktion, TRUNC(timestamp,'DY'). Diese Funktion konvertiert jeden Zeitstempel auf Mitternacht des vorherigen Sonntags. Wie lässt sich das am besten in MySQL umsetzen?

Oracle bietet auch TRUNC(timestamp,'MM') an, um einen Zeitstempel auf Mitternacht des ersten Tages des Monats, in dem er liegt, umzurechnen. In MySQL ist dies einfach:

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

Aber dieser DATE_FORMAT Trick funktioniert nicht für Wochen. Ich kenne zwar die WEEK(timestamp) Funktion, aber ich möchte wirklich nicht die Wochennummer innerhalb des Jahres; das Ganze bezieht sich auf mehrjährige Arbeiten.

Answer 1

Sie können sowohl YEAR(timestamp) als auch WEEK(timestamp) verwenden und beide Ausdrücke in der SELECT- und GROUP BY-Klausel verwenden.

Nicht übermäßig elegant, aber funktional...

Und natürlich können Sie diese beiden Datenteile auch in einem einzigen Ausdruck kombinieren, z. B. etwas wie

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

---

Bearbeiten: Wie Martin hervorhebt, können Sie auch die YEARWEEK(mysqldatefield)-Funktion verwenden, obwohl ihre Ausgabe nicht so benutzerfreundlich ist wie die längere Formel oben.

---

Bearbeiten 2 [3 1/2 Jahre später!]:
YEARWEEK(mysqldatefield) mit dem optionalen zweiten Argument (mode) auf 0 oder 2 eingestellt, ist wahrscheinlich der beste Weg, um nach vollständigen Wochen zu gruppieren (d. h. einschließlich Wochen, die über den 1. Januar hinausgehen), wenn dies gewünscht wird. Der Ansatz mit YEAR() / WEEK(), der in dieser Antwort ursprünglich vorgeschlagen wurde, hat den Effekt, dass die aggregierten Daten für solche "überkreuzenden" Wochen in zwei Teile aufgeteilt werden: eine mit dem früheren Jahr, eine mit dem neuen Jahr.
Ein sauberer Schnitt jedes Jahr, mit bis zu zwei partiellen Wochen, eine an jedem Ende, wird oft in der Buchhaltung usw. bevorzugt, und dafür ist der Ansatz mit YEAR() / WEEK() besser.

Answer 2

Ich habe es herausgefunden... es ist etwas umständlich, aber hier ist es.

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

Und wenn deine Unternehmensregeln besagen, dass deine Wochen am Montag beginnen, ändere das -1 zu -2.

---

Bearbeiten

Jahre sind vergangen und ich habe endlich Zeit gefunden, dies aufzuschreiben. https://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/

Answer 3

Die oben akzeptierte Antwort hat bei mir nicht funktioniert, da sie die Wochen nach alphabetischer, nicht aber nach chronologischer Reihenfolge geordnet hat:

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

Hier ist meine Lösung zum Zählen und Gruppieren nach Woche:

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

Erzeugt:

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

Answer 4

Sie können das konkatenierte Jahr und die Wochennummer (200945) mit der YEARWEEK() Funktion erhalten. Wenn ich Ihr Ziel richtig verstehe, sollte das Ihnen ermöglichen, Ihre Mehrjahresdaten zu gruppieren.

Wenn Sie den tatsächlichen Zeitstempel für den Beginn der Woche benötigen, ist es weniger schön:

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

Für die monatliche Sortierung könnten Sie die LAST_DAY() Funktion in Betracht ziehen - die Sortierung erfolgt nach dem letzten Tag des Monats, aber das sollte äquivalent zur Sortierung nach dem ersten Tag des Monats sein ... oder nicht?

Answer 5

Fügen Sie dies einfach in der Auswahl hinzu :

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

Und

group_by(week);