Compter les valeurs distinctes avec OVER (PARTITION par id)

Non, comme les états message d'erreur, DISTINCT n'est pas implémenté avec les fonctions windows. Complying info from ce lien dans votre cas vous pouvez utiliser quelque chose comme:

WITH uniques AS (
 SELECT congestion.id_element, COUNT(DISTINCT congestion.week_nb) AS unique_references
 FROM congestion
WHERE congestion.date >= '2014.01.01'
AND congestion.date <= '2014.12.31'
 GROUP BY congestion.id_element
)

SELECT congestion.date, congestion.week_nb, congestion.id_congestion,
   congestion.id_element,
ROW_NUMBER() OVER(
    PARTITION BY congestion.id_element
    ORDER BY congestion.date),
uniques.unique_references AS week_count
FROM congestion
JOIN uniques USING (id_element)
WHERE congestion.date >= '2014.01.01'
AND congestion.date <= '2014.12.31'
ORDER BY id_element, date

en fonction de la situation, vous pouvez aussi mettre un subquery directement dans SELECT-liste:

SELECT congestion.date, congestion.week_nb, congestion.id_congestion,
   congestion.id_element,
ROW_NUMBER() OVER(
    PARTITION BY congestion.id_element
    ORDER BY congestion.date),
(SELECT COUNT(DISTINCT dist_con.week_nb)
    FROM congestion AS dist_con
    WHERE dist_con.date >= '2014.01.01'
    AND dist_con.date <= '2014.12.31'
    AND dist_con.id_element = congestion.id_element) AS week_count
FROM congestion
WHERE congestion.date >= '2014.01.01'
AND congestion.date <= '2014.12.31'
ORDER BY id_element, date

je trouve que la façon la plus simple est d'utiliser un sous-jeu/CTE et l'agrégation conditionnelle:

SELECT c.date, c.week_nb, c.id_congestion, c.id_element,
       ROW_NUMBER() OVER (PARTITION BY c.id_element ORDER BY c.date),
       (CASE WHEN seqnum = 1 THEN 1 ELSE 0 END) as week_count
FROM (SELECT c.*,
             ROW_NUMBER() OVER (PARTITION BY c.congestion.id_element, c.week_nb
                                ORDER BY c.date) as seqnum
      FROM congestion c
     ) c
WHERE c.date >= '2014.01.01' AND c.date <= '2014.12.31'
ORDER BY id_element, date