✔ [PostgreSQL] MAX Werte bekommen / Häufigste Werte einer Gruppe ermitteln

Tommy57 · 23. März 2012

Hallo,

ich habe eine Tabelle, die verschiedene Autos beinhalten. Diese sind nach Typ, Baujahr und Farbe gelistet. Ich möchte nun eine Abfrage machen, die mir zu jedem Typ die beliebteste Farbe und das beliebteste Baujahr auswirft.

Die erste Abfrage sieht wie folgt aus:

Code:

SELECT
    type,
    year,
    color
    COUNT(type) AS volume
FROM
    sold_cars
GROUP BY    
    type,
    year,
    color

Ergebnismenge könnte so aussehen:

Code:

xl, 1999, 'b', 15
xl, 1998, 'b', 13
xl, 1999, 'r', 12
ec, 2002, 'g', 14
ec, 2003, 'g', 13

Was ich haben muss ist:

Code:

xl, 1999, 'b'
ec, 2002, 'g'

Mit dieser Abfrage kriege ich alle Ergebnisse. Nun möchte ich gerne immer nur von jedem Typ die Zeile haben, die den höchsten volume hat. Diese Abfrage soll nämlich später als JOIN an ein anderes Statement angehangen werden. Nur leider bekomme ich immer alle Zeilen und nicht nur die erste zu jedem type.
Hat da jemand eine Idee?

Gruß, Tommy

Thomas Darimont · 23. März 2012

Hallo,

ich habe die Fragestellung mal etwas "umfangreicher" mit analytischem SQL mit Oracle gelöst.

Unser Testszenario:
Mit OpenOffice Calc (oder Excel) Zufällige Testdaten in folgenden Bereichen erzeugen:
Marken: BMW Audi Mercedes Ford VW
Farben: schwarz rot gelb grün
Baujahr: Zahlen im Bereich 2005-2012

SQL:

create table sold_cars(
	type varchar(32),
	year number(4),
	color varchar(32)
);

insert into sold_cars values('Ford',2005,'schwarz');
insert into sold_cars values('VW',2008,'schwarz');
insert into sold_cars values('Audi',2007,'gelb');
insert into sold_cars values('Ford',2006,'rot');
insert into sold_cars values('Mercedes',2005,'grün');
insert into sold_cars values('BMW ',2007,'rot');
insert into sold_cars values('Audi',2007,'gelb');
insert into sold_cars values('VW',2010,'gelb');
insert into sold_cars values('Mercedes',2005,'grün');
insert into sold_cars values('VW',2012,'schwarz');
insert into sold_cars values('Audi',2005,'rot');
insert into sold_cars values('Audi',2006,'grün');
insert into sold_cars values('Mercedes',2006,'rot');
insert into sold_cars values('BMW ',2005,'rot');
insert into sold_cars values('Mercedes',2012,'rot');
insert into sold_cars values('VW',2008,'gelb');
insert into sold_cars values('BMW ',2012,'rot');
insert into sold_cars values('Mercedes',2010,'grün');
insert into sold_cars values('BMW ',2011,'grün');
insert into sold_cars values('VW',2012,'rot');
insert into sold_cars values('Ford',2008,'grün');
insert into sold_cars values('Mercedes',2007,'gelb');
insert into sold_cars values('Ford',2008,'schwarz');
insert into sold_cars values('BMW ',2005,'gelb');
insert into sold_cars values('BMW ',2010,'rot');
insert into sold_cars values('BMW ',2005,'grün');
insert into sold_cars values('Audi',2011,'grün');
insert into sold_cars values('BMW ',2010,'gelb');
insert into sold_cars values('Ford',2008,'schwarz');
insert into sold_cars values('BMW ',2008,'schwarz');
insert into sold_cars values('Audi',2005,'schwarz');
insert into sold_cars values('Ford',2008,'gelb');
insert into sold_cars values('BMW ',2011,'gelb');
insert into sold_cars values('Mercedes',2009,'rot');
insert into sold_cars values('Audi',2012,'grün');
insert into sold_cars values('Audi',2005,'schwarz');
insert into sold_cars values('Audi',2012,'gelb');
insert into sold_cars values('Mercedes',2008,'grün');
insert into sold_cars values('Mercedes',2012,'rot');
insert into sold_cars values('Audi',2006,'gelb');
insert into sold_cars values('BMW ',2010,'schwarz');
insert into sold_cars values('BMW ',2012,'rot');
insert into sold_cars values('Mercedes',2006,'rot');
insert into sold_cars values('Mercedes',2010,'gelb');
insert into sold_cars values('VW',2011,'schwarz');
insert into sold_cars values('Ford',2005,'schwarz');
insert into sold_cars values('Ford',2009,'schwarz');
insert into sold_cars values('BMW ',2007,'gelb');
insert into sold_cars values('Audi',2006,'grün');
insert into sold_cars values('BMW ',2009,'grün');
insert into sold_cars values('BMW ',2012,'rot');
insert into sold_cars values('Mercedes',2010,'rot');
insert into sold_cars values('BMW ',2011,'schwarz');
insert into sold_cars values('BMW ',2009,'gelb');
insert into sold_cars values('BMW ',2007,'rot');
insert into sold_cars values('Mercedes',2007,'gelb');

Abfrage:

SQL:

WITH 
  color_histogram AS
  ( 
    SELECT type, color, COUNT(*) COUNT FROM sold_cars GROUP BY type, color
  )
  , year_histogram AS
  ( 
    SELECT type, YEAR, COUNT(*) COUNT FROM sold_cars GROUP BY type, YEAR
  )
  , top_colors AS
  (
    SELECT type, MAX(color) top_color
    FROM
       (
        SELECT 
          ch.type, ch.color, dense_rank() over (partition BY ch.type order by ch.count DESC) rnk
        FROM 
          color_histogram ch
        )
    WHERE rnk = 1 -- Häufigste Farbe hat Rang 1
    GROUP BY type
  )
  , top_years AS
  (
    SELECT type, MAX(year) top_year
    FROM
    (
      SELECT 
        yh.type, yh.year, dense_rank() over (partition BY yh.type order by yh.count DESC) rnk
      FROM 
        year_histogram yh
    )
    WHERE rnk = 1 -- Häufigstes Jahr hat Rang 1
    GROUP BY type
  )
SELECT * FROM top_years JOIN top_colors USING (type)

Ergebnis:

Code:

TYPE                             TOP_YEAR TOP_COLOR                      
-------------------------------- -------- --------------------------------
Mercedes                             2010 rot                              
VW                                   2012 schwarz                          
Audi                                 2006 grün                             
Ford                                 2008 schwarz                          
BMW                                  2012 rot

Gruß Tom

Tommy57 · 23. März 2012

Woow, danke. Das sieht echt kompliziert aus. Ich probier das gleich mal aus

Thomas Darimont · 23. März 2012

Hallo,

wie oben beschrieben ist das Beispiel für Oracle... solltest du die Abfrage für MySQL brauchen, so müsste man die etwas umschreiben, da MySQL derzeit noch keine "WITH" CTE (Common Table Expressions) oder Subquery Factorings kann... http://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql
und leider auch noch kein keine rank (...) over (...) Analytic Function...

Gruß Tom

Tommy57 · 23. März 2012

Hi Tom,

danke für das Ergebnis, aber ich kann tatsächlich nichts damit anfangen. Dein Ansatz ist 100% das, was ich brauche, nur kann ich das nicht in das Postgres portieren.

Ich dachte ursprünglich an was wie:

JOIN (... ORDER BY vol DESC LIMIT 1)

nur bekomme ich dann leider nur exakt für einen Typen ein Ergebnis und nicht für jeden Typen jeweils ein Ergebnis.

Geht das nicht irgendwie einfacher?

Gruß, Tommy

Tommy57 · 23. März 2012

So, ich habe jetzt einen Lösungsansatz, der extrems merkwürdig ist, aber funktioniert:

Code:

SELECT
    x1.type,
    x1.vol,
    MAX(x2.year) AS bestyear,
    x2.color
FROM
(SELECT
    x0.type,
    MAX(x0.volume) AS vol
FROM
(SELECT
    type,
    year,
    color
    COUNT(type) AS volume
FROM
    sold_cars
GROUP BY    
    type,
    year,
    color) x0
GROUP BY
   x0.type) x1
INNER JOIN
(SELECT
    type,
    year,
    color
    COUNT(type) AS volume
FROM
    sold_cars
GROUP BY    
    type,
    year,
    color) x2
ON
    x2.type = x1.type
AND
    x2.volume = x1.vol
GROUP BY
    x1.type,
    x1.vol,
    x2.color

Um das Ganze kurz zu erklären:
Ich zähle alle Typen mit Jahrgang und Farbe und gebe nur die höchste Anzahl je Typ weiter.

Code:

SELECT
    x0.type,
    MAX(x0.volume) AS vol

Hierbei verliere ich jedoch Jahr und Farbe, was ich im nächsten Durchgang per Join wieder anhänge, da ich ja die Anzahl kenne.

Code:

(SELECT
    type,
    year,
    color
    COUNT(type) AS volume
FROM
    sold_cars
GROUP BY    
    type,
    year,
    color) x2
ON
    x2.type = x1.type
AND
    x2.volume = x1.vol

Um doppelte Typen zu vermeiden, mache ich noch ein MAX aufs Jahr.

Thomas Darimont · 23. März 2012

Hallo,

...
danke für das Ergebnis, aber ich kann tatsächlich nichts damit anfangen. Dein Ansatz ist 100% das, was ich brauche, nur kann ich das nicht in das Postgres portieren.

..hmmm schade. Wo liegt das Problem bei der Portierung? Postgresql unterstützt neben Common Table Expressions (CTE) -> WITH (http://www.postgresql.org/docs/8.4/static/queries-with.html) auch Windowing Functions ... rank() over (partition ...)
http://www.postgresql.org/docs/9.1/static/tutorial-window.html

Gruß Tom

Tommy57 · 23. März 2012

Hallo,

das Problem liegt darin, dass mir dein Statement zu hoch ist und ich beim abtippen hier und da Fehlermeldungen kriege und dann dementsprechend nicht weiter weiß.
Aber meine Variante tut es auch. Vllt nicht sehr elegant, aber ich bekomme genau das Ergebnis, dass ich brauche.
Danke auf jeden Fall für deine Hilfe.

Gruß, Tommy

Tommy57 · 23. März 2012

Hey Tom,

du hast vollkommen recht. Mit deiner Variante geht das schnell und einfach:

rank() OVER (PARTITION BY type ORDER BY volume DESC) AS rank

und beim Join:

ON
x1.type = x0.type
AND
x1.rank = 1

1.000 Dank. Genau das habe ich gesucht.

Gruß, Tommy

✔ [PostgreSQL] MAX Werte bekommen / Häufigste Werte einer Gruppe ermitteln

Tommy57

Erfahrenes Mitglied

Thomas Darimont

Erfahrenes Mitglied

Tommy57

Erfahrenes Mitglied

Thomas Darimont

Erfahrenes Mitglied

Tommy57

Erfahrenes Mitglied

Tommy57

Erfahrenes Mitglied

Thomas Darimont

Erfahrenes Mitglied

Tommy57

Erfahrenes Mitglied

Tommy57

Erfahrenes Mitglied

Neue Beiträge