ARCHIV

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 24091×

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.

      SELECT 2 + 5 AS soucet;
      soucet
      ------
      7
    

      SELECT 8 - 3 AS rozdil
      rozdil
      ------
      5
    

      SELECT 3 * 5 AS soucin
      soucin
      ------
      15
    

      SELECT (25.0 / 2.0) AS podil1, 25 / 2 AS podil2;
      soucin1 | soucin2
      -----------------
      12.5    | 12
    

      SELECT 17 % 5 AS zbytek;
      zbytek
      ------
      2
    

      SELECT 2^5 AS umocneni
      umocneni
      --------
      32
    

      SELECT |/16 AS odmocnina2;
      odmocnina2
      ----------
      4
    

      SELECT ||/64.0 AS odmocnina3;
      odmocnina3
      ----------
      4
    

      SELECT 5! as fakt;
      fakt
      ----
      120
    

      SELECT !!6 as fakt;
      fakt
      ----
      720
    

      SELECT @(-3 - 5) AS abs;
      abs
      ---
      8
    

      SELECT 12 & 5 AS logsoucin;
      logsoucin
      ---------
      4
    

      SELECT 12 | 5 AS logcoucet;
      logsoucet
      ---------
      13
    

      SELECT 5#4 AS xor1, 12#16 AS xor2, 21#42 as xor3;
       xor1 | xor2 | xor3 
       -----+------+------
          1 |   28 |   63
    

      SELECT ~17 AS negace;
      negace
      ------
      -18
    

      SELECT 1<<5 AS lposun;
      lposun
      ------
      32
    

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

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

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