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