Главная » Хабрахабр » Задачи и решения для бойца PostgreSQL

Задачи и решения для бойца PostgreSQL

Приветствую всех любителей SQL!

В интернете я редко встречал статьи, которые охватывают разные рабочие моменты и тонкости, связанные с обработкой данных в SQL.
Мне нравится, когда из одной статьи можно почерпнуть сразу много всего, пусть даже в общих чертах.
Поэтому решил написал свою статью, содержащую различные задачи и ответы с пояснениями к ним.
Подойдет для тех, кто хорошо освоил все базовые навыки и хочет развиваться дальше.

Даже интересно, где возникнут отличия) Представленные ответы подходят для 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.

0)*25 отработал бы более корректно SELECT (20/25.

2 Дана таблица "table_2" (с единственным столбцом "value"(INTEGER)) состоящая из следующих 5 строк:
1.

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, но думаю другие СУБД не сильно отличаются

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. Работа с текстом. Регулярные выражения

Думаю нужно уметь не только строить запросы, но и представлять результаты в нужном виде.
Регулярные выражения — это отдельная огромная тема, со множеством качественных статей. Поэтому я лишь покажу примеры, без подробных объяснений.

1. Допустим, есть таблица "table_5" с текстовым столбцом "X" и множеством разнообразных строк. 4. Каким запросом можно получить любые последние 10 символов каждой строки?

Ответ на 4.1

SQL позволяет придумать массу решений одной и той же задачи, к примеру:
самое простое, что приходит на ум это — right(X,10)
можно использовать регулярное выражение: substring( X, '.$' )
можно даже накостылять «извертеться»(во всех смыслах) так: reverse(substring(reverse(X) for 10))

2 Имеется таблица «table_6» с текстовым столбцом «X». 4. В таблице содержится одна строка(весь текст только на английском и русском языке):

'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)+2) 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)

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.

S. P. Строка должна содержать только русские символы, и переживать за возможное изменение английских слов не стоит.

(Если возникают трудности с заменой всех символов, то замените хотя бы один)

Пример строки:

X = 'Cтрoитeльствo или рeкoнcтрукция oбъeкта'

Ответ на 4.4

-- Несомненно, Replace(Replace(Replace(.. потрясающее решение, но
-- специальная функция гораздо изящнее для такого случая (1 символ на 1 символ)
-- *возможно TRANSLATE имеет другое название в других СУБД 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_1 при запросе: А) Какой результат получит User_2, если User_1 оставит транзакцию открытой (т.е.

SELECT discount FROM goods LIMIT 1;

Б) Что произойдет, если User_1 сделает ROLLBACK? Какие результаты получит User_2?

Какие результаты получит User_2? В) Что произойдет, если User_1 сделает COMMIT?

Ответы

Насколько я знаю READ UNCOMMITTED не поддерживается в PostgreSQL, и «грязные» (не подтвержденные) данные прочитать не получится

Ответы будут следующими:

(запрос словно подвиснет)
User_1 в своей транзакции будет видеть свою версию снимка базы, где discount уже равняется 15 А) Запрос User_2 будет ожидать COMMIT или ROLLBACK от User_1.

Б) Если User_1 сделает ROLLBACK, то значение discount останется прежним, а следом выполнится запрос User_2, который прибавит 10 к discount и discount будет равен 20

В) Если User_1 сделает COMMIT, то значение discount увеличится на 5, а следом выполнится запрос User_2, который прибавит 10 к discount и discount будет равен 25

Заключение

Думаю, что затронул достаточно интересные моменты.

Надеюсь, задачи помогут промотивировать начинающих, ведь скучно что-либо изучать без конкретных целей/задач/направлений.

А те, у кого возникали сложности, надеюсь, получили пинок направление развития. Могу порадоваться за тех, кому было легко ответить на все вопросы. Те кто мало что понял, но хочет освоить SQL, приглашаю на свою прошлую статью Курс молодого бойца PostgreSQL.

Жду каких-либо дополнений, решений особо интересных задач(можно своих) и прочих комментариев!

Желаю успехов в изучении SQL! Спасибо за внимание!


Оставить комментарий

Ваш email нигде не будет показан
Обязательные для заполнения поля помечены *

*

x

Ещё Hi-Tech Интересное!

Больше всех пахала лошадь, но председателем колхоза так и не стала

Мне стало интересно понять профит от этих штук. В последнее время в мобильном сообществе часто можно услышать про Flutter, React Native. В итоге было создано 4 (одинаковых с точки зрения выполняемых функции) приложения: нативное Android, нативное iOS, Flutter, React Native. ...

Бесплатная трансляция DotNext 2018 Moscow

Меньше недели осталось до конференции DotNext 2018 Moscow: она пройдет в конгресс-парке гостиницы «Рэдиссон Ройал Москва» 22-23 ноября. Между докладами будут вестись интервью с ключевыми спикерами конференции. По традиции, прямо на YouTube будет открыта бесплатная онлайн-трансляция первого зала (ссылка спрятана ...