Задача с курсами валют
Постановку задачи смотрите в посте вторника.

Довольно баянная задача. Периодически встречается на собесах.

1. Для решения используем конструкцию keep dense rank в режиме группировки по name (group by name).
2. Сортировка внутри групп по колонке date по убыванию (desc). Если есть null-значения в колонке date, то они уйдут в самый верх отсортированных значений (nulls last).
3. first - берем первый результат ранжирования внутри группы ибо desc-сортировка.
4. Для каждого требуемого столбца результата выбираем соответствующие столбцы - date/rate. min - получает минимальное значение из первого результата. В данном случае, без разницы min/max.

Итого:
select t.name
,min(ddate) keep(dense_rank first order by ddate desc nulls last) ddate
,min(rate) keep(dense_rank first order by ddate desc nulls last) rate
from FxRates t
group by t.name;

Запрос можно переписать на такой:
select t.name
,min(ddate) keep(dense_rank last order by ddate asc nulls first) ddate
,min(rate) keep(dense_rank last order by ddate asc nulls first) rate
from FxRates t
group by t.name;

Объяснения может не хватить, особенно, если вы никогда не сталкивались с этим. Рекомендую воссоздать тестовый пример и поиграться с конструкцией keep dense_rank first/last.

У меня в загашнике есть еще одна очень интересная задачка на эту тему 😉

#решениезадачи #яндекс

Отчет для отдела маркетинга

Задача с тестового задания с одной конторы в РФ. Довольно часто встречается в реальной работе.

Отделу маркетинга требуется сводная выгрузка по клиентам, с гранулярностью до клиента, при этом для каждого клиента в выборке должны быть «лучшие» адрес, телефон и адрес электронной почты. То есть, в результирующей выборке по каждому клиенту есть только одна строка.

При этом:
🔹Лучший адрес отбирается по приоритету фактический > регистрации > домашний, при наличии нескольких адресов одного приоритета выбирается наиболее полный (заполнено больше из перечня атрибутов city-street-house-flat, при равенстве по заполненности выбирается последний по дате внесения в базу.
🔹Лучший телефон это последний по дате внесения в базу.
🔹Лучший email это первый по дате внесения в базу.
🔹Данные по контактам и адресам – не архивные.

Полная постановка с примерами таблиц

Разбор решения в четверг 🎓

#задача

Отчет для отдела маркетинга

Постановка в посте вторника.

Итоговый запрос

select c.id
      ,c.name
      ,max(a.city || ', ' || a.street || ', ' || a.house || ' fl. ' || a.flat)
           keep(dense_rank first order by a.a_type desc, nvl2(a.city, 1, 0) 
+ nvl2(a.street, 1, 0) + nvl2(a.house, 1, 0) + nvl2(a.flat, 1, 0) desc, a.created desc) address
      ,max(ph.c_info) keep(dense_rank first order by ph.created desc) phone
      ,max(em.c_info) keep(dense_rank first order by em.created asc) email
   from client c
     left join address a on a.client_id = c.id and a.active = 'Y'
     left join contact ph on ph.client_id = c.id and ph.c_type = 1 and ph.active = 'Y'
     left join contact em on em.client_id = c.id and em.c_type = 2 and em.active = 'Y'   
 group by c.id, c.name;

Пояснения
Решение основано на аналитических функциях.

1️⃣ Основная сущность клиент. Соединяем с ним все дочерние.
2️⃣ В условиях соединения указываем “Y” - активность.
3️⃣ Таблицу contact соединяем дважды с разными типами контактов “1” и “2”.
4️⃣ Группируем по клиенту - фактически c.id, c.name это группировка до одного уникального клиента, просто для вывода имени.
5️⃣ Для получения требуемых данных применяем аналитическую функцию keep dense rank в режиме агрегации (group by указан).
6️⃣ Для получения адреса:
- группировка по клиенту у нас уже есть, т.е. мы работаем уже внутри партиции “одного клиента”.
- сортируем строки внутри партиции по типу (a.a_type desc), по заполненности из перечня атрибутов (сумма nvl2) и дате создания (a.created desc).
- берем первую запись first из отсортированной партиции.
max(a.city ', ' a.street ', ' a.house ' fl. ' a.flat) - говорит просто отдай MAX запись из отобранных (а это у нас только одна). Фактически она ни на что не влияет.
7️⃣ Для получения телефона применяется аналогичная конструкция как и для адреса с более простой сортировкой.
8️⃣ Для получения email применяется аналогичная конструкция как и для адреса с более простой сортировкой.

Если не хотите ломать глаза ➡️ в более читаемом виде.

Спасибо, всем кто публиковал свои решения в нашем чатике 👍

#решениезадачи #аналитическиефункции

Всем хорошей пятницы и выходных 🎊

#юмор

Расписание

Есть матрица расписания запусков (см. скрин)

Первая строка – 15-и минутные интервалы, вторая - часовые интервалы, третья - дни недели, четвертая - дни месяца, пятая - месяцы года. С помощью данной матрицы задается периодичность запусков.

Требуется написать функцию на PL/SQL, которая бы возвращала дату следующего запуска от двух входных параметров:
1️⃣ дата, от которой ведется отчет;
2️⃣ это текстовая переменная, в которой перечислены все выбранные ячейки. Ячейки разделены «,» (запятой), а строки разделены «;» (точкой с запятой),

Например, для данного рисунка расписание будет выглядеть следующим образом: 0,45;0,4,8,12,17,22;2,6;1,2,3,4,5,11,18,24;1,2,3,9,11;

Контрольный пример
Дата отсчета: 09.07.2010 23:36
Строка: 0,45;12;1,2,6;3,6,14,18,21,24,28;1,2,3,4,5,6,7,8,9,10,11,12;
Результат: 18.07.2010 12:00

Примечание. В данном примере, используется американский календарь, в котором 1 – это воскресенье, 2 – понедельник и т.д.

Тестовое из Магнита (сейчас не дают).

#задача

Задача "Расписание"

Постановка в посте вторника.
Задача с тестового задания в IT “Магнит” (сейчас уже не дают).

Решить можно двумя способами

1️⃣ “В лоб” - написав парсер переданной строки на уровни и проходы по уровням с разной степени упоротости в реализации. Получится очень много кода.

2️⃣ “Хитрый” - используя пакет dbms_scheduler (пакет для управления фоновыми задачами). Гуглится на раз-два. Элегантное решение - парсится строка, преобразовывается в формат подходящий для задания расписания и используется процедура dbms_scheduler.evaluate_calendar_string. Процедура возвращает следующую дату запуска джоба от заданной даты в соответствии с расписанием срабатывания.
Подробное решение.

В далеком 2015м я решал её первым способом. Кода получилось многовато. С одной стороны это хорошо, можно посмотреть, что вообще может написать человек используя PL/SQL или SQL. Мои студенты "рождали" такие портянки, что становилось страшно 🤓

Если хотите попрактиковаться в написании кода на PL/SQL - рекомендую реализовать первым способом.

#решениезадачи #расписания

Всем хорошей пятницы и выходных 🎊

#юмор

Задача. Английский алфавит

Задача
Вывести одним запросом заглавные/прописные/большие буквы латинского алфавита, каждая буква - отдельная строка, без использования union.

Уровень сложности: легкий

Было бы круто посмотреть на различные способы решения.
Как всегда, разбор в четверг 🎓

Обсудить в чатике

#задача

Задача. Английский алфавит

Задача
Вывести одним запросом заглавные/прописные/большие буквы латинского алфавита, каждая буква - отдельная строка, без использования union.

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

Приведу буквально одно:

select chr(ascii('A') + level - 1) 
  from dual 
connect by level <= ascii('Z') - ascii('A') + 1;

Всем неравнодушным респект, было интересно посмотреть на такое разнообразие 🔥

#решениезадачи

Всем хорошей пятницы и выходных 🎊

#юмор

Классификация современных баз данных
Автор: Николай Голов

Этот доклад публиковали примерно год назад на Podlodka. Он вполне актуален на текущий момент.
Довольно интересный обзор на разного рода системы и СУБД.

Основная суть: как организовать выбор баз(ы) для своей системы, за который через пару лет не станут мучительно стыдно перед коллегами?

Из всего прочего, понравилась мысль "это нормально, когда на проекте используется более чем одна СУБД для своих узкоспециализированных задача".
Вроде бы простая мысль, но не сразу очевидная аля "всё пилим в Оракле и точка".

Конечно, речь идет про приложения среднего слоя.
Если у вас вся логика на Oracle PL/SQL, вы дергаете веб-сервисы из БД - эта история не совсем про вас.
Однако, для расширения кругозора рекомендую заценить. Вдруг вы уже начал процесс импортозамещения и потихоньку распиливаете ваш Oracle-монолит 😉

Всем приятного просмотра 🎥

Обсудить в чатике

#видео

Как просто хранить сложные объекты
Автор: Филипп Дельгядо

Еще один доклад с Podlodka.
Мне всегда интересно посмотреть как разные компании/команды проектируют свои приложения. Тема бездонная. Нет какой-то серебряной пули - единого подхода, который можно использовать везде и всегда.
Соответственно, видео/доклады на тему проектирования приложений особенно привлекают внимание.

В этом видосе Филипп рассказал про подход хранения сложных объектов в РСУБД простыми способами.

Совсем кратко: сложные объекты условно делятся на две части. Одна - это обычные поля, через которые поддерживается целостность данных, вторая часть - json, который хранится в одном поле. При этом каждая строка объекта помечается версией, ведь формат json'a может меняться. Средний слой учитывает версию при сериализации/десереализации объекта.
Подробней в его докладе.

Кстати, было бы интересно узнать мнение коллег, кто сталкивался с продуктами компании OpenWay - Way4 и, возможно, с такой архитектурой.

Всем приятного просмотра 🎥

Обсудить в чатике

#видео

Пока не запретили английский 😉

Всем хорошей пятницы и выходных 🎊

#юмор

Всем привет!
Я сейчас активно пишу курс по оптимизации, поэтому следующая задачка будет из этой области, так сказать, по горячим следам.

Был выполнен такой запрос:

select parsing_schema_name
      ,substr(t.sql_text, 1, 100)
      ,count(*) cnt
  from v$sqlarea t
 group by t.parsing_schema_name, substr(t.sql_text, 1, 100)
 order by cnt desc;

Результаты на скриншоте.
Какие выводы можно сделать по результатам выполнения? Все ли нормально? Если да, то почему? Если нет, то, какие есть предположения по устранению проблем?

Уровень сложности: easy, самые основы.

Объяснение, как всегда, в четверг 🎓

Обсудить в чатике

#задача

Задача по оптимизации
Полную постановку смотрите в посте вторника.

Объяснение
Типичная проблема при неправильном использовании механизмов СУБД. DBA очень часто негодуют по этому поводу.

Итак, поехали разбирать.

1️⃣ Представление v$sqlarea показывает все выполнявшиеся запросы в БД (parent-курсоры).
2️⃣ Каждый запрос имеет свой sql_id (уникальный номер), hash_value (хэш от текста запроса) и др.
3️⃣ Как только СУБД начинает выполнять запрос, происходит его парсинг. В том числе, берется hash от текста запроса и сравнивается с тем, что уже есть в SGA в кэше запросов. Если запрос не найден, то производится жесткий разбор (hard parse). В итоге, разобранный запрос помещается в кэш запросов.

Поменяете регистр одной буквы в запросе - это будет новый запрос и новый hard parse со всеми сопутствующими расходами.

Следующие запросы, с точки зрения БД, разные.

select * from tab1 where id = 1;
select * from tab1 where id = 2;

Для каждого будет сгенерирован свой sql_id, свой план(возможно), будут помещены в кэш-запросов и т.п. множество накладных расходов.

4️⃣ Для решения подобной ситуации используются переменные связывания (bind vars). Вместо жестко заданного литерала (1, 2…) используется подстановка значения:

select * from tab1 where id = :var;

теперь в var можно подставлять значения 1, 2…и т.д.
Не будет hard parse при подстановке значений. Не забивается кэш-запросов, DBA довольны.

Том Кайт в своей книге “Oracle для профессионалов” буквально на первых же страницах пишет про связные переменные.

Это было теоретическое введение.

Перейдем к нашему примеру.
Некое приложение соединяющееся c БД под пользователем ONE_C выполняло однотипный запрос аж 11166 раз (см. скриншоты ⬆️).
Текст запросов отличается ровно в одной детали - использование разных ID в предикате (where t1…. = число). Да, из задания это не совсем очевидно.
Поскольку в текстовом виде это абсолютно разные запросы, бедная СУБД выполнила аж 11166 раза hard pars забив кэш-запросов.

Решение простое. Использовать переменную связывания в предикате, в которую подставлять конкретное значение.

select ... where t1… = :id

будет выполнен 1 раз hard parse и всё.

⚠️ Не надо нагружать СУБД бесполезной никому не нужной нагрузкой

К сожалению, в рассматриваемой БД таких запросов не мало. Нужно явно править приложение 🛠
Этим грешат начинающие DBD, разработчики, пишущие на других языках, выполняющие запросы к СУБД Oracle.

Уровень задачи легкий, т.к. это должен знать каждый кто использует СУБД Oracle. Это основы.

На курсе по оптимизации будем разбирать множество интересных кейсов 😉

Обсудить в чатике

#оптимизация #решениезадачи

——
Всем хорошей пятницы и выходных 🎊

#юмор

Очередная задачка на оптимизацию. Если вы периодически смотрите планы запросов, то могли столкнуться с подобным случаем.

Итак. Текст запроса

select t.first_name
  from hr.employees t
  join hr.departments d on d.department_id = t.department_id
 where t.first_name like 'Alex%';

План запроса на скриншоте ⬆️

Вопрос. Таблица departments указана в запросе, используется для JOIN с таблицей employees. Однако, в плане запроса она отсутствует. Почему так произошло?

Разбор, как всегда, в четверг 🎓

Обсудить в чатике

#задача
Oracle Developer