Apprenez ces astuces rapides dans PostgreSQL

PostgreSQL est l'un des dialectes SQL open source les plus populaires. L'un de ses principaux avantages est la possibilité d'étendre ses fonctionnalités avec certains outils intégrés.

Voyons ici quelques astuces PostgreSQL que vous pouvez commencer à utiliser pour faire passer vos compétences SQL au niveau supérieur.

Vous découvrirez comment:

  • Copiez rapidement des fichiers dans une base de données
  • Synthétiser les données au format tableau croisé
  • Tirez parti des tableaux et des données JSON dans SQL
  • Travailler avec des données géométriques
  • Exécutez des analyses statistiques directement sur votre base de données
  • Utilisez la récursivité pour résoudre les problèmes

Copier les données d'un fichier

Un moyen simple d'importer rapidement des données à partir d'un fichier externe consiste à utiliser la fonction COPY. Créez simplement la table que vous souhaitez utiliser, puis passez le chemin du fichier de votre jeu de données à la commande COPY.

L'exemple ci-dessous crée un tableau appelé revenu et le remplit à partir d'un fichier CSV généré de manière aléatoire.

Vous pouvez inclure des paramètres supplémentaires, pour indiquer le type de fichier (ici, le fichier est un CSV) et s'il faut lire la première ligne comme en-tête de colonne.

Vous pouvez en apprendre davantage ici.

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

Résumer les données à l'aide de la fonction de tableau croisé

Si vous vous imaginez en tant que professionnel de la feuille de calcul, vous serez probablement familiarisé avec la création de tableaux croisés dynamiques à partir de vidages de données. Vous pouvez faire la même chose dans PostgreSQL avec la fonction de tableau croisé.

La fonction de tableau croisé peut prendre des données dans le formulaire de gauche et les résumer dans le formulaire de droite (qui est beaucoup plus facile à lire). L'exemple ici suivra avec les données de revenus d'avant.

Tout d'abord, activez l'extension tablefunc avec la commande ci-dessous:

CREATE EXTENSION tablefunc;

Ensuite, écrivez une requête à l'aide de la fonction de tableau croisé:

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

Il y a deux choses à considérer lors de l'utilisation de cette fonction.

  • Commencez par transmettre une requête sélectionnant les données de votre table sous-jacente. Vous pouvez simplement sélectionner le tableau tel quel (comme indiqué ici). Cependant, vous souhaiterez peut-être filtrer, rejoindre ou agréger si nécessaire. Assurez-vous de commander correctement les données.
  • Ensuite, définissez la sortie (dans l'exemple, la sortie est appelée «résumé», mais vous pouvez l'appeler n'importe quel nom). Répertoriez les en-têtes de colonne que vous souhaitez utiliser et le type de données qu'ils contiendront.

La sortie sera comme indiqué ci-dessous:

 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)

Travailler avec des tableaux et JSON

PostgreSQL prend en charge les types de données de tableau multidimensionnel. Ceux-ci sont comparables à des types de données similaires dans de nombreux autres langages, y compris Python et JavaScript.

Vous voudrez peut-être les utiliser dans des situations où il est utile de travailler avec des données plus dynamiques et moins structurées.

Par exemple, imaginez un tableau décrivant les articles publiés et les balises de sujet. Un article peut ne pas comporter de balises ou en avoir plusieurs. Essayer de stocker ces données dans un format de tableau structuré serait inutilement compliqué.

Vous pouvez définir des tableaux à l'aide d'un type de données, suivi de crochets. Vous pouvez éventuellement spécifier leurs dimensions (cependant, cela n'est pas appliqué).

Par exemple, pour créer un tableau 1-D d'un nombre quelconque d'éléments de texte, vous utiliseriez text[]. Pour créer un tableau bidimensionnel trois par trois d'éléments entiers, vous utiliseriez int[3][3].

Jetez un œil à l'exemple ci-dessous:

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

Pour insérer des tableaux en tant qu'enregistrements, utilisez la syntaxe '{"first","second","third"}'.

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

Il y a beaucoup de choses que vous pouvez faire avec les tableaux dans PostgreSQL.

Pour commencer, vous pouvez vérifier si un tableau contient un élément donné. Ceci est utile pour le filtrage. Vous pouvez utiliser l'opérateur "contient" @>pour ce faire. La requête ci-dessous trouve tous les articles qui ont la balise "random".

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

Vous pouvez également concaténer (joindre) des tableaux à l'aide de l' ||opérateur ou vérifier les éléments qui se chevauchent avec l' &&opérateur.

Vous pouvez rechercher des tableaux par index (contrairement à de nombreux langages, les tableaux PostgreSQL commencent à compter à partir de un, au lieu de zéro).

SELECT tags[1] FROM articles;

En plus des tableaux, PostgreSQL vous permet également d'utiliser JSON comme type de données. Encore une fois, cela présente les avantages de travailler avec des données non structurées. Vous pouvez également accéder aux éléments par leur nom de clé.

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

Encore une fois, il y a beaucoup de choses que vous pouvez faire avec les données JSON dans PostgreSQL. Vous pouvez utiliser les opérateurs ->et ->>pour "décompresser" les objets JSON à utiliser dans les requêtes.

Par exemple, cette requête recherche les valeurs de la device_typeclé:

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

Et cette requête compte le nombre de sessions sur la version 1.0 ou antérieure de l'application:

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

Exécuter des analyses statistiques

Souvent, les gens considèrent que SQL est bon pour stocker des données et exécuter des requêtes simples, mais pas pour exécuter des analyses plus approfondies. Pour cela, vous devez utiliser un autre outil tel que Python ou R ou votre logiciel de tableur préféré.

Cependant, PostgreSQL apporte suffisamment de capacités statistiques pour vous aider à démarrer.

Par exemple, il peut calculer des statistiques récapitulatives, la corrélation, la régression et l'échantillonnage aléatoire. Le tableau ci-dessous contient quelques données simples avec lesquelles jouer.

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

Vous pouvez trouver la moyenne, la variance et l'écart type à l'aide des fonctions ci-dessous:

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

Vous pouvez également trouver la médiane (ou toute autre valeur de centile) à l'aide de la fonction percentile_cont:

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

Une autre astuce vous permet de calculer les coefficients de corrélation entre différentes colonnes. Utilisez simplement la fonction corr.

SELECT CORR(x,y) FROM stats;

PostgreSQL vous permet d'exécuter une régression linéaire (parfois appelée la forme la plus élémentaire d'apprentissage automatique) via un ensemble de fonctions intégrées.

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

Vous pouvez même exécuter des simulations Monte Carlo avec des requêtes uniques. La requête ci-dessous utilise les fonctions generate_series et nombre aléatoire pour estimer la valeur de π en échantillonnant au hasard un million de points à l'intérieur d'un cercle unitaire.

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

Travailler avec des données de forme

Un autre type de données inhabituel disponible dans PostgreSQL est les données géométriques.

C'est vrai, vous pouvez travailler avec des points, des lignes, des polygones et des cercles dans 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...

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!