Научите ове брзе трикове у ПостгреСКЛ-у

ПостгреСКЛ је један од најпопуларнијих СКЛ дијалеката отвореног кода. Једна од његових главних предности је могућност проширења његове функционалности помоћу неких уграђених алата.

Овде ћемо погледати неколико ПостгреСКЛ трикова које можете почети да користите да бисте своје СКЛ вештине подигли на виши ниво.

Сазнаћете како:

  • Брзо копирајте датотеке у базу података
  • Резимирајте податке у унакрсном формату
  • Искористите низове и ЈСОН податке у СКЛ-у
  • Рад са геометријским подацима
  • Покрените статистичке анализе директно на вашој бази података
  • Користите рекурзију за решавање проблема

Копирајте податке из датотеке

Једноставан начин брзог увоза података из спољне датотеке је употреба функције ЦОПИ. Једноставно креирајте табелу коју желите да користите, а затим пренесите пут датотеке вашег скупа података наредби ЦОПИ.

Пример у наставку ствара табелу која се назива приход и попуњава је из насумично генерисане ЦСВ датотеке.

Можете да укључите додатне параметре да бисте назначили тип датотеке (овде је датотека ЦСВ) и да ли ћете први ред читати као заглавља колона.

Овде можете сазнати више.

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

Резимирајте податке помоћу функције унакрсне табеле

Ако се замишљате као професионалац прорачунске табеле, вероватно ћете бити упознати са креирањем пивот табела на депонијама података. То можете учинити и у ПостгреСКЛ-у помоћу функције унакрсне табеле.

Функција унакрсне табеле може да узима податке у образац са леве стране, а сажима их у образац с десне стране (што је много лакше читати). Пример који следи следиће подаци о приходима од раније.

Прво, омогућите екстензију таблефунц помоћу наредбе испод:

CREATE EXTENSION tablefunc;

Затим напишите упит помоћу функције унакрсне табеле:

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

Постоје две ствари које треба узети у обзир приликом коришћења ове функције.

  • Прво проследите упит одабиром података из основне табеле. Можете једноставно одабрати табелу каква јесте (као што је овде приказано). Међутим, можда ћете желети да филтрирате, придружите се или обједините ако је потребно. Обавезно правилно распоредите податке.
  • Затим дефинишите излаз (у примеру се излаз назива „резиме“, али можете га назвати било којим именом). Наведите заглавља колона која желите да користите и тип података који ће садржати.

Излаз ће бити приказан доле:

 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)

Радите са низовима и ЈСОН-ом

ПостгреСКЛ подржава вишедимензионалне типове података низа. Они су упоредиви са сличним типовима података на многим другим језицима, укључујући Питхон и ЈаваСцрипт.

Можда ћете их желети користити у ситуацијама када вам помаже рад са динамичнијим, мање структурираним подацима.

На пример, замислите табелу која описује објављене чланке и ознаке предмета. Чланак не може имати ознаке или их може имати много. Покушај чувања ових података у формату структуриране табеле био би непотребно сложен.

Низове можете дефинисати помоћу типа података, а затим угластим заградама. По жељи можете одредити њихове димензије (међутим, ово се не примењује).

На пример, да бисте креирали 1-Д низ од било ког броја елемената текста, користили бисте text[]. Да бисте креирали тродимензионални дводимензионални низ целобројних елемената, користили бисте int[3][3].

Погледајте пример испод:

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

Да бисте уметнули низове као записе, користите синтаксу '{"first","second","third"}'.

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

У ПостгреСКЛ-у можете много тога да урадите са низовима.

За почетак можете проверити да ли низ садржи дати елемент. Ово је корисно за филтрирање. За то можете да користите оператер „садржи“ @>. Упит у наставку проналази све чланке који имају ознаку „насумично“.

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

Такође можете повезати (спојити) низове помоћу ||оператора или проверити да ли се елементи преклапају са &&оператором.

Низове можете претраживати по индексу (за разлику од многих језика, ПостгреСКЛ низови почињу да броје од једног, уместо од нуле).

SELECT tags[1] FROM articles;

Поред низова, ПостгреСКЛ вам такође омогућава да користите ЈСОН као тип података. Опет, ово пружа предности рада са неструктурираним подацима. Елементима можете да приступите и под именом кључа.

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

Опет, постоји много ствари које можете да урадите са ЈСОН подацима у ПостгреСКЛ-у. Можете користити ->и ->>операторе и да "распакујете" ЈСОН објекте за употребу у упитима.

На пример, овај упит проналази вредности device_typeкључа:

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

А овај упит броји колико је сесија било у верзији апликације 1.0 или старијој:

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

Извршити статистичке анализе

Људи често виде СКЛ као добар за складиштење података и покретање једноставних упита, али не и за извршавање детаљнијих анализа. За то бисте требали користити други алат као што је Питхон или Р или ваш омиљени софтвер за прорачунске таблице.

Међутим, ПостгреСКЛ са собом доноси довољно статистичких могућности да започнете.

На пример, може да израчуна збирне статистике, корелацију, регресију и случајно узорковање. Доња табела садржи неке једноставне податке са којима се можете поиграти.

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

Помоћу функција у наставку можете пронаћи средњу вредност, варијансу и стандардну девијацију:

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

Такође можете пронаћи медијану (или било коју другу перцентилску вредност) помоћу функције перцентиле_цонт:

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

Други трик вам омогућава да израчунате коефицијенте корелације између различитих колона. Једноставно користите функцију цорр.

SELECT CORR(x,y) FROM stats;

ПостгреСКЛ вам омогућава покретање линеарне регресије (која се понекад назива најосновнијим обликом машинског учења) путем скупа уграђених функција.

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

Можете чак и да покренете Монте Царло симулације са појединачним упитима. Упит у наставку користи функције генерирај_серију и случајне бројеве за процену вредности π случајним узорковањем једног милиона тачака унутар јединственог круга.

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

Рад са подацима о облику

Још један необичан тип података доступан у ПостгреСКЛ-у су геометријски подаци.

Тачно, можете радити са тачкама, линијама, полигонима и круговима унутар СКЛ-а.

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

Даље, дефинише рекурзивни израз који поставља упит fibonacci:

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

Коначно, користи клаузулу ВХЕРЕ да дефинише критеријуме за прекид, а затим бира колону к дајући излазни низ:

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

Можда се можете сетити другог примера рекурзије који би могао да се примени у ПостгреСКЛ?

Завршне напомене

Ето, ту сте - брзо прођите кроз неке сјајне функције које сте можда знали или не знали да ПостгреСКЛ може пружити. Нема сумње да треба покрити још карактеристика које се нису нашле на овој листи.

ПостгреСКЛ је богат и моћан програмски језик сам по себи. Дакле, следећи пут када заглавите у смишљању како да решите проблем у вези са подацима, погледајте и погледајте да ли је ПостгреСКЛ покривен. Можда ћете се изненадити колико често то ради!

Хвала за читање!