Re: COUNT

[Martin Ramsch <m.ramsch_(at)_computer.org>]

Frank Oellien schrieb am Samstag, den  9. Oktober 1999:
> I habe folgendes Problem. Ich habe eine Tabelle, die folgendermassen
> aufgebaut ist:
> 
> NSCNr | CellID | Konz.
>  123  |   1    |  2
>  123  |   2    |  2
>  123  |   3    |  2
>  123  |   1    |  2.5
>  123  |   2    |  2.5
>  124  |   1    |  2
>  124  |   3    |  3
>  125  |   1    |  1.6
>  125  |   2    |  1.5
>  126  |   2    |  1.5
>  126  |   4    |  1.5
> 
> Als Ergebnis moechte ich gerne die Anzahl aller verschiedenen (!)
> NSCNr fuer eine CellID haben:
> 
> CellID | count(NSCNr)
>   1    |     3
>   2    |     3
>   3    |     2
>   4    |     1
> 
> Der Befehl:
> select CellID, count(NSCNr) from tgi group by CellID;
> fuehrt allerdings nur zum Zaehlen aller NSCNr (was ja auch korrekt
> ist aber nicht meinem Wunsch entspricht):
> 
> CellID | count(NSCNr)
>   1    |     4
>   2    |     4
>   3    |     2
>   4    |     1
> 
> Wie muss die Anfrage richtig lauten?

Dafür gäbe es in SQL die COUNT-Variante 'COUNT(DISTINCT ...)', mit der
Deine Abfrage so aussehen würde:
  SELECT CellID, COUNT(DISTINCT NSCNr) FROM tgi GROUP BY CellID;

Leider unterstützt MySQL dieses COUNT(DISTINCT ...) aber erst ab
Version 3.23.x!


Falls Du eine ältere MySQL-Version benutzt, kommt man nur auf Umwegen
an das gewünschte Ergebnis:

- Selber auf der Anwendungsseite zählen:
    SELECT DISTINCT NSCNr, CellID FROM tgi ORDER by CellID;
  Und dann für jede CellID die Anzahl der ausgegebenen Zeilen
  mitzählen.

- Mit Hilfe einer temporären Hilfstabelle:
    CREATE TABLE tmp ( NSCNr INT, CellID INT );
    INSERT INTO tmp SELECT DISTINCT NSCNr, CellID FROM tgi;
    SELECT CellID, COUNT(NSCNr) FROM tmp GROUP BY CellID;
    DROP TABLE tmp;
  Der entscheidende Punkt ist wieder das SELECT DISTINCT, welches
  sicherstellt, daß keine Duplikate in der temporären Tabelle landen.

  Nachteil ist hier vorallem der zusätzliche Platzbedarf für die
  temporäre Tabelle.  Falls diese Abfrage mehrfach gleichzeitig laufen
  kann, muß man zudem darauf achten, daß es keine konkurrierenden
  Zugriffen auf die temporäre Tabelle geben kann - entweder durch
  Locking oder indem die temporäre Tabelle jedesmal einen anderen,
  eindeutigen Namen bekommt.  Letzteres geschieht ab MySQL V3.23.x
  automatisch, wenn man CREATE TEMPORARY TABLE nutzt.

- Mit Hilfe eines Self-Joins:
    SELECT t1.CellID
        ,  COUNT(t1.NSCNr)-COUNT(t2.NSCNr) AS cd
    FROM  tgi AS t1 LEFT JOIN
          tgi AS t2 ON t1.NSCNr=t2.NSCNr
                   AND t1.CellID=t2.CellID
                   AND t1.Konz<t2.Konz
    GROUP BY CellID;

  Die Ausgangsidee dabei ist, daß man zuviel gezählte Zeilen wieder
  abziehen will.  Welche sind das aber?  Das sind alle die Zeilen,
  wo es noch anderen Datensätze mit der gleichen (CellID, NSCNr)-
  Kombination gibt!

  Das könnte man mit einem LEFT JOIN so testen:
     
    FROM  tgi AS t1 LEFT JOIN
          tgi AS t2 ON t1.NSCNr=t2.NSCNr   /* gleiche NSCNr  */
                   AND t1.CellID=t2.CellID /* gleiche CellID */
                   AND t1.Konz!=t2.Konz    /* aber verschiedene Zeilen */

  Wenn die Tabelle einen Primärschlüssel besitzt, dann sollte man
  statt des Konz-Vergleichs die Primärschlüssel auf Verschiedenheit
  testen - Ziel ist halt sicherzustellen, daß zwei verschiedene Zeilen
  verbunden werden!

  Statt mit '!=' zu vergleiche, ist es aber geschickter, '<' zu
  verwenden.  Denn so gibt es pro (CellID, NSCNr)-Gruppe genau eine
  Ergebniszeile mit NULL-Werten auf der t2-Seite: nämlich für den
  t1-Datensatz mit der größten ID (hier: größtes Konz).

  Jetzt braucht man also nur noch die NULL-Werte in einer Spalte der
  t2-Seite zu zählen und hat die gewünschte Anzahl verschiedener
  Werte.
  Diese Zählen der Nullwerte ginge z.B. mit
     SUM( IF( ISNULL(t2.NSCNr), 1, 0 )

  Hübscher (und vermutlich schneller) ist aber, die Anzahl der
  NULL-Werte als Differenz der Gesamtanzahl=COUNT(t1.NSCNr) mit der
  Zahl der Nicht-NULL-Werte=COUNT(t2.NSCNr) zu berechnen.

Hmm, eben - kurz vorm abschicken der Mail - fiel mir noch eine
Verbesserung ein:

  Statt die NULL-Werte irgendwie kompliziert zu zählen, kann man per
  WHERE-Klausel die Ergebniszeilen mit rechtem NULL-Teil herausfiltern 
  und braucht dann nur noch ein einfaches COUNT im linken t1-Teil.

  Das hier ist also meine LEFT JOIN-Lösung:

  SELECT t1.CellID,  count(t1.NSCNr) AS NSCNr
  FROM   tgi AS t1 LEFT JOIN
         tgi AS t2 ON t1.NSCNr=t2.NSCNr
                  AND t1.CellID=t2.CellID
                  AND t1.id<t2.id
  WHERE  t2.NSCNr IS NULL
  GROUP BY CellID;

Dabei muß id eine Spalte mit Unique-Index sein oder gleich der
Primärschlüssel.
(Sonst könnte es beim '<'-Vergleich mehrere größte Elemente geben
 und es würde zu viel gezählt.)

Ciao,
  Martin
-- 
Martin Ramsch <m.ramsch_(at)_computer.org> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=5244 5EF3 B0B1 3826  E4EC 8058 7B31 3AD7

---
*** Abmelden von dieser Mailingliste funktioniert per E-Mail
*** an mysql-de-request_(at)_lists.4t2.com mit Betreff/Subject: unsubscribe