LINUXSOFT.cz Přeskoč levou lištu
Uživatel: Heslo:  
   CZUKPL

> PostgreSQL (8) - SELECT II.

Další možnosti výběru dat z PostgreSQL, tentokráte se zaměřením na funkce pro zpracování dat a operace s daty z výběru.

4.4.2005 06:00 | MaReK Olšavský | Články autora | přečteno 19742×

Komerční sdělení: Pořádáme Kurzy PgSQL

Při výběru dat z tabulky je efektivnější a rychlejší zpracovat pokud možno co největší část dat zpracovat ihned na straně PostgreSQL serveru. Pro zpracování číselných dat jsou k dispozici aritmetické operace a funkce. Funkce a operátory lze kombinovat a zanořovat stejně, jako v každém programovacím jazyce, jediným rozdílem je, že se výsledek nepřiřazuje do proměnné. Název, pod kterým bude výsledná hodnota vrácena lze určit klíčovým slovem AS.

Syntaxe zápisu operátorů a funkcí je uvedena níže. Na místě proměnných mohou být hodnoty vybrané z tabulky (tj. názvy sloupců), ale i konstanty zadané SQL dotazu a funkce nezávislé na datech (aktuální datum/čas, náhodné číslo,...).

SELECT a operator b AS jmeno ...
SELECT a oper1 (b oper2 c) AS jmeno ...
SELECT fce1(a) AS jmeno ...
SELECT fce2(a oper1 fce2(b)) AS jmeno ...

Operace s číselnými daty

Operace s číselnými hodnotami jsou standardní,pro celá čísla jsou k dispozici logické operace. Níže uvedená tabulka uvádí základní aritmetické operátory.

operátor popis příklad
+ součet
      SELECT 2 + 5 AS soucet;
      soucet
      ------
      7
    
- rozdíl
      SELECT 8 - 3 AS rozdil
      rozdil
      ------
      5
    
* součin
      SELECT 3 * 5 AS soucin
      soucin
      ------
      15
    
/ podíl, jestliže jsou obě čísla celá, jsou desetinná místa oříznuta
      SELECT (25.0 / 2.0) AS podil1, 25 / 2 AS podil2;
      soucin1 | soucin2
      -----------------
      12.5    | 12
    
% modulo, zbytek celočíselného dělení
      SELECT 17 % 5 AS zbytek;
      zbytek
      ------
      2
    
^ umocnění
      SELECT 2^5 AS umocneni
      umocneni
      --------
      32
    
|/ druhá odmocnina
      SELECT |/16 AS odmocnina2;
      odmocnina2
      ----------
      4
    
||/ třetí odmocnina
      SELECT ||/64.0 AS odmocnina3;
      odmocnina3
      ----------
      4
    
! faktoriál (přípona)
      SELECT 5! as fakt;
      fakt
      ----
      120
    
!! faktoriál (předpona)
      SELECT !!6 as fakt;
      fakt
      ----
      720
    
@ absolutní hodnota
      SELECT @(-3 - 5) AS abs;
      abs
      ---
      8
    
& bitový součin
      SELECT 12 & 5 AS logsoucin;
      logsoucin
      ---------
      4
    
| bitový součet
      SELECT 12 | 5 AS logcoucet;
      logsoucet
      ---------
      13
    
# exclusivní bitový součet
      SELECT 5#4 AS xor1, 12#16 AS xor2, 21#42 as xor3;
       xor1 | xor2 | xor3 
       -----+------+------
          1 |   28 |   63
    
~ bitová negace
      SELECT ~17 AS negace;
      negace
      ------
      -18
    
<< bitový posun doleva
(za operátorem se uvádí o kolik bitů je posun)
      SELECT 1<<5 AS lposun;
      lposun
      ------
      32
    
>> bitový posun doprava
(za operátorem se uvádí o kolik bitů je posun)
      SELECT 132>>2 AS pposun;
      pposun
      ------
      33
    

Exklusivní bitového součet (XOR) je definová tak, že jsou-li na stejné pozici (míněno stejném bitu) stejné hodnoty, je výsledkem v tomto bitu 0, liší-li se tyto hodnoty, tj jedna je 0 a druhá je jedna, je výsledkem tohoto bitového součtu hodnota 1. Tento operátor se používá často v hrách a při šifrování.

Kromě běžných aritmetických přistupují i operátory pro práci s binárními řetězci (BIT, BIT VARYING). Binární řetězce pro operace &, # a | musí mít stejnou délku, bitové posuny zachovávají délku původního řetězce a neuchovávají bit, který "přetekl". Pro upřesnění toho, že se jedná i bitová pole lze dát před konstantu prefix "B", toto je podobné, jako v jazyce C/C++. Níže jsou uvedeny již jen příklady bez vysvětlování významu operátorů.

SELECT B'10101' & B'10011' AS bitsoucin;
SELECT B'10101' | B'10011' AS bitsoucet;
SELECT B'10101' # B'10011' AS bitxor;
SELECT ~B'10101' AS bitnot;
SELECT B'10101' << 2 AS lposun;
SELECT B'10011' >> 3 AS pposun;

Číselné funkce

Funkce pro práci s desetinnými čísly (přesnost double precision) jsou v PostgreSQL implementovány pomocí knihoven na hostitelském systému jejich přesnost je proto ne tomto do značné míry závislá. Tzn., že ne 64 bitovém systému by měla být vyšší přesnost, než na 32 bitovém. Některé z operátorů jsou ekvivalentní níže uvedeným funkcím.

V níže uvedém seznamu funkcí je použito označení [dp], pokud je výstupní hodnota typu double precission, nebo je vyžadován vstup tohoto typu. Jestliže je návratová hodnota funkce stejného typu, jako vstupní hodnota, je uvedena zkratka [st], případně bude uveden jiný typ, jako je numeric, decimal...

funkce návratový typ popis příklad
abs(x) [st] absolutní hodnota SELECT abs(-5) AS abshodn;
cbrt([dp]) [dp] druhá odmocnina SELECT cbrt(15) AS odm2
ceil([dp])
ceil(numeric)
[st] nejmenší celé číslo, ne menší než vstup SELECT ceil(25.3) AS ceil1;
SELECT ceil(-25.3) AS ceil2;
degrees([dp]) [dp] převod radiánů na stupně SELECT degrees(.25) AS uhelstupne;
exp([dp])
exp(numeric)
[st] přirozená mocnina (ekvivalentní e^x) SELECT exp(7.3) AS exphodh;
floor([dp])
floor(numeric)
[st] největší celé číslo, ne větší než vstup SELECT floor(4.8) AS floor1;
SELECT floor(-4.8) AS floor2;
ln([dp])
ln(numeric)
[st] přirozený logaritmus SELECT ln(677.0) AS prirlog;
log([dp])
log(numeric)
[st] logaritmus při základu 10 (deset, ne 2 ve dvojkové soustavě) SELECT log(15) AS desetlog;
log(a,b)
a, b typu numeric
numeric logaritmus čísla a při základu b SELECT log(15,5) AS baselogar;
mod(a,b) [st] zbytek celočíselného dělení a/b SELECT mod(15,4) AS zbytek;
pi() [dp] hodnota pí SELECT pi();
pow(a,b)
a,b jsou [dp], případně numeric
[dp], případně numeric umocnění a na b (a^b) SELECT pow(3,2.5);
radians([dp]) [dp] převod velikosti úhlu z radiánů na stupně SELECT radians(90);
random() [dp] Náhodné číslo v rozsahu 0.0-1.0 SELECT 5*random();
round([dp])
round(numeric)
[st] Zaokrouhlení SELECT round(3.45);
round(a,b)
a,b typu [dp], nebo numeric
numeric zaokrouhlení čísla a na b desetinných míst SELECT round(3.5527,3);
sign([dp])
sign(numeric)
[st] znaménko čísla, -1 je-li záporné, 0 je-li O, 1 je-li kladné SELECT sign(-3.5);
sqrt([dp])
sqrt(numeric)
[st] druhá odmocnina čísla SELECT sqrt(.64);
trunc([dp])
trunc(numeric)
[st] oříznutí o desetinnou část SELECT trunc(2.99);
trunc(a,b)
a typu numeric, b je typu integer
numeric oříznutí a na b desetinných míst SELECT trunc(3.1179,2);
acos([dp]) [dp] arcus cosinus (inverzní fce ke cos) SELECT acos(-.022);
asin([dp]) arcus sinus (inverzní funkce k sin) SELECT asin(0.33)
atan([dp]) arcus tangens SELECT atan(.5);
atan2(a,b) arcus tangens z a/b SELECT atan2(3,4)
cos(a) cosinus z úhlu a uvedeného v radiánech SELECT cos(3.1);
cot(a) cotangens úhlu a SELECT cot(6.0);
sin(a) sinus úhlu a SELECT sin(1.57);
tan(a) tangens úhlu a SELECT tan(2.0)

Agregační funkce

Získaná data z databáze lze zpracovat též pomocí agregačních funkcí, které hodnoty z několika řádků zpracují do jedné, používají se například při sdružování dat do skupin pomocí GROUP BY a výběru pomocí HAVING.

Přehled agregačních funkcí je v níže uvedené tabulce. Příklady a vysvětlení možností při práci s agregačními funkcemi jsou za přehledovou tabulkou. Výrazem v parametru může být pouze název sloupce, případně pomocí aritmetických operátorů a funkcí zpracovaný obsah více sloupců.

funkce typ vstupního argumentu typ výstupní hodnoty popis
avg(výraz) smallint, integer, bigint, real, double precision, numeric, interval numeric pro celočíselný vstup, double precision pro ostatní aritmetický průměr ze vstupních hodnot
count(*)   bigint počet vstupních hodnot (řádků při výběru/ve skupině)
count(výraz) libovolný bigint počet vstupních hodnot, které odpovídají zadanému výrazu
max(výraz) číslo, řetězec, datum/čas stejný jako vstup maximální hodnota ze vstupu
min(výraz) číslo, řetězec, datum/čas stejný jako vstup minimální hodnota ze vstupu
stddev(výraz) smallint, integer, bigint, real, double precision, numeric double precision pro desetinná čísla, numeric pro ostatní průměrná odchylka ze vstupu
sum(výraz) smallint, integer, bigint, real, double precision, numeric, interval bigint pro smallint a integer, numeric pro bigint, double precision pro desetinná čísla a ostatní se vrací ve stejném typu, jako byl vstup součet všech hodnot ze vstupu
variance(výraz) smallint, integer, bigint, real, double precision, numeric double precision pro reálná čísla, numeric pro ostatní vážená odchylka (druhá mocnina průměrné odchylky)

Příklady na použití agregačních funkcí:

SELECT avg(unitprice), id_category, count(*) FROM products GROUP BY id_category;
         avg          | id_category | count 
----------------------+-------------+-------
 125.0000000000000000 |           3 |     2
  50.0000000000000000 |           2 |     2
  
SELECT sum(pieces*unitprice), id_category, stddev(unitprice),variance(unitprice) 
 FROM products GROUP BY id_category;
   sum   | id_category |       stddev        |       variance        
---------+-------------+---------------------+-----------------------
 2750.00 |           3 | 35.3553390593273762 | 1250.0000000000000000
  150.00 |           2 |                   0 |                     0

Závěr

Tento díl byl zaměřen na vyjmenování funkcí a operátorů nad čísly a binárními řetězci, společně s vyjmenováním agregačních funkcí, použitelných při výběru dat společně s jejich seskupováním pomocí GROUP BY, či omezování výběru pomocí agregačních funkcí použitím HAVING. Pro někoho mohl být tento díl trochu nudnější a připomínat přepis dokumentace, ale v takovýchto případech se příručka ani moc jinak uspořádat nedá. Bohužel ještě budou nejméně dva takovéto díly, protože zbývají funkce pro práci s řetězci, s datumem/časem, geometrickými a sítovými typy.

Verze pro tisk

pridej.cz

 

DISKUZE

Nejsou žádné diskuzní příspěvky u dané položky.



Příspívat do diskuze mohou pouze registrovaní uživatelé.
> Vyhledávání software
> Vyhledávání článků

15.5.2017 23:50 /František Kučera
Máš rád svobodný software a hardware nebo se o nich chceš něco dozvědět? Zajímá tě DIY, CNC, SDR nebo morseovka? Přijď na sraz spolku OpenAlt, který se bude konat ve čtvrtek 18. května od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5).
Přidat komentář

12.5.2017 16:42 /Honza Javorek
PyCon CZ, česká konference o programovacím jazyce Python, se po dvou úspěšných ročnících v Brně bude letos konat v Praze, a to 8. až 10. června. Na konferenci letos zavítá např. i Armin Ronacher, známý především jako autor frameworku Flask, šablon Jinja2/Twig, a dalších projektů. Těšit se můžete na přednášky o datové analytice, tvorbě webu, testování, tvorbě API, učení a mentorování programování, přednášky o rozvoji komunity, o použití Pythonu ve vědě nebo k ovládání nejrůznějších zařízení (MicroPython). Na vlastní prsty si můžete na workshopech vyzkoušet postavit Pythonem ovládaného robota, naučit se učit šestileté děti programovat, efektivně testovat nebo si v Pythonu pohrát s kartografickým materiálem. Kupujte lístky, dokud jsou.
Přidat komentář

2.5.2017 9:20 /Eva Rázgová
Putovní konference československé Drupal komunity "DrupalCamp Československo" se tentokrát koná 27. 5.2017 na VUT FIT v Brně. Můžete načerpat a vyměnit si zkušenosti z oblasti Drupalu 7 a 8, UX, SEO, managementu týmového vývoje, využití Dockeru pro Drupal a dalších. Vítáni jsou nováčci i experti. Akci pořádají Slovenská Drupal Asociácia a česká Asociace pro Drupal. Registrace na webu .
Přidat komentář

1.5.2017 20:31 /Pavel `Goldenfish' Kysilka
PR: 25.5.2017 proběhne v Praze konference na téma Firemní informační systémy. Hlavními tématy jsou: Informační systémy s vlastní inteligencí, efektivní práce s dokumenty, mobilní přístup k datům nebo využívání cloudu.
Přidat komentář

15.4.2017 15:20 /František Kučera
Máš rád svobodný software a hardware nebo se o nich chceš něco dozvědět? Zajímá tě IoT a radiokomunikace? Přijď na sraz spolku OpenAlt, který se bude konat ve středu 19. dubna od 18:30 v Šenkovně (Sokolská 60, Praha 2).
Přidat komentář

5.3.2017 19:12 /Redakce Linuxsoft.cz
PR: 23. března proběhne v Praze konferenci na téma Cloud computing v praxi. Hlavními tématy jsou: Nejžhavější trendy v oblasti cloudu a cloudových řešení, Moderní cloudové služby, Infrastruktura současných cloudů, Efektivní využití cloudu, Nástrahy cloudových řešení a jak se jim vyhnout.
Přidat komentář

27.2.2017 22:12 /František Kučera
Pozvánka na 137. sraz OpenAlt – Praha: Tentokrát jsme si pro vás připravili neobvyklou akci. Ve středu 1.3. v 17:30 nás přivítá sdružení CZ.NIC ve svých prostorách v Milešovské ulici číslo 5 na Praze 3, kde si pro nás připravili krátkou prezentaci jejich činnosti. Následně navštívíme jejich datacentrum pod Žižkovskou věží. Provedou nás prostory, které jsou běžnému smrtelníkovi nedostupné!
Po ukončení prohlídky se všchni odebereme do hostince U vodoucha, Jagelonská 21, Praha 3 pochutnat si na některém z vybraných piv či dát si něco na zub. Rezervaci máme od 19:30, heslo je OpenAlt.
Ale pozor! Do prostor datového centra máme omezený přístup, dostane se tam pouze 10 lidí! Takže kdo přijde dříve, ten má přednost, a občanky s sebou! Kdo nebude chtít na prohlídku datového centra, může se pomalu přesunout do hostince U vodoucha a u nepřeberné nabídky piv počkat na ostatní.
Přidat komentář

18.1.2017 0:49 /František Kučera
Členové a příznivci spolku OpenAlt se pravidelně schází v Praze a Brně. Fotky z pražských srazů za uplynulý rok si můžete prohlédnout na stránkách spolku. Příští sraz se koná už 19. ledna – tentokrát je tématem ergonomie ovládání počítače – tzn. klávesnice, myši a další zařízení. Také budete mít příležitost si prohlédnout pražský hackerspace Brmlab.
Přidat komentář

   Více ...   Přidat zprávičku

> Poslední diskuze

26.7.2017 21:12 / Jaromir Obr
Podminka

15.6.2017 9:34 / Ondřej Havlas
php,

10.6.2017 10:39 / Temple
sell home for cash

11.5.2017 23:32 / lelo
Re: Problém se správcem balíčků

11.5.2017 5:45 / davd mašek
Re: Problém se správcem balíčků

Více ...

ISSN 1801-3805 | Provozovatel: Pavel Kysilka, IČ: 72868490 (2003-2017) | mail at linuxsoft dot cz | Design: www.megadesign.cz | Textová verze