Postgres 9 ETL
lets go back to roots because roots mean culture
pridat stlpec s autoinkrementalnou hodnotou a PK
ALTER TABLE tablename ADD COLUMN id_aut SERIAL PRIMARY KEY;
updajtni tabulku na zaklade 2 parametroveho subselectu
UPDATE tab_finds SET datation = 4000 WHERE id_aut IN
(SELECT id_aut FROM tab_finds where material = ‘stavebni keramika’ AND datation < 3100);
texty, stringy char a varchar
lowercase/uppercase
UPDATE tabulka SET column=lower(column);
updatovanie tabuľky – concatenácia dvoch polí, s tým, že číselné polia sú prevedené na characters, všimni si parameter formátovania (FM)
UPDATE tab_polygony_sezony SET concat_season_polygon = (TO_CHAR(season,’FM9′) || TO_CHAR(r,’FM999′)) AS ddd FROM probe;
matematické operácie
pomerne intuitívne
UPDATE tabulka SET calcul = datace/(hustota*koeficient);
UPDATE tab_polygony_sezony
SET koeficient_rozestupy = ROUND
(rozestup_linii/(SELECT AVG(rozestup_linii)
FROM tab_polygony_sezony),2)
window funkcie
SELECT distinct(id_polygon), sum(“vaha”) OVER (PARTITION BY id_polygon) AS Vaha_overall
FROM tab_finds
where season=1
ORDER BY id_polygon;
joiny
poznáme nasledovné typy joinov v postgrese:
- cross join
- lateral join (nové od 9.3.)
- inner join
- left oute rjoin
- right outer join
- full outer join
left join abstraktny priklad
SELECT A.pka, A.c1, B.pkb, B.c2
FROM A
LEFT JOIN B ON A .pka = B.fka;
Lateral join
Chápem to tak, že je to nahradenie subselectu (resp. ešte lepšie – correlated subquery), ktorý má tú nevýhodu, že vracia len jeden record. Lateral join vracia sadu recordov, tak, akoby sa nad result setom loopovalo a zároveň hneď spojí (join) výsledky a hlavným dotazom. Pekné vysvetlenie na stackoverflow.
Fyzické joiny
Hore uvedené typy joinov sú logické typy – abstrakcie, ako spájať dáta v tabuľkách. Samotné joiny na fyzickej vrstve sa realizujú pomocou nested loop join, merge join, alebo hash join
Nested loop join
Nested lop join je operácia “každý s každým” – z jednej tabuľky sa vyberajú postupne hodnoty a loopom sa skúša, ktoré pasujú na hodnoty v druhej tabuľke. Náročné na CPU, pokiaľ je recordov v oboch tabuľkách hodne. Pekné vysvetlenie tu na YT.
Nested loop má niekoľko “podtypov”, podľa toho, aká presná implementácia sa použije (napr. materialized nested loop používa materialized views; indexed nested loop používa indexy a pod.)
Merge join
V princípe podobný ako nested loop join (porvnávanie zhody hodnôt), ale algoritmus nemusí iterovať (loopovať), pretože hodnoty sú zoradené. Z toho však vyplýva, že musí existovať buď index, alebo sa najprv musia hodnoty zoradiť. Pekné vysvetlenie tu na YT.
Merge join má tiež niekoľko podtypov v závislosti na presnej implementácii (materialized, indexed podľa toho, na ktorú tabuľku sa indexy využívajú a pod….).
Hash join
Hash join môže byť použitý len na equi-joins.
Podstatou je prehnanie hodnoty z 1. tabuľky hashovaciou funkciou a vytvorenie hash bucketu (chlieviky pre hashe, alebo hashová mapa). Následne sa hashovaciou funkciou porovnávajú hodnoty z 2. tabuľky a tým priradzujú do správnych bucketov. Je to pomerne rýchle a stavané aj na veľké tabuľky. Je to všem rýchle, len pokiaľ sa hashovacia tabuľka vojde do pamäte, inak sa bude tempovať na disk a komplikuje sa to. Vysvetlenie na YT tu.
Postgres to robí rýchlo, pokiaľ je to in memory a pokiaľ to nie je všetko in-memory, tak používa hybridnú hash table s šikmým vyhľadávaním dát. In-memory znamená, že sa to vojde do work_mem a postgres sa na začiatku rozhodne, že použije in-memory hash join vtedy, pokiaľ inner table je menšia, než 25% work_mem.
Efektivita rôznych typov joinov
Video s potrhlým chlapíkom od Oraclu ukazuje ešte raz a na príklade hracích kariet celý proces. Tento obrázok z videa jasne ukazuje efektivity jednotlivých typov joinov (počet fyzických operácií). No a prečo sa nepoužíva výlučne hash join? Pretože to platí len pre preliezanie celých oboch datasetov s operátorom “=”, čož nie je vždy náš prípad…
select na hodnoty inej tabulky cez M:N relation
Pri normalizácii sa často stane, že máme tabuľky poprepájané cez M:N reláciu (“pomocnú tabuľku”). Ako selektovať všetky záznamy z druhej tabuľky pre špecifický záznam z prvej tabuľky? Typický príklad z archelogickej opraxe: stratigrafické jednotky a fotky, každá entita má svoju vlastnú tabuľku. . Stratigrafická jednotka može byť na viacerých fotkách a fotka može obsahovať viac stratigrafických jednotiek ergo M:N. Robí sa to cez inner joiny na intermediate tabuľku a pekný rozklad je tu.
konverzia datových typov medzi Oracle a Postgres
PostgreSQL | Oracle | ||||
BIGINT | 64-bit integer | NUMBER(19) | |||
BIGSERIAL | 64-bit autoincrementing integer | Sequence and trigger | |||
BIT(n) | Fixed-length bit string | RAW(n/8) | |||
BIT VARYING(n) | Variable-length bit string | RAW(n/8) | |||
BOOLEAN, BOOL | True, false or NULL | CHAR(1) | |||
BYTEA | Variable-length binary data, ⇐ 2G | BLOB | |||
CHARACTER(n), CHAR(n) | Fixed-length string, 1 ⇐ n ⇐ 1G | CHARACTER(n), CHAR(n) | |||
CHARACTER VARYING(n) | Variable-length string, 1 ⇐ n ⇐ 1G | VARCHAR2(n) | |||
DATE | Date (year, month and day) | DATE | Includes time part | ||
DECIMAL(p,s), DEC(p,s) | Fixed-point number | NUMBER(p,s) | |||
DOUBLE PRECISION | Double-precision floating-point | BINARY_DOUBLE | |||
FLOAT4 | Single-precision floating-point | BINARY_FLOAT | |||
FLOAT8 | Double-precision floating-point | BINARY_DOUBLE | |||
INTEGER, INT | 32-bit integer | NUMBER(10) | |||
INT2 | 16-bit integer | NUMBER(5) | |||
INT4 | 32-bit integer | NUMBER(10) | |||
INT8 | 64-bit integer | NUMBER(20) | |||
INTERVAL | Date and time interval | INTERVAL YEAR TO MONTH | |||
INTERVAL DAY TO SECOND | |||||
INTERVAL unit | Date and time interval | NUMBER(5) | |||
INTERVAL YEAR TO MONTH | Date interval | INTERVAL YEAR TO MONTH | |||
INTERVAL DAY TO HOUR | Day and time interval | INTERVAL DAY(5) TO SECOND | |||
INTERVAL DAY TO MINUTE | Day and time interval | INTERVAL DAY(5) TO SECOND | |||
INTERVAL DAY TO SECOND[(p)] | Day and time interval | INTERVAL DAY(5) TO SECOND[(p)] | |||
INTERVAL HOUR TO MINUTE | Time interval | INTERVAL DAY(5) TO SECOND | |||
INTERVAL HOUR TO SECOND[(p)] | Time interval | INTERVAL DAY(5) TO SECOND[(p)] | |||
INTERVAL MINUTE TO SECOND[(p)] | Time interval | INTERVAL DAY(5) TO SECOND[(p)] | |||
MONEY | Currency amount | NUMBER(17,2) | |||
NUMERIC(p,s) | Fixed-point number | NUMERIC(p,s) | |||
REAL | Single-precision floating-point | BINARY_FLOAT | |||
SERIAL | 32-bit autoincrementing integer | Sequence and trigger | |||
SERIAL2 | 16-bit autoincrementing integer | Sequence and trigger | |||
SERIAL4 | 32-bit autoincrementing integer | Sequence and trigger | |||
SERIAL8 | 64-bit autoincrementing integer | Sequence and trigger | |||
SMALLINT | 16-bit integer | NUMBER(5) | |||
SMALLSERIAL | 16-bit autoincrementing integer | Sequence and trigger | |||
TEXT | Variable-length character data, ⇐ 1G | CLOB | |||
TIME(p) | Time with fraction | TIMESTAMP(p) | |||
TIME(p) WITH TIME ZONE | Time with fraction and time zone | TIMESTAMP(p) WITH TIME ZONE | |||
TIMETZ(p) | |||||
TIMESTAMP | Date and time with fraction | TIMESTAMP(p) | |||
TIMESTAMP(p) WITH TIME ZONE | Date and time with time zone | TIMESTAMP(p) WITH TIME ZONE | |||
TIMESTAMPTZ(p) | |||||
UUID | Universally unique identifier | CHAR(36) | |||
VARBIT(n) | Variable-length bit string | RAW(n/8) | |||
VARCHAR(n) | Variable-length string, 1 ⇐ n ⇐ 1G | VARCHAR2(n) | |||
XML | XML data | XMLTYPE |
pekný prehľad konverzií a funkcií i s linkami na konvertory tu
https://stackoverflow.com/questions/7869592/how-to-do-an-update-join-in-postgresql
postgres
25 Oct 18 at 23:06
jak se pocita median, neni na to built-in agregacni funkce (aka AVG(), MIN()…)
https://wiki.postgresql.org/wiki/Aggregate_Median
postgres
26 Oct 18 at 0:17
Poznamka pre autoincrement (SERIAL):
Od verzie 10 je tu nova syntax tohoto constraintu a to GENERATED AS IDENTITY
https://www.postgresqltutorial.com/postgresql-identity-column/
dobo
7 Nov 20 at 1:35
for joins see this:
https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-joins/
fabiano
19 Dec 23 at 8:44