Funkcje

Rozszerzenie

Funkcje

• Działają analogicznie jak funkcje w np. Excelu, językach programowania czyli

• Przyjmują od 0, 1, 2 lub więcej wartości jako dane wejściowe i

• Zwracają jedną wartość jako wynik

• Poznaliście już LENGTH()

Przykładowe funkcje

--CONCAT() funkcja sklejająca napisy
SELECT CONCAT('Hello',' ','World!');
SELECT CONCAT(first_name,' ',last_name) FROM actor;

--NOW() zwraca aktualny czas
SELECT NOW();

--SUBSTR() wycinanie napisu z napisu
SELECT SUBSTR('abcdefghijklmnzopqrstuvwxyz',1,8);
SELECT SUBSTR(first_name,1,1), SUBSTR(last_name,1,1) 
FROM actor;

--DATE_ADD() do manipulacji datami
SELECT DATE_ADD(NOW(), INTERVAL 1 QUARTER);
SELECT last_update, DATE_ADD(last_update, INTERVAL -1 DAY) 
FROM film;

Funkcje - zadanie

1. Napisz zapytanie, które zwróci inicjały aktorów jako jednokolumnowy rezultat

DISTINCT

Przypomnienie

Jak ograniczyć wynik do unikalnych zestawów wartości?

Lista różnych dat wydań filmów

SELECT DISTINCT
	release_year 
FROM film;

Jak ograniczyć wynik do unikalnych zestawów wartości?

Lista ratingów filmów

SELECT DISTINCT
	rating
FROM film;

DISTINCT - Zadanie

2. Napisz listę imion aktorów, które pojawiają się w opisach jakichkolwiek filmów zawartych w bazie.

Potem dla porównania popraw zapytanie tak, żeby zwracało tylko unikalne imiona.

Funkcje agregujące

Funkcje agregujące

• zliczają wartości z wielu wierszy do pojedyńczego wyniku

• niemal zawsze pomijają wartości null

SUM()

SELECT
	SUM(amount)
FROM payment;

Suma wszystkich płatności

MIN(), AVG(), MAX()

SELECT 
	MIN(amount),
	AVG(amount),
	MAX(amount) 
FROM payment;

Najmniejsza, średnia, największa kwota

MIN(), MAX()

SELECT 
	MIN(city),
	MAX(city)
FROM city ci
INNER JOIN country co
  ON ci.country_id = co.country_id
  AND co.country = 'Myanmar';

Alfabetycznie pierwsze i ostatnie miasto w Myanmarze

COUNT(), COUNT(DISTINCT)

SELECT 
	COUNT(city),
	COUNT(DISTINCT city)
FROM city;

Lista miast i unikalnych nazw miast

COUNT(), COUNT(DISTINCT)

SELECT 
	COUNT(null),
        COUNT(*),
	COUNT(1),
	COUNT(DISTINCT last_name)
FROM actor;

Liczba aktorów i unikalnych nazwisk

Funkcje agregujące

- zadanie

3. Na dwa różne sposoby policz średnią płatność dla pracownika o imieniu 'Mike'

GROUP BY

GROUP BY

• Grupowanie jest wykonane po filtrowaniu (WHERE), ale przed selekcją (SELECT).

• Poprawne użycie zwraca unikalne zestawy wybranych wartości i im przypisane agregacje (np. SUM()).

• Przy braku użycia funkcji agregujących, wynik jest jak przy użyciu DISTINCT

GROUP BY - DISTINCT

SELECT DISTINCT 
    rating, rental_rate 
FROM film 
ORDER BY rating, rental_rate;
-----------------------------
SELECT 
    rating, rental_rate 
FROM film 
GROUP BY rating, rental_rate 
ORDER BY rating, rental_rate;

GROUP BY + SUM

SELECT 
	staff_id,
	SUM(amount) 
FROM payment 
GROUP BY staff_id;

Suma kwot płatności

przyjętych przez

poszczególnych

pracowników

Bardziej skomplikowane

SELECT 
	rating,
 	AVG(rental_rate) 
FROM film 
WHERE rating > 'P'
GROUP BY
	rental_duration,
	rating 
ORDER BY rating;

Średnia stawka wypożyczenia filmu o ratingu 'P', 'PG-13', 'R' dla ratingu i długości wypoyczenia

Nazwiska aktorów

SELECT 
	last_name,
	COUNT(1)	
FROM actor
GROUP BY
	last_name
ORDER BY 2 desc;

Nazwiska aktorów z liczbą wystąpień, posortowane od najczęstszych

GROUP BY - zadanie

4*. Napisz zapytanie zwracające poniższy wynik.

Dwie widoczne wartości liczbowe to średnia płatności przypisanych do Jona i Mike'a

(albo ma ktoś pomysł, albo praca domowa)

HAVING

HAVING

• HAVING działa analogicznie jak WHERE, jednak warunki są sprawdzane po zgrupowaniu wyników,

• wartości funkcji agregujących można sprawdzić dopiero w klauzuli HAVING, nigdy w WHERE,

• w klauzuli WHERE można zawęzić zbiory przed agregacją.

HAVING + SUM

SELECT 
	staff_id,
	SUM(amount) 
FROM payment 
GROUP BY staff_id 
HAVING SUM(amount) > 33500;

Lista pracowników i suma kwot wypożyczeń dla pracowników o sumie > 33500

Łączenie funkcji

SELECT rating,
	MIN(rental_rate) AS `min`,
	MAX(rental_rate) AS `max`,
	COUNT(DISTINCT rental_rate) AS rates
FROM film 
WHERE rating in ('PG','PG-13','R')  
        AND rental_rate > 0.99
GROUP BY rating 
HAVING AVG(length) > 114;

Nieunikalne nazwiska

SELECT last_name
FROM actor
GROUP BY last_name
HAVING COUNT(1) > 1
ORDER BY last_name, COUNT(1) DESC

HAVING - zadanie

5. Napisz zapytanie, które wylistuje jedyną nieunikalną nazwę miasta jaka jest w zbiorze.

*Potem spróbuj napisać zapytanie, które wypisze to miasto, a następnie - jeżeli leżą one w różnych krajach - wypisze dwa z nich.

DESER - CASE

CASE

• CASE to wyrażnie warunkowe, odpowiada konstrukcjom typu IF z innych języków programowania, w tym z Excela

CASE
    WHEN ... THEN ...
    WHEN ... THEN ...
    ELSE ...
END

SELECT title, CASE
    WHEN length > 60 THEN 'Długometrażowy'
    WHEN length <= 60 THEN 'Krótkometrażowy'
    ELSE 'Brak danych' END as `Typ filmu`
FROM film
ORDER BY CASE 
    WHEN length <= 60 THEN 0 
    WHEN length > 60 THEN 1
    ELSE 2 END, title

CASE

Czemu nie użyłem `length` w sortowaniu?

CASE - zadanie

6. W Stanach Zjednoczonych (w bazie - 'United States') adresy ulic kończą się z reguły rodzajem ulic - ostatnie słowo to np. 'Avenue' albo 'Street'.

CASE - zadanie

6cd. Napisz zapytanie które odfiltruje adresy położone w USA, następnie:

• przypisz im w wyniku zapytania kategorię 'Avenue', 'Boulevard', 'Street' i 'Way' (dla adresów kończących się tym słowem)
• oraz 'Other' (dla reszty)
• oraz podlicz ile jest adresów w poszczególnych kategoriach.
• Wynik posortuj malejąco po
  liczności kategorii.