Variablen und Schleifen

[re342]

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:

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

 

[castorp]

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:

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

 

[akretschmer]

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.

 

[akretschmer]

Um es mal im Beispiel zu zeigen:

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]

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

 

[akretschmer]

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!

 

[Dravion]

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)