Lab06 - Instrukcja CASE i wyrażenie tabelaryczne CTE [ ver. BD1.2025.11.03.003 ]

• Instrukcja CASE
• Wyrażenie CTE (Common Table Expression)
• Rekurencja w CTE
• Polecenie INSERT, UPDATE i DELETE w CTE

W ramach zajęć zostaną przedstawione dwa rozwiązania technologiczne wspierające przetwarzanie danych w ramach polecenia SELECT. Na początek wprowadzimy instrukcję CASE umożliwiającą testowanie wartości. Kolejnym omawianym zagadnieniem będzie wyrażenie tabelaryczne CTE (ang. Common Table Expression) umożliwiające budowanie rozbudowanych zapytań do bazy danych włącznie z rekurencją.

Na początek zmodyfikujemy bazę Kurs dodając dodatkowe tabele. Schemat ERD zmodyfikowanej bazy danych przedstawia rys.1.

• Dodajemy tabelę zajecia

  CREATE TABLE zajecia ( id_kurs int, id_zaj int );
  ALTER TABLE zajecia ADD PRIMARY KEY ( id_kurs, id_zaj ); 
  ALTER TABLE zajecia ADD FOREIGN KEY ( id_kurs ) REFERENCES kurs ( id_kurs ) ;
  

• Dodajemy tabelę uczest_zaj

  CREATE TABLE uczest_zaj ( id_kurs int, id_zaj int, id_uczest int, obec int);
  ALTER TABLE uczest_zaj ADD PRIMARY KEY ( id_kurs, id_zaj, id_uczest );
  ALTER TABLE uczest_zaj ADD FOREIGN KEY ( id_kurs, id_zaj) REFERENCES zajecia ( id_kurs, id_zaj ) ;
  ALTER TABLE uczest_zaj ADD FOREIGN KEY ( id_uczest) REFERENCES uczestnik ( id_uczestnik ) ;
  

• Wypełniamy tabelę zajecia danymi.

  WITH RECURSIVE add_records (id_kurs, n) AS (
       SELECT id_kurs, 1 FROM kurs
       UNION ALL
       SELECT id_kurs, n+1 FROM add_records WHERE n<10 )
  INSERT INTO zajecia SELECT * FROM add_records;
  

• Wypełniamy tabelę uczest_zaj danymi (średnie uczestnictwo 80%).

    
  INSERT INTO uczest_zaj 
  SELECT id_kurs, id_zaj, u.id_uczest, CASE WHEN RANDOM() < .7 THEN 1 ELSE 0 END AS obec
  FROM zajecia z JOIN uczest_kurs u USING(id_kurs)
  ORDER BY id_kurs, id_zaj, 3 ;
  

Instrukcją CASE jest instrukcją warunkową. Na początek przedstawimy modyfikację wartości atrybutów. Jej funkcjonalność można porównać do konstrukcji IF .. THEN ... ELSE ... END w językach programistycznych. Konstrukcja polecenia CASE występuje w dwóch postaciach. W pierwszej porównujemy wartość lub zawartość kolumny z wartością po słowie THEN, w drugim przypadku wyznaczamy wartość logiczną wyrażenia. Poniżej obydwie formy instrucji CASE.

• Porównanie z wartością lub zawartością danej kolumny.

    SELECT a1, a2, 
       CASE wartość_lub_kolumna
         WHEN wartosc_1 THEN wynik_1
         WHEN wartosc_2 THEN wynik_2
         WHEN wartosc_3 THEN wynik_3
         [ ELSE wynik_gdy_brak_na_liscie ]
       END
    FROM relacja   
    

• Porównanie z wyznaczoną wartością logiczną.

    SELECT a1, a2, 
       CASE 
         WHEN wyrazenie_logiczne_1 THEN wynik_1
         WHEN wyrazenie_logiczne_2 THEN wynik_2
         WHEN wyrazenie_logiczne_3 THEN wynik_3
         [ ELSE wynik_gdy_brak_na_liscie ]
       END
    FROM relacja 
    

Instrukcja CASE może wystąpić również w innych fragmentach zapytania SQL. Można ją użyć w ramach kluzuli WHERE i HAVING w warunku ale również w klauzuli GROUP BY i ORDER BY.

Ciekawą formą zapytania jest kwerenda krzyżowa. Kwerenda krzyżowa umożliwia otrzymanie wyników w dwuwymiarowej przestrzeni. Na osi X umieszczamy jeden parametr, na osi Y drugi, wynik odczytujemy na przecięciu osi. Najczęściej jest to wynik zaregowany. Przykład kwerendy krzyżowej przedstawia poniższa tabela np. na przecięciu wartości (x,y) odczytujemy agregat np. dla wartości styczeń 2011 lub luty 2012, itd.

Przykłady wykorzystania instrukcji CASE w bazie danych KURS.

Zastąpienie ocen liczbowych oceną słowną dla uczestników określonego kursu.

SELECT imie, nazwisko,
   CASE ocena
     WHEN 3 THEN 'dostateczny'
     WHEN 4 THEN 'dobry'
     WHEN 5 THEN 'bardzo dobry'
     ELSE 'brak oceny'
   END
FROM uczestnik u JOIN uczest_kurs uk ON u.id_uczestnik=uk.id_uczest
WHERE id_kurs=1 
ORDER BY 2,1 ;

Lista ocen na poszczególnych kursach (kwerenda krzyżowa).

SELECT ko.opis,
   SUM(CASE WHEN uk.ocena = 3 THEN 1 ELSE 0 END ) as ocena_3,
   SUM(CASE WHEN uk.ocena = 4 THEN 1 ELSE 0 END ) as ocena_4,
   SUM(CASE WHEN uk.ocena = 5 THEN 1 ELSE 0 END ) as ocena_5,
   SUM(CASE WHEN uk.ocena is null THEN 1 ELSE 0 END ) as ocena_null
FROM kurs k JOIN uczest_kurs uk USING (id_kurs)
            JOIN kurs_opis ko on k.id_nazwa = ko.id_nazwa 
GROUP BY ko.opis, k.id_kurs	
ORDER BY k.id_kurs ;

Lista osób, które uzyskały ocenę 5 na kursie j.angielskiego, ocenę co najmniej 4 na kursie j.niemieckiego i ocenę co najmniej 3 na kursie j.hiszpanskiego.

SELECT imie, nazwisko, ko.opis, uk.ocena
FROM uczestnik u JOIN uczest_kurs uk ON u.id_uczestnik=uk.id_uczest  
                 JOIN kurs k USING ( id_kurs )
                 JOIN kurs_opis ko ON k.id_nazwa = ko.id_nazwa
WHERE uk.ocena >=
CASE 
   WHEN k.id_kurs IN (1, 2, 3, 4, 5) THEN 5
   WHEN k.id_kurs IN (6, 7, 8, 9) THEN 4
   WHEN k.id_kurs IN (10, 11) THEN 3
END 
ORDER BY 4 DESC ;

Wyrażenia CTE ( wspólne wyrażenia tablicowe ) upraszczają i zwiększają przejrzystość kodu polecenia SQL. Raz zdefiniowane struktury w ramach polecenia WITH można wykorzystać wiele razy. Wyrażenia CTE można wykorzystać również w ramach widoku, funcji czy procedury składowanej. Realizacja wyrażeń CTE zaczyna się od słowa kluczowego WITH po którym następuje deklaracja tabel CTE. Widoczność zdefiniowanych tabel CTE jest w ramach wyrażenia WITH oraz zapytania związanego z wyrażeniem. Wyrażenia CTE są szczególnie przydatne w przypadku rozbudowanych zapytań, łączących wiele tabel, które chcemy użyć w kolejnym kroku, wykonując na nich dodatkowe operacje. Poniżej struktura wyrażenia CTE.

  WITH table1_CTE [( atrybuty )] AS ( definicja zapytania )   -- definicja tabeli CTE  
       [ , table2_CTE, ... ]                                  -- definicje kolejnych tabel CTE
  SELECT [ atrybuty ] FROM tables_CTE ;                       -- zapytanie do tabel CTE
  

Przykłady wykorzystania wyrażeń CTE w bazie danych KURS.

Prezentacja funkcjonalności polecenia WITH.

WITH statementCTE AS ( select * from uczestnik )
SELECT * from statementCTE;

Kursy, które mają więcej niż 7 osób.

WITH statementCTE AS ( select id_kurs, count(*) as num from uczest_kurs group by id_kurs )
SELECT ko.opis from statementCTE JOIN kurs k USING ( id_kurs ) 
           JOIN kurs_opis ko ON k.id_nazwa = ko.id_nazwa
   WHERE num > 7;

Procentowy udział osób w poszczególnych kursach.

WITH totalCTE as ( select count(*)::float as tot from uczest_kurs ),
     kursCTE as ( select id_kurs, count(*)::float as num from uczest_kurs group by id_kurs ) 
SELECT ko.opis, (kCTE.num/(totalCTE.tot))::decimal(5,1) 
FROM totalCTE, kurs k JOIN kursCTE kCTE USING (id_kurs)
     JOIN kurs_opis ko ON k.id_nazwa = ko.id_nazwa;

Interesującą właściwością wspólnych wyrażeń tablicowych jest możliwość stosowania rekurencji w ich wnętrzu. Tego typu funkcjonalność, wykorzystujemy w np. zbiorach z określoną hierarchią elementów.

Definicja struktury wyrażeń rekurencyjnych WITH składa się z trzech elementów:

• Określenia zapytania zakotwiczającego, jest to zazwyczaj zbiór elementów stanowiących korzeń (lub korzenie).
• Zapytania rekursywnego – skorelowanego z wynikiem zwracanym przez zapytanie poprzednie. Odwołujemy się tu do struktury hierarchicznej. Operator UNION ALL łączy wszystkie przebiegi w finalny zbiór wynikowy. Ważne jest, aby zrozumieć, że w każdym kroku działamy tylko na zbiorze zwracanym przez krok poprzedni.
• Niejawnego waruneku zakończenia rekurencji. Jeśli zapytanie rekurencyjne, skorelowane, nie zwróci żadnego elementu, działanie CTE zostaje porzerwane.

Poniżej struktura rekurencyjnego wyrażenia CTE.

WITH RECURSIVE cte_name (
    CTE_definicja_zapytania    -- część nierekursywna ( zapytanie zakotwiczające )
    UNION [ALL]
    CTE_definicja_zapytania    -- cześć rekursywna ( zapytanie rekursyne, 
                               -- skorelowane z wynikiem poprzedniego zapytania )
) SELECT * FROM cte_name;
  

Przykłady rekurencyjnych zapytań CTE.

Przykład zapytania rekurencyjnego WITH.

WITH RECURSIVE 
test_with(n) AS ( VALUES(1)
                  UNION
                  SELECT n+1 FROM test_with WHERE n < 10 ) 
SELECT * FROM test_with ORDER BY n;

Realizacja silni z wykorzystaniem polecenia WITH.

WITH RECURSIVE Factorial (n, factorial) AS (
    SELECT 1, CAST(1 AS BIGINT)
    UNION ALL
    SELECT n + 1, (n + 1) * factorial
    FROM Factorial WHERE n < 10)
SELECT n, factorial FROM Factorial;

Prezentacja struktur hierarchicznych z wykorzystaniem rekurencyjnego polecenia WITH.

-- Tworzymy tablicę pracownik zawierającą id pracownika, 
-- nazwisko i id bezpośredniego przełożonego
-- Tabela zawiera połączenie typu self-join
CREATE TABLE emp ( empno INT, empname VARCHAR(20), mgrno INT ) ;
-- Wprowadzamy przykładowe dane
INSERT INTO emp VALUES ( 100, 'Kowalski',    null),
                       ( 101, 'Abacki',      100),
                       ( 102, 'Cabacki',     101),
                       ( 103, 'Dadacki',     102),
                       ( 104, 'Zazadzki',    101),
                       ( 105, 'Stachera',    104),
                       ( 106, 'Flisikowski', 100),
                       ( 107, 'Olech',       106),
                       ( 108, 'Płochocki',   106),
                       ( 109, 'Stachyra',    107),
                       ( 110, 'Sztuka',      109),
                       ( 111, 'Sosin',       110),
                       ( 112, 'Głowala',     110),
                       ( 113, 'Straszewski', 110),
                       ( 114, 'Dwojak',      100),
                       ( 115, 'Kotulski',    114),
                       ( 116, 'Łaski',       115),
                       ( 117, 'Iwanowicz',   115)  ; 
-- Zapytanie zwracające nazwisko pracownika i jego przełożonego
SELECT e.empno, e.empname, e.mgrno, m.empname
FROM emp e JOIN emp m ON e.mgrno = m.empno;			   
-- Zapytanie zwracające nazwisko pracownika, 
-- nazwisko bezpośredniego przełożonego i poziom w hierarchi 	
WITH RECURSIVE cte  					   
         AS ( SELECT empno, empname, mgrno, ''::varchar(20) as mgrname, 1 lvl from emp where mgrno is null 
              UNION ALL
              SELECT e.empno, e.empname, e.mgrno, c.empname, c.lvl+1 
              FROM emp e inner join cte c on e.mgrno = c.empno 
              WHERE e.mgrno is not null )                                                  
SELECT empname, mgrname, lvl FROM cte ORDER BY lvl; 
-- Zapytanie zwracające nazwisko pracownika,
-- poziom w hierarchi i listę przełożonych 				   
WITH RECURSIVE cte  					   
         AS ( SELECT empno, empname, mgrno, 1 lvl, ''::text as path from emp where mgrno is null 
              UNION ALL
              SELECT e.empno, e.empname, e.mgrno, c.lvl+1, concat( c.path, '->', c.empname ) 
              FROM emp e inner join cte c on e.mgrno = c.empno 
              WHERE e.mgrno is not null )                                                  
SELECT empname, lvl, path FROM cte ORDER BY lvl;

1. Conditional Expressions
2. PostgreSQL CASE
3. Queries (Common Table Expressions)
4. Learn PostgreSQL Recursive Query By Example