Warning
This page is located in archive. Go to the latest version of this course pages. Go the latest version of this page.

SQL

Připojení k databázi:

např podle podle zadání nulté úlohy.

  • host: slon.felk.cvut.cz
  • port: 5432
  • username: izs18_SK
  • databáze: izs18_SK

Další příklady: https://cw.fel.cvut.cz/wiki/courses/a4b33ds/cviceni-4-5

Dotazování na data v SQL - SELECT

Cílem cvičení je ukázka použití SQL dotazu SELECT

Studnicí testovacích dat nám bude databáze navržená v minulém cvičení. Testovací databáze je přístupná přes konto 0. skupiny.

Model senzorové sítě

SQL - SELECT

Seznam senzorů a veličin

Vypište v lidské podobě seznam senzorů a veličin, které tyto senzory měří.==

  • 1) začneme s tabulkou Senzor
 SELECT
    *
  FROM "senzor";
  • 2) nyní k tabulce Senzor připojíme tabulku VelicinaSenzor. Minimální kardinatity jsou 1, tedy použijeme INNER JOIN. Vzhledem k tomu, že atribut cizího klíče id_senzor v tabulce VelicinaSenzor má stejné jméno jako atribut primárního klíče v tabulce Senzor, můžeme pro spojení tabulek použít verze s USING
 SELECT
    *
 FROM "senzor"
   JOIN "velicinasenzor" USING (id_senzor);
  • 3) analogickým způsobem připojíme k těmto dvou tabulkám ještě tabulku Velicina
 SELECT
    *
 FROM "senzor"
   JOIN "velicinasenzor" USING (id_senzor)
   JOIN "velicina" USING (id_velicina);
  • 4) nyní vybereme ty atributy, které poskytují člověku danou informaci. Vzhledem k tomu, že jak tabulka Senzor, tak tabulka Velicina mají atribut pojmenovaný nazev, je nutné jednotlivé atributy příslušející k daným tabulkám odlišit pomocí notace Tabulka.atribut
 SELECT
    "senzor".nazev,popis,
    "velicina".nazev,
    znacka || ' [' || CASE WHEN jednotka IS NULL THEN '-' ELSE jednotka END || ']'
 FROM "senzor"
   JOIN "velicinasenzor" USING (id_senzor)
   JOIN "velicina" USING (id_velicina);

Průměrné denní hodnoty měření

Vypište průměrné denní hodnoty naměřené senzorem s id_senzor = 3 a veličinu id_veličina = 2.

* 1) Začneme tabulkou měření

 SELECT
    *
  FROM "mereni";

* 2) Pomocí WHEREové podmínky specifikujeme záznamy, které nás zajímají

 SELECT
    *
  FROM "mereni"
  WHERE id_senzor = 3 AND id_velicina = 2;

* 2b) (Volitelně, pokud bychom chtěli zobrazit průběh, můžeme záznamy seřadit sestupně podle času)

 SELECT
    *
  FROM "mereni"
  WHERE id_senzor = 3 AND id_velicina = 2
  ORDER BY cas ASC;

* 3) Nyní přistoupíme k agregaci záznamů. Chceme denní průměry, tedy musíme získat z času pouze datum - funkce date(). Připravíme si záznamy pomocí

 SELECT
    date(cas), hodnota
  FROM "mereni"
  WHERE id_senzor = 3 AND id_velicina = 2;
  • 3b) a provedeme agregaci podle hodnoty atributu date(cas). Agregační funkcí - funkcí, které pole hodnot agreguje do jedné hodnoty - bude průměr - avg(). Agregaci provádíme pomocí GROUP BY. Nezapomeňte, že ve výčtu atributů nesmí být nic jiného než atributy, podle kterých se agreguje a agregační funkce.
 SELECT
    date(cas), avg(hodnota)
  FROM "mereni"
  WHERE id_senzor = 3 AND id_velicina = 2
  GROUP BY date(cas);
  • 4) S výhodou použijeme řazení podle času z bodu 2b a máme použitelný výsledek.
 SELECT
    date(cas), avg(hodnota)
  FROM "mereni"
  WHERE id_senzor = 3 AND id_velicina = 2
  GROUP BY date(cas)
  ORDER BY date(cas) ASC;

Průměrné hodnoty veličiny větší než limit

  • 1) SQL dotaz pro průměrné hodnoty veličiny pro daný senzor již máme, jen jej zobecníme pro všechny senzory měřící danou veličinu. Musíme podle toho upravit i hodnoty atributů, podle kterých budeme agregovat - dvojicice date(cas), id_senzor
 SELECT
    date(cas), id_senzor, avg(hodnota)
  FROM "mereni"
  WHERE id_velicina = 2
  GROUP BY date(cas), id_senzor
  ORDER BY date(cas) ASC, id_senzor;
  • 2) Specifikujeme podmínku na hodnotu agregační funkce, budeme požadovat, aby průměrná hodnota veličiny avg(hodnota)>20. Podmínky na agregační funkce se specifikují pomocí klausule HAVING
 SELECT
    date(cas), id_senzor, avg(hodnota)
  FROM "mereni"
  WHERE id_velicina = 2
  GROUP BY date(cas), id_senzor
  HAVING avg(hodnota)>2.1
  ORDER BY date(cas) ASC, id_senzor;

Pozor, nezaměňujte WHERE podmínky na atributy před agregací s HAVING podmínkami na hodnoty výsledků agregačních funkcí!!!

  • 3) Pokud bychom pozměnili zadání na požadavek, aby byly vypsány senzory s průměrnou hodnotou veličiny větší než je průměr přes všechny senzory (tedy různé agregace), je nutné použít tzv. vnořeného SELECTu - “předpočítat si hodnotu”. Průměr dané veličiny získáme pomocí
 SELECT
    avg(hodnota)
  FROM "mereni"
  WHERE id_velicina = 2;
  • 3b) a tento mezivýsledek dosadíme do původního SELECTu (syntakticky do závorek)
 SELECT
    date(cas), id_senzor, avg(hodnota)
  FROM "mereni"
  WHERE id_velicina = 2
  GROUP BY date(cas), id_senzor
  HAVING avg(hodnota)> ( SELECT avg(hodnota) FROM "mereni" WHERE id_velicina = 2 )
  ORDER BY date(cas) ASC, id_senzor;
  • 4) vnořeného SELECTu lze využít i v jiných častech, například namísto spojovaní tabulek
 SELECT
    date(cas), id_senzor, avg(hodnota) || (SELECT CASE
                                                     WHEN jednotka IS NULL THEN ''
                                                     ELSE jednotka END
                                                  FROM "velicina" WHERE "velicina".id_velicina = "mereni".id_velicina)
  FROM "mereni"
  WHERE id_velicina = 2
  GROUP BY date(cas), id_senzor, id_velicina
  HAVING avg(hodnota)> ( SELECT avg(hodnota) FROM "mereni" WHERE id_velicina = 2 )
  ORDER BY date(cas) ASC, id_senzor;

Další datové operace v SQL - SELECT

courses/a5m33izs/cviceni/05.txt · Last modified: 2018/03/20 11:14 by rimnacm