Group by substr(FieldXY, 1)

Computer: Software & Programmierung SQL & Datenbank - Programmierung
#1

Liebe SQL-Profis

Ich möchte eine Tabelle gruppiert nach dem ersten Zeichen eines Feldes der Tabelle selektieren; in etwas so:

SELECT Field1 FROM TABLE1 GROUP BY SUBSTR(Field1, 1)
bzw. SELECT Field1 FROM TABLE1 GROUP BY LEFT(Field1`, 1)

Geht das überhaupt? Und wenn ja, wie wäre die korrekte Syntax?

Das Resultat würde dann etwa so aussehen:

0
1
2
4
8
a
c
f
i
j

u.s.w.

Auch der ganze Wert des Field1 aus dem ersten Gruppentreffer wäre ganz angenehm, den Substring könnt ich noch im Nachhinein vornehmen …

Im Moment löse ich das ganze so, dass ich eine Schleife von 0 bis Z ziehe und jeweils ein SELECT LIKE auf das Zeichen der Schleife absetze. Dummerweise muss ich dazu aber 35x einen SELECT ausführen, was ich eben optimieren möchte …

Implementiert würde das ganze in MySQL aus PHP heraus.

Danke für eure Tipps.

Gruss
Patrick

#2

hi Patrick

offenbar verwechselst du da was. dein beispiel lässt nämlich darauf schließen, dass du in wirklichkeit nicht gruppieren sondern einfach nur alle anfangsbuchstaben haben willst, die in der db vorkommen. das geht dann eher mit einem

select distinct substr(feldxy,1,1) from tabelle ;

gruppierungen haben nur in kombination mit einer gruppenfunktion einen sinn. also wenn du z.b. wissen willst, wie häufig deine anfangsbuchstaben vorkommen, kannst du sowas machen:

select substr(feldxy,1,1), count(\*) from tabelle group by substr(feldxy,1,1);

was definitiv nicht geht ist sowas:

select feldxy, count(\*) from tabelle group by substr(feldxy,1,1);
oder
select substr(feldxy,1,1), count(\*) from tabelle;

gruppenfunktionen wie count oder sum müssen immer entweder alleine in der select-klausel stehen, oder gemeinsam mit einem anderen ausdruck, der dann aber auch in der group-by-klausel stehen muss.

lg
erwin

#3

Im wesentlichen hat mein Vorschreiber ja schon alles gesagt, nur zwei Anmerkungen:

  • distinct ist nichts anderes als eine Kurzschreibe für Group By in bestimmten Fällen (z.B. ohne Aggregatesfunktion)

  • Man kann sehr wohl Aggregatsfunktionen und nicht aggregierte Werte gemeinsam selektieren. Das nennt sich dann analytische Funktion.

Beispiel (funktioniert bei Oracle ab 8x, ist aber meines Wissens SQL-Standard, sollte also auch bei DB2 und SQL-Server funktionieren):

select 
 count(\*) over (partition by spalte1),
 t.\*
from my\_table t
#4
  • distinct ist nichts anderes als eine Kurzschreibe für Group
    By in bestimmten Fällen (z.B. ohne Aggregatesfunktion)

NEIN! die beiden haben absolut nix miteinander zu tun. es ist nur so, dass man mit gruppierung in manchen fällen das selbe ergebnis bekommt. so wie man sehr viele abfragen auf mindestens 3 verschiedene arten formulieren kann.

ein distinct filtert einfach nur doppelte werte weg. es muss zwar sowohl beim distinct als auch beim group-by intern sortiert werden, trotzdem ist das distinct etwas schneller, da weniger logik erforderlich ist.

  • Man kann sehr wohl Aggregatsfunktionen und nicht aggregierte
    Werte gemeinsam selektieren. Das nennt sich dann analytische
    Funktion.

korrekt. ich habe aber absichtlich diese analytischen funktionen nicht berücksichtigt, da sie in vielen „kleinen“ datenbanken nicht immer richtig funktionieren. mit dem uralten sql92-standard kommt man meistens auch aus - und der funktioniert normalerweise überall.

lg
erwin

#5
  • distinct ist nichts anderes als eine Kurzschreibe für Group
    By in bestimmten Fällen (z.B. ohne Aggregatesfunktion)

—> Wie kommst du zu dieser Aussage ? Das hat absolut nicht miteinander zu tun.

#6

die alternative Schreibweise eines Distincts als Group by liefert exakt das gleiche und zwar per definition. Wenn die beiden Statements auf unterschiedliche Weise ausgeführt werden ist das ein Bug, oder zumindest ein Mangel des Datenbanksystems.

Oracle produziert in allen von mir getestetn Fällen den exakt gleichen Ausführungsplan.

Und wenn die Ausführungsweise und das Ergebnis gleich sind, wo soll da bitte der Unterschied sein?

group by a, b, c

heisst nichts anderes als ‚liefer mir für jede auftretende Werte-Kombination der Spalten a, b, c genau einen Datensatz‘
und genau das bedeutet auch

select distinct a, b, c

#7

die alternative Schreibweise eines Distincts als Group by
liefert exakt das gleiche und zwar per definition. Wenn die
beiden Statements auf unterschiedliche Weise ausgeführt werden
ist das ein Bug, oder zumindest ein Mangel des
Datenbanksystems.

Blöd. Du hast nbatürlich recht. Habe wohl nix überlegt, als ich meine obenstehende Antwort gepostet habe :smile:)

Die Execution Plans für beide Varianten sind dieselben

#8

Hallo Erwin

Hurra, es funktioniert!

Allerbesten Dank! Aus der Lösung habe ich nun noch eine Zusatzfrage: Was ist generell der schnellere Weg?

SELECT anyField FROM anyTable GROUP BY anyField WHERE wasWeissIch
oder
SELECT DISTINCT anyField FROM anyTable WHERE wasWeissIch

Bisher habe ich immer GROUP BY verwendet, denn ich kannte das Feature DISTINCT nicht. Aber vielleicht wäre DISTINCT bei Abfragen ohne Aggregate jeweils sinnvoller als GROUP BY?

Gruss
Patrick

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

#9

SELECT anyField FROM anyTable GROUP BY anyField WHERE
wasWeissIch
oder
SELECT DISTINCT anyField FROM anyTable WHERE wasWeissIch

  1. Die Where Klausel muss vor der Group By Klausel kommen.

  2. In meiner Erfahrung ist beides gleich schnell, was ja auch sinn macht da es das gleiche tut, das könnte aber von dem verwendeten Datenbanksystem abhängen.

  3. Wenn es einen Unterschied gibt, ist eine generelle Aussage zu solchen Performancefragen fast nie möglich, da zu viele Parameter eine Rolle spielen. Daher: Einfach mal ausprobieren. Wichtig dabei: DB-Systeme cachen Daten, d.h. wenn du das ganze N x ausführst, wird der erste Versuch vermutlich etwas länger dauern und kann dir deine Statistik durcheinander werfen

#10

Hallo Jens

Where for Group By: sorry, mein (unbewusster) Schreibfehler

Danke für die Antworten.
Gruss
Patrick

SELECT anyField FROM anyTable GROUP BY anyField WHERE
wasWeissIch
oder
SELECT DISTINCT anyField FROM anyTable WHERE wasWeissIch

#11

Distinct: Unerwartetes Resultat
Hallo Jens, Hallo Erwin

Ich setzte nun den Befehl so ein:

SELECT DISTINCT SUBSTR(feld, 1, 1) FROM tabelle

Wenn ich jedoch einen Wert mit Leerzeichen-beginnend im Feld ‚xy‘ habe wird dieser mit distinct als Nullstring zurückgegeben.
Auch wenn ich statt SUBSTR die LEFT(feld, 1)-Funktion einsetze erhalte ich denselben Effekt.

Beispiel
********
Folgende Werte sind in der Tabelle
„Hans“
„Erich“
" in Huhn"

mit Distinct erhalte ich folgende Resultate
„H“
„E“
„“ -> nichts statt leerzeichen

Ist das normal? Wenn ich allerdings ohne Distinct mit Substr arbeite erhalte ich korrekte Resultate
„H“
„E“
" " -> korrekt, Leerzeichen

Habt ihr da noch einen Tipp?

Danke und viele Grüsse
Patrick

SELECT DISTINCT anyField FROM anyTable WHERE wasWeissIch

#12

Habt ihr da noch einen Tipp?

Supportanfrage aufmache!? Wenn es sich wirklich so verhält wie beschrieben, ist das ein Bug. Bevor ich mich bei Oracle melde würde ich aber ein kleines vollständiges Beispiel in SQL-plus bauen, danach fragen die im Zweifelsfall eh. … Ach so, du kannst auch mal in Metalink schauen, mit ein bisschen Glück findet sich da schon ein Bug-Report

#13

Hallo Jens

Danke für die Antwort: ich hab’s ja mit mySQL erzeugt; werde da mal die Bug-Listen durchgehen. Wollte nur sichergehen, dass ich nicht nach einem Bug fahnde der möglicherweise ein Feature darstellt dessen Sinn ich nicht sähe oder so …

Gruss
Patrick

Supportanfrage aufmache!? Wenn es sich wirklich so verhält wie
beschrieben, ist das ein Bug. Bevor ich mich bei Oracle melde
würde ich aber ein kleines vollständiges Beispiel in SQL-plus
bauen, danach fragen die im Zweifelsfall eh. … Ach so, du
kannst auch mal in Metalink schauen, mit ein bisschen Glück
findet sich da schon ein Bug-Report