Uzziniet šos ātros trikus PostgreSQL

PostgreSQL ir viens no populārākajiem atvērtā pirmkoda SQL dialektiem. Viena no tās galvenajām priekšrocībām ir spēja paplašināt tās funkcionalitāti ar dažiem iebūvētiem rīkiem.

Apskatīsim dažus PostgreSQL trikus, kurus varat sākt izmantot, lai savas SQL prasmes pārceltu nākamajā līmenī.

Jūs uzzināsiet, kā:

  • Ātri kopējiet failus datu bāzē
  • Apkopojiet datus šķērsstaba formātā
  • Izmantojiet masīvu un JSON datu priekšrocības SQL
  • Darbs ar ģeometriskiem datiem
  • Palaidiet statistikas analīzes tieši savā datu bāzē
  • Izmantojiet rekursiju problēmu risināšanai

Kopēt datus no faila

Vienkāršs veids, kā ātri importēt datus no ārēja faila, ir izmantot funkciju KOPĒT. Vienkārši izveidojiet tabulu, kuru vēlaties izmantot, pēc tam ievadiet datu kopas faila ceļu komandai COPY.

Tālāk sniegtajā piemērā tiek izveidota tabula ar nosaukumu ieņēmumi un tā aizpildīta no nejauši ģenerēta CSV faila.

Varat iekļaut papildu parametrus, lai norādītu faila tipu (šeit fails ir CSV) un to, vai pirmo rindu lasīt kā kolonnu galvenes.

Jūs varat uzzināt vairāk šeit.

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

Apkopojiet datus, izmantojot krustzīmju funkciju

Ja jūs domājat par izklājlapu profesionāli, jūs, iespējams, būsiet iepazinušies ar rakurstabulu izveidi no datu izgāztuvēm. To var izdarīt arī PostgreSQL, izmantojot funkciju crosstab.

Crosstab funkcija var iegūt datus kreisajā formā un apkopot tos formā pa labi (kas ir daudz vieglāk lasāma). Šeit sniegtais piemērs tiks papildināts ar ieņēmumu datiem no iepriekšējiem.

Pirmkārt, iespējojiet tablefunc paplašinājumu ar šādu komandu:

CREATE EXTENSION tablefunc;

Pēc tam uzrakstiet vaicājumu, izmantojot krustzīmju funkciju:

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

Lietojot šo funkciju, jāņem vērā divas lietas.

  • Vispirms ievadiet vaicājumu, atlasot datus no pamatā esošās tabulas. Jūs varat vienkārši atlasīt tabulu tādu, kāda tā ir (kā parādīts šeit). Tomēr, ja nepieciešams, ieteicams filtrēt, pievienoties vai apkopot. Noteikti pasūtiet datus pareizi.
  • Pēc tam definējiet izvadi (piemērā izvadi sauc par “kopsavilkumu”, bet jūs to varat saukt par jebkuru nosaukumu). Uzskaitiet kolonnu galvenes, kuras vēlaties izmantot, un datu veidu, ko tās saturēs.

Rezultāts būs šāds:

 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)

Darbs ar masīviem un JSON

PostgreSQL atbalsta daudzdimensiju masīvu datu tipus. Tie ir salīdzināmi ar līdzīgiem datu tipiem daudzās citās valodās, ieskaitot Python un JavaScript.

Varat tos izmantot situācijās, kad tas palīdz strādāt ar dinamiskākiem, mazāk strukturētiem datiem.

Piemēram, iedomājieties tabulu, kurā aprakstīti publicētie raksti un priekšmetu tagi. Rakstam nedrīkst būt tagu, vai arī tam var būt daudz. Mēģināt saglabāt šos datus strukturētā tabulas formātā būtu nevajadzīgi sarežģīti.

Masīvus var definēt, izmantojot datu tipu, kam seko kvadrātiekavas. Pēc izvēles varat norādīt to izmērus (tomēr tas netiek piemērots).

Piemēram, lai izveidotu 1-D masīvu no jebkura teksta elementu skaita, jūs izmantojat text[]. Lai izveidotu trīs līdz trīs divdimensiju veselu skaitļu elementu masīvu, jūs izmantojat int[3][3].

Apskatiet šo piemēru:

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

Lai ievietotu masīvus kā ierakstus, izmantojiet sintaksi '{"first","second","third"}'.

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

Ar masīviem PostgreSQL var izdarīt daudzas lietas.

Sākumā varat pārbaudīt, vai masīvā ir dots elements. Tas ir noderīgi filtrēšanai. @>Lai to izdarītu, varat izmantot operatoru "satur" . Zemāk esošajā vaicājumā tiek atrasti visi raksti, kuriem ir atzīme "izlases".

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

Varat arī savienot (apvienot) masīvus, izmantojot ||operatoru, vai pārbaudīt, vai elementi pārklājas ar &&operatoru.

Masīvos var meklēt pēc indeksa (atšķirībā no daudzām valodām PostgreSQL masīvi sāk skaitīt no vienas, nevis uz nulli).

SELECT tags[1] FROM articles;

Papildus masīviem PostgreSQL ļauj jums izmantot JSON kā datu tipu. Tas atkal nodrošina priekšrocības, strādājot ar nestrukturētiem datiem. Elementiem var piekļūt arī pēc to atslēgas nosaukuma.

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

Atkal ir daudzas lietas, ko jūs varat darīt ar JSON datiem PostgreSQL. Jūs varat izmantot ->, un ->>operatorus uz "unpackage" JSON objektus izmantot vaicājumiem.

Piemēram, šis vaicājums atrod device_typeatslēgas vērtības :

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

Šis vaicājums skaita sesiju skaitu lietotnes versijā 1.0 vai vecākā versijā:

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

Veikt statistisko analīzi

Bieži vien cilvēki SQL uzskata par labu datu glabāšanai un vienkāršu vaicājumu izpildei, bet ne padziļinātāku analīžu veikšanai. Lai to izdarītu, jums jāizmanto cits rīks, piemēram, Python vai R vai iecienītākā izklājlapu programmatūra.

Tomēr PostgreSQL nodrošina pietiekami daudz statistikas iespēju, lai jūs sāktu darbu.

Piemēram, tā var aprēķināt statistikas kopsavilkumu, korelāciju, regresiju un izlases izlasi. Zemāk esošajā tabulā ir daži vienkārši dati, ar kuriem spēlēt.

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

Vidējo vērtību, dispersiju un standartnovirzi varat atrast, izmantojot tālāk norādītās funkcijas.

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

Mediānu (vai jebkuru citu procentiles vērtību) varat arī atrast, izmantojot funkciju 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;

Vēl viens triks ļauj aprēķināt korelācijas koeficientus starp dažādām kolonnām. Vienkārši izmantojiet korekcijas funkciju.

SELECT CORR(x,y) FROM stats;

PostgreSQL ļauj palaist lineāru regresiju (dažreiz to sauc par mašīnmācīšanās visvienkāršāko formu), izmantojot iebūvēto funkciju kopu.

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

Jūs pat varat palaist Montekarlo simulācijas ar atsevišķiem vaicājumiem. Tālāk esošajā vaicājumā tiek izmantotas generator_series un nejaušā skaitļa funkcijas, lai novērtētu π vērtību, nejauši atlasot vienu miljonu punktu apļa vienībā.

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

Darbs ar formas datiem

Vēl viens neparasts datu veids, kas pieejams PostgreSQL, ir ģeometriskie dati.

Pareizi, SQL ietvaros varat strādāt ar punktiem, līnijām, daudzstūriem un apļiem.

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

Pēc tam tas definē rekursīvo izteiksmi, kas vaicā fibonacci:

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

Visbeidzot, tā izmanto WHERE klauzulu, lai definētu izbeigšanas kritērijus, un pēc tam atlasa kolonnu x, lai iegūtu izejas secību:

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

Varbūt jūs varat iedomāties vēl vienu rekursijas piemēru, ko varētu ieviest PostgreSQL?

Nobeiguma piezīmes

Tātad, tur jums tas ir - ātri pārskatiet dažas lieliskas funkcijas, kuras, iespējams, nezināt, vai varētu zināt PostgreSQL. Nav šaubu, ka būtu vērts aplūkot vēl citas funkcijas, kas neiekļuva šajā sarakstā.

PostgreSQL ir bagāta un spēcīga programmēšanas valoda pati par sevi. Tātad, nākamreiz, kad izdomājat, kā atrisināt ar datiem saistītu problēmu, ieskatieties un pārliecinieties, vai esat iekļāvis PostgreSQL. Jūs varētu pārsteigt, cik bieži tas notiek!

Paldies, ka lasījāt!