SQL Komplexe Abfrage für Summe & spezielle Ausgabe

[asseroin]

Hallo Leute,


leider bin ich kein SQL Guru , bräuchte dennoch eine speziellere Anfrage um ein gewisses Problem zu lösen. Hoffentlich kann mir jemand behilflich sein.

Im Moment habe ich unter anderem eine Tabelle "cibr" mit folgenden Columns:

Create TABLE cibr
(
hitscore real,
sourcefile caracter varying(50) NOT NULL,
klassensource integer,
targetfile caracter varying(50) NOT NULL,
klassentarget integer,
format caracter varying(4) NOT NULL,
kompression integer NOT NULL,
CONSTAINT cbir_pkey PRIMATY KEY (sourcefile, targetfile, format, kopression)
)

da sieht eine Line z.b. so aus

85.6803207	file1.jpg	1	file2.jpg	1	jpg	60

Nun folgendes Problem:

Nun benötige ich eine Anfrage, bei der ich als Ausgabe format, kompression, avg_matching bekomme (wobei format einfach weggelassen werden kann).

Wobei avg_matching wie folgt definiert werden kann:

* für jedes sourcefile suche ich die Top 10 hitscores wobei : sourcefile != targetfile, klassensource == klassentarget
* Dann brauche ich den Mittelwert der Top 10 hitscores

Somit hätte ich dann für ein sourcefile das avg_matching der Top 10 matches
da stell ich mir z.b. sowas vor:

avg_matching sourcefile format kompression
66.5 file1.jpg jpg 60

nun brauche ich das für alle sourcefiles damit ich am Ende folgendes habe:

avg_matching format kompression
45.4 jpg 60

mein erfolgloser Versuch war folgender:

select a.format, a.kompression, avg(a.erkennungsrate) from 
	(
		select sourcefile,format, kompression,
count(*)*10 as erkennungsrate from v_cbir where rownum <=10 and klassesource = klassetarget
group by sourcefile, format, kompression
	) a group by format, kompression

Ausgabe ist folgende:

"format";"kompression";"avg"
"jxr";60;20.0649350649350649
"jp2";60;19.8061389337641357
"jpg";60;19.4830371567043619
"jxr";20;20.3114754098360656
"jxr";40;20.6446280991735537
"jp2";80;19.8861788617886179
"jpg";80;19.0865384615384615
"jpg";40;20.4383116883116883
"jpg";20;21.0576923076923077
"jxr";80;20.4508856682769726
"jp2";40;20.3908794788273616
"jp2";20;21.2052117263843648

ABER die avg ist einfach zu niedrig. Die Top 10 sind im Wertebereich von mindestens 50 bis zu 99. von daher muss an der Anfrage einfach etwas falsch sein. Wobei ich nicht weiss, ob die Anfrage auch das tut, was sie tun sollte.

Ich danke schon Mal im Voraus für jegliche Antworten!

 

[nico525]

Ich empfehle bei so etwas immer vom kleinen anzufangen und dann praktisch nach außen hin zu expandieren... Du hast ja eigentlich schon den richtigen Ansatz, allerdings erschließt sich mir nicht, was du dir mit folgendem ausgeben lassen willst?:

select sourcefile,format, kompression,
count(*)*10 as erkennungsrate from v_cbir where rownum <=10 and klassesource = klassetarget
group by sourcefile, format, kompression

Warum machst du das mit dem count? Immerhin brauchst du doch die hitscores, oder hab ich das falsch verstanden? Darüber hinaus würde ich die Limitierung von 0-10 über die LIMIT-Funktion realisieren.
Was bei dir aber wahrscheinlich zum größten Problem führt: du nimmst Ergebnisse und sortierst diese nicht nach deinen Top. Die Werte kommen also wahrscheinlich unsortiert aus der Datenbank raus...

Lass dir am besten einfach mal nur diese Abfrage anzeigen und prüfe, wo dein Fehler liegen könnte. Mir hilft es oft.
Wenn diese dann so ausgegeben wird, wie sie benötigt wird, baue ich die Abfrage außen rum: für das AVG der Gruppierung, etc...

EDIT: Wie ich gerade gelesen habe scheint das mit der rownum Oracle-Syntax zu sein... Da kenn ich mich nicht so mit aus. Dennoch fehlt glaube ich die Sortierung

 

[asseroin]

Ich werds mal von Innen nach Außen probieren und schauen, dass es vorher sortiert wird.