Autor, Název akce
Databázové systémy a SQL
Lekce 6
Daniel Klimeš

Autor, Název akce
Podmíněný výraz CASE
Daniel Klimeš, Databázové systémy a SQL
•CASE WHEN podminka THEN vysledek
WHEN podminka2 THEN vysledek 2
ELSE vysledek 3 END
•Až 127 WHEN,
•ELSE nepovinné,
•Vyhodnocování končí na první splněné podmínce
•Všechny výsledky musí být stejného datového typu
Příklad:
•SELECT    CASE WHEN vek IS NULL ‘neznamo’
     CASE WHEN vek < 30 THEN ‘kat < 30’
     CASE WHEN vek < 50 THEN ‘kat 30-49’
     CASE WHEN vek < 65 THEN ‘kat 50-64’
     ELSE  ‘kat 65 a starsi’ END
FROM pacienti
•

Autor, Název akce
Analytic SQL - ORACLE
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í
Ranking function – číslování řádků
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
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)
WHERE poradi < 10
Využití v sekci WHERE – nutné zapouzdření

Autor, Název akce
Windowing a 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(*) / (SELECT COUNT(*) FROM patient_study) * 100 procento
FROM patient_study
GROUP BY study_id
Procentické zastoupení – standardní 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
Mezisoučty

Autor, Název akce
Windowing a reporting function
Daniel Klimeš, Databázové systémy a SQL
SELECT study_id, COUNT(*),
  SUM(COUNT(*)) OVER (ORDER BY study_id
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
  / SUM(COUNT(*)) OVER () * 100
  kumul_procento
FROM patient_study
GROUP BY study_id
ORDER BY STUDY_ID
Kumulativní procentické zastoupení :
• UNBOUNDED PRECEDING
• UNBOUNDED FOLLOWING
• CURRENT ROW
• počet řádků PRECEDING
• počet řádků FOLLOWING
Klouzavý průměr:
AVG(COUNT(*)) OVER
(ORDER BY sloupec ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
•ROWS BETWEEN

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
• LEAD = hodnota z následujícího řádku

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
•Najděte 5 studií s nejvyšším průměrným měsíčním přírůstkem nových formulářů
•Nejprve připravte počty nových formulářů po měsících pro jednotlivé studie
(EVENT_HEADER. DATE_COLLECTED)
SELECT study_id, to_char(EH.DATE_COLLECTED, 'yyyy-mm'),
COUNT(*) FROM event_header eh
GROUP BY  study_id, to_char(EH.DATE_COLLECTED, 'yyyy-mm')
ORDER BY study_id, to_char(EH.DATE_COLLECTED, 'yyyy-mm')

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
SELECT study_id, to_char(EH.DATE_COLLECTED, 'yyyy-mm') mesic, COUNT(*),
AVG(COUNT(*)) OVER (PARTITION BY STUDY_ID) prumer
 FROM event_header eh
GROUP BY  study_id, to_char(EH.DATE_COLLECTED, 'yyyy-mm')
ORDER BY study_id, to_char(EH.DATE_COLLECTED, 'yyyy-mm')
Přidejte sloupec, který bude obsahovat průměrný počet nových formulářů
Zapouzdřete a vytvořte sloupec s pořadím podle průměru sestupně
SELECT study_id, MAX(prumer), RANK() OVER (ORDER BY MAX(prumer) DESC)
FROM (
SELECT study_id, to_char(EH.DATE_COLLECTED, 'yyyy-mm') mesic, COUNT(*),
AVG(COUNT(*)) OVER (PARTITION BY STUDY_ID) prumer
 FROM event_header eh
GROUP BY  study_id, to_char(EH.DATE_COLLECTED, 'yyyy-mm')
ORDER BY study_id, to_char(EH.DATE_COLLECTED, 'yyyy-mm'))
GROUP BY study_id
ORDER BY max(prumer) DESC

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
Vyberte jen prvních 5 záznamů
SELECT * FROM (
SELECT study_id, MAX(prumer),
RANK() OVER (ORDER BY MAX(prumer) DESC) poradi FROM (
SELECT study_id, to_char(EH.DATE_COLLECTED, 'yyyy-mm') mesic, COUNT(*),
AVG(COUNT(*)) OVER (PARTITION BY STUDY_ID) prumer
FROM event_header eh
GROUP BY  study_id, to_char(EH.DATE_COLLECTED, 'yyyy-mm')
ORDER BY study_id, to_char(EH.DATE_COLLECTED, 'yyyy-mm'))
GROUP BY study_id
ORDER BY max(prumer) DESC)
WHERE poradi <= 5
•Jaké je slabé místo uvedeného postupu?

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
Vypište kumulativní procentické zastoupení věku pacientek při diagnóze (po letech)
STUDY_ID = 169, Datum dg. QUESTION_ID=2646
•Nejprve spočítejte věk jednotlivých žen v době dg
SELECT p.patient_id, TRUNC(MONTHS_BETWEEN (ed.value, p.date_of_birth)/12)
FROM patients p, patient_study ps, event_header eh, event_subheader es, eav_date ed
WHERE p.patient_id = ps.patient_id and ps.study_id = 169
and eh.study_id = ps.study_id and EH.patient_id = ps.patient_id and
eh.header_uid = es.header_uid and es.subheader_id = ed.subheader_id
and ed.question_id = 2646 and p.test_patient = 0

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
SELECT vek, COUNT(*),
 SUM(COUNT(*)) OVER (ORDER BY vek ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) /
 SUM(COUNT(*)) OVER () * 100 kum_proc
FROM (
 SELECT p.patient_id, TRUNC(MONTHS_BETWEEN (ed.value, p.date_of_birth)/12) vek
 FROM patients p, patient_study ps, event_header eh, event_subheader es, eav_date ed
 WHERE p.patient_id = ps.patient_id and ps.study_id = 169
 and eh.study_id = ps.study_id and EH.patient_id = ps.patient_id and
 eh.header_uid = es.header_uid and es.subheader_id = ed.subheader_id
 and ed.question_id = 2646 and p.test_patient = 0 and
 TRUNC(MONTHS_BETWEEN (ed.value, p.date_of_birth)/12) between 1 and 100
)
GROUP BY vek
ORDER BY vek
•Seskupte podle věku a přidejte kumulativní procenta

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
Přidejte sloupec, který uvede rozdíl mezi hodnotou kumulativní četnosti aktuálního
věku s předchozím řádkem
SELECT vek, pocet, kum_proc -  LAG(kum_proc, 1) OVER (ORDER BY vek) narust
FROM (
 SELECT vek, COUNT(*) pocet,
  SUM(COUNT(*)) OVER (ORDER BY vek ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) /
  SUM(COUNT(*)) OVER () * 100 kum_proc
 FROM (
  SELECT p.patient_id, TRUNC(MONTHS_BETWEEN (ed.value, p.date_of_birth)/12) vek
  FROM patients p, patient_study ps, event_header eh, event_subheader es, eav_date ed
  WHERE p.patient_id = ps.patient_id and ps.study_id = 169
  and eh.study_id = ps.study_id and EH.patient_id = ps.patient_id and
  eh.header_uid = es.header_uid and es.subheader_id = ed.subheader_id
  and ed.question_id = 2646 and p.test_patient = 0 and
  TRUNC(MONTHS_BETWEEN (ed.value, p.date_of_birth)/12) between 1 and 100
 )
 GROUP BY vek
)
ORDER BY vek

Autor, Název akce
FIREBIRD – testovací databáze
Tabulka Pacienti
•ID
•Jmeno
•Datum_narozeni
•Pohlavi
Tabulka Vysetreni
•ID_vysetreni
•ID
•Datum_vysetreni
•Typ_vysetreni
• Vysledek
Minulé cvičení:
1.Vypište pro všechny pacienty výsledky vyšetření typu 2 a 9
ve tvaru ID, jmeno, vysledek typu 2, vysledek typu 9
1.
Daniel Klimeš, Databázové systémy a SQL
SELECT p.id, p.jmeno, v.vysledek as vysledek_metoda2, v2.vysledek as vysledek_metoda9
FROM pacienti p LEFT JOIN vysetreni v ON v.id = p.id AND v.typ_vysetreni = 2
LEFT JOIN vysetreni v2 ON v2.id = p.id AND v2.typ_vysetreni = 9

Autor, Název akce
FIREBIRD – testovací databáze
Tabulka Pacienti
•ID
•Jmeno
•Datum_narozeni
•Pohlavi
Tabulka Vysetreni
•ID_vysetreni
•ID
•Datum_vysetreni
•Typ_vysetreni
• Vysledek
Cvičení:
Zjistěte průměrný, minimální a maximální interval (počet dnů) mezi vyšetřeními
pro jednotlivé typy vyšetření (vynechte vyšetření ve stejný den).
Jinak řečeno, jak často se má chodit na jednotlivá vyšetření.
Typ_vysetreni, průměr (dnů), min (dnů), max(dnů)
Daniel Klimeš, Databázové systémy a SQL
•SELECT v.typ_vysetreni,
•   AVG(v2.datum_vysetreni - v.datum_vysetreni),
•   MIN(v2.datum_vysetreni - v.datum_vysetreni),
•   MAX(v2.datum_vysetreni - v.datum_vysetreni)
•   FROM vysetreni v, vysetreni v2
•WHERE v.id = v2.id AND v.datum_vysetreni < v2.datum_vysetreni
•     AND v.typ_vysetreni = v2.typ_vysetreni
•     AND NOT EXISTS (
•       SELECT * FROM vysetreni v3 WHERE v3.id = v.id AND v3.typ_vysetreni = v.typ_vysetreni
•                                  AND v3.datum_vysetreni > v.datum_vysetreni
•                                  AND v3.datum_vysetreni < v2.datum_vysetreni
•       )
•GROUP BY v.typ_vysetreni