[SQL] : Limitation comptage distinct sur une fenêtre de données

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 le 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

Source : Connect