Aprenda estos trucos rápidos en PostgreSQL

PostgreSQL es uno de los dialectos SQL de código abierto más populares. Una de sus principales ventajas es la posibilidad de ampliar su funcionalidad con algunas herramientas integradas.

Aquí, veamos algunos trucos de PostgreSQL que puede comenzar a usar para llevar sus habilidades de SQL al siguiente nivel.

Descubrirás cómo:

  • Copie archivos rápidamente en una base de datos
  • Resumir datos en formato de tabla cruzada
  • Aproveche las matrices y los datos JSON en SQL
  • Trabajar con datos geométricos
  • Ejecute análisis estadísticos directamente en su base de datos
  • Usa la recursividad para resolver problemas

Copiar datos de un archivo

Una forma sencilla de importar datos rápidamente desde un archivo externo es utilizar la función COPIAR. Simplemente cree la tabla que desea usar, luego pase la ruta de archivo de su conjunto de datos al comando COPY.

El siguiente ejemplo crea una tabla llamada ingresos y la llena desde un archivo CSV generado aleatoriamente.

Puede incluir parámetros adicionales, para indicar el tipo de archivo (aquí, el archivo es un CSV) y si leer la primera fila como encabezados de columna.

Puedes aprender más aqui.

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

Resumir datos con la función de tabla de referencias cruzadas

Si te apetece ser un profesional de las hojas de cálculo, probablemente estés familiarizado con la creación de tablas dinámicas a partir de volcados de datos. Puede hacer lo mismo en PostgreSQL con la función de tabla de referencias cruzadas.

La función de tabla de referencias cruzadas puede tomar datos en el formulario de la izquierda y resumirlos en el formulario de la derecha (que es mucho más fácil de leer). El ejemplo aquí continuará con los datos de ingresos anteriores.

Primero, habilite la extensión tablefunc con el siguiente comando:

CREATE EXTENSION tablefunc;

A continuación, escriba una consulta utilizando la función de tabla de referencias cruzadas:

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

Hay dos cosas a tener en cuenta al utilizar esta función.

  • Primero, pase una consulta seleccionando datos de su tabla subyacente. Simplemente puede seleccionar la tabla tal como está (como se muestra aquí). Sin embargo, es posible que desee filtrar, unir o agregar si es necesario. Asegúrese de ordenar los datos correctamente.
  • Luego, defina la salida (en el ejemplo, la salida se llama 'resumen', pero puede llamarla con cualquier nombre). Enumere los encabezados de columna que desea usar y el tipo de datos que contendrán.

La salida será como se muestra a continuación:

 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)

Trabajar con matrices y JSON

PostgreSQL admite tipos de datos de matriz multidimensionales. Estos son comparables a tipos de datos similares en muchos otros lenguajes, incluidos Python y JavaScript.

Es posible que desee utilizarlos en situaciones en las que sea útil trabajar con datos más dinámicos y menos estructurados.

Por ejemplo, imagine una tabla que describa los artículos publicados y las etiquetas de temas. Un artículo puede no tener etiquetas o puede tener muchas. Tratar de almacenar estos datos en un formato de tabla estructurado sería innecesariamente complicado.

Puede definir matrices utilizando un tipo de datos, seguido de corchetes. Opcionalmente, puede especificar sus dimensiones (sin embargo, esto no se aplica).

Por ejemplo, para crear una matriz 1-D de cualquier número de elementos de texto, usaría text[]. Para crear una matriz bidimensional de tres por tres de elementos enteros, usaría int[3][3].

Eche un vistazo al siguiente ejemplo:

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

Para insertar matrices como registros, use la sintaxis '{"first","second","third"}'.

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

Hay muchas cosas que puede hacer con matrices en PostgreSQL.

Para empezar, puede comprobar si una matriz contiene un elemento determinado. Esto es útil para filtrar. Puede utilizar el operador "contiene" @>para hacer esto. La siguiente consulta busca todos los artículos que tienen la etiqueta "aleatorio".

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

También puede concatenar (unir) matrices usando el ||operador, o verificar elementos superpuestos con el &&operador.

Puede buscar matrices por índice (a diferencia de muchos idiomas, las matrices de PostgreSQL comienzan a contar desde uno, en lugar de cero).

SELECT tags[1] FROM articles;

Además de las matrices, PostgreSQL también le permite usar JSON como tipo de datos. Nuevamente, esto ofrece las ventajas de trabajar con datos no estructurados. También puede acceder a los elementos por su nombre de clave.

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

Nuevamente, hay muchas cosas que puede hacer con datos JSON en PostgreSQL. Puede utilizar los operadores ->y ->>para "descomprimir" los objetos JSON para utilizarlos en consultas.

Por ejemplo, esta consulta busca los valores de la device_typeclave:

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

Y esta consulta cuenta cuántas sesiones estaban en la versión 1.0 o anterior de la aplicación:

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

Ejecutar análisis estadísticos

A menudo, las personas consideran que SQL es bueno para almacenar datos y ejecutar consultas simples, pero no para ejecutar análisis más profundos. Para eso, debe usar otra herramienta como Python o R o su software de hoja de cálculo favorito.

Sin embargo, PostgreSQL trae consigo suficientes capacidades estadísticas para comenzar.

Por ejemplo, puede calcular estadísticas de resumen, correlación, regresión y muestreo aleatorio. La siguiente tabla contiene algunos datos sencillos para jugar.

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

Puede encontrar la media, la varianza y la desviación estándar utilizando las funciones siguientes:

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

También puede encontrar la mediana (o cualquier otro valor de percentil) usando la función 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;

Otro truco te permite calcular los coeficientes de correlación entre diferentes columnas. Simplemente use la función corr.

SELECT CORR(x,y) FROM stats;

PostgreSQL le permite ejecutar regresión lineal (a veces llamada la forma más básica de aprendizaje automático) a través de un conjunto de funciones integradas.

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

Incluso puede ejecutar simulaciones de Monte Carlo con consultas únicas. La siguiente consulta utiliza las funciones generate_series y de números aleatorios para estimar el valor de π al muestrear al azar un millón de puntos dentro de un círculo unitario.

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

Trabajar con datos de formas

Otro tipo de datos inusual disponible en PostgreSQL son los datos geométricos.

Así es, puede trabajar con puntos, líneas, polígonos y círculos dentro de 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!