Partition by, die ersten 3 Zeilen

Hallo zusammen,

ich bräuchte mal wieder eure Hilfe. S. Screenshot.

Ich hätte gerne die grün markieren Zeilen. Auswahl erfolgt wie folgt:

Für jeden Typ möchte ich drei Paletten (falls es mehr als 3 gibt ist es egal welche 3, da Stichprobe) und für diese Paletten möchte ich von jeder 3 Kartons (auch egal welche 3, da Stichprobe). Gibt es nur 1 oder 2 Paletten zu einem Typ, dann eben nur 1 oder 2. Sind nur 2 oder 1 Kartons auf der Palette dann eben nur 2 oder 1.

Hinweise: Paletten_IDs und Karton_IDs sind eindeutig. Eine Palette kann mehrere Kartons enthalten. Der Typ beschreibt den Inhalt des Kartons, ist aber nicht weiter wichtig. Denkt euch im Karton sind „As“, „Bs“ usw. in Form von Metallbuchstaben drin.

Mein Ansatz war ein partition by Typ, Paletten_ID in Verbindung mit first_value. Hat aber leider nicht geklappt.

Ich benutze Oracle!

Vielen Dank im Voraus. Wenn etwas unklar ist, fragt gerne nach.

 

Du kannst row_number() nutzen und damit die Rows zählen. Das in einem Subselect. Außen drum herum nur die auswählen, wo row_number innen <= 3 ist. Die intelligentere Methode wäre ein lateral join. Wenn ich mal zeit haben, könnte ich das sogar zeigen ...

 

ROW_NUMBER() ist nicht verkehrt allerdings musst du eine zufällige Sortierung erzwingen, sonst ist es ja keine Stichprobe. Ohne Sortierung ist es zwar theoretisch wilkürlich aber faktisch meist reproduzierbar.

 

Depends. Es zählt halt erst mal alles durch. Wenn das einige Milliarden Rows sind, zieht sich das schon mal, und wenn man dann außen drum herum von den einigen Milliarden alle bis auf 3 entsorgt, dauert auch das noch einmal. Da ist ein lateral join um einiges intelligenter.

 

Danke euch. Wir bewegen uns im Bereich kleiner 10 Mio.
An Row_number() habe ich auch schon gedacht, aber:
Ich bräuchte hier 2x Row_number(), oder? Einmal brauche ich 3 Paletten je Typ, und dann 3 Kartons je Palette. Und diese Verschachtelung krig ich irgendwie nicht gebacken. Ja hier evtl mal jemand ein simples Beispiel?

 

row_number() over ( partition by ... order by ...) sollte tun.

 

Hat fast funktioniert.
Also den row_num, der mit die Kartons auf den Paletten zählt hab ich hinbekommen mit

Partition by PalettenId order by karton_id.

Er numeriert mir so alle Kartons durch für jede Palette und ich kann auf <=3 einschränken, damit habe ich immer 3 Kartons je Palette. Nun bräuchte ich aber noch einen row_num, der die Paletten durch numeriert aber bei einem neuen Typ neu zu zählen beginnt. Auf obige Tabelle bezogen hätten jedoch gleiche Paletten natürlich die gleiche Nummer, wodurch ich irgendwo verschachteln muss - ich weiß aber nicht wo.

 

Hier mal ein Beispiel in MSSQL, eventuell kennt Oracle kein newid() sondern irgendetwas anderes um Zufall zu erzeugen:

Code:

WITH tabelle(typ,palette,karton) AS (
   SELECT   'A',1,1 UNION ALL
   SELECT   'A',1,2 UNION ALL
   SELECT   'A',1,3 UNION ALL
   SELECT   'A',1,4 UNION ALL
   SELECT   'A',1,5 UNION ALL
   SELECT   'A',2,6 UNION ALL
   SELECT   'A',2,7 UNION ALL
   SELECT   'A',3,8 UNION ALL
   SELECT   'A',4,9 UNION ALL
   SELECT   'A',5,10 UNION ALL
   SELECT   'A',5,11 UNION ALL
   SELECT   'A',6,12 UNION ALL
   SELECT   'A',6,13 UNION ALL
   SELECT   'B',7,14 UNION ALL
   SELECT   'B',7,15 UNION ALL
   SELECT   'B',8,16 UNION ALL
   SELECT   'C',9,17 UNION ALL
   SELECT   'C',9,18 UNION ALL
   SELECT   'C',9,19 UNION ALL
   SELECT   'C',9,20 UNION ALL
   SELECT   'C',11,21 UNION ALL
   SELECT   'C',11,22
   )
SELECT   t4.typ,
     t4.palette,
     t4.karton
FROM   (

SELECT   ROW_NUMBER() OVER (PARTITION BY t2.typ,t2.palette ORDER BY newid()) AS zeile,
     t2.typ,
     t2.palette,
     t3.karton
FROM   (

SELECT   ROW_NUMBER() OVER (PARTITION BY t1.typ ORDER BY newid()) AS zeile,
     t1.typ,
     t1.palette
FROM   tabelle t1

     ) t2
INNER JOIN tabelle t3
ON     t2.typ = t3.typ
AND     t2.palette = t3.palette
WHERE   t2.zeile <= 3

     ) t4
WHERE   t4.zeile <= 3

 

dbms_random.value z.B.

 

Bei MSSQL gibt es noch rand() aber das erzeugt nur einen zufälligen Wert pro Query, das war bisher immer irgendwie nutzlos :-/

 

Der Vollständigkeit halber noch das Oracle Statement mit (pseudo)zufälliger Sortierung:

Code:

WITH tabelle AS (
   SELECT   'A' TYP ,1 paletten_id,1 karton_id from dual UNION ALL
   SELECT   'A',1,2 from dual UNION ALL
   SELECT   'A',1,3 from dual UNION ALL
   SELECT   'A',1,4 from dual UNION ALL
   SELECT   'A',1,5 from dual UNION ALL
   SELECT   'A',2,6 from dual UNION ALL
   SELECT   'A',2,7 from dual UNION ALL
   SELECT   'A',3,8 from dual UNION ALL
   SELECT   'A',4,9 from dual UNION ALL
   SELECT   'A',5,10 from dual  UNION ALL
   SELECT   'A',5,11 from dual  UNION ALL
   SELECT   'A',6,12 from dual  UNION ALL
   SELECT   'A',6,13 from dual  UNION ALL
   SELECT   'B',7,14 from dual  UNION ALL
   SELECT   'B',7,15 from dual  UNION ALL
   SELECT   'B',8,16 from dual  UNION ALL
   SELECT   'C',9,17 from dual  UNION ALL
   SELECT   'C',9,18 from dual  UNION ALL
   SELECT   'C',9,19 from dual  UNION ALL
   SELECT   'C',9,20 from dual  UNION ALL
   SELECT   'C',11,21 from dual UNION ALL
   SELECT   'C',11,22 from dual
   )
select * from(
select typ,paletten_id,karton_id,row_number() over (partition by paletten_id order by dbms_random.value() ) as num from tabelle) where num <=3

Wie würde das denn aussehen? Sobald ich irgendwo einen Sort hab, muss alles durchsucht werden.

 

Code:

test=*# select * from michi ;
 typ | p_id | k_id
-----+------+------
 a  | p1  | k1
 a  | p1  | k2
 a  | p1  | k3
 a  | p1  | k4
 a  | p1  | k5
 a  | p2  | k6
 a  | p2  | k7
 a  | p3  | k8
 a  | p4  | k9
 a  | p5  | k10
 a  | p5  | k11
 a  | p6  | k12
 a  | p6  | k13
 b  | p7  | k14
 b  | p7  | k15
 b  | p8  | k16
 c  | p9  | k17
 c  | p9  | k18
 c  | p9  | k19
 c  | p9  | k20
 c  | p11  | k21
 c  | p11  | k22
(22 rows)

test=*# with t as (select distinct typ from michi ) select t.typ, p.p_id, k.k_id from t left join lateral (select distinct p_id from michi where typ=t.typ limit 3) p on (true) left join lateral (select distinct k_id from michi where typ=t.typ and p_id = p.p_id limit 3) k on (true) order by t.typ, p.p_id, k.k_id;
 typ | p_id | k_id
-----+------+------
 a  | p1  | k1
 a  | p1  | k2
 a  | p1  | k3
 a  | p2  | k6
 a  | p2  | k7
 a  | p3  | k8
 b  | p7  | k14
 b  | p7  | k15
 b  | p8  | k16
 c  | p11  | k21
 c  | p11  | k22
 c  | p9  | k17
 c  | p9  | k18
 c  | p9  | k19
(14 rows)

test=*#

Sortierung ist abweichend, da textuell sortiert. Da sind 2 lateral joins drin, je mit einem LIMIT 3, da ja nur 3 angezeigt werden sollen. Mit passenden Indexen sollten diese Joins recht schnell gehen, da ja nach je 3 abgebrochen werden kann.

Ich weiß aber nicht, ob Ora lateral joins kann.

 

Bei MICHI hast schon mal einen Sort Unique drauf, denn muss die DB machen.
Dann hast nochmal in jedem Join einen Sort Unique drinnen, den die Datenbank auch machen muss bevor die LIMIT Funktion angewendet wird.

Ich würd jetzt nicht behaupten wollen, dass das schneller ist als ein einziger Sort.

PS: Ja Ora kann lateral joins.

 

Diese Anwendung hier mag vielleicht nicht so perfekt sein. Ich sah mal eine Demo, die wie folgt war: quasi ein Forum, N Threads, zu jedem dann wieder (und zwar sehr viele) Nachrichten. Und nun zeige zu jedem Thread die 3 aktuellsten. Das war dann auf viele verschieden Wegen realisiert, u.a. mit row_number() und Check außen drum auf row_number <= 3, aber auch mit Stored Procs. Und anderen Wegen. Die 'lateral join' - Lösung war um Größenordnungen die schnellste Lösung.