SQL Server propose un ensemble de fonctions d’agrégation comme la somme, la moyenne, le comptage, etc..., ces fonctions combinées aux clauses OVER, offrent des possibilités de calcul et d'analyse très intéressantes.
Pour répondre à une problématique métier dont la solution technique devait, initialement, s'appuyer sur un :
COUNT (DISTINCT MaColonne1) OVER (PARTITION BY MaColonne2), j'ai été confronté à l'erreur suivante :
Incorrect syntax near 'distinct'
Après quelques recherches, il s’avère que c'est une limitation liée au moteur de base de données SQL Server.
En testant la requête sur une instance SQL 2012, cette limitation est toujours présente, la seule chose qui a été améliorée est le message d'erreur, qui est beaucoup plus explicite :
Use of DISTINCT is not allowed with the OVER clause
Une solution de contournement, consiste à combiner la fonction DENSE_RANK : qui permet de caclculer l
e rang de la ligne par valeur distincte, et la fonction MAX pour récupérer la valeur la plus élevée de ce rang.
/* Requête initialement utilisée*/
SELECT DISTINCT
[CalendarYear]
,[FrenchMonthName]
,COUNT(DISTINCT([WeekNumberOfYear]))
OVER (PARTITION BY
[CalendarYear]) AS
NombreSemaineParAnnee
FROM [dbo].[DimDate]
/* Solution de contournement basée sur le Dense_Rank et le Max*/
WITH cte AS
(
SELECT [CalendarYear]
,[FrenchMonthName]
,DENSE_RANK() OVER (PARTITION BY [CalendarYear] ORDER
BY [WeekNumberOfYear])
AS NombreSemaineParAnnee
FROM [dbo].[DimDate]
)
SELECT DISTINCT
[CalendarYear]
,[FrenchMonthName]
,MAX(NombreSemaineParAnnee)
OVER (PARTITION BY
[CalendarYear]) AS
NombreSemaineParAnnee
FROM cte
