• PODSTAWY BAZ DANYCH

Podstawy baz danych 10

Baza

DVD Rental Service

source

restore

CTE - Common Table Expression

SAMPLE

WITH cte_film AS (
    SELECT
        film_id,
        title,
        (CASE
            WHEN length < 30 THEN 'Short'
            WHEN length < 90 THEN 'Medium'
            ELSE 'Long'
        END) length
    FROM
        film
)
SELECT
    film_id,
    title,
    length
FROM
    cte_film
WHERE
    length = 'Long'
ORDER BY
    title;

Recursive

CREATE TABLE employees (
	employee_id serial PRIMARY KEY,
	full_name VARCHAR NOT NULL,
	manager_id INT
);

INSERT INTO employees (
	employee_id,
	full_name,
	manager_id
)
VALUES
	(1, 'Michael North', NULL),
	(2, 'Megan Berry', 1),
	(3, 'Sarah Berry', 1),
	(4, 'Zoe Black', 1),
	(5, 'Tim James', 1),
	(6, 'Bella Tucker', 2),
	(7, 'Ryan Metcalfe', 2),
	(8, 'Max Mills', 2),
	(9, 'Benjamin Glover', 2),
	(10, 'Carolyn Henderson', 3),
	(11, 'Nicola Kelly', 3),
	(12, 'Alexandra Climo', 3),
	(13, 'Dominic King', 3),
	(14, 'Leonard Gray', 4),
	(15, 'Eric Rampling', 4),
	(16, 'Piers Paige', 7),
	(17, 'Ryan Henderson', 7),
	(18, 'Frank Tucker', 8),
	(19, 'Nathan Ferguson', 8),
	(20, 'Kevin Rampling', 8);

WITH RECURSIVE subordinates AS (
	SELECT
		employee_id,
		manager_id,
		full_name
	FROM
		employees
	WHERE
		employee_id = 2
	UNION
		SELECT
			e.employee_id,
			e.manager_id,
			e.full_name
		FROM
			employees e
		INNER JOIN subordinates s ON s.employee_id = e.manager_id
)
SELECT
	*
FROM
	subordinates;

Window Function

select round(avg(rental_rate), 2)
from film
select name, round(avg(rental_rate), 2)
from film
inner join film_category using(film_id)
inner join category using(category_id)
group by category_id
select
	title,
	name,
	round(avg(rental_rate) OVER (partition by category_id), 2)
from film
inner join film_category using(film_id)
inner join category using(category_id)

Functions

  • aggregation functions
    • AVG
    • SUM
    • MIN
    • MAX
    • COUNT
  • dedicated window functions
select
  title,
  name,
  rental_rate,
  ROW_NUMBER() OVER(
    PARTITION BY category_id
    ORDER BY rental_rate
  )
from film
inner join film_category using(film_id)
inner join category using(category_id)
select
  title,
  name,
  rental_rate,
  RANK() OVER(
    PARTITION BY category_id
    ORDER BY rental_rate
  )
from film
inner join film_category using(film_id)
inner join category using(category_id)
select
  title,
  name,
  rental_rate,
  DENSE_RANK() OVER(
    PARTITION BY category_id
    ORDER BY rental_rate
  )
from film
inner join film_category using(film_id)
inner join category using(category_id)
select
  DISTINCT name,
  FIRST_VALUE (rental_rate) OVER(
    PARTITION BY category_id
    ORDER BY rental_rate
  ) as lowest_category_rental_rate
from film
inner join film_category using(film_id)
inner join category using(category_id)
select
  title,
  name,
  rental_rate,
  LAG (rental_rate, 1) OVER(
    PARTITION BY category_id
    ORDER BY rental_rate
  ) as previous_price,
  rental_rate - LAG (rental_rate, 1) OVER(
    PARTITION BY category_id
    ORDER BY rental_rate
  ) as diff_current_previous
from film
inner join film_category using(film_id)
inner join category using(category_id)
select
  title,
  name,
  rental_rate,
  LEAD (rental_rate, 1) OVER(
    PARTITION BY category_id
    ORDER BY rental_rate
  ) as previous_price,
  rental_rate - LEAD (rental_rate, 1) OVER(
    PARTITION BY category_id
    ORDER BY rental_rate
  ) as diff_current_previous
from film
inner join film_category using(film_id)
inner join category using(category_id)

Import

COPY table(col1,col2)
FROM '/tmp/persons.csv' DELIMITER ',' CSV HEADER;
COPY table(col1,col2)
FROM 'C:\tmp\persons.csv' DELIMITER ',' CSV HEADER;

Ćwiczenia

  • Przeczytać dokumentacje
  • Uruchomic przykłady
  • utworzyc tabele old_employees (z taka sama schema jak employees)
    • przepisac wszystkich (usunac ze starej, dodac do nowej) wyszystkich pracownikow, ktorch manager_id dzieli sie przez 2 bez reszty (uzyc CTE)
  • jaka jest roznica pomiedzy
WITH processed_payment AS (
    UPDATE payment SET amount = amount * 1.01
    RETURNING *
)
SELECT * FROM payment;

a

WITH processed_payment AS (
    UPDATE payment SET amount = amount * 1.01
    RETURNING *
)
SELECT * FROM processed_payment;
  • zastap podzapytania z poprzednich cwiczen uzywajac CTE