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

Variablen und Schleifen

Dieses Thema im Forum "PostgreSQL" wurde erstellt von re342, 19 Oktober 2018.

  1. re342

    re342 Neuer Benutzer

    Hallo,

    ich suche nach einer Möglichkeit, in PostgreSQL vor der eigentlichen Abfrage Variablen oder While-Schleifen verwenden zu können, um vorab einen Wert zu berechnen.

    Ein Beispiel:
    Ein Hotel hat 100 Plätze. In einer Tabelle 'hotel' wird für Gruppen gespeichert, als wievielter sie das Hotel gebucht haben (id), den Namen der Gruppe (name) und die Anzahl der Personen (members). Ich möchte eine Abfrage erstellen, die mir alle Gruppen anzeigt, die noch genug Plätze im Hotel bekommen. Zum Beispiel sieht die Tabelle so aus:

    id (integer) | name (character varying) | members (integer)
    2 | Schachverein | 25
    5 | RuderClub | 10
    1 | FußballVerein | 21
    3 | Klasse8d | 30
    4 | FachschaftMathematik | 20
    6 | FamilieMeier | 5

    In dem Fall sollte die Ausgabetabelle [FußballVerein, Schachverein, Klasse8d, FachschaftMathematik] sein, weil das die ersten vier Registrierungen sind, welche zusammen 96 Plätze einnehmen. Mit dem RuderClub wären die 100 Plätze überstiegen, also kommt der nicht mehr in die Ausgabe.

    -----

    Mithilfe von Variablen und Schleifen wäre ein Problem in der Art leicht zu lösen. PostgreSQL akzeptiert aber die Syntax nicht. Mein Versuch:

    Code:
    DECLARE @i int;
    DECLARE @counter int;
    SET @i = 0;
    SET @counter = 0;
    WHILE (@counter <= 100)
    BEGIN
        SET @i += 1;
        SET @counter += (SELECT members FROM hotel WHERE id = @i)
    END
    GO
    
    SELECT name
    FROM hotel
    WHERE id <= @i
    ORDER BY id
    
    PostgreSQL sieht schon direkt beim Deklarieren der Variable einen Syntaxfehler. Ich habe schon viel ausprobiert und recherchiert, aber das erstellen von Variablen bisher nicht richtig hinbekommen. Kann mir jemand helfen, dieses einfache Beispiel zum Laufen zu bekommen?

    Danke & Gruß
    re342
     
  2. castorp

    castorp Datenbank-Guru

    Dafür brauchst Du keine Schleifen. Das kann mit sog. Window Funktionen machen.

    Wenn man dem sum() in einer Window Funktion ein "order by" mitgibt, dann wird die kumulierte Summe bis zu dem Datensatz berechnet. Und die schränkst Du einfach auf <= 100 ein:

    Code:
    select id, name, members
    from (
      select id, name, members, sum(members) over (order by id) as sofar
      from hotel
    ) t
    where sofar <= 100
    order by id;
    Online Beispiel: https://rextester.com/UOCOIA20013

    Noch ein wenig Hintergrundinfos: SQL (die Abfragesprache) kennt keine Schleifen, Variablen oder ähnliche Konstrukte - sowas gibt es nur in prozeduralen Sprachen.

    Postgres (wie auch z.B. Oracle, DB2 oder Firebird) trennt klar zwischen der Abfragesprache SQL und prozeduraler Sprache wie z.B. PL/pgSQL - und nur dort gibt es Schleifen oder Variablen.

    Aber in den meisten Fällen ist es falsch in SQL in Schleifen zu denken. Grundsätzlich sind relationale Datenbanken dazu gedacht Mengen (im mathematischen Sinn) zu verarbeiten. Schleifen fallen in die Kategorie "einzelne Datensätze verarbeiten" und das ist in den meisten Fällen unnötig und fast immer langsamer als ein mengen-orientierter Ansatz
     
    re342 und akretschmer gefällt das.
  3. akretschmer

    akretschmer Datenbank-Guru

    man könnte noch ergänzen, daß der Nachteil ist, daß die Tabelle komplett gelesen werden muß. Falls diese sehr groß ist und man davon ausgehen kann, daß alle Werte da >= 1 sind könnte man im inneren select evtl. ein order by members desc limit 100 einfügen.
     
    re342 gefällt das.
  4. akretschmer

    akretschmer Datenbank-Guru

    Um es mal im Beispiel zu zeigen:

    Code:
    test=# create table hotel (id serial primary key, members int);
    CREATE TABLE
    test=*# insert into hotel (members) select random() * 30 from generate_series(1, 100000000) s;
    INSERT 0 100000000
    test=*# explain analyse select * from (select *, sum(members) over (order by id) from hotel) foo where sum < 100;
                                                                    QUERY PLAN                                                                 
    -------------------------------------------------------------------------------------------------------------------------------------------
     Subquery Scan on foo  (cost=18831767.58..21831768.42 rows=33333343 width=16) (actual time=83223.317..168511.094 rows=4 loops=1)
       Filter: (foo.sum < 100)
       Rows Removed by Filter: 99999996
       ->  WindowAgg  (cost=18831767.58..20581768.07 rows=100000028 width=16) (actual time=83223.314..158952.703 rows=100000000 loops=1)
             ->  Sort  (cost=18831767.58..19081767.65 rows=100000028 width=8) (actual time=83219.064..98368.087 rows=100000000 loops=1)
                   Sort Key: hotel.id
                   Sort Method: external merge  Disk: 1761296kB
                   ->  Seq Scan on hotel  (cost=0.00..1442478.28 rows=100000028 width=8) (actual time=1.660..15971.089 rows=100000000 loops=1)
     Planning Time: 7.026 ms
     Execution Time: 168787.314 ms
    (10 rows)
    
    test=*# explain analyse select * from (select *, sum(members) over (order by id) from hotel order by id limit 100) foo where sum < 100;
                                                                          QUERY PLAN                                                                     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
     Subquery Scan on foo  (cost=0.57..114.03 rows=33 width=16) (actual time=3.208..3.521 rows=4 loops=1)
       Filter: (foo.sum < 100)
       Rows Removed by Filter: 96
       ->  Limit  (cost=0.57..112.78 rows=100 width=16) (actual time=3.205..3.486 rows=100 loops=1)
             ->  WindowAgg  (cost=0.57..112217037.41 rows=100000028 width=16) (actual time=3.194..3.446 rows=100 loops=1)
                   ->  Index Scan using hotel_pkey on hotel  (cost=0.57..110717036.99 rows=100000028 width=8) (actual time=3.176..3.239 rows=101 loops=1)
     Planning Time: 0.187 ms
     Execution Time: 3.594 ms
    (8 rows)
    
    test=*#
    
     
    re342 gefällt das.
  5. re342

    re342 Neuer Benutzer

    Vielen Dank für die Antworten! Ihr habt mir sehr geholfen! Diese SQL-Funktion kannte ich noch nicht.
     
  6. akretschmer

    akretschmer Datenbank-Guru

    you are welcome. PostgreSQL ist immer wieder voller Überraschungen ;-)

    Im Ernst:

    • man kann mit Schleifen etc. arbeiten. Ist aber fast immer der falsche Weg
    • PostgreSQL ist eine sehr, ähm, lebendige Datenbank. Mit einer coolen Community. Und versucht, Dinge zuerst einmal richtig zu machen. Und dann schnell. Manchmal erscheint PostgreSQL (in der Entwicklung) daher träge oder altbacken. Das trügt. Die Geschwindigkeit, mit der neue Features aufgenommen werden, hat sich die letzten Jahre signifikant verbessert. Das liegt aber auch daran, daß mittlerweile (hinter den Kulissen) viele Entwicklungen von Firmen finanziert werden - und der Community zugute kommen.
    • es ist sinnvoll, sich mit den ganzen Features zu beschäftigen, die PG bietet. Das sind sehr viele! Von Version zu Version mehr.
    • Du solltest mal Community-Veranstaltungen besuchen. Z.B. nächste Woche in Lissabon (pgconf.eu) oder nächstes Jahr im Mai in Leipzig (pgconf.de)

    tl;dr

    Willkommen bei PostgreSQL!
     
  7. Dravion

    Dravion Benutzer

    Solche Kontrollkonstrukte lassen sich ganz gut mit PL/pgSQL abbilden.

    // PL/pgSQL while loop - example
    CREATE OR REPLACE FUNCTION fibonacci (n INTEGER)
    RETURNS INTEGER AS $$
    DECLARE
    counter INTEGER := 0 ;
    i INTEGER := 0 ;
    j INTEGER := 1 ;
    BEGIN

    IF (n < 1) THEN
    RETURN 0 ;
    END IF;

    LOOP
    EXIT WHEN counter = n ;
    counter := counter + 1 ;
    SELECT j, i + j INTO i, j ;
    END LOOP ;

    RETURN i ;
    END ;
    $$ LANGUAGE plpgsql;


    #Output:
    test=# SELECT * FROM fibonacci(12);
    fibonacci
    -----------
    144
    (1 row)
     
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