Warum top n nicht so?

Bachelor Konzeptionelle Modellierung
1

Disclaimer: Dieser Thread wurde aus dem alten Forum importiert. Daher werden eventuell nicht alle Formatierungen richtig angezeigt. Der ursprüngliche Thread beginnt im zweiten Post dieses Threads.

2

Warum top n nicht so?
TOP 10 besten Kunden nach Umsatz

select K.vorname as Vorname, K.nachname as Nachname, sum(P.preis) as Umsatz
from kunde K, bestellung B, bestellung_produkt BP, Produkt P
where K.kreditkartennummer = B.kunde AND
      B.id = BP.bestellung AND
      BP.artikelnummer = P.artikelnummer
group by K.kreditkartennummer
order by Umsatz asc
limit 10

Why not so ?

3

Soweit ich weiß ist “limit” ein MySQL-spezifisches Feature das es in anderen Systemen nicht gibt.

4

[quote=wischmopp]
Soweit ich weiß ist “limit” ein MySQL-spezifisches Feature das es in anderen Systemen nicht gibt.
[/quote]Es steht zumindest nicht im Standard drin. Und nach dem sollen wir uns richten.

5

ok, dann wie passt man die obige anfrage auf non-limit um ? :slight_smile: wäre da ein view sinnvoll, oder?

6

Das war zumindest Teil der Musterlösung, und wenn ich mich nicht irre, auch Teil der Aufgabe? Ja, ist sinnvoll :smiley:

7 
CREATE VIEW PersonUmsatz as 
select K.kreditkartennummer, K.vorname as Vorname, K.nachname as Nachname, sum(P.preis) as Umsatz
from kunde K, bestellung B, bestellung_produkt BP, Produkt P
where K.kreditkartennummer = B.kunde AND
      B.id = BP.bestellung AND
      BP.artikelnummer = P.artikelnummer
group by K.kreditkartennummer

Ok erstmal ein View. Wie komm ich dann auf die TOP 10 Kunden nach Umsatz? :huh:

8

Zwei Anmerkungen vorweg:

  1. Ich hab ansonsten nicht auf Inhalt geachtet sondern nur umgeformt.
  2. Was in der „select“ Klausel steht sollte auch im „group by“ stehen (außer Aggregationsfunktionen)

create view blub AS
select K.kreditkartennummer as Kreditkartennummer, K.vorname as Vorname, K.nachname as Nachname, sum(P.preis) as Umsatz
from kunde K, bestellung B, bestellung_produkt BP, Produkt P
where K.kreditkartennummer = B.kunde
AND B.id = BP.bestellung
AND BP.artikelnummer = P.artikelnummer
group by K.kreditkartennummer, K.Vorname, K.Nachname;

select b1.Vorname as Vorname, b1.Nachname as Nachname, b1.Umsatz as Umsatz, count() as Rang
from blub b1, blub b2
where b1.Umsatz <= b2.Umsatz
group by b1.Kreditkartennummer, b1.Vorname, b1.Nachname, b1.Umsatz
having count() <= 10
order by Rang ASC;

Also so mach ich es zumindest immer:

  1. erstelle view der nurnoch die relevanten Daten enthält einschließlich primary key (macht das ganze leichter, weil man keine joins mehr braucht in der top-n query)
  2. erstelle top-n query
    2.1) from: kreuzprodukt von zwei mal dem selben view bilden
    2.2) where: vergleiche die relevante Spalte von beiden Views, und wähle die aus wo das größte Element nur sich selbst besitzt
    2.3) group by: den Elementen aus der ersten Tabelle (sodass die Rang 1 Gruppierung aus nur aus einem Tupel besteht)
    2.4) having: Anzahl der Ränge festlegen (bzw. Anzahl der Tupel in der last-rank Gruppierung die du erfassen willst)
    2.5) select: Wähle alle Elemente aus dem „group by“ aus und zusätzlich die Anzahl der Tupel pro Gruppierung als Rang.
    2.6) order by: Rang ASC;

Du kannst natürlich auch beides in einer Abfrage machen aber das macht es im Grunde nur komplizierter und anfälliger für Fehler.

9

Dein Code. Ist fast genau gleich wie meiner, aber eine Frage hab ich denn noch :slight_smile: Kommt unten

SELECT 
  B1.Vorname AS Vorname, 
  B1.Nachname AS Nachname, 
  B1.Umsatz AS Umsatz, 
  count(*) AS Rang 
FROM Blub B1, Blub B2 
WHERE
  B1.Umsatz <= B2.Umsatz 
GROUP BY 
  B1.Kreditkartennummer, 
  B1.Vorname, 
  B1.Nachname, 
  B1.Umsatz 
HAVING count(*) <= 10 
ORDER BY 
  Rang ASC;

Mein Code: (Hier falsch, das [P.Kreditkartennummer = Q.Kreditkartennummer AND] muss raus.

SELECT 
  P.Kreditkartennummer AS Kreditkartennummer, 
  P.Vorname AS Vorname, 
  P.Nachname AS Nachname, 
  P.Umsatz as Umsatz, 
  count(*) AS Rang 
FROM PersonUmsatz P, PersonUmsatz Q 
WHERE
  P.Kreditkartennummer = Q.Kreditkartennummer AND P.Umsatz <= Q.Umsatz 
GROUP BY 
  P.Kreditkartennummer, 
  P.Vorname, 
  P.Nachname, 
  P.Umsatz 
HAVING count(*) <= 10
ORDER BY 
  Rang ASC;

Warum muss man da P.Kreditkartennummer = Q.Kreditkartennummer rausmachen? Ich hab genau 1000 results.

How come? Also wie genau wirkt sich der Join dort aus mit P.Kreditkartennummer = Q.Kreditkartennummer

10

Liegt hauptsächlich daran, dass du genau 1000 Tupel in deinem View hast. :stuck_out_tongue_winking_eye:

Du willst ja erreichen, dass in einer Gruppierung immer alle anderen Tupel aus dem zweiten View drin sind die weniger Umsatz haben. Genau das verhinderst du aber indem du vorschreibst, dass nur die genommen werden die den gleichen Primärschlüssel haben (sprich bei dir ist in jeder Gruppierung genau ein Tupel enthalten, dieses Tupel besitzt einfach nur alle Attribute zwei mal).

/edit
Um es vielleicht klarer zu machen:
Du hast bisher das Kreuzprodukt immer nur als Joinersatz benutzt. Hier ist aber wirklich das Kreuzprodukt erwünscht, denn du willst ja alle möglichen Kombinationen haben.

Als Beispiel du hast einen View blub mit folgenden Ausprägungen:

Name | Umsatz
aa | 100
bb | 200
cc | 300
dd | 400

Jetzt machst du eine Top-N query:
from blub b1, blub b2

aa | 100 | aa | 100
aa | 100 | bb | 200
aa | 100 | cc | 300
aa | 100 | dd | 400
bb | 200 | aa | 100
bb | 200 | bb | 200
bb | 200 | cc | 300
bb | 200 | dd | 400
cc | 300 | aa | 100
cc | 300 | bb | 200
cc | 300 | cc | 300
cc | 300 | dd | 400
dd | 400 | aa | 100
dd | 400 | bb | 200
dd | 400 | cc | 300
dd | 400 | dd | 400

where b1.Umsatz <= b2.Umsatz

aa | 100 | aa | 100
aa | 100 | bb | 200
aa | 100 | cc | 300
aa | 100 | dd | 400
bb | 200 | bb | 200
bb | 200 | cc | 300
bb | 200 | dd | 400
cc | 300 | cc | 300
cc | 300 | dd | 400
dd | 400 | dd | 400

group by b1.Name, b1.Umsatz

aa | 100 | aa | 100
aa | 100 | bb | 200
aa | 100 | cc | 300
aa | 100 | dd | 400

bb | 200 | bb | 200
bb | 200 | cc | 300
bb | 200 | dd | 400

cc | 300 | cc | 300
cc | 300 | dd | 400

dd | 400 | dd | 400

having count(*) <= 3;

bb | 200 | bb | 200
bb | 200 | cc | 300
bb | 200 | dd | 400

cc | 300 | cc | 300
cc | 300 | dd | 400

dd | 400 | dd | 400

select b1.Name, b1.Umsatz, count(*) as Rang

bb | 200 | 3
cc | 300 | 2
dd | 400 | 1

order by Rang ASC;

dd | 400 | 1
cc | 300 | 2
bb | 200 | 3

1 „Gefällt mir“
11

Nicht nur MySQL kann das: PostgreSQL: Documentation: 9.0: LIMIT and OFFSET
Oracle aber zum beispiel wieder nicht, dort macht man das z.B. über ein Subquery, in dem man im SELECT noch ein ROWNUM xy abfragt, die man in der WHERE-Clause dann begrenzen kann. Andere Datenbanken bieten Aggregatsfunktionen wie RANK() an. All das ist allerdings nicht standardkonform und wird wohl deshalb nicht gelehrt ([size=5]auch wenn man in der Praxis TOP-N-Queries natürlich nicht so wie in der Vorlesung besprochen machen will, um nen Vendor-lock-in kommt man IMHO bei DBs ohnehin schwer rum :([/size]).

12

[quote=pr0wl][…]Also wie genau wirkt sich der Join dort aus mit P.Kreditkartennummer = Q.Kreditkartennummer
[/quote]

Um deine Frage noch wirklich zu beantworten (mit der gleichen Abfrage wie oben):

Name | Umsatz
aa | 100
bb | 200
cc | 300
dd | 400

Jetzt machst du eine Top-N query:
from blub b1, blub b2

aa | 100 | aa | 100
aa | 100 | bb | 200
aa | 100 | cc | 300
aa | 100 | dd | 400
bb | 200 | aa | 100
bb | 200 | bb | 200
bb | 200 | cc | 300
bb | 200 | dd | 400
cc | 300 | aa | 100
cc | 300 | bb | 200
cc | 300 | cc | 300
cc | 300 | dd | 400
dd | 400 | aa | 100
dd | 400 | bb | 200
dd | 400 | cc | 300
dd | 400 | dd | 400

where b1.Umsatz <= b2.Umsatz
AND b1.name = b2.Name

aa | 100 | aa | 100
bb | 200 | bb | 200
cc | 300 | cc | 300
dd | 400 | dd | 400

group by b1.Name, b1.Umsatz

aa | 100 | aa | 100

bb | 200 | bb | 200

cc | 300 | cc | 300

dd | 400 | dd | 400

having count(*) <= 3

aa | 100 | aa | 100

bb | 200 | bb | 200

cc | 300 | cc | 300

dd | 400 | dd | 400

select b1.Name, b1.Umsatz, count(*) as Rang

aa | 100 | 1
bb | 200 | 1
cc | 300 | 1
dd | 400 | 1

order by Rang ASC; :

aa | 100 | 1
bb | 200 | 1
cc | 300 | 1
dd | 400 | 1

13

woher weiß ich denn bei dem rotmarkierten, dass die erste relation weggefallen ist und nicht die letzte?
und wieso wird nach b1 selected obwohl b2 größer sein soll(bei dem WHERE) als b1?

14

Beim rot markierten Teil wurden mit “having” die Gruppen gefiltert, danach ob sie kleiner/gleich 3 sind. Und die erste Gruppe war halt nun mal 4 groß.
Wenn du b2 gruppieren und auswählen würdest hättest du bottom-n. Der Vergleich muss so sein, da man für jedes b1 daran interessiert ist wieviele b2 größer/gleich sind. Beim größten Element (dd) ist das logischerweise nur das Element selbst, bei denen darunter jeweils eins mehr als beim Element darüber. Hoffe das war jetzt verständlich :stuck_out_tongue:

15

ja war sehr verständlich :slight_smile: danke!!

16

#include
using abstraktionsschicht;

[size=5](Oder: import java.foo.shit.hibernate.*)[/size]

17

Kann man machen, ist aber nur entweder umstaendlich oder lahm oder kaputt…