СУБД. Лекция 3

Выборка данных (продолжение)

• COLLATION и регистронезависимый поиск;
• Выборка данных:
  • Подзапросы;
  • Оконные функции;
  • UNION;
  • Еще раз про JOIN-ы;
  • Рекурсивные запросы.
• VIEW.

Регистронезависимый поиск

Регистронезависимый поиск

• COLLATION
• LOWER(name)
• CITEXT

COLLATION

Простая сортировка обычно даёт не тот результат, который ожидается пользователем.

COLLATION

В разных языках разные преобразования в верхний/нижний регистр

SELECT
    LOWER('WINDOWS' COLLATE "en_US.utf8"),
    UPPER('linux' COLLATE "en_US.utf8")

SELECT
    LOWER('WINDOWS' COLLATE "tr_TR.utf8"),
    UPPER('linux' COLLATE "tr_TR.utf8")

COLLATION

В PostgreSQL поддержка COLLATION реализована на базе C-функции strcoll_l.

Это порождает ряд проблем:

• Список COLLATION-ов зависит от настроек операционной системы;
• Разное поведение COLLATION-ов под Linux/Windows/MacOS;
• Нет регистронезависимого сравнения.

С PostgreSQL 10 (окт 2019) появилась поддержка COLLATION на базе библиотеки ICU.

LOWER(name)

CREATE TABLE person (
    id SERIAL PRIMARY KEY,
    name TEXT COLLATE "ru_RU.utf8",
    ...
);
CREATE UNIQUE INDEX uniq_name ON person (LOWER(name));

SELECT * FROM person WHERE LOWER(name) = LOWER('Jack Sparrow');

Проблемы:

• Нужно не забывать LOWER(name) во всех запросах.
• Если вы объявляете столбец как UNIQUE или PRIMARY KEY, неявно создаваемый индекс будет чувствительным к регистру.

CITEXT

CREATE EXTENSION IF NOT EXISTS citext;
CREATE TABLE person (
    id SERIAL PRIMARY KEY,
    name CITEXT COLLATE "ru_RU.utf8",
    ...
);
CREATE UNIQUE INDEX uniq_name ON person (name);

SELECT * FROM person WHERE name = 'Jack Sparrow';

Выборка данных

SELECT

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE }
        [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]

SUBQUERIES

SELECT
    col0,
    (SELECT col1 FROM table1 WHERE table1.id = table0.id),
    (SELECT col2 FROM table1 WHERE table1.id = table0.id)
FROM
    table0;

SELECT *
FROM t1
WHERE column1 = (SELECT MAX(column2) FROM t2);

SELECT *
FROM t1 AS t
WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);

SUBQUERIES: Expressions

• EXISTS (subquery)
• expression [NOT] IN (subquery)
• expression operator ANY/SOME (subquery)
• expression operator ALL (subquery)

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);
 
SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);
 
SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);
 
SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

ROW SUBQUERIES

SELECT *
FROM t1
WHERE (col1, col2)
	= (SELECT col3, col4 FROM t2 WHERE id = 10);
 
SELECT *
FROM t1
WHERE ROW (col1, col2)
	= (SELECT col3, col4 FROM t2 WHERE id = 10);

SELECT column1, column2, column3
FROM t1
WHERE (column1, column2, column3)
    IN (SELECT column1, column2, column3 FROM t2);

[NOT] EXISTS

SELECT DISTINCT store_type
FROM stores
WHERE EXISTS (
    SELECT *
    FROM cities_stores
    WHERE cities_stores.store_type = stores.store_type
);
 
SELECT DISTINCT store_type
FROM stores
WHERE NOT EXISTS (
    SELECT *
    FROM cities_stores
    WHERE cities_stores.store_type = stores.store_type
);

SUBQUERIES in FROM

SELECT ... FROM (subquery) [AS] name ...
 
SELECT AVG(sum_column1)
FROM (
    SELECT SUM(column1) AS sum_column1
    FROM t1
    GROUP BY column1
) AS t1;

Оконные функции

SELECT
  ROW_NUMBER() OVER (ORDER BY age ASC) AS rownumber,
  person_id, person_name, age
FROM person;

SELECT
  ROW_NUMBER() OVER w AS rownumber,
  RANK() OVER w AS ranking,
  person_id, person_name, age
FROM person
WINDOW w AS (ORDER BY age ASC);

SELECT
  count(*) AS unfiltered,
  count(*) FILTER (WHERE i < 5) AS filtered
FROM generate_series(1,10) AS s(i);

Оконные функции

Оконные функции

SELECT DISTINCT
  department_id,
  FIRST_VALUE(person_name) OVER w,
  FIRST_VALUE(age) OVER w
FROM person
WINDOW w as (PARTITION BY department_id);

SELECT DISTINCT ON (department_id)
  department_id,
  person_name,
  age
FROM person;

UNION

SELECT ...
UNION [ALL | DISTINCT]
SELECT ...
[UNION [ALL | DISTINCT]
SELECT ...]

SELECT * FROM sales2010
UNION
SELECT * FROM sales2011;

SELECT * FROM sales2010
UNION ALL
SELECT * FROM sales2011;

Объединения таблиц (JOIN)

Набор данных

CROSS JOIN

SELECT * FROM Employee CROSS JOIN Department;

CROSS JOIN

SELECT * FROM Employee, Department;

INNER JOIN

SELECT *
FROM Employee
INNER JOIN Department
ON Employee.DepartmentID = Department.DepartmentID;

INNER JOIN

SELECT *
FROM Employee, Department
WHERE Employee.DepartmentID = Department.DepartmentID;

NATURAL JOIN

SELECT *
FROM Employee
NATURAL JOIN Department;

JOIN USING

SELECT *
FROM Employee
JOIN Department USING (DepartmentID);

LEFT OUTER JOIN

SELECT *
FROM Employee
LEFT OUTER JOIN Department
ON Employee.DepartmentID = Department.DepartmentID;

RIGHT OUTER JOIN

SELECT Employee.*, Department.*
FROM Department
RIGHT OUTER JOIN Employee
ON Employee.DepartmentID = Department.DepartmentID;

FULL OUTER JOIN

SELECT *
FROM Employee
FULL OUTER JOIN Department
ON Employee.DepartmentID = Department.DepartmentID;

FULL OUTER JOIN

SELECT *
FROM Employee
FULL OUTER JOIN Department
ON Employee.DepartmentID = Department.DepartmentID;

SELECT *
FROM Employee
LEFT OUTER JOIN Department
ON Employee.DepartmentID = Department.DepartmentID;
 
UNION ALL
 
SELECT *
FROM Employee
RIGHT OUTER JOIN Department
ON Employee.DepartmentID = Department.DepartmentID
WHERE Employee.DepartmentID IS NULL;

SELF-JOIN

SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
FROM Employee F
INNER JOIN Employee S ON F.Country = S.Country
WHERE F.EmployeeID < S.EmployeeID
ORDER BY F.EmployeeID, S.EmployeeID;

SELF-JOIN

SUBQUERIES vs JOIN

Коррелирующий подзапрос

SELECT E.*
FROM Employee E WHERE EXISTS (
  SELECT *
  FROM Department D WHERE D.DepartmentID = E.DepartmentID
);

Не коррелирующий подзапрос

SELECT E.*
FROM Employee E WHERE E.DepartmentID IN (
  SELECT DepartmentID
  FROM Department D
);

JOIN

SELECT E.*
FROM Employee E
JOIN Department D ON (E.DepartmentID = D.DepartmentID);

FAKE TABLE

SELECT 1 AS n
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5;

FAKE TABLE

WITH RECURSIVE series(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM series WHERE n < 5
)
SELECT * FROM series ORDER BY n;

SELECT n FROM unnest(ARRAY[
    1, 2, 3, 4, 5
]) n ORDER BY n;

SELECT n
FROM generate_series(1, 5) n;

FAKE TABLE

SELECT * FROM unnest(ARRAY[
    1, 2, 3
], ARRAY[
    'foo', 'bar', 'baz'
]) n (a, b) ORDER BY a;

SELECT * FROM (
  VALUES (1, 'foo'), (2, 'bar'), (3, 'baz')
) n (a, b)

Получение идентификаторов до вставки

CREATE TABLE city (
    id bigserial NOT NULL PRIMARY KEY,
    region_id bigint NOT NULL,
    name varchar(128) NOT NULL
);

SELECT nextval(pg_get_serial_sequence('city', 'id'));

SELECT nextval(pg_get_serial_sequence('city', 'id'))
FROM generate_series(1, 100);

SELF UNION

SELECT col1 FROM tbl
UNION ALL
SELECT col2 FROM tbl;

SELECT unnest(array[col1, col2]) as col1 FROM tbl;

WITH RECURSIVE

WITH RECURSIVE recursetree (id, path, name) AS (
    SELECT id, array[id], name FROM tree
    WHERE parent_id = 0
  UNION ALL
    SELECT t.id, array_append(path, t.id), t.name
    FROM tree t
    JOIN recursetree rt ON rt.id = t.parent_id
  )
SELECT id, array_to_string(path, '.') as path, name FROM recursetree
ORDER BY path;

Еще немного про WITH

Надо всем аватарам, у которых сдан квест на "Кащея" отправить "Меч кладенец" по игровой почте.

CREATE TABLE avatar (
    id        SERIAL PRIMARY KEY,
    name      VARCHAR(64)
);
CREATE TABLE quest (
    id        BIGSERIAL PRIMARY KEY,
    quest_rid INT,
    avatar_id INT REFERENCES avatar (id) ON DELETE CASCADE
);
CREATE TABLE mail (
    id           SERIAL PRIMARY KEY,
    recipient_id INT REFERENCES avatar (id) ON DELETE CASCADE,
    mail_rid     INT
);
CREATE TABLE item (
    id           BIGSERIAL PRIMARY KEY,
    mail_id      INT REFERENCES mail (id) ON DELETE CASCADE,
    item_rid     INT
);

Еще немного про WITH

Надо всем аватарам, у которых сдан квест на "Кащея" отправить "Меч кладенец" по игровой почте.

-- Получить всех, кто выполнил квест
SELECT * FROM quest q WHERE q.quest_rid = 42;

-- Создать письма
INSERT INTO mail (recipient_id, mail_rid)
SELECT q.avatar_id, 17
FROM quest q WHERE q.quest_rid = 42;

-- Создать предметы в письмах
INSERT INTO item (mail_id, item_rid)
SELECT mail_id, 7291
FROM mail m WHERE ???;

Еще немного про WITH

Надо всем аватарам, у которых сдан квест на "Кащея" отправить "Меч кладенец" по игровой почте.

--- Сохраняем номер последнего добавленного письма
CREATE TEMPORARY TABLE tmp_mail ON COMMIT DROP AS
    SELECT GREATEST(MAX(id), 0) as last_id FROM mail;

-- Создать письма
INSERT INTO mail (recipient_id, mail_rid)
SELECT q.avatar_id, 17
FROM quest q WHERE q.quest_rid = 42;

-- Создать предметы в письмах
INSERT INTO item (mail_id, item_rid)
SELECT m.id, 7291
FROM mail m WHERE m.id > (SELECT last_id FROM tmp_mail);

Еще немного про WITH

Надо всем аватарам, у которых сдан квест на "Кащея" отправить "Меч кладенец" по игровой почте.

-- Создать письма и сохранить их идентификаторы
CREATE TEMPORARY TABLE tmp_mail ON COMMIT DROP AS
WITH new_mail AS (
  INSERT INTO mail (recipient_id, mail_rid)
  SELECT q.avatar_id, 17
  FROM quest q WHERE q.quest_rid = 42
  RETURNING id
)
SELECT * FROM new_mail;

-- Создать предметы в письмах
INSERT INTO item (mail_id, item_rid)
SELECT m.id, 7291
FROM mail m WHERE m.id > (SELECT last_id FROM tmp_mail);

Еще немного про WITH

Надо всем аватарам, у которых сдан квест на "Кащея" отправить "Меч кладенец" по игровой почте.

-- Создать письма и предметы в письмах
WITH new_mail AS (
  INSERT INTO mail (recipient_id, mail_rid)
  SELECT q.avatar_id, 17
  FROM quest q WHERE q.quest_rid = 42
  RETURNING id
)
INSERT INTO item (mail_id, item_rid)
SELECT m.id, 7291
FROM new_mail m;

Составление запросов

Составление запроса. Пример 1

Найти имена и классы, которые имеют друзей только в том же классе. Вернуть результат, отсортированный по классу, затем имени в классе.

CREATE TABLE highschooler (
    id    BIGSERIAL PRIMARY KEY,
    name  VARCHAR(128),
    grade INT
);

CREATE TABLE friend (
    id1 INT REFERENCES highschooler (id) ON DELETE CASCADE,
    id2 INT REFERENCES highschooler (id) ON DELETE CASCADE
);

Дружба взаимная, если есть запись (123, 456), то есть и (456, 123).

Составление запроса. Пример 1

Найти имена и классы, которые имеют друзей только в том же классе. Вернуть результат, отсортированный по классу, затем имени в классе.

Составление запроса. Пример 1

Найти имена и классы, которые имеют друзей только в том же классе. Вернуть результат, отсортированный по классу, затем имени в классе.

Есть в том же классе и нет в других классах
==
Есть друзья и нет в других классах

Составление запроса. Пример 1

Найти имена и классы, которые имеют друзей только в том же классе. Вернуть результат, отсортированный по классу, затем имени в классе.

    SELECT H.name, H.grade
FROM highschooler AS H
JOIN friend AS F ON (H.Id = F.Id1)
LEFT JOIN highschooler AS O ON (F.Id2 = O.Id AND H.Grade <> O.Grade)

SELECT H.name, H.grade
FROM highschooler AS H
JOIN friend AS F ON (H.Id = F.Id1)
LEFT JOIN highschooler AS O ON (F.Id2 = O.Id AND H.Grade <> O.Grade)
GROUP BY H.grade, H.name, H.id
HAVING COUNT(O.Id) = 0
ORDER BY H.grade, H.name
    

Составление запроса. Пример 2

Найти разницу между средней оценкой фильмов выпущенных до 1980 года, и средней оценкой фильмов выпущенных после 1980 года.

CREATE TABLE movie (
    mID      SERIAL PRIMARY KEY,
    title    VARCHAR(128),
    year     INT,
    director VARCHAR(128)
);

CREATE TABLE rating (
    rID        SERIAL PRIMARY KEY,
    mID        INT REFERENCES movie (mID) ON DELETE CASCADE,
    stars      INT,
    ratingDate TIMESTAMP
);

Составление запроса. Пример 2

Найти разницу между средней оценкой фильмов выпущенных до 1980 года, и средней оценкой фильмов выпущенных после 1980 года.

SELECT
    AVG(CASE WHEN year > 1980 THEN stars ELSE NULL END) AS after,
    AVG(CASE WHEN year < 1980 THEN stars ELSE NULL END) AS before
FROM (
    SELECT M.mID, M.year, AVG(R.stars) as stars
    FROM movie M
    JOIN rating R USING (mID)
    GROUP BY M.mID, M.year
) AS R

SELECT
    AVG(CASE WHEN year > 1980 THEN stars ELSE NULL END) AS after,
    AVG(CASE WHEN year < 1980 THEN stars ELSE NULL END) AS before
FROM (
    SELECT M.mID, M.year, AVG(R.stars) as stars
    FROM Movie M
    JOIN Rating R USING (mID)
    GROUP BY M.mID, M.year
) AS R

Составление запроса. Пример 2

Найти разницу между средней оценкой фильмов выпущенных до 1980 года, и средней оценкой фильмов выпущенных после 1980 года.

SELECT
    AVG(CASE WHEN year > 1980 THEN stars ELSE NULL END) -
    AVG(CASE WHEN year < 1980 THEN stars ELSE NULL END) AS result
FROM (
    SELECT M.mID, M.year, AVG(R.stars) as stars
    FROM movie M
    JOIN rating R USING (mID)
    GROUP BY M.mID
) AS R

Составление запроса. Пример 2

Найти разницу между средней оценкой фильмов выпущенных до 1980 года, и средней оценкой фильмов выпущенных после 1980 года.

SELECT AVG(R.s_after) - AVG(R.s_before) AS result
FROM (
    SELECT
        AVG(CASE WHEN year > 1980 THEN stars ELSE NULL END) AS s_after,
        AVG(CASE WHEN year < 1980 THEN stars ELSE NULL END) AS s_before
    FROM movie M 
    JOIN rating R USING (mID)
    GROUP BY M.mID
) AS R

Представления (VIEW)

VIEW

Представление (VIEW)

Объект базы данных, являющийся результатом выполнения запроса к базе данных, определенного с помощью оператора SELECT, в момент обращения к представлению.

CREATE
    [ OR REPLACE ]
    [ TEMP | TEMPORARY ]
    [ RECURSIVE ]
VIEW name [ ( column_name [, ...] ) ]
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS query
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ];

Преимущества VIEW

• Дает возможность гибкой настройки прав доступа к данным за счет того, что права даются не на таблицу, а на представление.
• Позволяет разделить логику хранения данных и программного обеспечения.
• Удобство в использовании за счет автоматического выполнения таких действий как доступ к определенной части строк и/или столбцов, получение данных из нескольких таблиц и их преобразование с помощью различных функций.

Ограничения VIEW

• Нельзя повесить триггер на представление;
• Таблицы и представления, присутствующие в определении представления должны существовать.

Особенности VIEW

CREATE VIEW v AS
    SELECT a.id, b.id FROM a, b;
-- ERROR: column "id" specified more than once
-- SQL-состояние: 42701

CREATE VIEW v (a_id, b_id) AS
    SELECT a.id, b.id FROM a,b;
CREATE VIEW v AS
    SELECT a.id a_id, b.id b_id FROM a,b;

Особенности VIEW

• Если в обоих операторах встречается условие WHERE, то оба этих условия будут выполнены как если бы они были объединены оператором AND.
• Если в определении представления есть конструкция ORDER BY, то она будет работать только в случае отсутствия во внешнем операторе SELECT, обращающемся к представлению, собственного условия сортировки. При наличии конструкции ORDER BY во внешнем операторе сортировка, имеющаяся в определении представления, будет проигнорирована.

Изменяемые VIEW

• Список FROM в запросе, определяющем представлении, должен содержать ровно один элемент, и это должна быть таблица или другое изменяемое представление.
• Определение представления не должно содержать предложения WITH, DISTINCT, GROUP BY, HAVING, LIMIT и OFFSET на верхнем уровне запроса.
• Определение представления не должно содержать операции с множествами (UNION, INTERSECT и EXCEPT) на верхнем уровне запроса.
• Список выборки в запросе не должен содержать агрегатные и оконные функции, а также функции, возвращающие множества.

Изменение VIEW

CREATE TABLE films (
    imdb        varchar(16) PRIMARY KEY,
    title       varchar(40) NOT NULL,
    kind        varchar(10)
);

CREATE OR REPLACE VIEW comedies AS
    SELECT *
    FROM films
    WHERE kind = 'Comedy'
    WITH CASCADED CHECK OPTION;

INSERT INTO comedies (imdb, title, kind)
    VALUES ('tt0114709', 'Toy Story 1', 'Animation');
-- ERROR:  new row violates check option for view "comedies"
-- DETAIL:  Failing row contains (tt0114709, Toy Story 1, Animation).

INSERT INTO comedies (imdb, title, kind)
    VALUES ('tt1156398', 'Zombieland', 'Comedy');