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

  • 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?



  • #2
    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.
    PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

    Kommentar


    • #3
      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.

      Kommentar


      • #4
        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?

        PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

        Kommentar


        • #5
          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.

          Kommentar


          • #6
            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.

            Kommentar


            • #7
              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!

              Kommentar


              • #8
                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.

                Kommentar


                • #9
                  -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.

                  Kommentar


                  • #10
                    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.

                    Kommentar


                    • #11
                      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

                      Kommentar


                      • #12
                        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
                        ;

                        Kommentar


                        • #13
                          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 

                          Kommentar


                          • #14
                            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

                            Kommentar


                            • #15
                              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

                              Kommentar

                              Lädt...
                              X