Gruppieren von Daten nach Woche in SQL Server

Dies ist ein zusätzlicher Inhalt für den LearnSQL.de Kurs Kundenverhaltensanalyse in SQL Server.

In diesem Kurs haben wir Ihnen gezeigt, wie Sie den Kundenlebenszyklus (Kundengewinnung, Konvertierung, Aktivität, Bindung und Abwanderung) mit SQL analysieren können. Wir haben Kohorten von Kundenregistrierungen besprochen, d.h. Gruppen von Kunden, die sich im gleichen Zeitraum (z.B. gleiche Woche, gleicher Monat) registriert haben. Durch die Analyse von Kundenregistrierungskohorten können Sie Registrierungstrends erkennen und Registrierungskohorten mit Marketingkampagnen in Verbindung bringen.

In der Praxis sind wöchentliche Registrierungskohorten am besten geeignet. Jährliche, vierteljährliche oder monatliche Registrierungskohorten sind für eine aussagekräftige Analyse zu ungenau. Andererseits sind tägliche oder stündliche Registrierungskohorten zu spezifisch. Wöchentliche Registrierungskohorten sind in der Regel genau richtig.

In diesem Artikel werden wir verschiedene Möglichkeiten zur Gruppierung von Daten nach Wochen in SQL Server untersuchen.

Wie gruppiert man Daten in SQL Server nach Woche?

SQL Server bietet eine Funktion namens DATEPART(), die einen bestimmten Teil(Jahr, Quartal, Monat, Woche, Stunde, Minute usw.) eines bestimmten Datums zurückgibt.

Um Kunden, die sich im Jahr 2018 registriert haben, nach der Woche zu gruppieren, können Sie diese Abfrage verwenden:

SELECT
  DATEPART(week, RegistrationDate) AS Week,
  COUNT(CustomerID) AS Registrations
FROM Customers
WHERE '20180101' <= RegistrationDate
  AND RegistrationDate < '20190101'
GROUP BY DATEPART(week, RegistrationDate)
ORDER BY DATEPART(week, RegistrationDate);

Wie Sie sehen, benötigt die Funktion DATEPART() zwei Argumente: Datumsteil (d. h. die Kennung des gewünschten Teils) und das Datum, aus dem Sie den Teil extrahieren.

Die Funktion DATEPART() hat zwei datepart-Argumente, die Wochendaten zurückgeben:

• week (auch abgekürzt wk, ww).
• iso_week (auch abgekürzt isowk, isoww).

Wir werden die Unterschiede zwischen diesen beiden Typen in Kürze erläutern. Doch zunächst müssen wir eine andere Einstellung besprechen.

Die DATEFIRST-Einstellung

Mit der DATEFIRST-Einstellung wird SQL Server mitgeteilt, welcher Wochentag als erster Tag der Woche betrachtet werden soll. DATEFIRST kann einer der folgenden Werte sein:

Die Einstellung DATEFIRST hängt von der Sprachversion von SQL Server ab. Der Standardwert für U.S.-Englisch ist 7 (d.h. Sonntag).

Sie können den Wert von DATEFIRST wie folgt ändern:

SET DATEFIRST 1

DATEFIRST ist eine Sitzungseinstellung, d. h. Sie können sie ändern, ohne dass sich dies auf andere Benutzer auswirkt.

Den aktuellen Wert der Einstellung DATEFIRST können Sie mit der Funktion @@DATEFIRST ermitteln:

SELECT @@DATEFIRST;

Ergebnis:

7

Der erste Tag der Woche ist 7, d. h. Sonntag.

Verwendung von DATEPART() mit Woche

Nachdem wir nun die Einstellung DATEFIRST kennen, wollen wir uns ansehen, wie DATEPART() mit week funktioniert.

Das Verhalten von DATEPART() mit Woche hängt davon ab, welchen Tag DATEFIRST als ersten Tag der Woche eingestellt hat. Die Wochen eines jeden Jahres sind separat nummeriert. Woche 1 ist die Woche, die den 1. Januar enthält (aber nicht unbedingt mit ihm beginnt).

Schauen wir uns ein Beispiel an. Die Abbildung zeigt drei verschiedene Kalender für den Januar 2019; jeder gibt einen anderen Wochentag als ersten Tag der Woche an:

• Im ersten Kalender ist der Sonntag der erste Tag der Woche, wie er in den Vereinigten Staaten üblich ist.
• Der zweite Kalender zeigt den Montag als ersten Tag der Woche an, wie er in Europa üblich ist.
• Der dritte Kalender verwendet den Donnerstag als ersten Tag der Woche. (Dies ist nur ein Beispiel für eine untypische Woche.)

DATEPART() mit Woche verwendet die in der Abbildung gezeigte Wochennummerierung:

• Wenn der Sonntag der erste Tag der Woche ist (DATEFIRST = 7), beginnt Woche 2 am Sonntag, dem 6. Januar, und endet am Samstag, dem 12. Januar.
• Wenn Montag der erste Tag der Woche ist (DATEFIRST = 1), beginnt Woche 2 am Montag, dem 7. Januar, und endet am Sonntag, dem 13. Januar.
• Wenn der Donnerstag der erste Tag der Woche ist (DATEFIRST = 4), beginnt Woche 2 am Donnerstag, dem 3. Januar, und endet am Mittwoch, dem 9. Januar.

Eine typische Verwendung von DATEPART() mit week ist die Gruppierung von Daten nach Wochen über die GROUP BY-Klausel. Wir verwenden sie auch in der SELECT Klausel, um die Wochennummer anzuzeigen. Sehen Sie sich die folgende Abfrage und ihr Ergebnis an:

SELECT
  DATEPART(week, RegistrationDate) AS Week,
  COUNT(CustomerID) AS Registrations
FROM Customers
WHERE '20180101' <= RegistrationDate
  AND RegistrationDate < '20190101'
GROUP BY DATEPART(week, RegistrationDate)
ORDER BY DATEPART(week, RegistrationDate);

Wenn Sie die Wochennummern nicht lesen können, lesen Sie den Artikel Wie man den ersten Tag der Woche ermittelt.

Beachten Sie, dass bei DATEPART() mit Woche die Woche, in der das Jahr endet und das nächste beginnt, oft geteilt wird. Mit anderen Worten: Die letzten Tage des Dezembers fallen in die Woche 52/53 des Vorjahres, während die ersten Tage des Januars in der Woche 1 des neuen Jahres liegen.

Die Verwendung von DATEPART() mit iso_week

DATEPART() hat ein weiteres Datumsargument für Wochen: iso_week. Dieses Argument behandelt die Wochen gemäß ISO 8601, einer internationalen Norm für den Austausch von Datums- und Zeitangaben.

In der ISO 8601-Norm beginnen die Wochen am Montag. Die Woche 1 eines Jahres ist die Woche, in der der erste Donnerstag des Jahres liegt. Diese Art der Wochennummerierung wird in der Regel in europäischen Ländern verwendet.

Die Woche 1 des Jahres 2017 war zum Beispiel Montag, der 2. Januar, bis Sonntag, der 8. Januar. Es ist wichtig zu beachten, dass der 1. Januar, wenn er auf einen Freitag, Samstag oder Sonntag fällt, als Teil der Woche 52/53 des vorherigen Jahres betrachtet wird.

Hier ist der Kalender für Januar 2017:

Auch hier verwenden Sie normalerweise DATEPART() mit iso_week in GROUP BY und SELECT:

SELECT
  DATEPART(iso_week, RegistrationDate) AS Week,
  COUNT(CustomerID) AS Registrations
FROM Customers
WHERE '20180101' <= RegistrationDate
  AND RegistrationDate < '20190101'
GROUP BY DATEPART(iso_week, RegistrationDate)
ORDER BY DATEPART(iso_week, RegistrationDate);

Wenn Sie die Wochennummern nicht lesen können, lesen Sie den Artikel Wie man den ersten Tag der Woche ermittelt.