Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Partition by, die ersten 3 Zeilen

Dieses Thema im Forum "Oracle" wurde erstellt von Michi_R, 23 Februar 2017.

  1. Michi_R

    Michi_R Fleissiger Benutzer

    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.

    upload_2017-2-23_22-15-7.png
     
  2. akretschmer

    akretschmer Datenbank-Guru

    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 ...
     
  3. ukulele

    ukulele Datenbank-Guru

    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.
     
  4. akretschmer

    akretschmer Datenbank-Guru

    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.
     
  5. Michi_R

    Michi_R Fleissiger Benutzer

    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?
     
  6. akretschmer

    akretschmer Datenbank-Guru

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

    Michi_R Fleissiger Benutzer

    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.
     
  8. ukulele

    ukulele Datenbank-Guru

    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
     
  9. unficyp

    unficyp Fleissiger Benutzer

    dbms_random.value z.B.
     
  10. ukulele

    ukulele Datenbank-Guru

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

    drdimitri Datenbank-Guru

    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.
     
  12. akretschmer

    akretschmer Datenbank-Guru

    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.
     
  13. drdimitri

    drdimitri Datenbank-Guru

    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.
     
  14. akretschmer

    akretschmer Datenbank-Guru

    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.
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden