Autor, Název akce
Databázové systémy a SQL
Lekce 6 – Pokročilé konstrukce SQL
Daniel Klimeš

Autor, Název akce
“Nadstandardní“ SQL
Daniel Klimeš, Databázové systémy a SQL
Ø Určování pořadí záznamů
Ø Hodnoty předchozích a následujících řádků
Ø
Ø Rozšířené agregace
Ø Výpočet procent
Ø Parciální agregace
Ø Kumulativní součet
Ø Klouzavý průměr
… OVER (PARTITION BY sloupec ORDER BY sloupec)
Rozšíření SQL o

Autor, Název akce
Ranking functions – číslování řádků
Daniel Klimeš, Databázové systémy a SQL
RANK, DENSE_RANK, ROW_NUMBER
• RANK( ) OVER ([PARTITION BY sex] ORDER BY date_of_birth DESC)
• RANK( ) OVER (ORDER BY date_of_birth DESC NULLS LAST)
• Není možné používat za WHERE a HAVING  - nutné zanoření
   Can not be used after WHERE and HAVING as condition
Sloupec X
RANK
DENSE_RANK
ROW_NUMBER
100
1
1
1
200
2
2
2
200
2
2
3
300
4
3
4
400
5
4
5

Autor, Název akce
Ranking function
Daniel Klimeš, Databázové systémy a SQL
SELECT patient_id, sex, date_of_birth,
RANK( ) OVER (PARTITION BY sex ORDER BY date_of_birth DESC NULLS LAST),
DENSE_RANK( ) OVER (PARTITION BY sex ORDER BY date_of_birth DESC NULLS LAST),
ROW_NUMBER( ) OVER (PARTITION BY sex ORDER BY date_of_birth DESC NULLS LAST)
FROM patients LIMIT 100
Příklad:
SELECT * FROM (
  SELECT patient_id, sex, date_of_birth,
  RANK( ) OVER (PARTITION BY sex ORDER BY date_of_birth DESC NULLS LAST) poradi
  FROM patients) x
WHERE poradi < 10
Využití v sekci WHERE – nutné zapouzdření
… Další varianta  hledání nejstaršího studenta

Autor, Název akce
LAG, LEAD
Daniel Klimeš, Databázové systémy a SQL
• LAG (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
• LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
SELECT study_id, TO_CHAR (date_of_enrollment, 'yyyy'), COUNT(*) letos,
LAG(COUNT(*),1,’0’) OVER(PARTITION BY study_id
        ORDER BY TO_CHAR (date_of_enrollment, 'yyyy') ) loni
FROM patient_study
GROUP BY study_id, TO_CHAR (date_of_enrollment, 'yyyy')
ORDER BY study_id, TO_CHAR (date_of_enrollment, 'yyyy')
• LAG = hodnota z předchozího řádku / previous row value
• LEAD = hodnota z následujícího řádku / next row value
Pozn. POSTGRESQL 9.1: LAG(COUNT(*),1, '0')

Autor, Název akce
Reportovací „window“ funkce
Daniel Klimeš, Databázové systémy a SQL
SELECT studies, COUNT(*) FROM student
GROUP BY studies
SELECT studies, COUNT(*) pocet , COUNT(*) * 100.0/(SELECT COUNT(*) FROM student) procento FROM
student
GROUP BY studies
SELECT  COUNT(*) FROM student
SELECT studies, COUNT(*) pocet, COUNT(*) *100.0 / SUM(COUNT(*)) OVER () procento FROM student
GROUP BY studies
Agregační funkce s procentickým vyjádřením
Procentické zastoupení – standardní SQL:
Analytická funkce

Autor, Název akce
Window and reporting function
Daniel Klimeš, Databázové systémy a SQL
SELECT study_id, COUNT(*),
COUNT(*) / SUM(COUNT(*)) OVER () * 100 procento
FROM patient_study
GROUP BY study_id
Analytická funkce
SELECT study_id, COUNT(*),
COUNT(*) * 100.0 / (SELECT COUNT(*) FROM patient_study) procento
FROM patient_study
GROUP BY study_id
Procentické zastoupení – standardní SQL:

Autor, Název akce
Parciální součty
Daniel Klimeš, Databázové systémy a SQL
UPDATE student SET completiontype= 'Z'
WHERE mod(uco,2) = 1 – Rozdělení datového souboru
SELECT completiontype, studies,  count(*) pocet, COUNT(*) *100.0 / SUM(COUNT(*)) OVER () procento
FROM student
GROUP BY completiontype, studies
ORDER BY completiontype
SELECT completiontype, studies,  count(*) pocet, COUNT(*) *100.0 / SUM(COUNT(*)) OVER () procento ,
COUNT(*) *100.0 / SUM(COUNT(*)) OVER (PARTITION BY completiontype) proc_podskupiny
FROM student
GROUP BY completiontype, studies
ORDER BY completiontype

Autor, Název akce
Parciální součty
Daniel Klimeš, Databázové systémy a SQL
SELECT study_id, study_site, COUNT(*),
COUNT(*) /  SUM(COUNT(*)) OVER (PARTITION BY study_id) * 100  procento
FROM patient_study
GROUP BY study_id, study_site
Procentické zastoupení pracovišť (počtu jejich pacientů)  v jednotlivých studiích

Autor, Název akce
Kumulativní součet
Daniel Klimeš, Databázové systémy a SQL
SELECT studies, COUNT(*) pocet  FROM student
GROUP BY studies
SELECT studies, COUNT(*) pocet, SUM(COUNT(*)) OVER (ORDER BY studies)  FROM student
GROUP BY studies
SELECT sex, studies, COUNT(*) pocet,
SUM(COUNT(*)) OVER (PARTITION BY sex ORDER BY studies) kumulace_skupina,
SUM(COUNT(*)) OVER (ORDER BY sex, studies) kumulace_celkem  FROM student
GROUP BY sex, studies
ORDER BY sex, studies

Autor, Název akce
Klouzavý průměr
Daniel Klimeš, Databázové systémy a SQL
• UNBOUNDED PRECEDING
• UNBOUNDED FOLLOWING
• CURRENT ROW
• počet řádků PRECEDING
• počet řádků FOLLOWING
AVG(sloupec) OVER
(ORDER BY sloupec ROWS BETWEEN x PRECEDING AND CURRENT ROW)
•ROWS BETWEEN
CREATE TABLE pocet_pacientu as
SELECT TO_CHAR(date_of_enrollment, 'yyyy-mm') mesic, COUNT(*) pocet FROM patient_study
WHERE date_of_enrollment >= '2004-01-01'
GROUP BY TO_CHAR(date_of_enrollment, 'yyyy-mm')
ORDER BY TO_CHAR(date_of_enrollment, 'yyyy-mm')

Autor, Název akce
Klouzavý průměr
Daniel Klimeš, Databázové systémy a SQL
SELECT * FROM pocet_pacientu
ORDER BY mesic
SELECT mesic, pocet,
ROUND(AVG(pocet) OVER (ORDER BY mesic ROWS BETWEEN 3 PRECEDING AND CURRENT ROW),1) klouzavy_prumer
FROM pocet_pacientu
SELECT AVG(pocet) FROM pocet_pacientu

Autor, Název akce
Cvičení/task
Spočítejte v tabulce pocet_pacientu
Compute on table pocet_pacientu
•Kumulativní počet pacientů
•Procento měsíčního počtu
•k celkovému počtu
•k maximálnímu počtu
•k průměrnému počtu
•k ročnímu průměru
•K předchozímu měsíci
•
•Klouzavý průměr za 2 uplynulé měsíce
•

Daniel Klimeš, Databázové systémy a SQL

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
SELECT mesic, pocet
FROM pocet_pacientu
ORDER BY mesic

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
SELECT mesic, pocet,
SUM(pocet) OVER (ORDER BY mesic) kumulativni_pocet,
SUM(pocet) OVER () suma,
MAX(pocet) OVER () maximum,
AVG(pocet) OVER () prumer,
AVG(pocet) OVER (PARTITION BY SUBSTR(mesic,1,4)) rocni_prumer,
LAG(pocet,1,'0') OVER (ORDER BY mesic) predchozi,
ROUND(AVG(pocet) OVER (ORDER BY mesic ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING),1) klouzavy_prumer
FROM pocet_pacientu
ORDER BY mesic

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
• Zanoření a dopočet procent
SELECT mesic, pocet,
pocet * 100/ suma suma_proc,
pocet * 100/ maximum max_proc,
pocet * 100 / predchozi predchozi_proc
FROM (
SELECT mesic, pocet,
SUM(pocet) OVER (ORDER BY mesic) kumulativni_pocet,
SUM(pocet) OVER () suma,
MAX(pocet) OVER () maximum,
AVG(pocet) OVER () prumer,
AVG(pocet) OVER (PARTITION BY SUBSTR(mesic,1,4)) rocni_prumer,
LAG(pocet,1,'0') OVER (ORDER BY mesic) predchozi,
ROUND(AVG(pocet) OVER (ORDER BY mesic ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING),1) klouzavy_prumer
FROM pocet_pacientu
) a
ORDER BY mesic

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
• Ošetření dělení nulou
SELECT mesic, pocet,
pocet * 100/ suma suma_proc,
pocet * 100/ maximum max_proc,
CASE WHEN predchozi > 0 THEN pocet * 100 / predchozi ELSE 0 END predchozi_proc
FROM (
….
) a

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
• Zobrazte kumulativní procentické zastoupení pacientů podle věku
• Věk, počet pacientů, kumulativní procento

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
• Zobrazte kumulativní procentické zastoupení pacientů podle věku
• Věk, počet pacientů, kumulativní procento
SELECT EXTRACT (YEAR FROM AGE(date_of_birth))
FROM patients limit 100
SELECT vek, COUNT(*) FROM (
   SELECT EXTRACT (YEAR FROM AGE(date_of_birth)) vek
   FROM patients) a
WHERE vek > 0 and vek < 100
GROUP BY vek
ORDER BY vek

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
SELECT vek, pocet, kum_pocet * 100 / pocet_celkem kum_procento
FROM (
  SELECT vek, COUNT(*) pocet, SUM(COUNT(*)) OVER (ORDER BY VEK)
    kum_pocet, SUM(COUNT(*)) OVER () pocet_celkem
  FROM (
    SELECT EXTRACT (YEAR FROM AGE(date_of_birth)) vek
    FROM patients) a
  WHERE vek > 0 and vek < 100
  GROUP BY vek
  ORDER BY vek
) b

Autor, Název akce
Another window functions
Daniel Klimeš, Databázové systémy a SQL
• FIRST_VALUE (sloupec)
• LAST_VALUE (sloupec)
• NTH_VALUE (sloupec, poradi)
Srovnání s únorovou hodnotou daného roku
SELECT mesic, pocet,
NTH_VALUE(pocet,2) OVER (PARTITION BY SUBSTR(mesic,1,4)) unor_rok
FROM pocet_pacientu

Autor, Název akce
Other aggregate functions
Daniel Klimeš, Databázové systémy a SQL
https://www.postgresql.org/docs/10/functions-aggregate.html
SELECT
percentile_cont(0.5) WITHIN GROUP (ORDER BY a),
percentile_disc(0.5) WITHIN GROUP (ORDER BY a)
FROM (
  SELECT a FROM generate_series(1,5) a
) x
Medián