Задачи и решения для бойца PostgreSQL
В этой статье содержатся различные задачи и ответы с пояснениями к ним. Она подойдет для тех, кто хорошо освоил все базовые навыки и хочет развиваться дальше. Представленные ответы подходят для PostgreSQL (большинство задач подойдут и для других СУБД, но результаты и решения могут быть иными. Даже интересно, где возникнут отличия). Звездочкой отмечено что-либо сугубо для PostgreSQL* (таких моментов не много)
1. Немного о числовых операциях
1.1 Выполнятся ли эти запросы? Какие результаты они вернут?
-- А) Начнем с простого запроса
SELECT 3/2;
-- Б)
SELECT min('Какой-то текст'::TEXT), avg('Какой-то текст'::TEXT);
-- В)* Почему данный запрос может вернуть FALSE, возможно ли такое поведение СУБД?
SELECT 7.2 = (3.8::FLOAT + 3.4)
-- Г)
SELECT (20/25)*25.0;Ответы на 1.1
А) Ответ: 1
Будет показана только целая часть, т.к. при операции используются целые числа. Такое часто встречается и в других языках.
Б) Ответ: запрос не выполнится.
avg выдаст ошибку, т.к. принимает только числа и временные интервалы*
Однако функция min / max может выполняться на текстовых данных (в соответствии с алфавитной сортировкой в БД).
Иногда это может быть полезно, когда нужно хотя бы посмотреть на столбец, который не перечислен в GROUP BY
Или когда к числам нужно применить алфавитную сортировку, при которой '10' < '2'
В) Ответ: FALSE
Может показаться странным, но такое допустимо, т.к. это особенность представления компьютером некоторых чисел с плавающей точкой, число может принять вид 7.1(9)
Вспоминается, как когда-то я долго разбирался с запросом, не зная этого
Г) Ответ: 0. подвох в том, что выражение в скобках будет =0
SELECT (20/25.0)*25 отработал бы более корректно
1.2 Дана таблица "table_2" (с единственным столбцом "value"(INTEGER)) состоящая из следующих 5 строк:
|
value |
|
5 |
|
5 |
|
NULL |
|
5 |
|
5 |
Какой результат вернет запрос:
SELECT (avg(value)*count(*)) - sum(value) FROM table_2;
Варианты ответов
- -4
- 0
- NULL
- 5
- Вызовет ошибку, т.к. не указан GROUP BY
- Ни один из перечисленных
Ответ 1.2
ответ: 5
Агрегатные функции, примененные к конкретному столбцу, игнорируют NULL, однако count(*) посчитает все строки
5 * 5 — 20
2. Общие вопросы
2.1 В каких случаях запрос может вернуть не всё содержимое таблицы? (parent_id INTEGER, таблица наполнена разнообразными данными)
SELECT * FROM any_table WHERE parent_id = parent_id;
А как поведет себя запрос ниже? Какие данные он выведет? *PostgreSQL
SELECT * FROM any_table WHERE parent_id IS NOT DISTINCT FROM parent_id;
Ответы на 2.1
Первый запрос покажет все записи, кроме тех, где parent_id является NULL
Второй запрос покажет все записи таблицы. IS DISTINCT FROM по логике похож на оператор != в котором NULL идентичен NULL
IS NOT DISTINCT FROM логически обратит неравенство в равенство
2.2. Какой результат будет у запроса?
-- А)
SELECT * FROM (
SELECT 1
UNION ALL
SELECT 1
) x(y)
UNION
(
SELECT 2
UNION ALL
SELECT 2
);
Ответ на 2.2
Результатом будет 2 строки со значениями 1 и 2, UNION удалит все дубликаты в результирующей выборке, а не только между двумя объединяемыми таблицами. Замечал, что не для всех это очевидно.
2.3 Напишите запрос, который покажет завтрашнюю дату.
Ответ на 2.3
SELECT CAST((now()+ INTERVAL '1 DAY') AS DATE)
Не все часто работают с датами, но какой-то минимум освоить стоит
*Решение для Postgres, но думаю другие СУБД не сильно отличаются
Если работа с датами Вам в новинку, то советую поэкспериментировать с запросом
Например:
— заменить DAY на (week, month, year и т.д.)
— заменить +1 на -9000
— заменить DATE на TIME
— убрать CAST
— оставить только NOW()
и т.д.
И, вдохновившись какими-то результатами, отправляйтесь читать MANUAL, все темы там подробно раскрыты
2.4 Операторы UPDATE, DELETE , INSERT и MERGE созданы для манипулирования данными в таблицах. А является ли выполнение SELECT .. «безопасным»? Может ли какой-либо запрос повлиять на данные в таблице?
Ответ на 2.4
Вопрос может показаться примитивным, однако…
В самом начале изучения SQL, у меня складывалось мнение, что этот оператор может только показывать данные, но:
Помимо того, что SELECT способен заблокировать таблицу на изменение (BEGIN; SELECT… FOR UPDATE) *
SELECT способен вызывать функции, которые могут выполнять практически любые манипуляции.
Новичкам нужно это понимать сразу, а не после выполнения «маленького информационного» запроса на Production сервере
3. Only PostgreSQL
3.1 Опишите, что произойдет при выполнении данного запроса в SQL диалоге:
SELECT * INTO wtf FROM pg_stat_activity;
Ответ на 3.1
Обычно SELECT INTO используется в функциях plpgsql, для записи значения в переменную.
Вне plpgsql эффект команды будет аналогичен запросу ниже:
CREATE TABLE wtf AS SELECT * FROM pg_stat_activity;
3.2 что покажет данный «простой» запрос
SELECT wtf_ FROM pg_stat_activity AS wtf_ ;
Ответ на 3.2
pg_stat_activity системное представление (VIEW) активных процессов в базе.
Особенность запроса в том, что будет выведен один столбец со строками (ROW) имеющими TYPE pg_stat_activity (или другой таблицы). Знать это нужно скорее тем, кто пишет функции, подробнее можно почитать в мануале
Вопрос добавил потому, что новичок может легко по ошибке получить такой результат, и не понимать в чем дело
4. Работа с текстом. Регулярные выражения
Нужно уметь не только строить запросы, но и представлять результаты в нужном виде. Регулярные выражения — это отдельная огромная тема, со множеством качественных статей. Поэтому ниже показаны примеры, без подробных объяснений.
4.1. Допустим, есть таблица "table_5" с текстовым столбцом "X" и множеством разнообразных строк. Каким запросом можно получить любые последние 10 символов каждой строки?
Ответ на 4.1
SQL позволяет придумать массу решений одной и той же задачи, к примеру:
самое простое, что приходит на ум — это right(X,10)
можно использовать регулярное выражение: substring( X, '.{0,10}$' )
можно даже накостылять «извертеться»(во всех смыслах) так: reverse(substring(reverse(X) for 10))
4.2 Имеется таблица «table_6» с текстовым столбцом «X». В таблице содержится одна строка(весь текст только на английском и русском языке):
'Lorem 3 Ipsum 23 standard 7 dummy 17 text Ultimate Answer of Life ?? 777'
А) Напишите запрос, который вернет символы с 42-го по 68-ый из этой строки
Б) Как вытащить только ЗАГЛАВНЫЕ (русские или английские) буквы в строке с помощью SQL?
В) Как посчитать сумму чисел (не цифр) в строке с помощью SQL
SQL набросок
WITH table_6(X) AS(
SELECT 'Lorem 3 Ipsum 23 standard 7 dummy 17 text Ultimate Answer of Life ?? 777'::TEXT
)
SELECT X FROM table_6
Ответы на 4.2
-- ТУТ должен быть WITH из "SQL наброска"
-- А)
SELECT SUBSTRING(LEFT(X,68) FROM 42 ) FROM table_6 -- 1 вариант
SELECT SUBSTRING(X, 42, (68-42)+1) FROM table_6 -- 2 вариант
-- 3 вариант с Вас
-- Б) Все просто, заменяем всё кроме заглавных букв на пустоту
SELECT regexp_replace(X,'[^A-ZА-ЯЁ]', '','g') FROM table_6
-- Буква 'Ё' обычно не входит в диапазон А-Я
-- без параметра 'g' замена произойдет лишь 1 раз
-- В) Без регулярных выражений задача может показаться кошмаром
-- С помощью regexp_matches и жадного поиска** получаем массивы чисел в столбик, и суммируем вытащив из массива
SELECT sum(x[1]::INT)
FROM (
SELECT regexp_matches(X,'[0-9]+','g') FROM table_6
) AS y(x)
-- * в других СУБД функции могут иначе называться
-- ** если уберем +, то получим сумму цифр (будет браться лишь 1 символ, а не вся максимальная последовательность)
4.3 Как заменить в тексте (ячейке таблицы) все двойные (тройные и более) пробелы на одинарный пробел? (по традиции: таблица "table_7" со столбцом "X") (P.S. достаточно будет написать SELECT возвращающий нужный результат, а не UPDATE table_7 ...)
Ответ на 4.3
WITH table_7(X) AS (SELECT 'Lorem 3 Ipsum 23 standard 7
dummy 11
text'::TEXT)
-- 1 вариант. Заменяем только пробелы (2 и более подряд)
SELECT regexp_replace(X, '( ){2,}', ' ', 'g') FROM table_7
-- 2 вариант. Заменяем все пробельные символы (табуляция, неразрывный пробел, перевод строки и т.д.) на один пробел, даже если эти символы чередуются
SELECT regexp_replace(X, '\s+', ' ', 'g') FROM table_7
-- Отчаянный вариант! Думаю те, кто когда-либо искал решение подобной задачи, натыкались на такое "изящное" решение. Без использования регулярных выражений..
-- Работает весьма хитро, на любом количестве пробелов, главное, чтобы текст не содержал используемых подстановочных символов
-- Не рекомендую такое использовать, но для разминки ума стоит понять, как оно работает
SELECT replace(replace(replace(X, ' ', '<>'), '><', ''), '<>', ' ') FROM table_7
4.4 Имеется строка "X" в которой допущены опечатки. Вместо русских букв (е, о, с, С ) были использованы внешне похожие на них символы английского алфавита. Произведите замену данных символов с помощью SQL.
P.S. Строка должна содержать только русские символы, и переживать за возможное изменение английских слов не стоит.
(Если возникают трудности с заменой всех символов, то замените хотя бы один)
Пример строки: X = 'Cтрoитeльствo или рeкoнcтрукция oбъeкта'
Ответ на 4.4
-- Несомненно, Replace(Replace(Replace(.. потрясающее решение, но
-- специальная функция гораздо изящнее для такого случая (1 символ на 1 символ)
SELECT TRANSLATE('Cтрoитeльствo или рeкoнcтрукция oбъeкта', 'Cceo', 'Ссео')
4.5 Напишите запрос, который преобразует строку:
'иВАнОв ИВан иВановиЧ' к виду 'Иванов Иван Иванович'
Ответ на 4.5
-- Все просто, когда имеется такая функция
SELECT initcap('иВАнОв ИВан иВановиЧ')
*Возможно в других СУБД имеются аналоги
Бонусное задание для тех, кто справился
Здорово, если есть готовая функция
А сможете преобразовать наоборот? (желательно не теряя отступов).
Возможно задача не типичная, но для развития будет полезна.
'иВАнОв ИВан иВановиЧ' преобразовать к 'иВАНОВ иВАН иВАНОВИЧ'
а инвертировать регистр?
Ответ на бонусное задание
SELECT string_agg(LOWER(LEFT(x,1)) || UPPER(SUBSTRING(x from 2)), '' ORDER BY rn)
FROM
(SELECT * FROM regexp_split_to_table(' иВАнОв ИВан иВановиЧ
4 TesT', '\y') WITH ORDINALITY y(x, rn) ) AS z
-- *Решение для PostgreSQL, но смысл везде такой же
-- Придумал на скорую руку, без использования процедурного языка
-- возможно неуклюжее и не производительное решение, но для данного примера подходит
-- сохраняет все пробельные символы между словами.
-- WITH ORDINALITY нужен для сохранения порядка символов (доступен с версии 9.4)
-- Разбираем строку по границам слова
-- получаем отдельно строки со словами и разделяющими их символами
-- обрабатываем..
5. Чуть-чуть о транзакциях
Транзакции очень важная вещь в СУБД, достаточно важно понимать основные моменты.
Попробую смоделировать пример:
Допустим, есть таблица «goods» с которой собираются работать два пользователя.
В ней имеется целочисленный столбец discount равный 10 для всех строк.
Настройки базы данных стандартные (READ COMMITTED — чтение зафиксированных данных).
Пользователь User_1 открывает транзакцию, выполняет следующий запрос:
BEGIN; UPDATE goods SET discount = discount + 5;
Секундой позже, другой пользователь (User_2)
Выполняет без открытия транзакции почти такой же запрос:
UPDATE goods SET discount = discount + 10;
Как думаете, что произойдет при следующих раскладах:
А) Какой результат получит User_2, если User_1 оставит транзакцию открытой (т.е. не подтвердит транзакцию / не откатит изменения)?
Что увидит User_1 при запросе:
SELECT discount FROM goods LIMIT 1;
Б) Что произойдет, если User_1 сделает ROLLBACK? Какие результаты получит User_2?
В) Что произойдет, если User_1 сделает COMMIT? Какие результаты получит User_2?
Ответы
Насколько я знаю READ UNCOMMITTED не поддерживается в PostgreSQL, и «грязные» (не подтвержденные) данные прочитать не получится
Ответы будут следующими:
А) Запрос User_2 будет ожидать COMMIT или ROLLBACK от User_1. (запрос словно подвиснет)
User_1 в своей транзакции будет видеть свою версию снимка базы, где discount уже равняется 15
Б) Если User_1 сделает ROLLBACK, то значение discount останется прежним, а следом выполнится запрос User_2, который прибавит 10 к discount и discount будет равен 20
В) Если User_1 сделает COMMIT, то значение discount увеличится на 5, а следом выполнится запрос User_2, который прибавит 10 к discount и discount будет равен 25
Другая версия этой задачи
Немного другая версия задачи 13 от пользователя kirill_petrov на особенность READ COMMITTED
-- Имеется таблица с двумя строками CREATE TABLE goods (discount) AS (SELECT 10::INT UNION ALL SELECT 15); -- 1. User_1 выполняет запрос (транзакция остается открыта): BEGIN; UPDATE goods SET discount = discount + 5; --2. User_2 выполняет запрос: UPDATE goods SET discount = discount + 100 WHERE discount = 15 --3. User_1 выполняет COMMIT;
Какие данные окажутся в таблице?