Na 7 laboratorium zajmiemy się złożonymi zapytaniami w SQL. Wykorzystamy kilka przydatnych funkcji, z których dotychczas nie korzystaliśmy. Tak jak w poprzednich tygodniach pracujemy na bazie employees.

Złożone zapytania

UNION

Uwaga do zadań z poprzedniego tygodnia. Poniższa kwerenda da jeden wiersz, ponieważ UNION jest domyślnie sumą zbiorów (bez powtórzeń)

SELECT max(to_date) AS maxData FROM salaries
UNION
  SELECT max(to_date) AS maxData FROM titles;

Aby mieć powtarzające się wiersze używamy UNION ALL

SELECT max(to_date) AS maxData FROM salaries
UNION ALL
  SELECT max(to_date) AS maxData FROM titles;

Wystarczy, aby wiersze nie były identyczne, aby zadziałał nam zwykły UNION

SELECT max(to_date) AS maxData, 'salaries' FROM salaries
UNION 
  SELECT max(to_date) AS maxData, 'titles' FROM titles;

CASE

Ćwiczenie

Znajdź procent pracowników, którzy wykonują pracę inżynierską i nie wykonują pracy inżynierskiej. Wyniki zaokrąglij do drugiego miejsca po przecinku i wypisz w następującym formacie ‘45.65%’

Podpowiedź: Przypisz do każdego pracownika czy wykonuje on pracę inżynierską czy nie

Ćwiczenie

Znajdź liczbę pracowników aktualnie pracujących, pracujących do 31 grudnia 1990, i pozostałych

GROUP CONCAT

Zadanie pożyczone od dr Giniewicza

Spośród pracowników zatrudnionych w momencie zbierania danych, znajdź dziesięć osób, które otrzymały najwyższy awans (podwyżkę) od momentu zatrudnienia. Jeśli kilka osób otrzymało taki sam awans, wybierz pracownika zatrudnionego później (szybciej awansował). Nie używaj tabel tymczasowych.

Rozwiązanie naiwne

SELECT max(salary) - min(salary) as podwyzka, emp_no FROM salaries
GROUP BY emp_no
HAVING max(year(to_date))=9999
ORDER BY podwyzka DESC, min(to_date) DESC
LIMIT 10;

Dlaczego może ono być niepoprawne?

Zadanie pomocnicze

Znajdź liczbę pracowników, którzy obecnie zarabiają mniej niż w momencie zatrudniania

Podpowiedź: mogą przydać się funkcje concat, group_concat, substring_index, min, cast. Uwaga: zapytanie może zająć 30 sekund lub więcej!

Zmienne użytkownika

SET @liczba=23;
SELECT @liczba;

Zmiennym możemy przypisać wartości pochodzące z zapytań

SELECT @liczba:=COUNT(*) FROM salaries;

Możemy je następnie wykorzystywać w kolejnych zapytaniach

SELECT @minus_one_sigma:=AVG(salary)-STDDEV(salary),@plus_one_sigma:=AVG(salary)+STDDEV(salary) FROM salaries;
SELECT COUNT(*)/@liczba FROM salaries WHERE salary>@minus_one_sigma AND salary<@plus_one_sigma;

Pytanie bonusowe

Co możesz, na tej podstawie, powiedzieć o zgodności pensji z rozkładem normalnym? W jaki sposób, jakim zapytaniem, próbował(a)byś weryfikować taką hipotezę?

Ćwiczenie

Znajdź 95 kwantyl pensji

Naiwne rozwiązanie

Łączymy dwa razy tabele, dla każdej pensji zliczamy liczbę pensji od niej mniejszych. Jaka jest złożoność obliczeniowa takiego rozwiązania?

Poniżej podpowiedzi, do dwóch możliwych sposób rozwiązania tego problemu

Numer wiersza w mysql

MySQL nie ma bezpośredniego wsparcia dla numeru wiersza. Można go natomiast emulować, za pomocą zmiennej (szczegóły).

Limit

Klauzulę LIMIT stosowaliśmy dotychczas do wybieranie pierwszych n wyników zapytania. Daje ono jednak więcej możliwości, o czym można przeczytać tutaj.

Correlated subqueries

Przykład

Z bazy ratings: Wybierz wszystkie filmy, które mają średnią liczbę gwiazdek większa niż średnia liczba gwiazdek, jakie otrzymały wszystkie filmy reżysera.

SELECT m1.title, m1.director, AVG(stars) as avgStars
FROM Rating JOIN Movie m1 ON Rating.mID=m1.mID GROUP BY m1.mID
HAVING avgStars> (SELECT AVG(stars) FROM Rating JOIN Movie ON Rating.mID=Movie.mID
WHERE Movie.director=m1.director);

Ćwiczenie

Z bazy employees: Znajdź liczbę pracowników, którzy zarabiają więcej niż przeciętne wynagrodzenie na ich stanowisku. Dla uproszczenia nie patrzymy na to czy pracują obecnie. Tylko czy kiedykolwiek zarabiali więcej niż przeciętna.

SELECT COUNT(DISTINCT titles.emp_no)
FROM titles JOIN salaries ON titles.emp_no=salaries.emp_no
WHERE salary > (
 SELECT AVG(salary)
 FROM salaries JOIN titles t2 ON salaries.emp_no=t2.emp_no
 WHERE t2.title = titles.title);

Powyższe zapytanie jest skrajnie niefektywne, dla każdego wiersza wykonujemy złożone podzapytanie. Jak zrobić to lepiej?