Ankündigung

Einklappen
Keine Ankündigung bisher.

Aufsteigende Rangfolge für Gruppen in SQL

Einklappen

Neue Werbung 2019

Einklappen
X
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

  • knuffiwuffi
    hat ein Thema erstellt Aufsteigende Rangfolge für Gruppen in SQL.

    Aufsteigende Rangfolge für Gruppen in SQL

    Hallo zusammen,

    ich habe eine passende SELECT Anweisung, die mir das angestrebte Ergenis liefert:

    Code:
    SELECT @row_number:=CASE WHEN @group_by_value=group_by_value THEN @row_number+1 ELSE 1 END AS current_rank,@group_by_value:=group_by_value AS group_by_value
    FROM xdata_import, (SELECT @row_number:=0,@group_by_value:='') AS t
    ORDER BY id_pk DESC
    Ich erhalte eine korrekte Ansicht:

    Code:
    row_number | group_by_value
    1 | A
    2 | A
    3 | A
    1 | B
    2 | B
    1 | C
    2 | C
    3 | C
    Nur benötige ich den Wert in der Spalte current_rank.
    Wie bastel ich da eine UPDATE Anweisung drum herum, damit row_number in die jeweilige Zeile bei current_rank geschrieben wird?


  • protestix
    antwortet
    Auch wenn es schon etwas her ist,ich hätte da noch eine Lösung parat

    PHP-Code:
    SELECT t1.idt1.group_valuest1.value_needs_rankt1.expected_rank,COUNT(lesser.id) + AS rank
    FROM table_with_samples 
    AS t1
    LEFT JOIN table_with_samples 
    AS lesser ON lesser.value_needs_rank t1.value_needs_rank AND lesser.group_values t1.group_values
    GROUP BY t1
    .idt1.group_values,t1.value_needs_rankt1.expected_rank 
    SQLfiddle
    Beschrieben unter anderem bei SOF

    Einen Kommentar schreiben:


  • knuffiwuffi
    antwortet
    Ufff...ich bin grad etwas Baff. Vielen lieben Dank für die ausführliche Antwort. Das muss ich mir wirklich einmal im Detail anschauen, wie Du das gezaubert hast.

    Meine aktuelle Lösung funktioniert. Ich werde aber auf jeden Fall versuchen, deine Ratschläge zu implementieren; und wichtiger noch: zu verstehen!
    Direkte Kommunikation mit der DB dürfte schneller sein, als eine Sub Query per PHP Schleife auf die DB zu schießen.

    THANKQ

    Einen Kommentar schreiben:


  • erc
    antwortet
    Zitat von knuffiwuffi Beitrag anzeigen
    Leider keine Option in PHPMyAdmin gefunden, um BASE64 auf string umzustellen.
    Auch ein CAST() oder BASE64() funktioniert nicht.

    Kann ich das nicht direkt als lesbaren Zahlenwert ausgeben (DB Einstellung?!)?
    Hatte bei meinen Versuchen bislang leider keinen Erfolg.
    Das ist Client abhängig. Wenn du das im Client nicht umstellen kannst, kannst du das auch in SQL machen. Den case block in CAST([case block] AS signed) setzen.

    Zitat von knuffiwuffi Beitrag anzeigen
    Das Teil als Sub-Query umzuschreiben kann ich leider nicht.
    Dazu fehlt mir die Praxis in MySQL.
    Das sieht dann so aus:

    PHP-Code:
    SELECT sorted_rows.*, @rank := CASE
        
    WHEN @partval group_values AND @rankval value_needs_rank THEN @rank
        WHEN 
    @partval group_values AND (@rankval := value_needs_rankIS NOT NULL THEN @rank 1
        WHEN 
    (@partval := group_valuesIS NOT NULL AND (@rankval := value_needs_rankIS NOT NULL THEN 1
    END 
    AS rank_for_value
    FROM 
    (
        
    SELECT table_with_samples.*,
        
    FROM table_with_samples
        ORDER BY group_values
    value_needs_rank
    ) AS sorted_rows, (SELECT @rank := NULL, @partval := NULL, @rankval := NULL) AS 
    Wenn du das nicht machst, besteht die möglichkeit, dass da kompletter unsinn raus kommt.


    Ich versteh jetzt auch das Problem. Wenn du den Rank so zählen willst, wird das ziemlich fummlig. Entweder führst du eine Variable mit, die zählt wieviel gleiche Ergebnisse hintereinander getroffen wurden:

    @rank := CASE
    WHEN @partval = group_values AND @rankval = value_needs_rank THEN @rank [den fall musst du mitzählen, als z.B. in @offeset]
    WHEN @partval = group_values AND (@rankval := value_needs_rank) IS NOT NULL THEN @rank + 1 [statt + 1, kommt hier der @offeset drauf]
    WHEN (@partval := group_values) IS NOT NULL AND (@rankval := value_needs_rank) IS NOT NULL THEN 1
    END AS rank_for_value,

    Das ist nicht so trivial (bzw. fummlig), weil das meiner Meinung nach nicht in der CASE Anweisung gemacht werden kann. Das muss separat erfolgen. Wenn das aber eh separat gemacht werden muss, kann auch gleich ein anderer Ansatz gewählt werden. Statt den "rank" erzeugst du erstmal die ROW_NUMBER bzw @sequence (wie in deinem anderen Query) pro Gruppe.

    Ungefähr so:
    @rank := CASE
    WHEN @partval = group_values AND @rankval = value_needs_rank THEN @rank + 1
    WHEN @partval = group_values AND (@rankval := value_needs_rank) IS NOT NULL THEN @rank + 1
    WHEN (@partval := group_values) IS NOT NULL AND (@rankval := value_needs_rank) IS NOT NULL THEN 1
    END AS rank_for_value,

    Das lässt sich vereinfachen:
    @rank := CASE
    WHEN @partval = group_values THEN @rank + 1
    WHEN (@partval := group_values) IS NOT NULL THEN 1
    END AS rank_for_value,

    Das nennst du um in @sequence (auch @partval!) Und dann kannst du den ursprünglichen Code abwandeln:

    @rank := CASE
    WHEN @partval = group_values AND @rankval = value_needs_rank THEN @rank
    WHEN @partval = group_values AND (@rankval := value_needs_rank) IS NOT NULL THEN @sequence
    WHEN (@partval := group_values) IS NOT NULL AND (@rankval := value_needs_rank) IS NOT NULL THEN 1
    END AS rank_for_value,

    Sollte dann irgendwie so aussehen:

    PHP-Code:
    SELECT
        
    ...,
        @
    sequence := CASE
            
    WHEN @spartval group_values THEN @sequence 1
            WHEN 
    (@spartval := group_valuesIS NOT NULL THEN 1
        END 
    AS sequence,
        @
    rank := CASE
            
    WHEN @partval group_values AND @rankval value_needs_rank THEN @rank
            WHEN 
    @partval group_values AND (@rankval := value_needs_rankIS NOT NULL THEN @sequence
            WHEN 
    (@partval := group_valuesIS NOT NULL AND (@rankval := value_needs_rankIS NOT NULL THEN 1
        END 
    AS rank_for_value,
    FROM 

    Einen Kommentar schreiben:


  • knuffiwuffi
    antwortet
    Ich belasse es vorerst bei einer PHP Schleife für die Gruppen und erstelle je Gruppe eine temporäre Tabelle für die Ränge:

    Code:
    CREATE TABLE table_with_samples
        (`id` int, `group_values` varchar(1), `value_needs_rank` decimal(10, 2))
    ;
    
    INSERT INTO table_with_samples
        (`id`, `group_values`, `value_needs_rank`,)
    VALUES
        (1, 'A', 1.22),
        (2, 'A', 1.35),
        (3, 'A', 6.53),
        (4, 'B', 4.00),
        (5, 'B', 4.00),
        (6, 'B', 5.63),
        (7, 'B', 6.67)
    ;
    Code:
    CREATE TABLE IF NOT EXISTS table_tmp AS
        SELECT
            group_values,
            value_needs_rank,
            rank
        FROM (
            SELECT
                        group_values,
                        value_needs_rank,
                IF(value_needs_rank = @_last_value_needs_rank, @cur_rank := @cur_rank, @cur_rank := @_sequence) AS rank,
                @_sequence := @_sequence + 1,
                @_last_value_needs_rank := value_needs_rank
            FROM table_with_samples, (SELECT @cur_rank := 1, @_sequence := 1, @_last_current_price_product := NULL) r
            WHERE group_values = 'B'
            ORDER BY value_needs_rank
        ) ranked
    ;

    Einen Kommentar schreiben:


  • knuffiwuffi
    antwortet
    Die Sortierung funktioniert ja, aber nicht für Gruppierungen:

    Code:
    SELECT
        group_values,
        value_needs_rank,
        rank
    FROM (
        SELECT
            *,
            IF(value_needs_rank = @_last_value_needs_rank, @cur_rank := @cur_rank, @cur_rank := @_sequence) AS rank,
            @_sequence := @_sequence + 1,
            @_last_value_needs_rank := value_needs_rank
        FROM table_with_samples, (SELECT @cur_rank := 1, @_sequence := 1, @_last_value_needs_rank := NULL) r
        ORDER BY value_needs_rank ASC
    ) ranked
    A 1,2 1
    A 1,5 2
    B 4 3
    B 4 3
    B 5,3 5
    A 6,5 6
    B 6,7 7

    Einen Kommentar schreiben:


  • knuffiwuffi
    antwortet
    Ja, ich benutze aktuell MySQL 5.7.24.
    Danke für den Hinweis!

    Leider keine Option in PHPMyAdmin gefunden, um BASE64 auf string umzustellen.
    Auch ein CAST() oder BASE64() funktioniert nicht.

    Kann ich das nicht direkt als lesbaren Zahlenwert ausgeben (DB Einstellung?!)?
    Hatte bei meinen Versuchen bislang leider keinen Erfolg.

    Das Teil als Sub-Query umzuschreiben kann ich leider nicht.
    Dazu fehlt mir die Praxis in MySQL.

    Vielleicht irgendwo ein nachvollziehbares Beispiel?

    Mein Problem ist, daß ich die Aufgabe nicht mit einer SQL Anweisung lösen kann.
    Ich finde im Netz überwiegend nur RANK OVER + PARTITION Lösungen.

    Die Lösung mit dem CASE finde ich sehr interessant, da auch einfach zu lesen.
    Nur die Query logisch aufzubauen...da knacke ich dran.

    Einen Kommentar schreiben:


  • erc
    antwortet
    -Partion und Rank gibts erst ab Mysql 8. Benutzt du eine ältere Version kann das damit nicht funktionieren.

    -Zu 1. Du verwendest phpmyadmin? Irgendwo gibts die Option binäre Werte als Base64 anzuzeigen und die ist aktiv.

    -Der Query falsch. Du kannst nicht in der SELECT Klausel mit Variablen zählen und gleichzeitig sortieren. Die Reihnfolge wie die Ausdrücke in der SELECT Klausel ausgeführt werden, ist "zufällig". Die Sortierung musst du als Subquery machen.

    -Dein eigentliches Problem versteh ich nicht.

    Einen Kommentar schreiben:


  • knuffiwuffi
    antwortet
    Hallo zusammen,

    ich wollte es jetzt doch noch einmal versuchen, ob sich RANK OVER + PARTITION BY für eine gruppierte Sortierung nicht vollständig als SQL für MySQL abbilden läß; und zwar so, daß es leicht zu lesen ist:

    http://sqlfiddle.com/#!9/fa2f24/2

    Code:
    CREATE TABLE table_with_samples
        (`id` int, `group_values` varchar(1), `value_needs_rank` decimal(10, 2), `expected_rank` int, `save_rank_for_value` int)
    ;
    Code:
    INSERT INTO table_with_samples
        (`id`, `group_values`, `value_needs_rank`,`expected_rank`, save_rank_for_value)
    VALUES
        (1, 'A', 1.2, 1, NULL),
        (2, 'A', 1.5, 2, NULL),
        (3, 'A', 6.5, 3, NULL),
        (4, 'B', 4, 1, NULL),
        (5, 'B', 4, 1, NULL),
        (6, 'B', 5.3, 3, NULL),
        (7, 'B', 6.7, 4, NULL)
    ;
    Code:
    SELECT table_with_samples.*, @rank := CASE
        WHEN @partval = group_values AND @rankval = value_needs_rank THEN @rank
        WHEN @partval = group_values AND (@rankval := value_needs_rank) IS NOT NULL THEN @rank + 1
        WHEN (@partval := group_values) IS NOT NULL AND (@rankval := value_needs_rank) IS NOT NULL THEN 1
    END AS rank_for_value
    FROM table_with_samples, (SELECT @rank := NULL, @partval := NULL, @rankval := NULL) AS x
    ORDER BY group_values, value_needs_rank;
    Bislang bin ich die Gruppen mit einer PHP Schleife durchgegangen, da ich, auch mit der hier angebotenen Hilfe es bislang nicht wirklich verstanden habe, diese Aufgabe richtig zu lösen.
    Sämtliche Versuche mit RANK OVER und Partition haben nicht funktioniert.

    Ich möchte den Wert für die Rangfolge direkt in die DB schreiben (save_rank_for_value).

    Mein aktuelles Problem:

    1. Der Wert rank_for_value wird wie folgt angezeigt "MQ=="...habe ich bislang noch nicht gesehen. Ich hätte INT erwartet.
    2. Mein SELECT kann ich leicht lesen. Müßte jetzt nur noch für die Gruppen umgebaut werden. Hier fehlt mir ein Lösungsansatz, da ich nicht weiss, wie ich die Rangfolge mit "Lücken" durchgehend schreiben kann.

    Im vermute, daß ich eine zweite Variable (@rank2) benötigen, die durchgehend um 1 erhöht wird.

    Bin für jede Hilfe dankbar.

    Einen Kommentar schreiben:


  • Perry Staltic
    antwortet
    Zitat von knuffiwuffi Beitrag anzeigen

    Ich habe es zwischenzeitlich über ein PHP Loop gelößt.

    Ich nehme mir also eine Gruppe, verteile die Ränge, und dann die nächste Gruppe (Loop); bis alle Gruppen eine Rangfolge erhalten haben.
    Dieses Feedback ist eine gute Gelegenheit einmal festzustellen, dass gute Vorschläge niemand hindern, eine schlechte Lösung zu wählen.
    Ich weiß nicht genau wie der PHP Code zur geschilderten Lösung aussieht, aber es hätte mit einem SQL Statement gemacht werden können. Wahrscheinlich ist die Datenmenge so klein, dass nicht mal auffällt, wieviel Zeit die PHP Lösung kostet. Schade!

    Einen Kommentar schreiben:


  • knuffiwuffi
    antwortet
    Etwas spät, aber danke für eure Lösungen.

    Ich habe es zwischenzeitlich über ein PHP Loop gelößt.

    Ich nehme mir also eine Gruppe, verteile die Ränge, und dann die nächste Gruppe (Loop); bis alle Gruppen eine Rangfolge erhalten haben.

    Einen Kommentar schreiben:


  • erc
    antwortet
    Du kannst in einem UPDATE auch JOINS und Subqueries verwenden.

    Code:
    UPDATE
        xdata_import INNER JOIN
        (
            dein schöner query
        ) AS ranks ON (xdata_import.pk_id = ranks.pk_id)
    SET
        xdata_import.rank = ranks.current_rank
    PS: MySql unterstützt seit Version 8 auch window functions (siehe akretschmers Beitrag). Damit würde in diesem Fall das abartige @var rumgefrimel entfallen.

    Einen Kommentar schreiben:


  • akretschmer
    antwortet
    Zitat von knuffiwuffi Beitrag anzeigen
    Hier ist das Problem auch beschrieben: https://searchoracle.techtarget.com/...ber-in-a-group
    Code:
    test=*# select * from knuffiwuffi ;
     g | value
    ---+-------
     1 |     5
     1 |     6
     1 |     3
     1 |     8
     2 |     4
     2 |     3
     2 |     6
     2 |     8
     2 |     9
    (9 rows)
    
    test=*# select *, row_number() over (partition by g) from knuffiwuffi ;
     g | value | row_number
    ---+-------+------------
     1 |     5 |          1
     1 |     6 |          2
     1 |     3 |          3
     1 |     8 |          4
     2 |     4 |          1
     2 |     3 |          2
     2 |     6 |          3
     2 |     8 |          4
     2 |     9 |          5
    (9 rows)
    
    test=*# select *, row_number() over (partition by g order by value) from knuffiwuffi ;
     g | value | row_number
    ---+-------+------------
     1 |     3 |          1
     1 |     5 |          2
     1 |     6 |          3
     1 |     8 |          4
     2 |     3 |          1
     2 |     4 |          2
     2 |     6 |          3
     2 |     8 |          4
     2 |     9 |          5
    (9 rows)
    
    test=*#

    Nur, auch ein SELECT...ich würde den Rang gerne direkt in die DB schreiben.
    Damit die Daten nach dem nächsten Insert/Update/Delete dann falsch sind?

    Einen Kommentar schreiben:


  • knuffiwuffi
    antwortet
    Hier ist das Problem auch beschrieben: https://searchoracle.techtarget.com/...ber-in-a-group

    Nur, auch ein SELECT...ich würde den Rang gerne direkt in die DB schreiben.

    Einen Kommentar schreiben:


  • akretschmer
    antwortet
    Deine Frage ergibt keinen Sinn. Es gibt, wenn Du row_number() oder rank() oder dense_rank() benötigst, passende Funktionen, und das Ergebniss solcher Funktionen speichert man nicht in der Tabelle (via UPDATE wie von Dir gesucht), sondern berechnet es halt bei der Abfrage.

    Einen Kommentar schreiben:

Lädt...
X