Przypominam sobie SQL

Ćwiczenia SQL

W ramach treningu SQL-a i poznawania postgresa robię (powoli) ćwiczenia ze strony pgexercises.com.

Można na stronie rozwiązywać mini-zadania w różnych kategoriach (od prostych wyszukiwań przez złączenia, modyfikacje danych, aż po funkcje agregujące). Schemat bazy danych do ćwiczeń jest bardzo prosty, ale z niektórymi zadaniami miałam pod górkę:

Dlatego uważam, że stronka w sam raz nadaje się do odświeżenia wiadomości o SQL-u.

Swoje odpowiedzi z komentarzami wrzucę pewnie za jakiś czas do GitHuba jako gist.

Postęp !

Obecnie przechodzę przez “dział” z funkcjami agregującymi. Swój “postęp” widzę na stronie w postaci listy z “odhaczonymi” ćwiczeniami:

}}

Moje dotychczasowe odkrycia:

• jeśli kolumna ma typ timestamp i potrzebuję wybrać wiersze dotyczące pewnej daty, to mogę:
  • w warunku WHERE użyć operatorów porównania z literałem napisowym oznaczającym datę, czyli np. poszukując eventów mających miejsce 10 maja 2017 roku mogę mapisać event < '2017-05-11' and event >= '2017-05-10'
  • lub użyć funkcji, która podaną wartość typu timestamp “zaokrągli” do podanej “pozycji”, zerując “pozycje” o mniejszym znaczeniu: where date_trunc('day', event) = '2017-05-10'
• wstawiając wyliczone dane instrukcją insert zamiast

  1 2	insert into cd.facilities(facid, name, membercost, guestcost, initialoutlay, monthlymaintenance) values (select max(f.facid) + 1 as facid from cd.facilities f, 'Spa', 20, 30, 100000,800);

  muszę napisać

  1 2	insert into cd.facilities (facid, name, membercost, guestcost, initialoutlay, monthlymaintenance) select (select max(facid) from cd.facilities)+1, 'Spa', 20, 30, 100000, 800;

  i na razie nie rozumiem, dlaczego pierwsza składnia jest niepoprawna.

Arytmetyka dat i funkcje związane z czasem

Postgres ma wiele ciekawych funkcji, których można użyć w zapytaniach, dzięki czemu nie trzeba już ich wyliczać w języku programowania. Na przykład:

• wykonując operacje na datach można użyć bardzo intuicyjnej arytmetyki
• dodatkowe funkcje pozwalają tworzyć wartości typów interwałowych, daty czy timestampów

Plany

Po zakończeniu ćwiczeń z SQL-a zamierzam przyjrzeć się bliżej interesującym mnie obszarom:

• typom json i jsonb oraz jsonpath pozwalającym na wyszukiwanie danych w obiektach json json types
• typom związanym z geometrią pozwalającym przechowywać punkty, proste, wieokąty, koła czy ścieżki
• tablicom, a właściwie typom tablicowym
• zakresom range types

Oprócz tego postgres obsługuje wiele innych typów (adresy internetowe, identyfikatory obieków, typy domenowe, typy złożone), o których na razie wiem tylko tyle, że postgres je obsługuje 🤔.

Dokumentacja funkcji oferowanych przez postgresa naprawdę robi wrażenie; chciałabym je choćby pobieżnie przejrzeć; w szczególności chciałabym poznać funkcje agregujące i “okienkowe”, i spróbować ich użyć podczas praktycznej analizy prawdziwych danych.

Zobaczymy - drzemki mojej córeczki są coraz krótsze, czasu coraz mniej, a do odkrycia tyle interesujących rzeczy 😄.

Aktualizacja 2021-10-05

Nie można użyć aliasu kolumny w klauzuli HAVING

Postgres nie pozwala na użycie aliasu kolumny (np. wyliczonej złożoną formułą przy użyciu funkcji agregującej) w klauzuli having; trzeba

• powtórzyć wyliczenie w klauzuli having albo
• zapakować wywołanie w zewnętrzny select i użyć zwykłego warunki where

Tego dowiedziałam się próbując optymalnie rozwiązać zadanie o przychodach. Natknęłam się na poniższy błąd:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

exercises=# select f.name, 
        sum(b.slots * (case when b.memid=0 then f.guestcost else f.membercost end)) as revenue 
from cd.bookings b join cd.facilities f on b.facid = f.facid 
group by f.facid 
having revenue > 1000 
order by revenue;
;
ERROR:  column "revenue" does not exist
LINE 5: having revenue > 1000 
               ^
exercises=#

Musiałam więc użyć drugiego sposobu, czyli:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

select name, revenue from (
select f.name as name, 
        sum(b.slots * (
            case when b.memid = 0 
            then f.guestcost 
            else f.membercost 
            end)) as revenue 
from cd.bookings b join cd.facilities f 
on b.facid = f.facid 
group by f.facid  
order by revenue
) as x  where x.revenue < 1000;

Funkcja extract

Składnia: extract (field from source) Jako source można podać wartość typu timestamp, date (będzie zrzutowany na timestamp), time oraz interval. Wartość field to jedna wartość z: century, day, decade, dow, doy, epoch, hour, isodow, isoyear itd (pełna lista).

Można, zamiast date_part('month', eventstamp) użyć funkcji extract(month from eventstamp), chyba łatwiej się ją wprowadza, bo nie trzeba wpisywać apostrofów przy literale napisowym :)

Na przykład:

1
2
3
4
5
6
7

select 
	extract(year from a.date) as year, 
	extract(day from a.date) as day, 
	extract(doy from a.date) day_of_year,  
	extract(isodow from a.date) day_of_week_monday_is_1,  
 	extract(timezone_hour from a.date) tz 
from (select now() date) a;

ROLLUP

O, to jest świetne: można łatwo policzyć sumy częściowe i zbiorcze hierarchicznie (przykładowe zadanie):

1
2
3
4
5

select facid,  extract(month from starttime) as month, sum(slots) as slots 
from cd.bookings 
where extract(year from starttime) = 2012 
group by rollup(facid, month) 
order by facid, month;

ROUND

Zaokrągla wartość do podanej liczby miejsc po przecinku. Użyłam w ten sposób, choć nie wiedziałam wcześniej, że taka funkcja istnieje. Może jest w standardzie i/lub używałam jej już wcześniej?

Ponieważ zaokrąglanie jest bardziej złożone, niż może się wydawać, sprawdzam w dokumentacji jak działa round:

Rounds to nearest integer. For numeric, ties are broken by rounding away from zero. For double precision, the tie-breaking behavior is platform dependent, but “round to nearest even” is the most common rule.

1
2
3
4
5
6
7
8
9

select 
	round(123.456, 3) a,
	round(123.456, 2) b,
	round(123.456, 1) c, 
	round(123.456, 0) d,
	round(123.45, -1) e, 
	round(159.50, 0) f,
	round(159.49, 0) g
	;

Windowing functions

Pierwsze możliwe użycie w zadaniu count members - tym razem przeczytałam rozwiązanie. A później:

• tutorial wprowadzający w temat funkcji okienkowych
• składnia funckji okienkowych
• funkcje okienkowe wymagają zastanowienia - mam dość mizerną intuicję na temat tego, w jakich sytuacjach się je stosuje

Correlated subqueries

Skorelowane podzapytanie pojawiło się w zadaniu rolling average.

Ciekawe: używamy zapytania z funkcją generate_series (jest to jedna z tzw. set generating functions) do wygenerowania listy dat z pewnego miesiąca. Później wykonujemy zapytanie dla każdej wygenerowanej w ten sposób daty (wygenerowany zbiór z datami znajduje się w sekcji FROM, a sam SELECT wybiera dwie kolumny: datę oraz średnią wyliczoną przy pomocy zapytania skorelowanego.

To jest przykładowe rozwiązanie rolling average:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

select 	dategen.date,
	(
		-- correlated subquery that, for each day fed into it,
		-- finds the average revenue for the last 15 days
		select sum(case
			when memid = 0 then slots * facs.guestcost
			else slots * membercost
		end) as rev

		from cd.bookings bks
		inner join cd.facilities facs
			on bks.facid = facs.facid
		where bks.starttime > dategen.date - interval '14 days'
			and bks.starttime < dategen.date + interval '1 day'
	)/15 as revenue
	from
	(
		-- generates a list of days in august
		select 	cast(generate_series(timestamp '2012-08-01',
			'2012-08-31','1 day') as date) as date
	)  as dategen
order by dategen.date;

Moje próby były niestety nieudane. To jedna z moich zawiłych prób liczenia średniej:

• niepoprawnie założyłam, że mam dane dla wszystkich dni sierpnia 2012 ORAZ dane dla wszystkich 15 dni poprzedzających 1 sierpnia
• próbowałam najpierw liczyć sumy przychodów grupując po dniach (all_revs)
• a później liczyłam średnią funkcją okienkową zawierającą 16 dni bez dnia bieżącego (żeby złapać 15 poprzednich)
• … przy czym trochę się męczyłam arytmetyką dat:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

with all_revs as (
	select date(starttime) d, 
					sum(case when b.memid = 0 
						then slots * f.guestcost 
						else slots * f.membercost 
						end) rev
	from cd.facilities f join cd.bookings b ON b.facid = f.facid
	where date(starttime) >= ('2012-08-01'::DATE - '16 days'::INTERVAL) and date(starttime) < '2012-09-01'
	group by d
	order by d),
 all_avgs as (
select d, rev, avg(rev) over (rows 16 preceding exclude current row) aa
from all_revs)
select d, aa from all_avgs 
where extract(year from d) = 2012 and extract(month from d) = 8; 

Wniosek: czas przerobić kolejny dział daty i przyjrzeć się funkcjom z nimi związanym.

Gist

Dokumentację moich rozwiązań zamieściłam w tym miejscu: gist.

Ten wpis jest częścią serii sql.

• 2021-04-11 - Postgres - daty
• 2021-16-10 - Postgres - krótka analiza danych dot. zajęć dodatkowych
• 2021-04-10 - Postgres - JSON
• 2021-04-10 - Postgres - ćwiczenia
• 2021-30-09 - Postgres - instalacja