Groupe SQL par fréquence dans une plage de dates
J'ai besoin d'écrire une procédure stockée qui accepte une date de début, une date de fin et une fréquence (jour, semaine, mois, sortingmestre, année) et génère un jeu de résultats basé sur ces parameters. Évidemment, la partie simple est la requête par plage de dates, mais comment regroupez-vous par fréquence?
Donc, si vous avez un set de données brutes comme ceci:
Date Count --------------------- 11/15/2011 6 12/16/2011 9 12/17/2011 2 12/18/2011 1 12/18/2011 4 Et j'appelle mon proc stocké comme ceci:
sp_Report '1/1/2011', '12 / 31/2011 ',' semaine '
- La jointure SQL par rapport aux plages de dates?
- Inclure les mois manquants dans la requête Group By
- SQL Server: récupération des loggings entre deux dates?
Je m'attendrais à des résultats comme celui-ci:
WeekOf Count --------------------- 11/19/2011 6 12/17/2011 11 12/24/2011 5
Il y a quelques questions ici:
1) Comment puis-je déterminer la date de la fin de la semaine (semaine se terminant le dimanche)?
2) Comment puis-je regrouper par plage de dates WeekOf?
Le script suivant représente la sortie d'une manière unifiée: il affiche les dates de début et de fin de la période, ainsi que le nombre total de points pour la période.
Cela a également déterminé les moyens de find les valeurs à grouper par. Fondamentalement, vous pouvez voir trois templates distincts: un pour la fréquence 'day' , un autre pour 'week' et un autre pour tous les autres types de fréquence.
Le premier est le plus simple: PeriodStart et PeriodEnd ne sont que des Date .
Pendant des semaines, j'utilise une astuce assez connue, selon laquelle le premier jour de la semaine est dérivé de la date donnée en en soustrayant une valeur inférieure d'un chiffre à son numéro de semaine. La fin de la semaine est similaire: nous ajoutons simplement 6 à la même expression.
Les mois, sortingmestres et années sont regroupés de la manière suivante. Le integer d'unités correspondantes entre la date zéro et la date donnée est ajouté à la date zéro. Cela nous donne le début de la période. La fin est trouvée très similaire, seulement nous ajoutons le nombre qui est supérieur à la différence. Cela produit le début de la période suivante , donc nous soustrayons un jour, ce qui nous donne la date de fin correcte.
SELECT PeriodStart, PeriodEnd, Count = SUM(Count) FROM ( SELECT PeriodStart = CASE @Frequency WHEN 'day' THEN Date WHEN 'week' THEN DATEADD(DAY, 1 - DATEPART(WEEKDAY, Date), Date) WHEN 'month' THEN DATEADD(MONTH, DATEDIFF(MONTH, 0, Date), 0) WHEN 'quarter' THEN DATEADD(QUARTER, DATEDIFF(QUARTER, 0, Date), 0) WHEN 'year' THEN DATEADD(YEAR, DATEDIFF(YEAR, 0, Date), 0) END, PeriodEnd = CASE @Frequency WHEN 'day' THEN Date WHEN 'week' THEN DATEADD(DAY, 7 - DATEPART(WEEKDAY, Date), Date) WHEN 'month' THEN DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, Date) + 1, 0)) WHEN 'quarter' THEN DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, Date) + 1, 0)) WHEN 'year' THEN DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, Date) + 1, 0)) END, Count FROM atable WHERE Date BETWEEN @DateStart AND @DateEnd ) s GROUP BY PeriodStart, PeriodEnd
-
EXEC spReport '1/1/2011', '12/31/2011', 'day':PeriodStart PeriodEnd Count ----------- ---------- ----- 2011-11-15 2011-11-15 6 2011-12-16 2011-12-16 9 2011-12-17 2011-12-17 2 2011-12-18 2011-12-18 5 -
EXEC spReport '1/1/2011', '12/31/2011', 'week':PeriodStart PeriodEnd Count ----------- ---------- ----- 2011-11-13 2011-11-19 6 2011-12-11 2011-12-17 11 2011-12-18 2011-12-24 5 -
EXEC spReport '1/1/2011', '12/31/2011', 'month':PeriodStart PeriodEnd Count ----------- ---------- ----- 2011-11-01 2011-11-30 6 2011-12-01 2011-12-31 16 -
EXEC spReport '1/1/2011', '12/31/2011', 'quarter':PeriodStart PeriodEnd Count ----------- ---------- ----- 2011-10-01 2011-12-31 22 -
EXEC spReport '1/1/2011', '12/31/2011', 'year':PeriodStart PeriodEnd Count ----------- ---------- ----- 2011-01-01 2011-12-31 22
Remarque: à partir de MSDN :
Évitez l'utilisation du préfixe sp_ lors de la désignation des procédures. Ce préfixe est utilisé par SQL Server pour désigner les procédures système. L'utilisation du préfixe peut entraîner la rupture du code d'application s'il existe une procédure système portant le même nom. Pour plus d'informations, consultez Conception de procédures stockées (moteur de database) .
Create procedure MyProc @startDate DateTime, @endDate DateTime, @freq varChar(5) As If @freq = "day" Select DateAdd(day, 0, datediff(day, 0, date)), Frequency, Sum(Count) From Table Group By DateAdd(day, 0, datediff(day, 0, date)) Else If @freq = "week" Select DateAdd(week, 0, datediff(week, 0, date)), Frequency, Sum(Count) From Table Group By DateAdd(week, 0, datediff(week, 0, date)) Else If @freq = "Month" Select DateAdd(Month, 0, datediff(Month, 0, date)), Frequency, Sum(Count) From Table Group By DateAdd(Month, 0, datediff(Month, 0, date)) Else If @freq = "Quarter" Select DateAdd(Quarter, 0, datediff(Quarter, 0, date)), Frequency, Sum(Count) From Table Group By DateAdd(Quarter, 0, datediff(Quarter, 0, date)) Else If @freq = "Year" Select DateAdd(Year, 0, datediff(Year, 0, date)), Frequency, Sum(Count) From Table Group By DateAdd(Year, 0, datediff(Year, 0, date))
Quelque chose comme ça devrait fonctionner.
select date_column, sum(count) from @table where date_column between @start_date and @end_date group by case @frequency when 'week' then datepart(week,date_column ) when 'year' then datepart(year,date_column) when 'quarter' then datepart(quarter,date_column) when ... end;
select `date` as weekOf, sum(amt) from myTable where `date` between '2011-10-01' and '2012-01-01' group by week(`date` )