Μάθετε αυτά τα γρήγορα κόλπα στο PostgreSQL

Η PostgreSQL είναι μια από τις πιο δημοφιλείς διαλέκτους SQL ανοιχτού κώδικα. Ένα από τα κύρια πλεονεκτήματά του είναι η δυνατότητα επέκτασης της λειτουργικότητάς του με ορισμένα ενσωματωμένα εργαλεία.

Εδώ, ας δούμε μερικά κόλπα PostgreSQL που μπορείτε να αρχίσετε να χρησιμοποιείτε για να μεταφέρετε τις δεξιότητές σας SQL στο επόμενο επίπεδο.

Θα μάθετε πώς:

  • Αντιγράψτε γρήγορα αρχεία σε μια βάση δεδομένων
  • Συνοψίστε τα δεδομένα σε μορφή διασταύρωσης
  • Επωφεληθείτε από πίνακες και δεδομένα JSON στο SQL
  • Εργαστείτε με γεωμετρικά δεδομένα
  • Εκτελέστε στατιστικές αναλύσεις απευθείας στη βάση δεδομένων σας
  • Χρησιμοποιήστε την αναδρομή για την επίλυση προβλημάτων

Αντιγραφή δεδομένων από ένα αρχείο

Ένας εύκολος τρόπος γρήγορης εισαγωγής δεδομένων από εξωτερικό αρχείο είναι να χρησιμοποιήσετε τη συνάρτηση COPY. Απλώς δημιουργήστε τον πίνακα που θέλετε να χρησιμοποιήσετε και, στη συνέχεια, μεταφέρετε το αρχείο διαδρομής του συνόλου δεδομένων σας στην εντολή COPY.

Το παρακάτω παράδειγμα δημιουργεί έναν πίνακα που ονομάζεται έσοδα και τον συμπληρώνει από ένα αρχείο CSV που δημιουργήθηκε τυχαία.

Μπορείτε να συμπεριλάβετε επιπλέον παραμέτρους, για να υποδείξετε τον τύπο αρχείου (εδώ, το αρχείο είναι CSV) και αν θα διαβάσετε την πρώτη σειρά ως κεφαλίδες στήλης.

Μπορείτε να μάθετε περισσότερα εδώ.

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

Συνοψίστε τα δεδομένα χρησιμοποιώντας τη συνάρτηση crosstab

Εάν θέλετε να είστε επαγγελματίας υπολογιστικού φύλλου, πιθανότατα θα εξοικειωθείτε με τη δημιουργία συγκεντρωτικών πινάκων από απορρίψεις δεδομένων. Μπορείτε να κάνετε το ίδιο στο PostgreSQL με τη συνάρτηση crosstab.

Η συνάρτηση crosstab μπορεί να λάβει δεδομένα με τη μορφή στα αριστερά και να τα συνοψίσει στη φόρμα στα δεξιά (που είναι πολύ πιο εύκολο να διαβαστεί). Το παράδειγμα εδώ θα ακολουθήσει με τα δεδομένα εσόδων από πριν.

Αρχικά, ενεργοποιήστε την επέκταση tablefunc με την παρακάτω εντολή:

CREATE EXTENSION tablefunc;

Στη συνέχεια, γράψτε ένα ερώτημα χρησιμοποιώντας τη συνάρτηση crosstab:

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

Υπάρχουν δύο πράγματα που πρέπει να λάβετε υπόψη κατά τη χρήση αυτής της λειτουργίας.

  • Αρχικά, περάστε ένα ερώτημα επιλέγοντας δεδομένα από τον υποκείμενο πίνακα σας. Μπορείτε απλά να επιλέξετε τον πίνακα ως έχει (όπως φαίνεται εδώ). Ωστόσο, μπορεί να θέλετε να φιλτράρετε, να συμμετάσχετε ή να συγκεντρώσετε εάν απαιτείται Φροντίστε να παραγγείλετε σωστά τα δεδομένα.
  • Στη συνέχεια, ορίστε την έξοδο (στο παράδειγμα, η έξοδος ονομάζεται «περίληψη», αλλά μπορείτε να την ονομάσετε οποιοδήποτε όνομα). Αναφέρετε τις κεφαλίδες στηλών που θέλετε να χρησιμοποιήσετε και τον τύπο δεδομένων που θα περιέχουν.

Η έξοδος θα είναι όπως φαίνεται παρακάτω:

 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)

Εργαστείτε με πίνακες και JSON

Η PostgreSQL υποστηρίζει πολυδιάστατους τύπους δεδομένων πίνακα. Αυτά είναι συγκρίσιμα με παρόμοιους τύπους δεδομένων σε πολλές άλλες γλώσσες, συμπεριλαμβανομένων των Python και JavaScript.

Ίσως θελήσετε να τα χρησιμοποιήσετε σε καταστάσεις όπου βοηθά στην εργασία με πιο δυναμικά, λιγότερο δομημένα δεδομένα.

Για παράδειγμα, φανταστείτε έναν πίνακα που περιγράφει δημοσιευμένα άρθρα και ετικέτες θέματος. Ένα άρθρο δεν θα μπορούσε να έχει ετικέτες ή θα μπορούσε να έχει πολλές. Η προσπάθεια αποθήκευσης αυτών των δεδομένων σε μορφή δομημένου πίνακα θα ήταν περιττή περίπλοκη.

Μπορείτε να ορίσετε πίνακες χρησιμοποιώντας έναν τύπο δεδομένων, ακολουθούμενο από αγκύλες. Μπορείτε προαιρετικά να καθορίσετε τις διαστάσεις τους (ωστόσο, αυτό δεν ισχύει).

Για παράδειγμα, για να δημιουργήσετε έναν πίνακα 1-D οποιουδήποτε αριθμού στοιχείων κειμένου, θα χρησιμοποιούσατε text[]. Για να δημιουργήσετε μια τρισδιάστατη δισδιάστατη συστοιχία ακέραιων στοιχείων, θα χρησιμοποιούσατε int[3][3].

Ρίξτε μια ματιά στο παρακάτω παράδειγμα:

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

Για να εισαγάγετε πίνακες ως εγγραφές, χρησιμοποιήστε τη σύνταξη '{"first","second","third"}'.

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

Υπάρχουν πολλά πράγματα που μπορείτε να κάνετε με πίνακες στο PostgreSQL.

Για αρχή, μπορείτε να ελέγξετε αν ένας πίνακας περιέχει ένα δεδομένο στοιχείο. Αυτό είναι χρήσιμο για το φιλτράρισμα. Μπορείτε να χρησιμοποιήσετε τον τελεστή "περιέχει" @>για να το κάνετε αυτό. Το παρακάτω ερώτημα βρίσκει όλα τα άρθρα που έχουν την ετικέτα "τυχαία".

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

Μπορείτε επίσης να συνδυάσετε (ενώστε μαζί) πίνακες χρησιμοποιώντας το ||χειριστή ή να ελέγξετε για αλληλεπικαλυπτόμενα στοιχεία με το &&χειριστή.

Μπορείτε να αναζητήσετε πίνακες κατά ευρετήριο (σε αντίθεση με πολλές γλώσσες, οι πίνακες PostgreSQL αρχίζουν να μετρούν από μία, αντί για μηδέν).

SELECT tags[1] FROM articles;

Όπως και οι πίνακες, το PostgreSQL σάς επιτρέπει επίσης να χρησιμοποιήσετε το JSON ως τύπο δεδομένων. Και πάλι, αυτό παρέχει τα πλεονεκτήματα της εργασίας με μη δομημένα δεδομένα. Μπορείτε επίσης να αποκτήσετε πρόσβαση σε στοιχεία με το βασικό τους όνομα.

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"}');

Και πάλι, υπάρχουν πολλά πράγματα που μπορείτε να κάνετε με τα δεδομένα JSON στο PostgreSQL. Μπορείτε να χρησιμοποιήσετε τους τελεστές ->και ->>να "αποσυσκευάσετε" τα αντικείμενα JSON για χρήση σε ερωτήματα.

Για παράδειγμα, αυτό το ερώτημα βρίσκει τις τιμές του device_typeκλειδιού:

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

Και αυτό το ερώτημα μετράει πόσες περιόδους σύνδεσης ήταν στην έκδοση 1.0 ή παλαιότερη εφαρμογή:

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

Εκτελέστε στατιστικές αναλύσεις

Συχνά, οι άνθρωποι βλέπουν τη SQL ως καλή για την αποθήκευση δεδομένων και την εκτέλεση απλών ερωτημάτων, αλλά όχι για την εκτέλεση πιο αναλυτικών αναλύσεων. Για αυτό, πρέπει να χρησιμοποιήσετε ένα άλλο εργαλείο όπως το Python ή το R ή το αγαπημένο σας λογισμικό υπολογιστικού φύλλου.

Ωστόσο, η PostgreSQL διαθέτει αρκετές στατιστικές δυνατότητες για να ξεκινήσετε.

Για παράδειγμα, μπορεί να υπολογίσει συνοπτικά στατιστικά στοιχεία, συσχέτιση, παλινδρόμηση και τυχαία δειγματοληψία. Ο παρακάτω πίνακας περιέχει μερικά απλά δεδομένα για να παίξετε.

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);

You can find the mean, variance and standard deviation using the functions below:

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

You can also find the median (or any other percentile value) using the percentile_cont function:

-- 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;

Another trick lets you calculate the correlation coefficients between different columns. Simply use the corr function.

SELECT CORR(x,y) FROM stats;

PostgreSQL lets you run linear regression (sometimes called the most basic form of machine learning) via a set of inbuilt functions.

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

You can even run Monte Carlo simulations with single queries. The query below uses the generate_series and random number functions to estimate the value of π by randomly sampling one million points inside a unit circle.

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());

Work with shape data

Another unusual data type available in PostgreSQL is geometric data.

That's right, you can work with points, lines, polygons and circles within SQL.

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...

Στη συνέχεια, ορίζει την αναδρομική έκφραση που υποβάλλει ερωτήματα fibonacci:

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

Τέλος, χρησιμοποιεί μια ρήτρα WHERE για να καθορίσει τα κριτήρια τερματισμού και, στη συνέχεια, επιλέγει τη στήλη x για να δώσει την ακολουθία εξόδου:

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

Ίσως μπορείτε να σκεφτείτε ένα άλλο παράδειγμα αναδρομής που θα μπορούσε να εφαρμοστεί στο PostgreSQL;

Τελικές παρατηρήσεις

Λοιπόν, το έχετε - μια γρήγορη διερεύνηση ορισμένων εξαιρετικών δυνατοτήτων που ίσως ή δεν γνωρίζετε ότι θα μπορούσε να προσφέρει η PostgreSQL. Δεν υπάρχει αμφιβολία ότι αξίζει να καλύψετε περισσότερα χαρακτηριστικά που δεν τα κατατάχθηκαν σε αυτήν τη λίστα.

Η PostgreSQL είναι μια πλούσια και ισχυρή γλώσσα προγραμματισμού από μόνη της. Έτσι, την επόμενη φορά που θα κολλήσετε να καταλάβετε πώς να λύσετε ένα πρόβλημα που σχετίζεται με τα δεδομένα, ρίξτε μια ματιά και δείτε εάν έχετε καλύψει το PostgreSQL. Μπορεί να εκπλαγείτε πόσο συχνά συμβαίνει!

Ευχαριστώ για την ανάγνωση!