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

в 14:14, , рубрики: postgres, postgresql, sql, tips and tricks, Администрирование баз данных, базы данных, Программирование, СУБД, тестовые задачи, тесты

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


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

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

Публикация несет образовательный характер, и является продолжением моей прошлой статьи Курс молодого бойца 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, но думаю другие СУБД не сильно отличаются

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)+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.

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

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

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

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_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

Заключение

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

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

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

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

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

Автор: postgres

Источник

* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js