Lernen Sie diese schnellen Tricks in PostgreSQL

PostgreSQL ist einer der beliebtesten Open-Source-SQL-Dialekte. Einer der Hauptvorteile ist die Möglichkeit, die Funktionalität mit einigen integrierten Tools zu erweitern.

Schauen wir uns hier einige PostgreSQL-Tricks an, mit denen Sie beginnen können, Ihre SQL-Kenntnisse auf die nächste Stufe zu heben.

Sie erfahren, wie Sie:

  • Kopieren Sie schnell Dateien in eine Datenbank
  • Fassen Sie Daten im Kreuztabellenformat zusammen
  • Nutzen Sie Arrays und JSON-Daten in SQL
  • Arbeiten Sie mit geometrischen Daten
  • Führen Sie statistische Analysen direkt in Ihrer Datenbank durch
  • Verwenden Sie die Rekursion, um Probleme zu lösen

Kopieren Sie Daten aus einer Datei

Eine einfache Möglichkeit, Daten schnell aus einer externen Datei zu importieren, ist die Verwendung der Funktion KOPIEREN. Erstellen Sie einfach die Tabelle, die Sie verwenden möchten, und übergeben Sie den Dateipfad Ihres Datasets an den Befehl COPY.

Im folgenden Beispiel wird eine Tabelle mit dem Namen "Umsatz" erstellt und aus einer zufällig generierten CSV-Datei gefüllt.

Sie können zusätzliche Parameter einfügen, um den Dateityp anzugeben (hier ist die Datei eine CSV) und ob die erste Zeile als Spaltenüberschriften gelesen werden soll.

Hier können Sie mehr erfahren.

CREATE TABLE revenue ( store VARCHAR, year INT, revenue INT, PRIMARY KEY (product, year) ); COPY revenue FROM '~/Projects/datasets/revenue.csv' WITH HEADER CSV;

Fassen Sie Daten mit der Kreuztabellenfunktion zusammen

Wenn Sie sich als Tabellenkalkulationsprofi vorstellen, sind Sie wahrscheinlich mit dem Erstellen von Pivot-Tabellen aus Datenabbildern vertraut. Sie können dasselbe in PostgreSQL mit der Kreuztabellenfunktion tun.

Die Kreuztabellenfunktion kann Daten im Formular links aufnehmen und im Formular rechts zusammenfassen (was viel einfacher zu lesen ist). Das Beispiel hier folgt mit den Umsatzdaten von zuvor.

Aktivieren Sie zunächst die tablefunc-Erweiterung mit dem folgenden Befehl:

CREATE EXTENSION tablefunc;

Schreiben Sie als Nächstes eine Abfrage mit der Kreuztabellenfunktion:

SELECT * FROM CROSSTAB( 'SELECT * FROM revenue ORDER BY 1,2' ) AS summary( store VARCHAR, "2016" INT, "2017" INT, "2018" INT );

Bei Verwendung dieser Funktion sind zwei Dinge zu beachten.

  • Übergeben Sie zunächst eine Abfrage, in der Sie Daten aus Ihrer zugrunde liegenden Tabelle auswählen. Sie können die Tabelle einfach so auswählen, wie sie ist (wie hier gezeigt). Möglicherweise möchten Sie jedoch bei Bedarf filtern, verknüpfen oder aggregieren. Stellen Sie sicher, dass Sie die Daten korrekt bestellen.
  • Definieren Sie dann die Ausgabe (im Beispiel heißt die Ausgabe "Zusammenfassung", Sie können sie jedoch auch beliebig nennen). Listen Sie die Spaltenüberschriften auf, die Sie verwenden möchten, und den Datentyp, den sie enthalten sollen.

Die Ausgabe erfolgt wie folgt:

 store | 2016 | 2017 | 2018 ---------+---------+---------+--------- Alpha | 1637000 | 2190000 | 3287000 Bravo | 2205000 | 982000 | 3399000 Charlie | 1549000 | 1117000 | 1399000 Delta | 664000 | 2065000 | 2931000 Echo | 1795000 | 2706000 | 1047000 (5 rows)

Arbeiten Sie mit Arrays und JSON

PostgreSQL unterstützt mehrdimensionale Array-Datentypen. Diese sind vergleichbar mit ähnlichen Datentypen in vielen anderen Sprachen, einschließlich Python und JavaScript.

Möglicherweise möchten Sie sie in Situationen verwenden, in denen es hilfreich ist, mit dynamischeren, weniger strukturierten Daten zu arbeiten.

Stellen Sie sich beispielsweise eine Tabelle vor, in der veröffentlichte Artikel und Betreff-Tags beschrieben werden. Ein Artikel könnte keine Tags haben, oder es könnte viele haben. Der Versuch, diese Daten in einem strukturierten Tabellenformat zu speichern, wäre unnötig kompliziert.

Sie können Arrays mithilfe eines Datentyps definieren, gefolgt von eckigen Klammern. Sie können optional ihre Abmessungen angeben (dies wird jedoch nicht erzwungen).

Um beispielsweise ein 1-D-Array mit einer beliebigen Anzahl von Textelementen zu erstellen, würden Sie verwenden text[]. Um ein dreidimensionales dreidimensionales Array von ganzzahligen Elementen zu erstellen, würden Sie verwenden int[3][3].

Schauen Sie sich das folgende Beispiel an:

CREATE TABLE articles ( title VARCHAR PRIMARY KEY, tags TEXT[] );

Verwenden Sie die Syntax, um Arrays als Datensätze einzufügen '{"first","second","third"}'.

INSERT INTO articles (title, tags) VALUES ('Lorem ipsum', '{"random"}'), ('Placeholder here', '{"motivation","random"}'), ('Postgresql tricks', '{"data","self-reference"}');

Es gibt viele Dinge, die Sie mit Arrays in PostgreSQL tun können.

Zunächst können Sie überprüfen, ob ein Array ein bestimmtes Element enthält. Dies ist nützlich zum Filtern. Sie können dazu den Operator "enthält" @>verwenden. Die folgende Abfrage findet alle Artikel, die das Tag "zufällig" haben.

SELECT * FROM articles WHERE tags @> '{"random"}';

Sie können Arrays auch mit dem ||Operator verketten (zusammenfügen) oder mit dem &&Operator auf überlappende Elemente prüfen .

Sie können Arrays nach Index durchsuchen (im Gegensatz zu vielen anderen Sprachen zählen PostgreSQL-Arrays von eins statt von null).

SELECT tags[1] FROM articles;

Mit PostgreSQL können Sie neben Arrays auch JSON als Datentyp verwenden. Dies bietet wiederum die Vorteile der Arbeit mit unstrukturierten Daten. Sie können auf Elemente auch über ihren Schlüsselnamen zugreifen.

CREATE TABLE sessions ( session_id SERIAL PRIMARY KEY, session_info JSON ); INSERT INTO sessions (session_info) VALUES ('{"app_version": 1.0, "device_type": "Android"}'), ('{"app_version": 1.2, "device_type": "iOS"}'), ('{"app_version": 1.4, "device_type": "iOS", "mode":"default"}');

Auch hier gibt es viele Dinge, die Sie mit JSON-Daten in PostgreSQL tun können. Mit den Operatoren ->und ->>können Sie die JSON-Objekte "entpacken", die in Abfragen verwendet werden sollen.

Diese Abfrage findet beispielsweise die Werte des device_typeSchlüssels:

SELECT session_info -> 'device_type' AS devices FROM sessions;

Und diese Abfrage zählt, wie viele Sitzungen in der App-Version 1.0 oder früher waren:

SELECT COUNT(*) FROM sessions WHERE CAST(session_info ->> 'app_version' AS decimal) <= 1.0;

Führen Sie statistische Analysen durch

SQL wird häufig als gut zum Speichern von Daten und zum Ausführen einfacher Abfragen angesehen, nicht jedoch zum Ausführen eingehenderer Analysen. Verwenden Sie dazu ein anderes Tool wie Python oder R oder Ihre bevorzugte Tabellenkalkulationssoftware.

PostgreSQL bietet jedoch genügend statistische Funktionen, um Ihnen den Einstieg zu erleichtern.

Beispielsweise können zusammenfassende Statistiken, Korrelationen, Regressionen und Zufallsstichproben berechnet werden. Die folgende Tabelle enthält einige einfache Daten, mit denen Sie herumspielen können.

CREATE TABLE stats ( sample_id SERIAL PRIMARY KEY, x INT, y INT ); INSERT INTO stats (x,y) VALUES (1,2), (3,4), (6,5), (7,8), (9,10);

Sie können den Mittelwert, die Varianz und die Standardabweichung mit den folgenden Funktionen ermitteln:

SELECT AVG(x), VARIANCE(x), STDDEV(x) FROM stats;

Sie können den Median (oder einen anderen Perzentilwert) auch mit der Funktion perzentile_cont ermitteln:

-- median SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) FROM stats; -- 90th percentile SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY x) FROM stats;

Mit einem anderen Trick können Sie die Korrelationskoeffizienten zwischen verschiedenen Spalten berechnen. Verwenden Sie einfach die Corr-Funktion.

SELECT CORR(x,y) FROM stats;

Mit PostgreSQL können Sie eine lineare Regression (manchmal als die grundlegendste Form des maschinellen Lernens bezeichnet) über eine Reihe integrierter Funktionen ausführen.

SELECT REGR_INTERCEPT(x,y), REGR_SLOP(x,y), REGR_R2(x,y) FROM stats;

Sie können sogar Monte-Carlo-Simulationen mit einzelnen Abfragen ausführen. Die folgende Abfrage verwendet die Funktionen generate_series und random number, um den Wert von π durch zufälliges Abtasten von einer Million Punkten innerhalb eines Einheitskreises zu schätzen.

SELECT CAST( COUNT(*) * 4 AS FLOAT ) / 1000000 AS pi FROM GENERATE_SERIES(1,1000000) WHERE CIRCLE(POINT(0.5,0.5),0.5) @> POINT(RANDOM(), RANDOM());

Arbeiten Sie mit Formdaten

Ein weiterer ungewöhnlicher Datentyp, der in PostgreSQL verfügbar ist, sind geometrische Daten.

Richtig, Sie können mit Punkten, Linien, Polygonen und Kreisen in SQL arbeiten.

Points are the basic building block for all geometric data types in PostgreSQL. They are represented as (x, y) coordinates.

SELECT POINT(0,0) AS "origin", POINT(1,1) AS "point";

You can also define lines. These can either be infinite lines (specified by giving any two points on the line). Or, they can be line segments (specified by giving the 'start' and 'end' points of the line).

SELECT LINE '((0,0),(1,1))' AS "line", LSEG '((2,2),(3,3))' AS "line_segment";

Polygons are defined by a longer series of points.

SELECT POLYGON '((0,0),(1,1),(0,2))' AS "triangle", POLYGON '((0,0),(0,1),(1,1),(1,0))' AS "square", POLYGON '((0,0),(0,1),(2,1),(2,0))' AS "rectangle";

Circles are defined by a central point and a radius.

SELECT CIRCLE '((0,0),1)' as "small_circle", CIRCLE '(0,0),5)' as "big_circle";

There are many functions and operators that can be applied to geometric data in PostgreSQL.

You can:

  • Check if two lines are parallel with the ?|| operator:
SELECT LINE '((0,0),(1,1))' ?|| LINE '((2,3),(3,4))';
  • Find the distance between two objects with the operator:
SELECT POINT(0,0)  POINT(1,1);
  • Check if two shapes overlap at any point with the && operator:
SELECT CIRCLE '((0,0),1)' && CIRCLE '((1,1),1)';
  • Translate (shift position) a shape using the + operator:
SELECT POLYGON '((0,0),(1,2),(1,1))' + POINT(0,3);

And lots more besides - check out the documentation for more detail!

Use recursive queries

Recursion is a programming technique that can be used to solve problems using a function which calls itself. Did you know that you can write recursive queries in PostgreSQL?

There are three parts required to do this:

  • First, you define a starting expression.
  • Then, define a recursive expression that will be evaluated repeatedly
  • Finally, define a "termination criteria" - a condition which tells the function to stop calling itself, and return an output.

The query below returns the first hundred numbers in the Fibonacci sequence:

WITH RECURSIVE fibonacci(n,x,y) AS ( SELECT 1 AS n , 0 :: NUMERIC AS x, 1 :: NUMERIC AS y UNION ALL SELECT n + 1 AS n, y AS x, x + y AS y FROM fibonacci WHERE n < 100 ) SELECT x FROM fibonacci;

Let's break this down.

First, it uses the WITH clause to define a (recursive) Common Table Expression called fibonacci. Then, it defines an initial expression:

WITH RECURSIVE fibonacci(n,x,y) AS ( SELECT 1 AS n , 0 :: NUMERIC AS x, 1 :: NUMERIC AS y...

Next, it defines the recursive expression that queries fibonacci:

 ...UNION ALL SELECT n + 1 AS n, y AS x, x + y AS y FROM fibonacci...

Finally, it uses a WHERE clause to define the termination criteria, and then selects column x to give the output sequence:

...WHERE n < 100 ) SELECT x FROM fibonacci;

Perhaps you can think of another example of recursion that could be implemented in PostgreSQL?

Final remarks

So, there you have it - a quick run through of some great features you may or may not have known PostgreSQL could provide. There are no doubt more features worth covering that didn't make it into this list.

PostgreSQL is a rich and powerful programming language in its own right. So, next time you are stuck figuring out how to solve a data related problem, take a look and see if PostgreSQL has you covered. You might surprised how often it does!

Thanks for reading!