Praktična uporaba časovnih funkcij v PostgreSQL
Tema tokratnega prispevka bo praktična uporaba nekaterih časovnih funkcij v PostgreSQL, kar si bomo ogledali na konkretnem primeru ugotavljanja prekrivanja članstva osebe v dveh različnih društvih.
Predpostavimo, da imamo na voljo dva seznama. V prvem seznamu se nahajajo osebe, ki so člani društva A, poleg vsake osebe pa je zabeleženo kdaj se je v društvo včlanila ter kdaj je iz društva izstopila. Če iz društva ni izstopila, podatka o izstopu ni. V drugem seznamu imamo podobne podatke za članstvo v društvu B. Zanima nas torej katere osebe so bile v nekem obdobju članice obeh društev ter v katerem obdobju je to bilo.
Podatki iz prvega seznama – članstvo v društvu A:
Anica A. 1.12.2008 14.10.2010 Boris B. 23.12.2004 Cene C. 1.2.2009 31.5.2009 Danijela D. 1.6.2007 23.11.2008 Evgenija E. 15.2.2012 10.10.2012 Franc F. 18.2.2012 Gordana G. 7.1.2011 10.10.2012
Podatki iz drugega seznama – članstvo v društvu B:
Anica A. 1.6.2008 3.3.2009 Cene C. 15.2.2010 Danijela D. 20.11.2008 15.6.2011 Evgenija E. 15.2.2012 Hinko H. 14.12.2004 31.10.2007 Ivana I. 21.2.2005 13.3.2006 Jože J. 1.7.2012 14.3.2013
Podatke, ki se nahajajo v dveh “tab-delimited” tekstovnih datotekah (drustvoA.txt ter drustvoB.txt) najprej uvozimo v PostgreSQL. Še prej pa ustvarimo novo bazo clanstvo.
createdb clanstvo psql clanstvo
Sedaj podatke uvozimo v bazo podatkov.
create table drustvo_a (oseba text, vstop text, izstop text); copy drustvo_a (oseba, vstop, izstop) from '/home/matej/dataimport/drustvoA.txt' with csv header delimiter E'\t';
create table drustvo_b (oseba text, vstop text, izstop text); copy drustvo_b (oseba, vstop, izstop) from '/home/matej/dataimport/drustvoB.txt' with csv header delimiter E'\t';
Podatki se sedaj nahajajo v dveh tabelah (drustvo_a in drustvo_b), datumi pa so zapisani v tekstovni obliki. Zato sedaj ustvarimo novi spremenljivki (datum_vstopa ter datum_izstopa) in sicer v obliki datum (date). Ustvarimo še dodatno spremenljivko trajanje_clanstva, ki je tipa časovno obdobje (daterange). Spremeljivko trajanje_clanstva nastavimo tako, da kjer datum izstopa manjka, kot konec obdobja lahko navedemo nek datum daleč v prihodnosti (npr. 31. 12. 9999) ali pa uporabimo poseben datumski tip infinity:
alter table drustvo_a add column datum_vstopa date;
alter table drustvo_a add column datum_izstopa date;
update drustvo_a set datum_vstopa = regexp_replace(vstop, E'^(\\d?\\d)\.(\\d?\\d)\.(\\d\\d\\d\\d)', E'\\3-\\2-\\1')::timestamp;
update drustvo_a set datum_izstopa = regexp_replace(izstop, E'^(\\d?\\d)\.(\\d?\\d)\.(\\d\\d\\d\\d)', E'\\3-\\2-\\1')::timestamp;
alter table drustvo_a add column trajanje_clanstva daterange;
update drustvo_a set trajanje_clanstva = ('[' || datum_vstopa || ',' || case when datum_izstopa is null then 'infinity' else datum_izstopa end || ']')::daterange;
Isto naredimo tudi za tabelo drustvo_b.
Datumsko obdobje (daterange) je lahko odprtega ali zaprtega tipa.
Odprt tip označujemo z navadnim oklepajem in ne vključuje datumske meje. Obdobje “(2008-12-01,2008-15-01)” torej ne vključuje datumov 2008-12-01 ter 2008-15-01, pač pa samo 2008-13-01 in 2008-14-01.
Zaprt tip, ki vključuje datumsko mejo pa označujemo z zaprtim oklepajem. Obdobje “[2008-12-01,2008-15-01]” torej vključuje vse dni, vključno z 2008-12-01 in 2008-15-01. Datumski interval je seveda lahko tudi “mešan” (npr. “[2008-12-01,2008-15-01)“).
Izpis ustvarjenih tabel je sedaj torej naslednji:
List of relations Schema | Name | Type | Owner -------+-----------+-------+------- public | drustvo_a | table | root public | drustvo_b | table | root (2 rows)
Struktura tabele drustvo_a pa:
Table "public.drustvo_a"
Column | Type | Modifiers
------------------+-----------+-----------
oseba | text |
vstop | text |
izstop | text |
datum_vstopa | date |
datum_izstopa | date |
trajanje_clanstva | daterange |
Mimogrede, ker v podatkih v praksi pogosto prihaja do napak, se splača preveriti koliko je primerov, ko je datum izstopa naveden pred datumom vstopa. V kolikor naletimo na take primere, se je potrebno odločiti kaj bomo naredili z njimi – lahko jih popravimo, lahko jih izbrišemo oz. ne upoštevamo, itd., vsekakor pa se takšne in podobne logične kontrole splača izvesti.
Podatke iz prve tabele izpišemo:
select * from drustvo_a;
oseba | vstop | izstop | datum_vstopa | datum_izstopa | trajanje_clanstva -------------+------------+------------+--------------+---------------+------------------------- Anica A. | 1.12.2008 | 14.10.2010 | 2008-12-01 | 2010-10-14 | [2008-12-01,2010-10-15) Boris B. | 23.12.2004 | | 2004-12-23 | | [2004-12-23,infinity) Cene C. | 1.2.2009 | 31.5.2009 | 2009-02-01 | 2009-05-31 | [2009-02-01,2009-06-01) Danijela D. | 1.6.2007 | 23.11.2008 | 2007-06-01 | 2008-11-23 | [2007-06-01,2008-11-24) Evgenija E. | 15.2.2012 | 10.10.2012 | 2012-02-15 | 2012-10-10 | [2012-02-15,2012-10-11) Franc F. | 18.2.2012 | | 2012-02-18 | | [2012-02-18,infinity) Gordana G. | 7.1.2011 | 10.10.2012 | 2011-01-07 | 2012-10-10 | [2011-01-07,2012-10-11)
Iz izpisa je razvidno, da PostgreSQL izpis datumskega obdobja prilagodi tako, da je spodnja meja intervala zaprta, zgornja pa odprta.
Npr. pri osebi Danijela D. smo imeli naslednje podatke: datum vstopa: 1.6.2007, datum izstopa: 23.11.2008, interval pa je bil zaprt na obeh straneh. Na izpisu pa je prikazano obdobje “[2007-06-01,2008-11-24)“, ki pomeni od vključno 2007-06-01 do 2008-11-24 (ki pa ni vključen).
Sedaj sledi pregled obdobij prekrivanja:
select * from drustvo_a, drustvo_b where (drustvo_a.oseba = drustvo_b.oseba) and (drustvo_a.trajanje_clanstva && drustvo_b.trajanje_clanstva);
Kot je razvidno, smo pri izpisu podatkov iz obeh tabel dali pogoj, da se podatki povežejo tako, da se bo v obeh tabelah ujemalo ime osebe (drustvo_a.oseba = drustvo_b.oseba) ter da se bo obdobje članstva v društvu A in v društvu B prekrivalo (drustvo_a.trajanje_clanstva && drustvo_b.trajanje_clanstva). Kot je razvidno, smo pri tem uporabili operator &&, ki poišče prekrivanje (ang. overlap).
Ukaz vrne naslednji izpis:
oseba | vstop | izstop | datum_vstopa | datum_izstopa | trajanje_clanstva | oseba | vstop | izstop | datum_vstopa | datum_izstopa | trajanje_clanstva -------------+-----------+------------+--------------+---------------+-------------------------+-------------+------------+-----------+--------------+---------------+------------------------- Anica A. | 1.12.2008 | 14.10.2010 | 2008-12-01 | 2010-10-14 | [2008-12-01,2010-10-15) | Anica A. | 1.6.2008 | 3.3.2009 | 2008-06-01 | 2009-03-03 | [2008-06-01,2009-03-04) Danijela D. | 1.6.2007 | 23.11.2008 | 2007-06-01 | 2008-11-23 | [2007-06-01,2008-11-24) | Danijela D. | 20.11.2008 | 15.6.2011 | 2008-11-20 | 2011-06-15 | [2008-11-20,2011-06-16) Evgenija E. | 15.2.2012 | 10.10.2012 | 2012-02-15 | 2012-10-10 | [2012-02-15,2012-10-11) | Evgenija E. | 15.2.2012 | | 2012-02-15 | | [2012-02-15,infinity)
Izpis je seveda precej nepregleden, zato ga nekoliko popravimo:
select drustvo_a.oseba as ime_osebe, drustvo_a.trajanje_clanstva * drustvo_b.trajanje_clanstva as obdobje_prekrivanja, upper(drustvo_a.trajanje_clanstva * drustvo_b.trajanje_clanstva) - lower(drustvo_a.trajanje_clanstva * drustvo_b.trajanje_clanstva) as dni_prekrivanja from drustvo_a, drustvo_b where (drustvo_a.oseba = drustvo_b.oseba) and (drustvo_a.trajanje_clanstva && drustvo_b.trajanje_clanstva);
Na tem izpisu pa se sedaj prikažejo imena oseb, ki so bile hkrati včlanjeni v obe društvi, obdobje prekrivanja članstva v obeh društvih ter koliko dni je trajalo to prekrivanje. Za izpis obdobja prekrivanja smo uporabili operator * , ki poišče presek obeh časovnih obdobij (ang. intersection):
ime_osebe | obdobje_prekrivanja | dni_prekrivanja -------------+-------------------------+----------------- Anica A. | [2008-12-01,2009-03-04) | 93 Danijela D. | [2008-11-20,2008-11-24) | 4 Evgenija E. | [2012-02-15,2012-10-11) | 239
Izpis lahko še malce prilagodimo:
select drustvo_a.oseba as ime_osebe, drustvo_a.trajanje_clanstva as clanstvo_v_drustvu_a, drustvo_b.trajanje_clanstva as clanstvo_v_drustvu_a, drustvo_a.trajanje_clanstva * drustvo_b.trajanje_clanstva as obdobje_prekrivanja, upper(drustvo_a.trajanje_clanstva * drustvo_b.trajanje_clanstva) - lower(drustvo_a.trajanje_clanstva * drustvo_b.trajanje_clanstva) as dni_prekrivanja from drustvo_a, drustvo_b where (drustvo_a.oseba = drustvo_b.oseba) and (drustvo_a.trajanje_clanstva && drustvo_b.trajanje_clanstva) order by dni_prekrivanja desc;
V tem primeru sta poleg prej omenjenih podatkov izpisana še obdobja članstva v prvem in drugem društvu, zapisi pa so sortirani po številu dni prekrivanja:
ime_osebe | clanstvo_v_drustvu_a | clanstvo_v_drustvu_a | obdobje_prekrivanja | dni_prekrivanja -------------+-------------------------+-------------------------+-------------------------+----------------- Evgenija E. | [2012-02-15,2012-10-11) | [2012-02-15,infinity) | [2012-02-15,2012-10-11) | 239 Anica A. | [2008-12-01,2010-10-15) | [2008-06-01,2009-03-04) | [2008-12-01,2009-03-04) | 93 Danijela D. | [2007-06-01,2008-11-24) | [2008-11-20,2011-06-16) | [2008-11-20,2008-11-24) | 4
Podatke seveda lahko izpišemo v CSV ali tab-delimited datoteko ter nato prenesemo v program za delo s preglednicami podatkov (npr. LibreOffice Calc ali Excel) kjer nato izrišemo grafe, itd..
Kot smo lahko videli na podlagi predstavljenega primera, je uporaba časovnih funkcij v PostgreSQL zelo enostavna, z njimi pa je mogoče opraviti zelo kompleksne – in v praksi tudi zelo uporabne – analize. In seveda – PostgreSQL ne poklekne niti, če imamo v bazi velike količine zapisov.
Ključne besede: baze podatkov, PostgreSQL