✔ Werte in eine Gruppierung prüfen --> Anzahl

[HonniCilest]

Hallo,

Ich möchte die Anzahl von Identifiern zählen (gruppiert), die einen bestimmten Wert in der Gruppierung aufweisen oder eben nicht, nur diesen oder diesen neben anderen. Ich selber bin auf diese Anforderung in Postgres gestoßen, die Frage ist für mich aber eher allgemeiner Natur. Ich habe eine Lösung gefunden, welche funktioniert und möchte nur wissen, ob das auch irgednwie einfacher geht.

Angenommen ich habe eine Tabelle mit Personen und Fahrzeugen. Und ich möchte jetzt Auto genauer betrachten.

Da gibt es für mich folgende Fälle:
- Anzahl von Personen, die ein Auto oder ein Auto neben anderen Fahrzeugen besitzen
>> Das ist einfach

SELECT Count(Person)
FROM foobar
WHERE Fahrzeug = 'Auto'

- Anzahl von Personen, die außer einem Auto kein anderes Fahrzeug besitzen

- Anzahl von Personen, welche ein Auto besitzen und außerdem ein anderes Fahrzeug

- Anzahl von Personen, welche überhaupt kein Auto besitzen


Letzteren 3 habe ich damit gelöst, dass ich ein geschachteltes Statement hatte, das innere hat die Person gruppiert und die Liste aggregiert. Die äußere hat dann je nach Anforderung überprüft, ob der Wert nur Auto/Auto und Trennzeichen/kein Auto enthält.

Also z.B. für den Fall kein Auto

SELECT Count(Person)
FROM (SELECT Person, string_agg(Fahrzeug, ';') AS Fahrzeuge
FROM foobar
GROUP BY Person) AS List
WHERE NOT Fahrzeuge LIKE '%Auto%'

Ich würde gerne wissen ob es auch eine Möglichkeit gibt auf die Verschachtelung und vorallem auf die Agrregatsfunktion zu verzichten.

Danke!

Gruß
HC

 

[Yaslaw]

Es gibt viele Möglichkeiten.
Du solltest nicht Werte zu Strings zusammensetzen und diese dann auswerten. Da können keine Indexe angewendet werden

Hier einige Beispiel

- Anzahl von Personen, die außer einem Auto kein anderes Fahrzeug besitzen

select count(foo.PERSON)
from
 (
  select f1.PERSON
  from FOOBAR f1
  where f1.FAHRZEUG = 'Auto'
  group by f1.PERSON
  having count(f1.FAHRZEUG) = (select count(f1.FAHRZEUG) from FOOBAR f2 where f2.PERSON = f1.PERSON)
 ) foo

- Anzahl von Personen, welche ein Auto besitzen und außerdem ein anderes

select count(foo.PERSON)
from
 (
  select PERSON
  from FOOBAR
  where FAHRZEUG = 'Auto'
  group by PERSON
  having count(FAHRZEUG) > 1
 ) foo

- Anzahl von Personen, welche überhaupt kein Auto besitzen

select count(foo.PERSON)
from FOOBAR foo
 left join (
  select distinct PERSON
  from FOOBAR
  where FAHRZEUG = 'Auto'
 ) m
 on foo.PERSON = m.PERSON
where m.PERSON is null

-- oder

select count(PERSON)
from FOOBAR
where PERSON not in (
 select distinct PERSON
 from FOOBAR
 where FAHRZEUG = 'Auto'
)

 

[HonniCilest]

Du hast mich bei deinen Beispielen etwas falsch verstanden, vielleicht war ich mit meinem Anschauungsbild auch etwas unglücklich. Eine Person kann kein weiteres Auto haben in meinem Fall.

Es geht also darum ob
- Person nur den Fahrzeugtyp Auto hat (kein Fahrrad, kein Wohnmobil etc.)
- Person ein Auto hat und z.B. auch ein Fahrrad
- Alle Fahrzeuge einer Person nichts Autos sind


Und diese Personen, bei denen das zutrifft dann entsprechend zählen

 

[Yaslaw]

Du hast genügend Beispiele um dir da etwas zusammenzuschustern

 

[Biber3]

Moin HonniCilest,

Ich finde ja Beispiele mit reichlich Interpretationsspielraum auch immer wieder unterhaltsam.
War bestimmt total motivierend für Yaslaw.

Für folgenden unterstellten Tabelleninhalt;

PERSON; FAHRZEUG
"Hans";"Auto"
"Bärbel";"Wohnmobil"
"Klaus";"Fahrrad, Moped, Wohnmobil"
"Gunnar";"Auto, Bus"
"Oliver";"Auto, Moped"
"Anne";"Deoroller"

würde folgendes Statement ..

 select 'Summen' as "MfGBiber"
 , Sum(case when Fahrzeug = 'Auto' and length(Fahrzeug)< 6                then 1 else 0 end) as "AutoUndNixAnderes"
 , Sum(case when length(fahrzeug) = length(replace(fahrzeug, 'Auto', '')) then 1 else 0 end) as "nixAutoAberAnderes"
 , Sum(case when Fahrzeug like '%Auto%' and length(fahrzeug)> 5           then 1 else 0 end) as "AutoUndAnderesMehr"
from foobar

... diese Summen bringen
MfGBiber;AutoUndNixAnderes;nixAutoAberAnderes;AutoUndAnderesMehr
Summen ;1 ;3 ;2

Personen, die in Tabelle foobar gelandet sind, obwohl sie gar kein Fahrzeug haben, die werden nicht berücksichtigt.
Musst du wissen, ob es so etwas geben kann/darf.


Schönes Wochenende
Biber

 

[HonniCilest]

Hallo,

wie erwähnt hatte ich eine funktionierende Lösung. Auch wenn diese im sinne einer Indexierung eventuell nicht optimal ist, so war der Aufruf für mich doch eine einmalige Auswertungsgeschichte und die Frage hier nur rein Interesse halber. Bisher sehe ich keinen Ansatz, welcher 'übersichtlicher' zu sein scheint, als meine Lösung.

Um weiterhin Unklarheiten zu bereinigen, die Beispieltabelle würde in meinem Sinn so aussehen:

1. PERSON; FAHRZEUG
2. "Hans";"Auto"
3. "Hans";"Wohnmobil"
4. "Hans";"Fahrrad"
5. "Gunnar";"Auto"
6. "Oliver";"Moped"
7. "Anne";"Deoroller"

Anzahl der Besitzer mit Auto: 2
Nur auto: 1
Auto und andere: 1
Kein auto: 2

In meinem realen Beispiel wären es Kunden ID's und digitale Bereiche die besucht wurden. Auswertung für einen speziellen Bereich

Bei erneutem Besuchen des Bereiches wird der Eintrag ggf. Geupdated jedoch nicht erneut ergänzt. Kunden ohne einen besuchten Bereich gibt es nicht.

Danke dennoch für alle Antworten.


Gruß
HC

 

[Biber3]

Na ja, honniCilest,

dennoch muss jede x-beliebige Lösung performanter sein als die Variante über string_Agg(), siehe auch Yaslaws Hinweis.

Und bei deinem Tabellenaufbau brauchst du auch kein langsames " ...LIKE '%Auto%'.. , sondern kannst mit "..='Auto' auch einen evtl. vorhandenen Index nutzen.

Eine "SUM(CASE WHEN"-Variante ginge so:

Select sum(allfzg) as FzgGesamt
       , sum(case when auto#> 0 and nichtauto#=0 then 1 else 0 end) as NurAuto
       , sum(case when auto#= 0 and nichtauto#>0 then 1 else 0 end) as NichtAuto
       , sum(case when auto#> 0 and nichtauto#>0 then 1 else 0 end) as AutoUndMehr
  From (   
        Select Person,
             count(fahrzeug) as allfzg
            , sum(case when fahrzeug='Auto' then 1 else 0 end) as auto#
            , sum(case when fahrzeug<>'Auto' then 1 else 0 end) as nichtauto#         
             from foobar
        group by person
;

Mag nicht leserlicher sein, aber ich würde den Teufel tun und die Datenbank mit 3 unterschiedlichen Abfragen nacheinander quälen.

Ich würde immer alle Ergebnisse mit einer Query holen.
Auch, weil ich sonst 3 wahre Ergebnisse bekomme, aber zu drei unterschiedlichen Zeitpunkten.
Da kann sich schon die Tabelle geändert haben und die Summe der Teilergebnisse wird ungleich dem Gesamtergebnis.

Grüße
Biber

 

[HonniCilest]

Das letzte Beispiel habe ich nun verstanden und lässt sich sicherlich prima auf den eigentlichen Fall adaptieren. Ich kannte Case bisher nicht bei Datenbanken, ich habe etwas gelernt. Ich danke dir.