Count distinct-Werte mit OVER(PARTITION BY id)

Ist es möglich, count distinct-Werte in Verbindung mit window-Funktionen, wie OVER(PARTITION BY id)? Derzeit meine Abfrage lautet wie folgt:

SELECT congestion.date, congestion.week_nb, congestion.id_congestion,
   congestion.id_element,
ROW_NUMBER() OVER(
    PARTITION BY congestion.id_element
    ORDER BY congestion.date),
COUNT(DISTINCT congestion.week_nb) OVER(
    PARTITION BY 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

Jedoch, wenn ich versuche, um die Abfrage auszuführen, bekomme ich folgenden Fehler:

"COUNT(DISTINCT": "DISTINCT is not implemented for window functions"

InformationsquelleAutor user007 | 2014-02-12

  1. 2

    Nicht, wie die Fehlermeldung besagt, DISTINCT ist nicht implementiert, die mit windows-Funktionen. Aplying info aus dieser link in Ihrem Fall könnten Sie so etwas wie:

    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

    Je nach situation könnten Sie auch mit einer Unterabfrage direkt in 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

    InformationsquelleAutor Simo Kivistö

  2. 0

    Ich finde, dass der einfachste Weg ist die Verwendung einer Unterabfrage/CTE und konditionellen:

    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

    InformationsquelleAutor Gordon Linoff

Schreibe einen Kommentar