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

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

• Выборка данных:
  • Подзапросы;
  • UNION;
  • Еще раз про JOIN-ы;
  • Рекурсивные запросы.
• Иерархические структуры в базах данных.
• Примеры составления запросов.

Организационные моменты

• Не забывайте отмечаться на лекциях!
• Оставляйте обратную связь по завершению лекции!

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

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 ] [...] ]

FROM. Типы таблиц

• Постоянные таблицы
• Временные таблицы
• Виртуальные таблицы (представления)
• Подзапросы (вложенные запросы)

WHERE. Фильтрация

• =
• !=, <>
• >, <, >=, <=
• OR, AND, NOT
• a BETWEEN x AND y
• a IN (x, y, z...)
• a LIKE p (_, %)

SELECT *
FROM persons
WHERE department_id IN (
    SELECT id FROM departments WHERE company_id = 42
)

CASE

CASE WHEN condition THEN result
     [WHEN ...]
     [ELSE result]
END

NULLIF(value1, value2)

GREATEST(value [, ...])

LEAST(value [, ...])

SELECT a,
   CASE WHEN a=1 THEN 'one'
        WHEN a=2 THEN 'two'
        ELSE 'other'
   END
FROM test;

ORDER BY. Сортировка

SELECT select_list
FROM table_expression
ORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }]
      [, sort_expression2 [ASC | DESC] [NULLS { FIRST | LAST }] ...]

SELECT a, b FROM table1 ORDER BY a + b, c;

SELECT a + b AS sum, c FROM table1 ORDER BY sum;
SELECT a, max(b) FROM table1 GROUP BY a ORDER BY 1;

# SELECT a + b AS sum, c FROM table1 ORDER BY sum + c;
ERROR:  column "sum" does not exist
LINE 1: SELECT a + b AS sum, c FROM table1 ORDER BY sum + c;
                                                    ^

GROUP BY. Формирование групп

• Группировка по одному столбцу
• Группировка по нескольким столбцам
• Группировка посредством выражений

GROUP BY. Формирование групп

SELECT * FROM employee;

GROUP BY. Формирование групп

SELECT grade, department, COUNT(*)
FROM employee
GROUP BY grade, department
ORDER BY grade, department;

Агрегаторы

aggregate_name (
    [ ALL | DISTINCT ] expression [ , ... ]
    [ order_by_clause ]
) [ FILTER ( WHERE filter_clause ) ]

SELECT
  string_agg(name, ',' ORDER BY name),
  avg(age),
  count(*) FILTER (WHERE age BETWEEN 13 AND 19) AS teens
FROM persons;

Агрегаторы

Агрегаторы

Агрегаторы

HAVING. Фильтрация после группировки

SELECT grade, department, COUNT(*)
FROM employee
GROUP BY grade, department
HAVING COUNT(*) > 3;

SELECT * FROM (
    SELECT grade, department, COUNT(*) as employee_cnt
    FROM employee
    GROUP BY grade, department
) a
WHERE employee_cnt > 3;

LIMIT. Разбивка на страницы

ISO SQL:2003 - Window Functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY age ASC) AS rownumber,
    person_id, person_name, age
  FROM person
) AS foo
WHERE rownumber <= 10;

SELECT * FROM (
  SELECT
    RANK() OVER (ORDER BY age ASC) AS ranking,
    person_id, person_name, age
  FROM person
) AS foo
WHERE ranking <= 10;

LIMIT. Разбивка на страницы

Синтаксис MySQL:

SELECT
  person_id, person_name, age
FROM person
LIMIT 10
OFFSET 20;

Синтаксис DB2:

SELECT
  person_id, person_name, age
FROM person
FETCH FIRST 10 ROWS ONLY;

PostgreSQL поддерживает оба синтаксиса

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

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

Это подзапрос, который содержит ссылку на столбцы из включающего его запроса (назовем его основным). Таким образом, коррелирующий подзапрос будет выполняться для каждой строки основного запроса, так как значения столбцов основного запроса будут меняться.

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

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

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;

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 (Common Table Expressions)

WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ),
     top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

WITH (Common Table Expressions)

Главный плюс и минус CTE в том, что запрос описанный в WITH будет вычисляться единожды ДО выполнения основного запроса.

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

По-умолчанию это происходит, только если запрос WITH задействуется в родительском запросе всего в одном месте, а не в нескольких.

WITH w AS (
    SELECT * FROM big_table
)
SELECT * FROM w WHERE key = 123;

Будет аналогичен:

SELECT * FROM big_table WHERE key = 123;

WITH RECURSIVE

WITH RECURSIVE recursetree (id, parent_id, level, name) AS (
    SELECT id, parent_id, 0, name FROM tree
    WHERE parent_id = 0
  UNION ALL
    SELECT t.id, t.parent_id, level+1, t.name
    FROM tree t
    JOIN recursetree rt ON rt.id = t.parent_id
  )
SELECT * FROM recursetree;

WITH RECURSIVE

WITH RECURSIVE recursetree (id, path, name) AS (
    SELECT id, array_append('{}'::int[], 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 RECURSIVE

WITH RECURSIVE t(n) AS (
    VALUES (1)
    UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;
    

Иерархические структуры в базах данных

Список смежных вершин (Adjacency List)

Список смежных вершин (Adjacency List)

CREATE TABLE IF NOT EXISTS category(
    id     serial primary key,
    name   varchar(20) not null,
    parent int default null references category (id)
);

INSERT INTO category VALUES
        (1,'ELECTRONICS',NULL),
        (2,'TELEVISIONS',1),(3,'TUBE',2),
        (4,'LCD',2),
        (5,'PLASMA',2),
        (6,'PORTABLE ELECTRONICS',1),
        (7,'MP3 PLAYERS',6),
        (8,'FLASH',7),
        (9,'CD PLAYERS',6),
        (10,'2 WAY RADIOS',6);

Список смежных вершин (Adjacency List)

SELECT * FROM category ORDER BY id;

Список смежных вершин (Adjacency List)

Получение всего дерева

SELECT t1.name AS lvl1, t2.name as lvl2, t3.name as lvl3, t4.name as lvl4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.id
LEFT JOIN category AS t3 ON t3.parent = t2.id
LEFT JOIN category AS t4 ON t4.parent = t3.id
WHERE t1.name = 'ELECTRONICS';

Список смежных вершин (Adjacency List)

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

WITH RECURSIVE tree(id, name, parent, lvl) AS (
    SELECT t.id, t.name, t.parent, 0 from category t WHERE t.parent IS NULL
    UNION ALL
    SELECT t.id, t.name, t.parent, t1.lvl + 1 FROM category t
        JOIN tree AS t1 ON t1.id = t.parent
) SELECT CONCAT(REPEAT('-', lvl::int), name) FROM tree ORDER BY id;

Список смежных вершин (Adjacency List)

Получение листовых узлов

SELECT t1.name
FROM category as t1
         LEFT JOIN category as t2 on t1.id = t2.parent
WHERE t2.id is null;

Список смежных вершин (Adjacency List)

Получение одной ветви

SELECT t1.name AS lvl1, t2.name as lvl2, t3.name as lvl3, t4.name as lvl4
FROM category AS t1
         LEFT JOIN category AS t2 ON t2.parent = t1.id
         LEFT JOIN category AS t3 ON t3.parent = t2.id
         LEFT JOIN category AS t4 ON t4.parent = t3.id
WHERE t1.name = 'ELECTRONICS' AND t4.name = 'FLASH';

Список смежных вершин (Adjacency List)

Получение путей рекурсивно

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

Список смежных вершин (Adjacency List)

Вложенное множество (Nested Set)

Вложенное множество (Nested Set)

Вложенное множество (Nested Set)

Вложенное множество (Nested Set)

CREATE TABLE IF NOT EXISTS nested_category
(
    id   serial primary key,
    name text not null,
    lft  int  not null,
    rgt  int  not null
);

INSERT INTO nested_category
VALUES (1, 'ELECTRONICS', 1, 20),
       (2, 'TELEVISIONS', 2, 9),
       (3, 'TUBE', 3, 4),
       (4, 'LCD', 5, 6),
       (5, 'PLASMA', 7, 8),
       (6, 'PORTABLE ELECTRONICS', 10, 19),
       (7, 'MP3 PLAYERS', 11, 14),
       (8, 'FLASH', 12, 13),
       (9, 'CD PLAYERS', 15, 16),
       (10, '2 WAY RADIOS', 17, 18);

Вложенное множество (Nested Set)

SELECT * FROM nested_category ORDER BY id;

Вложенное множество (Nested Set)

SELECT node.name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND parent.name = 'ELECTRONICS'
ORDER BY node.lft;

Вложенное множество (Nested Set)

Получение всего дерева

SELECT CONCAT(REPEAT('-', (COUNT(parent.name) - 1)::int), node.name) AS name
FROM nested_category AS node,
     nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name, node.lft
ORDER BY node.lft;

Вложенное множество (Nested Set)

Получение листовых узлов

SELECT name
FROM nested_category
WHERE rgt = lft + 1;
    

Вложенное множество (Nested Set)

Получение одной ветви

SELECT parent.name
FROM nested_category AS node, nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.name = 'FLASH'
ORDER BY parent.lft;
    

Вложенное множество (Nested Set)

Материализованный путь (Materialized Path)

Материализованный путь (Materialized Path)

CREATE TABLE IF NOT EXISTS materialized_category
(
    id   SERIAL PRIMARY KEY,
    name TEXT  NOT NULL,
    path INT[] NOT NULL
);

INSERT INTO materialized_category
VALUES (1, 'ELECTRONICS', '{1}'),
       (2, 'TELEVISIONS', '{1,2}'),
       (3, 'TUBE', '{1,2,3}'),
       (4, 'LCD', '{1,2,4}'),
       (5, 'PLASMA', '{1,2,5}'),
       (6, 'PORTABLE ELECTRONICS', '{1,6}'),
       (7, 'MP3 PLAYERS', '{1,6,7}'),
       (8, 'FLASH', '{1,6,7,8}'),
       (9, 'CD PLAYERS', '{1,6,9}'),
       (10, '2 WAY RADIOS', '{1,6,10}');

Материализованный путь (Materialized Path)

SELECT * FROM materialized_category ORDER BY id;

Материализованный путь (Materialized Path)

Получение всего дерева

SELECT id,
        CONCAT(REPEAT('-', (ARRAY_LENGTH(path, 1) - 1)::int), name),
        path
FROM materialized_category;

Материализованный путь (Materialized Path)

Комбинированный подход

Имеет смысл комбинировать следующие подходы:

• Списки смежности + материализованный путь (Adjacency List + Materialized Path)
• Списки смежности + вложенные множества (Adjacency List + Nested Set)

Комбинировать же Nested Set и Materialized Path особого смысла не имеет, т.к. существенного выигрыша ни в чтении, ни в записи вы не получите.