Sužinokite šiuos greitus triukus „PostgreSQL“

„PostgreSQL“ yra viena populiariausių atvirojo kodo SQL tarmių. Vienas iš pagrindinių jo pranašumų yra galimybė išplėsti savo funkcionalumą naudojant kai kuriuos įmontuotus įrankius.

Pažvelkime į keletą „PostgreSQL“ gudrybių, kurias galite pradėti perkelti savo SQL įgūdžius į kitą lygį.

Sužinosite, kaip:

  • Greitai nukopijuokite failus į duomenų bazę
  • Apibendrinkite duomenis kryželiu
  • Pasinaudokite masyvų ir JSON duomenų pranašumais SQL
  • Darbas su geometriniais duomenimis
  • Vykdykite statistinę analizę tiesiogiai savo duomenų bazėje
  • Norėdami išspręsti problemas, naudokite rekursiją

Nukopijuokite duomenis iš failo

Paprastas būdas greitai importuoti duomenis iš išorinio failo yra naudoti funkciją KOPIJA. Tiesiog sukurkite norimą naudoti lentelę, tada duomenų rinkinio failų kelyje perduokite komandai COPY.

Toliau pateiktame pavyzdyje sukuriama lentelė, vadinama pajamomis, ir užpildoma iš atsitiktinai sugeneruoto CSV failo.

Galite nurodyti papildomus parametrus, nurodydami failo tipą (čia failas yra CSV) ir tai, ar pirmąją eilutę skaityti kaip stulpelių antraštes.

Čia galite sužinoti daugiau.

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

Apibendrinkite duomenis naudodami kryžkelės funkciją

Jei mėgstate save kaip skaičiuoklės profesionalą, tikriausiai susipažinsite su sukimosi lentelių kūrimu iš duomenų kaupimo vietų. Tą patį galite padaryti naudodami „PostgreSQL“ naudodami kryžkelės funkciją.

„Crosstab“ funkcija gali pateikti duomenis kairėje esančioje formoje ir apibendrinti dešinėje esančia forma (kurią daug lengviau perskaityti). Čia pateiktame pavyzdyje bus pateikti ankstesnių pajamų duomenys.

Pirmiausia įgalinkite „tablefunc“ plėtinį naudodami toliau nurodytą komandą:

CREATE EXTENSION tablefunc;

Tada parašykite užklausą naudodami kryžkelės funkciją:

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

Naudojant šią funkciją reikia atsižvelgti į du dalykus.

  • Pirmiausia pateikite užklausą, pasirinkdami duomenis iš savo pagrindinės lentelės. Galite tiesiog pasirinkti tokią lentelę, kokia ji yra (kaip parodyta čia). Tačiau, jei reikia, galbūt norėsite filtruoti, prisijungti arba kaupti. Būtinai teisingai užsisakykite duomenis.
  • Tada apibrėžkite išvestį (pavyzdyje išvestis vadinama „santrauka“, tačiau galite ją pavadinti bet kokiu vardu). Išvardykite norimas naudoti stulpelių antraštes ir jose esantį duomenų tipą.

Išvestis bus tokia, kaip parodyta žemiau:

 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)

Darbas su masyvais ir JSON

„PostgreSQL“ palaiko daugialypius masyvo duomenų tipus. Tai galima palyginti su panašiais duomenų tipais daugeliu kitų kalbų, įskaitant „Python“ ir „JavaScript“.

Galbūt norėsite juos naudoti situacijose, kai tai padeda dirbti su dinamiškesniais, mažiau struktūrizuotais duomenimis.

Pavyzdžiui, įsivaizduokite lentelę, kurioje aprašomi paskelbti straipsniai ir temų žymos. Straipsnyje negali būti žymų arba gali būti daug. Bandyti saugoti šiuos duomenis struktūrinės lentelės formatu būtų be reikalo sudėtinga.

Masyvus galite apibrėžti naudodami duomenų tipą, po kurio laužtiniai skliaustai. Galite pasirinktinai nurodyti jų matmenis (tačiau tai nėra vykdoma).

Pavyzdžiui, norėdami sukurti 1-D masyvą iš bet kurio skaičiaus teksto elementų, naudosite text[]. Norėdami sukurti trijų po tris dviejų matmenų sveikųjų skaičių masyvą, naudosite int[3][3].

Pažvelkite į toliau pateiktą pavyzdį:

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

Norėdami įterpti masyvus kaip įrašus, naudokite sintaksę '{"first","second","third"}'.

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

„PostgreSQL“ masyvuose galite padaryti daugybę dalykų.

Pirmiausia galite patikrinti, ar masyve yra nurodytas elementas. Tai naudinga filtruojant. Norėdami tai padaryti, galite naudoti operatorių "yra" @>. Žemiau pateiktoje užklausoje surandami visi straipsniai, turintys žymę „atsitiktinis“.

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

Taip pat galite susieti (sujungti) masyvus naudodami ||operatorių arba patikrinti, ar elementai nesutampa su &&operatoriumi.

Masyvuose galite ieškoti pagal indeksą (skirtingai nei daugeliu kalbų, „PostgreSQL“ masyvai pradedami skaičiuoti nuo vienos, o ne nuo nulio).

SELECT tags[1] FROM articles;

Be masyvų, „PostgreSQL“ taip pat leidžia naudoti JSON kaip duomenų tipą. Vėlgi, tai suteikia pranašumų dirbant su nestruktūrizuotais duomenimis. Elementus taip pat galite pasiekti pagal jų pagrindinį pavadinimą.

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

Vėlgi, yra daug dalykų, kuriuos galite padaryti naudodami „JSON“ duomenis „PostgreSQL“. Galite naudoti ->ir ->>operatorius, kad „išpakuotumėte“ JSON objektus, kuriuos norite naudoti užklausose.

Pavyzdžiui, ši užklausa suranda device_typerakto reikšmes :

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

Ši užklausa skaičiuoja, kiek seansų buvo programos 1.0 ar ankstesnėje versijoje:

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

Atlikti statistinę analizę

Dažnai žmonės SQL vertina kaip tinkamą duomenų saugojimui ir paprastų užklausų vykdymui, bet ne išsamesnės analizės vykdymui. Tam turėtumėte naudoti kitą įrankį, pvz., „Python“ ar „R“, arba savo mėgstamą skaičiuoklės programinę įrangą.

Tačiau „PostgreSQL“ suteikia pakankamai statistinių galimybių, kad galėtumėte pradėti.

Pavyzdžiui, jis gali apskaičiuoti statistikos suvestinę, koreliaciją, regresiją ir atsitiktinę imtį. Žemiau esančioje lentelėje yra keletas paprastų duomenų, su kuriais galima žaisti.

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

Vidurkį, dispersiją ir standartinį nuokrypį galite rasti naudodamiesi toliau pateiktomis funkcijomis:

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

Taip pat galite rasti medianą (ar bet kurią kitą procentilės vertę) naudodami funkciją 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;

Kitas triukas leidžia apskaičiuoti koreliacijos koeficientus tarp skirtingų stulpelių. Tiesiog naudokite korekcijos funkciją.

SELECT CORR(x,y) FROM stats;

„PostgreSQL“ leidžia atlikti linijinę regresiją (kartais vadinamą paprasčiausia mašininio mokymosi forma) per integruotų funkcijų rinkinį.

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

Jūs netgi galite paleisti Monte Karlo modeliavimą su viena užklausa. Toliau pateiktoje užklausoje naudojamos „generator_series“ ir atsitiktinių skaičių funkcijos, kad būtų galima įvertinti π reikšmę atsitiktinai atrenkant vieną milijoną taškų vieneto apskritime.

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

Darbas su formos duomenimis

Kitas neįprastas „PostgreSQL“ duomenų tipas yra geometriniai duomenys.

Teisingai, jūs galite dirbti su taškais, linijomis, daugiakampiais ir apskritimais SQL sistemoje.

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

Tada jis apibrėžia rekursyvią išraišką, kuri pateikia užklausą fibonacci:

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

Galiausiai ji naudoja sąlygą WHERE, kad apibrėžtų nutraukimo kriterijus, ir tada pasirenka x stulpelį, kad gautų išvesties seką:

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

Galbūt galite pagalvoti apie kitą rekursijos pavyzdį, kurį būtų galima įdiegti „PostgreSQL“?

Baigiamosios pastabos

Taigi, jūs turite tai - greitai peržiūrėkite keletą puikių funkcijų, kurias galbūt žinote, bet galbūt nežinojote „PostgreSQL“. Be abejonės, verta įtraukti daugiau funkcijų, kurios nepateko į šį sąrašą.

„PostgreSQL“ yra turtinga ir galinga programavimo kalba. Taigi, kai kitą kartą užstrigsite sugalvoję, kaip išspręsti su duomenimis susijusią problemą, pasidomėkite ir sužinokite, ar „PostgreSQL“ apėmė jus. Galite nustebti, kaip dažnai tai vyksta!

Ačiū, kad skaitėte!