Opi nämä nopeat temput PostgreSQL: ssä

PostgreSQL on yksi suosituimmista avoimen lähdekoodin SQL-murteista. Yksi sen tärkeimmistä eduista on kyky laajentaa toimintojaan joillakin sisäänrakennetuilla työkaluilla.

Katsotaanpa tässä muutamia PostgreSQL-temppuja, joiden avulla voit viedä SQL-taitosi seuraavalle tasolle.

Opit kuinka:

  • Kopioi tiedostot nopeasti tietokantaan
  • Yhteenveto tiedoista ristiintyyppimuodossa
  • Hyödynnä matriiseja ja JSON-tietoja SQL: ssä
  • Työskentele geometristen tietojen kanssa
  • Suorita tilastolliset analyysit suoraan tietokannassasi
  • Käytä rekursiota ongelmien ratkaisemiseen

Kopioi tiedot tiedostosta

Helppo tapa tuoda tietoja nopeasti ulkoisesta tiedostosta on käyttää KOPIO-toimintoa. Luo yksinkertaisesti taulukko, jota haluat käyttää, ja siirrä sitten tietojoukon tiedostopolku COPY-komentoon.

Alla oleva esimerkki luo taulukon nimeltä tulo ja täyttää sen satunnaisesti tuotetusta CSV-tiedostosta.

Voit sisällyttää ylimääräisiä parametreja osoittamaan tiedostotyypin (tässä tiedosto on CSV) ja siitä, luetaanko ensimmäinen rivi sarakeotsikoina.

Voit oppia lisää täältä.

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

Yhteenveto tiedoista ristiintoiminnolla

Jos pidät itsestäsi laskentataulukon ammattilaisena, olet todennäköisesti perehtynyt pivot-taulukoiden luomiseen tietojen kaatopaikoista. Voit tehdä saman PostgreSQL: ssä ristiintoiminnolla.

Ristiintoiminto voi ottaa tiedot vasemmalla olevassa muodossa ja tiivistää ne oikealla olevassa muodossa (joka on paljon helpompi lukea). Tässä oleva esimerkki jatkuu aikaisempien tulotietojen kanssa.

Ota ensin tablefunc-laajennus käyttöön alla olevalla komennolla:

CREATE EXTENSION tablefunc;

Kirjoita seuraavaksi kysely ristiintoiminnolla:

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

Tätä toimintoa käytettäessä on otettava huomioon kaksi asiaa.

  • Anna ensin kysely valitsemalla tiedot alla olevasta taulukosta. Voit yksinkertaisesti valita taulukon sellaisenaan (kuten tässä on esitetty). Haluat kuitenkin suodattaa, liittyä tai koota tarvittaessa. Tilaa tiedot oikein.
  • Määritä sitten lähtö (esimerkissä lähtöä kutsutaan 'yhteenvedoksi', mutta voit kutsua sitä millä tahansa nimellä). Luettele haluamasi sarakeotsikot ja niiden sisältämä tietotyyppi.

Tulos on seuraava:

 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)

Työskentele matriisien ja JSON: n kanssa

PostgreSQL tukee moniulotteisia matriisitietotyyppejä. Nämä ovat verrattavissa vastaaviin tietotyyppeihin monilla muilla kielillä, mukaan lukien Python ja JavaScript.

Haluat ehkä käyttää niitä tilanteissa, joissa se auttaa työskentelemään dynaamisempien, vähemmän jäsenneltyjen tietojen kanssa.

Kuvittele esimerkiksi taulukko, joka kuvaa julkaistuja artikkeleita ja aihetunnisteita. Artikkelissa ei voi olla tunnisteita tai sillä voi olla monia. Yritä tallentaa nämä tiedot jäsenneltyyn taulukkomuotoon olisi tarpeetonta monimutkaista.

Voit määrittää taulukot käyttämällä tietotyyppiä, jota seuraavat hakasulkeet. Voit halutessasi määrittää niiden mitat (tätä ei kuitenkaan pakoteta).

Voit esimerkiksi luoda 1-D-taulukon mistä tahansa määrästä tekstielementtejä text[]. Voit luoda kolmiulotteisen kaksiulotteisen kokonaiselementtiryhmän käyttämällä int[3][3].

Katso seuraava esimerkki:

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

Jos haluat lisätä taulukoita tietueina, käytä syntaksia '{"first","second","third"}'.

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

PostgreSQL: ssä voi tehdä paljon asioita matriisien avulla.

Aluksi voit tarkistaa, sisältääkö taulukko tietyn elementin. Tästä on hyötyä suodatuksessa. Voit käyttää "sisältää" -operaattoria @>tähän. Alla olevasta kyselystä löytyvät kaikki artikkelit, joissa on tunniste "random".

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

Voit myös ketjuttaa (liittää yhteen) taulukot ||operaattorin avulla tai tarkistaa päällekkäiset elementit &&operaattorin kanssa.

Voit hakea matriiseista hakemiston mukaan (toisin kuin monilla kielillä, PostgreSQL-taulukot alkavat laskea yhdestä nollan sijasta).

SELECT tags[1] FROM articles;

Taulukoiden lisäksi PostgreSQL antaa sinun käyttää JSON-tiedostoa myös tietotyyppinä. Tämä tarjoaa jälleen edut strukturoimattomien tietojen kanssa työskentelystä. Voit käyttää elementtejä myös niiden avaimen nimellä.

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-tiedoilla voidaan tehdä monia asioita PostgreSQL: ssä. Voit käyttää ->ja ->>-operaattoreita "purkamaan" kyselyissä käytettäviä JSON-objekteja.

Esimerkiksi tämä kysely löytää device_typeavaimen arvot :

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

Ja tämä kysely laskee, kuinka monta istuntoa oli sovelluksen versiossa 1.0 tai vanhemmalla:

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

Suorita tilastolliset analyysit

Usein ihmiset pitävät SQL: ää hyvänä tietojen tallentamiseen ja yksinkertaisten kyselyjen suorittamiseen, mutta eivät perusteellisempien analyysien suorittamiseen. Tätä varten sinun on käytettävä toista työkalua, kuten Python tai R tai suosikki taulukkolaskentaohjelmisto.

PostgreSQL tuo kuitenkin mukanaan tarpeeksi tilastollisia ominaisuuksia aloittaaksesi.

Se voi esimerkiksi laskea yhteenvetotilastot, korrelaation, regressio- ja satunnaisotannan. Alla oleva taulukko sisältää joitain yksinkertaisia ​​tietoja, joita voi pelata.

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

Löydät keskiarvon, varianssin ja keskihajonnan alla olevien toimintojen avulla:

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

Löydät mediaanin (tai minkä tahansa muun prosenttipisteen) myös percentile_cont-funktiolla:

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

Toinen temppu antaa sinun laskea korrelaatiokertoimet eri sarakkeiden välillä. Käytä vain corr-toimintoa.

SELECT CORR(x,y) FROM stats;

PostgreSQL antaa sinun suorittaa lineaarinen regressio (jota kutsutaan joskus koneoppimisen perustavimmaksi muodoksi) sisäänrakennettujen toimintojen kautta.

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

Voit jopa suorittaa Monte Carlon simulaatioita yksittäisillä kyselyillä. Alla oleva kysely käyttää generointisarja- ja satunnaislukufunktioita estimoimaan π: n arvon ottamalla satunnaisesti miljoonan pisteen ympyrän sisällä.

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

Työskentele muototietojen kanssa

Toinen epätavallinen PostgreSQL-tiedostotyyppi on geometriset tiedot.

Aivan oikein, voit työskennellä pisteiden, viivojen, monikulmioiden ja ympyröiden kanssa SQL: ssä.

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

Seuraavaksi se määrittää rekursiivisen lausekkeen, joka kysyy fibonacci:

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

Lopuksi se käyttää WHERE-lauseketta lopetuskriteerien määrittelemiseen ja valitsee sitten sarakkeen x antamaan tulosekvenssin:

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

Ehkä voit ajatella toista esimerkkiä rekursiosta, joka voitaisiin toteuttaa PostgreSQL: ssä?

Loppuhuomautukset

Joten, sinulla on se - nopea läpi joitakin hienoja ominaisuuksia, joita saatat olla tai ei ehkä tiedä PostgreSQL voisi tarjota. Ei ole epäilystäkään enemmän piirteitä, jotka eivät kuulu tähän luetteloon.

PostgreSQL on itsessään rikas ja tehokas ohjelmointikieli. Joten, kun seuraavan kerran olet juuttunut selvittämään, miten ratkaista tietoihin liittyvä ongelma, katsokaa ja katso, onko PostgreSQL kattanut. Saatat yllättyä, kuinka usein se tapahtuu!

Kiitos lukemisesta!