PostgreSQL: Eigene Aggregate schreiben

Eigene Aggregatsfunktionen schreiben

Hans-Jürgen Schönig

www.postgresql-support.de

Hans-Jürgen Schönigwww.postgresql-support.de

Überblick


Worum es gehen wird

I Wie funktionieren PostgreSQL Aggregates?I Wie kann man einfache Aggregates schreiben?I Wie können Windowing Functions optimiert werden?I Wie schreibt man Hypothetical Aggregates?


Ein einfaches Aggregate

I Ein Aggregate ist “etwas anders”I Eine “normale” Funktion gibt eine Zelle pro Aufruf retourI Ein Aggregate gibt eine Zeile pro Gruppe zurück


Ein einfache Beispiele:

I “Normale” Funktionen geben eine Zeile pro Aufruf zurück:

SELECT md5(x::text) FROM generate_series(1, 3) AS x;md5

----------------------------------c4ca4238a0b923820dcc509a6f75849bc81e728d9d4c2f636f067f89cc14862ceccbc87e4b5ce2fe28308fd9f2a7baf3

(3 rows)


Einfache Beispiele:

I Ein Aggregate macht aus vielen Zeilen eine Zeile:

SELECT sum(x) FROM generate_series(1, 3) AS x;sum

-----6

(1 row)


Eigene Aggregates schreiben

CREATE AGGREGATE name ( [ argmode ] [ argname ]arg_data_type [ , ... ] ) (SFUNC = sfunc, STYPE = state_data_type[ , SSPACE = state_data_size ][ , FINALFUNC = ffunc ] [ , FINALFUNC_EXTRA ][ , INITCOND = initial_condition ][ , MSFUNC = msfunc ] [ , MINVFUNC = minvfunc ][ , MSTYPE = mstate_data_type ][ , MSSPACE = mstate_data_size ][ , MFINALFUNC = mffunc ] [ , MFINALFUNC_EXTRA ][ , MINITCOND = minitial_condition ][ , SORTOP = sort_operator ]

)Hans-Jürgen Schönigwww.postgresql-support.de

Unser erstes Ziel

I Wir beginnen mit einfachen StringoperationenI Damit lässt sich der Workflow einfach verdeutlichenI Komplexere Prozesse laufen nach dem selben Schema


Eine einfache Funktion

CREATE FUNCTION xagg(text, text) RETURNS text AS$$

BEGINRAISE NOTICE 'xagg: "%" - adding %', $1, $2;RETURN $1 || $2;

END;$$ LANGUAGE 'plpgsql';


Daraus kann man ein Aggregate machen

I xagg wird für jede Zeile aufgerufen

CREATE AGGREGATE simpleagg(text) (INITCOND = '',SFUNC = xagg,STYPE = text

);


Ein Versuch

SELECT simpleagg(x::text)FROM generate_series(1, 4) AS x;

NOTICE: xagg: "" - adding 1NOTICE: xagg: "1" - adding 2NOTICE: xagg: "12" - adding 3NOTICE: xagg: "123" - adding 4simpleagg-----------1234

(1 row)


Kompliziertere Berechnungen

I Zeilenoperationen reichen oft nicht ausI Um Berechnungen abzuschließen, kann eine FINALFUNC

definiert werden.I Beispiel: avg dividiert am Ende die Summe durch die Anzahl


Eine einfache FINALFUNC

CREATE FUNCTION sample_final(text) RETURNS text AS$$

SELECT $1 || 'X';$$ LANGUAGE 'sql';

CREATE AGGREGATE simpleagg(text) (INITCOND = '',SFUNC = xagg,FINALFUNC = sample_final,STYPE = text

);


Die FINALFUNC in Action

test=# SELECT simpleagg(x::text)FROM generate_series(1, 4) AS x;

NOTICE: xagg: "" - adding 1NOTICE: xagg: "1" - adding 2NOTICE: xagg: "12" - adding 3NOTICE: xagg: "123" - adding 4simpleagg

-----------1234X

(1 row)


Windowing und Analytics


Aggregate und Analytics

I Alle Aggregate können auch als Windows verwendet werdenI Effizienz kann bei komplexeren Frame Clauses ein Thema

werden

SELECT ...OVER (ORDER BY ...

ROWS BETWEEN x PREDECING AND y FOLLOWING) ...


Ein Beispiel für schlechte Performance (1):

test=# SELECT x, simpleagg(x::text)OVER (ORDER BY x ROWS BETWEEN

3 PRECEDING AND 0 FOLLOWING )FROM generate_series(1, 6) AS x;

NOTICE: xagg: "" - adding 1NOTICE: xagg: "1" - adding 2NOTICE: xagg: "12" - adding 3NOTICE: xagg: "123" - adding 4



NOTICE: xagg: "" - adding 2NOTICE: xagg: "2" - adding 3NOTICE: xagg: "23" - adding 4NOTICE: xagg: "234" - adding 5NOTICE: xagg: "" - adding 3NOTICE: xagg: "3" - adding 4NOTICE: xagg: "34" - adding 5NOTICE: xagg: "345" - adding 6



x | simpleagg---+-----------1 | 1X2 | 12X3 | 123X4 | 1234X5 | 2345X6 | 3456X

(6 rows)

=> Die Anzahl der Funktionsaufrufe ist explodiert


Funktionsaufrufe reduzieren

I Das Ziel ist, die Anzahl der Funktionsaufrufe zu reduzieren.I Transition Functions helfen in diesem Fall.I Transition Functions sind seit PostgreSQL 9.4 möglich


Wie das funktioniert

I Wir benötigen eine MSFUNC und eine MINVFUNC

CREATE FUNCTION x_msfunc(text, text) RETURNS text AS $$BEGIN

RAISE NOTICE 'x_msfunc: % / %', $1, $2;RETURN $1 || $2;



Der zweite Teil des Aggregates

CREATE FUNCTION x_minvfunc(text, text) RETURNS text AS $$BEGIN

RAISE NOTICE 'x_minvfunc: previous % / removing %',$1, $2;

RETURN substring($1, 2, length($1) - 1);END;$$ LANGUAGE 'plpgsql';


Das Aggregat definieren

CREATE AGGREGATE myagg(text) (stype = text,sfunc = xagg,initcond = '',msfunc = x_msfunc,minvfunc = x_minvfunc,mstype = text,MINITCOND = ''

);

I Die Logik ist sehr ähnlich wie vorherI Eine der Funktionen wird für jede Zeile aufgerufen.I Ein optionaler Funktionsaufruf passiert am Ende.


Eine Demo-Abfrage

SELECT x, myagg(x::text) OVER (PARTITION BY x % 2 ORDER BY x::textROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)

FROM generate_series(1, 9) AS xORDER BY x;


Das Ergebnis sieht so aus

x | myagg---+-------1 | 1352 | 2463 | 3574 | 4685 | 5796 | 687 | 798 | 89 | 9

(9 rows)


Hinter den Kulissen passiert (1)

NOTICE: x_msfunc: / 2NOTICE: x_msfunc: 2 / 4NOTICE: x_msfunc: 24 / 6NOTICE: x_minvfunc: previous 246 / removing 2NOTICE: x_msfunc: 46 / 8NOTICE: x_minvfunc: previous 468 / removing 4NOTICE: x_minvfunc: previous 68 / removing 6NOTICE: x_msfunc: / 1NOTICE: x_msfunc: 1 / 3NOTICE: x_msfunc: 13 / 5


Was hinter den Kulissen passiert (2)

NOTICE: x_minvfunc: previous 135 / removing 1NOTICE: x_msfunc: 35 / 7NOTICE: x_minvfunc: previous 357 / removing 3NOTICE: x_msfunc: 57 / 9NOTICE: x_minvfunc: previous 579 / removing 5NOTICE: x_minvfunc: previous 79 / removing 7


Hypothetical Aggregates


Was sind Hypothetical Aggregates?

I Hypothetical Aggregates beantworten die “what if” FrageI Wie wäre das Ergebnis, würde ein bestimmter Wert existieren?I Ein klassisches Beispiel:

test=# SELECT x % 2 AS odd_even,rank(5) WITHIN GROUP (ORDER BY x)

FROM generate_series(1, 10) AS xGROUP BY 1;

odd_even | rank----------+------

0 | 31 | 3

(2 rows)


Die Syntax für Ordered Sets

CREATE AGGREGATE name ( [ [ argmode ] [ argname ]arg_data_type [ , ... ] ]

ORDER BY [ argmode ] [ argname ]arg_data_type [ , ... ] ) (

SFUNC = sfunc,STYPE = state_data_type[ , SSPACE = state_data_size ][ , FINALFUNC = ffunc ][ , FINALFUNC_EXTRA ][ , INITCOND = initial_condition ][ , HYPOTHETICAL ]

)


Weitere Funktionen werden benötigt

I Wieder benötigen wir eine SFUNC:

CREATE FUNCTION hypo_sfunc(text, text) RETURNS text AS$$

BEGINRAISE NOTICE 'hypo_sfunc: % / %', $1, $2;RETURN $1 || $2;



Eine FINALFUNC

I Eine Funktion wird wieder am Ende aufgerufen

CREATE FUNCTION hypo_final(text, text, text)RETURNS text AS

$$BEGIN

RAISE NOTICE 'hypo_final: % / % / %', $1, $2, $3;RETURN $1 || $2;



Die Definition des Aggregates

CREATE AGGREGATE whatif(text ORDER BY text) (INITCOND = '',STYPE = text,SFUNC = hypo_sfunc,FINALFUNC = hypo_final,FINALFUNC_EXTRA = true,HYPOTHETICAL

);


Ein Beispiel

SELECT (x % 2)::text, whatif('abc')WITHIN GROUP (ORDER BY x::text )

FROM generate_series(1, 4) AS xGROUP BY 1;


Das Ergebnis

NOTICE: hypo_sfunc: / 2NOTICE: hypo_sfunc: 2 / 4NOTICE: hypo_final: 24 / abc / <NULL>NOTICE: hypo_sfunc: / 1NOTICE: hypo_sfunc: 1 / 3NOTICE: hypo_final: 13 / abc / <NULL>text | whatif

------+--------0 | 24abc1 | 13abc

(2 rows)


Finally . . .


Gibt es Fragen?

Cybertec Schönig & Schönig GmbHGröhrmühlgasse 26A-2700 Wiener Neustadt, Austria

www.postgresql-support.deEmail: [email protected]


Data & Analytics

PostgreSQL: Eigene Aggregate schreiben