• PODSTAWY BAZ DANYCH

Podstawy baz danych 03

Baza

DVD Rental Service

source

restore

# osx/linux
psql -h localhost -U admin -W -d postgres < dvdrental.sql

# window
psql.exe -h localhost -U admin -W -d postgres < dvdrental.sql

Aggregation

GROUP BY

Każde grupowanie powiązane jest z agregacją danych.

SELECT title, COUNT(actor)
FROM film
INNER JOIN film_actor USING (film_id)
INNER JOIN actor USING (actor_id)
GROUP BY title
ORDER BY title
SELECT title, COUNT(actor) AS counter, ARRAY_AGG (first_name || ' ' || last_name) actors
FROM film
INNER JOIN film_actor USING (film_id)
INNER JOIN actor USING (actor_id)
GROUP BY title
ORDER BY title, counter
SELECT country, STRING_AGG (email, ';') email_list
FROM customer
INNER JOIN address USING (address_id)
INNER JOIN city USING (city_id)
INNER JOIN country USING (country_id)
GROUP BY country
ORDER BY country;

HEAVING

Służy do zawężania wyników na podstawie wyników agregacji.

SELECT SUM(amount) AS total_amount, customer_id
FROM payment
GROUP BY customer_id
HAVING SUM(amount) > 100
ORDER BY total_amount DESC
LIMIT 10
SELECT rating, MIN (replacement_cost)
FROM film
GROUP BY rating
HAVING MIN (replacement_cost) > 9

ROLLUP & GROUPING

=> SELECT * FROM items_sold;
 make  | model | sales
-------+-------+-------
 Foo   | GT    |  10
 Foo   | Tour  |  20
 Bar   | City  |  15
 Bar   | Sport |  5
(4 rows)

=> SELECT make, model, GROUPING(make,model), sum(sales) FROM items_sold GROUP BY ROLLUP(make,model);
 make  | model | grouping | sum
-------+-------+----------+-----
 Foo   | GT    |        0 | 10
 Foo   | Tour  |        0 | 20
 Bar   | City  |        0 | 15
 Bar   | Sport |        0 | 5
 Foo   |       |        1 | 30
 Bar   |       |        1 | 20
       |       |        3 | 50

(7 rows)

Ćwiczenia

UPDATE film SET release_year = 2000 + FLOOR(random() * 9 + 1)

NOTE update

UPDATE address SET city_id = FLOOR(random() * 600 + 1)
  1. pokaż ilość filmów wyprodukowanych w każdym roku
  2. pokaż wypożyczalnie, które miały najwięcej wypożyczeń filmów z 2004 roku, wynik posortuj wg ilosci wypożyczen malejąco
  3. napisac zapytanie prezentujace ponizszy wynik
      country       |         city          | customer_counter
--------------------+-----------------------+------------------
 India              | Etawah                |                6
 Brazil             | Ibirit                |                5
 Austria            | Linz                  |                4
 Dominican Republic | La Romana             |                4
 India              | Allappuzha (Alleppey) |                4
 Iran               | Esfahan               |                4
 Japan              | Kanazawa              |                4
 Moldova            | Chisinau              |                4
 Russian Federation | Serpuhov              |                4
 Russian Federation | Kamyin                |                4