Текст функции к задаче 1

Автор: Ruslan
#leroymerlin #собеседование
Oracle Developer

Итоги собеседования в Леруа Мерлен

Оба собеседования прошел успешно.

🔸Корпоративная культура в Леруа Мерлен:
В Леруа очень ценят людей, можно назвать это даже фанатизмом. Мне после российского ритейла было очень непривычно.
Лучше всего корпоративная культура отражена в диалоге, который у меня произошел с одним из моих коллег.

Вкратце, была обнаружена ошибка в отчете об остатках(диалог проходил в личном чате):
Я: "Косяк в стоках у тебя. Мы продаем в день ~2 ярда, а стоков у нас на триллион руб? Такого быть не может!"
Коллега: "Давай без таких резких фраз, они на меня давят. Я в целом не очень люблю оценочных суждений и резких слов. Фразу у тебя косяк я воспринимаю тяжело".

Необходимо быть аккуратным в коммуникации, а порой и в своей собственной активности лишь бы не задеть чувств менее активных коллег. Таков уж Леруа!

🔸Организационная структура процессов развития:
Организационная структура компании состоит из доменов, подразделения которые сосредотачивают в себе определенные функции бизнеса и поддержки, аналог дирекций/управлений/служб в др. компаниях.

Леруа на текущий момент распиливает все купленные коробочные решения на микросервисы, в связи с этим активное развитие получило продуктовое направление - разработка продуктов внутренними силами доменов.

Примерная структура домена выглядит следующим образом:
▫️Операционная команда;
▫️Архитектор;
▫️Product Owner(PO);
▫️Team Lead(TL);
▫️Дата команда
Под каждый продукт выделяется отдельный PO, TL и дата команда на его развитие.

Домен, отвечающий за развитие и поддержку Даты Платформы, отвечает за выкатку релизов. Дата команда участвует в разработке до момента, когда будет сделан pull request в Git репозиторий.

🔸Релизная политика Data Platform(DP) в Леруа Мерлен:
Согласно релизной политике DP, накат на прод происходит не чаще, чем 1 раз в неделю. Прозябал какой-нибудь грант на чтение и релиз переносится на следующую неделю.
Такой регламент сильно тормозит развитие отчетности в компании и приводит к тому, что основное большинство скриптов находится "на руках" у аналитиков и разработчиков. Большие "портянки" скриптов переносятся в BI инструменты вместо элегантного однострочного обращения к таблице или представлению.

Инвестиционные затраты(CAPEX) на развитие отчетности и модели ценообразования(средняя ставка по рынку аутстаффинга ~25к/день):
CAPEX(мес) = 6 FT аутстаффинга * 25к * 22(кол-во раб. дн./мес.) ~ 3,3 млн. руб./мес. + ФОТ внутренних сотрудников даты команды.

⚠️Чем реже релизы, тем дольше люди сидят на проектах, тем больше денег платит компания за аутстафф. Сумма для небольшой команды довольно внушительная.

🔸Общее впечатление:
В целом, положительное.

Плюсы:
▫️В компании действительно развитая инженерная культура, "не боятся" новых технологий и готовы к развитию.
▫️Очень спокойный темп работы. Кому надоели дедлайны "Вчера" - вам в Леруа!

Есть и минусы:
▫️Слабо развито направление бизнес-аналитики, если сравнивать с тем, что я видел в Food Ритейле.
Учитывая, что менеджмент компании решил изменить позиционирование Леруа на рынке DIY в сторону развития омниканальных продаж(ее основными конкурентами становятся e-commerce гиганты Wildberries, Ozon, Yandex Market), усиление этого направления крайне важно для компании.
▫️ Немного смутил неопытный молодой состав руководителей. Видимо, текущая ситуация и продажа компании местному менеджменту, вынудило головную компанию ADEO перевести опытных ключевых сотрудников к себе в штат.

И ДА, от Леруа мне поступало предложение перейти к ним в штат в один из других доменов...

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

Автор: Ruslan
#leroymerlin #собеседование
Oracle Developer

Задача. Транспонирование данных

Даны следующие коллекции

create type tr_dml as object(name varchar2(4000), value varchar2(4000));
create type tt_dml as table of tr_dml;
create type tr_tbl_row as object(id varchar2(4000), t_row_val tt_dml);
create type tt_tbl_row as table of tr_tbl_row;

Пример данных

SELECT *
FROM TABLE (tt_tbl_row(
        tr_tbl_row(
                1,
                tt_dml(
                        tr_dml('NAME', 'Иван'),
                        tr_dml('SURNAME', 'Иванов'),
                        tr_dml('MIDDLE_NAME', 'Иванович'),
                        tr_dml('AGE', '45'),
                        tr_dml('GENDER', 'M')
                    )
            ),
        tr_tbl_row(
                2,
                tt_dml(
                        tr_dml('NAME', 'Петр'),
                        tr_dml('SURNAME', 'Петров'),
                        tr_dml('MIDDLE_NAME', 'Петрович'),
                        tr_dml('AGE', '35'),
                        tr_dml('GENDER', 'M')
                    )
            ),
        tr_tbl_row(
                3,
                tt_dml(
                        tr_dml('NAME', 'Михаил'),
                        tr_dml('SURNAME', 'Иванов'),
                        tr_dml('MIDDLE_NAME', 'Михайлович'),
                        tr_dml('AGE', '25'),
                        tr_dml('GENDER', 'M')
                    )
            )
    ));

Названия полей заранее известны ('NAME', 'SURNAME', 'MIDDLE_NAME', 'AGE', 'GENDER') и данные валидные.

Необходимо вывести в sql запросе все объекты и их данные, у которых поле SURNAME = ИВАНОВ

Пример ответа

1,Иван,Иванов,Иванович,45,M
3,Михаил,Иванов,Михайлович,25,M

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

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

Автор: Константин Андронов
#задача
Oracle Developer

Решение задачи. Транспонирование данных

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

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

with exmpl as
 (select *
    from table(tt_tbl_row(tr_tbl_row(1,
                                     tt_dml(tr_dml('NAME', 'Иван'),
                                            tr_dml('SURNAME', 'Иванов'),
                                            tr_dml('MIDDLE_NAME', 'Иванович'),
                                            tr_dml('AGE', '45'),
                                            tr_dml('GENDER', 'M'))),
                          tr_tbl_row(2,
                                     tt_dml(tr_dml('NAME', 'Петр'),
                                            tr_dml('SURNAME', 'Петров'),
                                            tr_dml('MIDDLE_NAME', 'Петрович'),
                                            tr_dml('AGE', '35'),
                                            tr_dml('GENDER', 'M'))),
                          tr_tbl_row(3,
                                     tt_dml(tr_dml('NAME', 'Михаил'),
                                            tr_dml('SURNAME', 'Иванов'),
                                            tr_dml('MIDDLE_NAME', 'Михайлович'),
                                            tr_dml('AGE', '25'),
                                            tr_dml('GENDER', 'M'))))))
select *
  from (select e.id
              ,v.name
              ,v.value
          from exmpl e
         cross join table(e.t_row_val) v)
pivot (max(value) for name in('NAME' as name,
                         'SURNAME' as surname,
                         'MIDDLE_NAME' as middle_name,
                         'AGE' as age,
                         'GENDER' as gender))
 where upper(surname) like 'ИВАНОВ';

Объяснение

Имеем коллекцию, состоящую из id и вложенной таблицы key - value

Первым шагом необходимо плоско развернуть вложенную таблицу, чтобы получить данные вида

|id | name |value |
|—-|—————|————|
|1 |NAME | Иван |
|1 |SURNAME |Иванов |

Для этого используем CROSS JOIN (так как надо соединить все строки вложенной таблицы с ее id) и функцию table(), позволяющую работать с вложенной таблицей так, как будто это обычная таблица

В полученном результате названия полей - значения в колонке name, что не позволяет фильтровать по ним данные.

Нам заранее известны названия полей, что позволяет транспонировать матрицу результатов предыдущего шага при помощи оператора PIVOT. По условию задачи данные валидны, поэтому можем использовать агрегатную функцию MAX (или любую другую, которая выведет нам значения из колонки value) внутри оператора PIVOT.

Транспонировав результат, остается только отфильтровать данные (с учетом регистра строки в поле SURNAME)

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

Коллеги, накидали вариантов в чате, тоже полезно заценить решения 😉

👍, если понравилась задачка.

Автор: Константин Андронов
#решениезадачи #pivot #транспонирование
Oracle Developer

Восстанавливаем текст запроса

Друзья всем привет!

Судя по отклику, задание по восстановлению текста из плана запроса всем зашло. Давайте повторим 😉

Я привел сразу с вариантами ответов - должно быть очень просто для тех кто знает.

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

#задача
Oracle Developer

Восстанавливаем текст запроса. Анализ

Давайте проведем анализ приведенного плана:
🔹select statement - значит выполняется select;
🔹nested loops - операция соединения множеств, значит, как минимум, есть join;
🔹порядок выполнения операций: 3-4-2-5-1-0;
🔹возле шагов с id =3 и 4 стоят звездочки, это отсылка к блоку "Predicate Information";
🔹на 3м шаге табличка departments фильтруется по полю manager_id с предикатом = :v с алиасом "t";
🔹на 4м шаге используется диапазонное сканирование индекса (range scan) с доступом по предикату соединения department_id с алиасом "e".

Уже только по "Predicate information" можно сделать вывод, что правильный вариант №2.

В этом плане мне нравится маленькая изюминка - наличие второго nested loops, т.е. второго соединения множеств.

Откуда он берется?
С 11й версии Oracle добавил оптимизацию получения данных.

В первом nested loops отбираются все необходимые строки для employees по индексу EMP_DEPARTMENT_IX, а уже затем результат соединения (2й шаг) соединяется с таблицей employees (5й и 1й шаги). Достаются необходимые колонки из employees для материализации результата. Наглядней было бы с дополнительным блоком "Column Projection".

Таким образом, в первом соединении не вытаскиваются лишние данные из employees (а вдруг они вообще не понадобятся, если не подойдут по предикатам соединения).
—
Задание направлено на понимание планов запросов, когда какие операции используются и т.п. Этакий reverse engineering.
Тут "изи" вариант - с вариантами выбора.

В моем курсе по оптимизации, мы разбираем подобные задачи для усвоения материала и натаскивания на собеседования ✅
Первый поток уже во всю идет, окончание в декабре 🎓

Понравилась задачка? Ставь 👍
Обсудить в чатике 💬

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

Друзья, всем привет!
Давненько не было постов. Слишком много перемен, было некогда 🤦🏻‍♂️

Итак, давно обещанный #конкурс задачек по Oracle PL/SQL 🔥
Всего в конкурсе участвует 6 задач.

Как будет проходить
1️⃣ На этой неделе (вторник, среда, четверг) опубликую задачи (по две каждый день).

2️⃣ Можете сразу предлагать варианты решения в чатике, обсуждать задачки, возможные решения.

3️⃣ На следующей недели опубликую - решения авторов (понедельник, вторник, среда).

4️⃣ Создам голосование на лучшую задачку. Первые три места призовые.

5️⃣ Подведем итог 31 декабря и переведу денежные призы ТОП-3 авторам 🎄
1 место - 5К, 2 место - 3К, 3 место - 2К.
Рублей, конечно же, кому нужна загнивающая валюта, типа $ 😂

Приз для аудитории
Автор ответа (не важно на какую задачу), набравший максимальное количество реакций в чатике, будет так же одарен.
Коллеги, не скупимся на реакции к ответам в чате 😉

Конкурс проводит впервые - посмотрим, как пойдет. Идеи по проведению конкурса так же приветствуются 💡

—-
Тем временем, продолжается набор на курсы:
🔸Основы Oracle PL/SQL - осталось 5 мест.
🔸Основы оптимизации Oracle SQL - мест нет.
🔸Секционирование в Oracle - осталось 1 место.
За подробностями в личку.

Oracle Developer
#конкурс

Задача 1. Вычисление факториала

Чистым SQL вычислить факториал числа.

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

Oracle Developer
#конкурс

Задача 2. Создание deadlock

Имеем таблицу в 10к строк (содержимое не имеет значения). Надо написать такой селект, который при одновременном запуске в разных сессиях приведет к дедлоку с максимальной вероятностью. Отдельно надо указать как эти разные сессии запустить одновременно.

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

Oracle Developer
#конкурс

Задача 3. Вывод календаря

Вывести средствами SQL календарь на текущий месяц "как в винде".

Например на сентябрь 2023 надо вывести
ПН ВТ СР ЧТ ПТ СБ ВС
28 29 30 31 1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 1

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

Oracle Developer
#конкурс

Задача 4. Ускорение выполнение запроса

Ускорить выполнение запроса "select * from asd", где "asd" может быть, как таблицей, так и вьюхой.
Этот вопрос не имеет конкретного ответа, количество верных вариантов не ограничено.

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

Oracle Developer
#конкурс

Задача 5. Поиск недостающих кодов

Имеется таблица mytbl. В ней есть столбец code, который содержит заглавные буквы и цифры длиной 3.
Между минимальным и максимальным значениями этого столбца необходимо найти недостающие коды.
Для примера, столбец может содержать такие значения как '8TS' или 'A56'. Но не содержит значения типа 'A_4', '7F' или '2 C'.

Тестовые данные:

create table mytbl(code varchar2(3));
insert into mytbl values('8TS');
insert into mytbl values('A56');
insert into mytbl values('912');
insert into mytbl values('BZ0');

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

Oracle Developer
#конкурс

Задача 6. Сбор мусора Михалычем

Бомж-аутист Михалыч каждое утро обходит район и собирает в свои пакеты выброшенные пустые бутылки.

У Михалыча в силу обстоятельств каждое утро может быть разное количество пакетов разной вместимости (какой-то пакет вмещает 5 бутылок, какой-то - только 3 и т.д.). Количество найденных пустых бутылок каждое утро тоже может быть разным.

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

Напишите запрос, который для набора пакетов и количества бутылок нарисует таблицу Михалыча.

Например для исходных данных:

Пакеты:
Вместимость 3
Вместимость 2
Вместимость 5
Вместимость 2
Вместимость 1

Бутылки: 11 

Результат запроса должен быть таким:
------------------------------|
Пакет         | Бутылки       |
------------------------------|
Вместимость 5 | 1, 2, 3, 4, 5 |
------------------------------|
Вместимость 3 | 6, 7, 8       |
------------------------------|
Вместимость 2 | 9, 10         |
------------------------------|
Вместимость 2 | 11            |
------------------------------|
Вместимость 1 |               |
------------------------------|

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

Oracle Developer
#конкурс

Друзья, всем привет!

На этой недели было опубликовано 6 конкурсных задачек.
На следующей - решения авторов и голосование. Итоги подведем 31го декабря ⛄️

Если вы были заняты на этой недели, вы можете уделить им время на выходных. Закидывайте решения в чатик 💬
Кто знает, вдруг именно ваш ответ наберет большинство реакций и вы получите 🎁

Всем хороших выходных! 👍🏻

—-
Тем временем, продолжается набор на курсы:
🔸Основы Oracle PL/SQL - 5 мест. Старт: 15.01
🔸Основы оптимизации Oracle SQL - мест нет. Старт: 15.01
🔸Секционирование в Oracle - 1 место. Старт: 04.02
За подробностями в личку.

Oracle Developer
#конкурс

Задача 1. Вычисление факториала. Решение

📝 Постановка
🧑🏻‍💻 Автор

✅ Решение автора

with
step1(num) as
 (select 3 /*стартовое число*/
    from dual),
step2(x,y) as
 (select 1, 1
    from dual
  union all
  select x + 1, y * (x + 1)
    from step2, step1
   where x < step1.num)
select y as factorial 
  from step2, step1 
 where x = num;

⚠️ Напомню, что нет единственно правильного решения, поэтому прошу отнестись с пониманием к авторским решениям.

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

Oracle Developer
#конкурс #решениезадачи

Задача 2. Создание deadlock. Решение

📝 Постановка
🧑🏻‍💻 Автор

✅ Решение автора

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

select some_field 
  from some_table 
 order by dbms_random.random() for update;

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

Oracle Developer
#конкурс #решениезадачи

Задача 3. Вывод календаря. Решение

📝 Постановка
🧑🏻‍💻 Автор

✅ Решение автора

🗯 "Запрос написан немного в лоб. Может быть коллеги предложат что-то более симпатичное"

WITH par_date AS
 (SELECT to_date('26.09.2023', 'dd.mm.yyyy') AS par_date FROM dual),
t AS
 (SELECT (pd.par_date - 40 + LEVEL) AS d FROM par_date pd CONNECT BY LEVEL <= 80),
tt AS
 (SELECT t.d
        ,to_char(d, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') AS dd
        ,to_char(d, 'IW') AS w
        ,to_char(d, 'MM') AS m
    FROM t
        ,par_date pd),
ttt AS
 (SELECT *
    FROM tt
  pivot(MAX(tt.d), MAX(tt.m) AS m
     FOR dd IN('MON', 'TUE', 'WED', 'THU', 'FRI', 'SAT', 'SUN'))
   ORDER BY 2)
SELECT ttt.w       AS "НЕД"
       ,EXTRACT(DAY FROM ttt."'MON'") AS "ПН"
       ,EXTRACT(DAY FROM ttt."'TUE'") AS "ВТ"
       ,EXTRACT(DAY FROM ttt."'WED'") AS "СР"
       ,EXTRACT(DAY FROM ttt."'THU'") AS "ЧТ"
       ,EXTRACT(DAY FROM ttt."'FRI'") AS "ПТ"
       ,EXTRACT(DAY FROM ttt."'SAT'") AS "СБ"
       ,EXTRACT(DAY FROM ttt."'SUN'") AS "ВС"
  FROM ttt
      ,par_date pd
 WHERE ttt."'MON'_M" = to_char(pd.par_date, 'MM')
    OR ttt."'TUE'_M" = to_char(pd.par_date, 'MM')
    OR ttt."'WED'_M" = to_char(pd.par_date, 'MM')
    OR ttt."'THU'_M" = to_char(pd.par_date, 'MM')
    OR ttt."'FRI'_M" = to_char(pd.par_date, 'MM')
    OR ttt."'SAT'_M" = to_char(pd.par_date, 'MM')
    OR ttt."'SUN'_M" = to_char(pd.par_date, 'MM');

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

Oracle Developer
#конкурс #решениезадачи

Задача 4. Ускорение выполнение запроса. Решение

📝 Постановка
🧑🏻‍💻 Автор

✅ Решение автора

Для таблицы
1️⃣ HWM - если в таблице мало строк, а HWM высокий, необходимо сбросить HWM.
2️⃣ Проверить наличие policy и оптимизировать их, если проблема в них.
3️⃣ Проверить наличие блокировок и оптимизировать их, если необходимо.
4️⃣ Разбить таблицу на несколько более мелких, чтобы уменьшить время выполнения запросов.
5️⃣ Оптимизировать структуру таблицы, чтобы уменьшить размер записей и ускорить операции чтения и записи.

Для вьюхи
1️⃣ Проверить правильность связанных таблиц и переписать запрос, если необходимо.
2️⃣ Использовать хинты для оптимизации запроса.
3️⃣ Проверить наличие индексов и оптимизировать их, если необходимо.
4️⃣ Проверить статистику и обновить ее, если необходимо.
5️⃣ Проверить наличие блокировок и оптимизировать их, если необходимо.
6️⃣ Использовать материализованные вьюхи для ускорения выполнения запроса.
7️⃣ Разбить сложную вьюху на несколько более простых, чтобы уменьшить время выполнения.
8️⃣ Использовать партиционирование таблицы для улучшения производительности запросов.
9️⃣ Использовать кэширование результатов выполнения запросов на вьюху, чтобы уменьшить время выполнения.

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

—

⚠️ Напомню, что осталось:
Основы оптимизации Oracle SQL. Старт: 15.01
1 место Секционирование в Oracle. Старт: 04.02

Хочешь бустануть свои знания? 🚀 Пиши в личку пока не стало поздно.

Oracle Developer
#конкурс #решениезадачи

Задача 5. Поиск недостающих кодов. Решение

📝 Постановка
🧑🏻‍💻 Автор

✅ Решение автора

with chars as
 (select case
           when level > 10 then
            chr(54 + level)
           else
            to_char(level - 1)
         end as letter
    from dual
  connect by level <= 36),
codes as
 (select c2.letter  c1.letter  c0.letter as code
    from chars c0, chars c1, chars c2)
select *
  from codes t
 where not exists (select 1 from mytbl f where t.code = f.code)
   and t.code between (select min(code) from mytbl) and (select max(code) from mytbl);

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

Oracle Developer
#конкурс #решениезадачи

Задача 6. Сбор мусора Михалычем. Решение

📝 Постановка
🧑🏻‍💻 Автор

✅ Решение автора

Примерный алгоритм:
1) Сортируем пакеты по убыванию мест в них и таким образом проставляем номер каждому пакету.
2) Для каждого пакета генерим строки с номерами мест для бутылок, которые в него могут влезть.
3) Сопоставляем место в пакете номеру бутылки.
4) Для каждого пакета агрегируем все бутылки, которые смогли сопоставить на шаге (3).

with 
-- пакеты
p as (
select 3 as cnt from dual
 union all
select 2 as cnt from dual
 union all
select 5 as cnt from dual
 union all
select 2 as cnt from dual
 union all
select 1 as cnt from dual
),
-- бутылки
b as (
 select level as num
   from dual
connect by level <= 11
)

-- пакеты с поместившимися бутылками
select 'Вместимость '||c2.pack_cnt as pack, 
       listagg(c2.bottle_num,', ') within group (order by c2.bottle_num) as bottles
  from (
-- бутылки на местах в пакетах
select c1.*,
       -- сопоставляем место в пакете номеру бутылки по порядку
       (select bb.bottle_num
          from (select row_number() over (order by b.num) as bottle_num from b) bb
         where bb.bottle_num = c1.pack_place) bottle_num
  from (
-- места в пакетах   
select p.pack_num,
       p.pack_cnt,
       row_number() over (order by p.pack_num,d.n) as pack_place
  from (select row_number() over (order by p.cnt desc) as pack_num, p.cnt as pack_cnt from p) p,
       (select level as n from dual connect by level <= (select max(p.cnt) from p)) d
 where d.n <= p.pack_cnt
 order by p.pack_num,d.n
       )c1 
       )c2
 group by c2.pack_num,c2.pack_cnt
 order by c2.pack_num;

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

Oracle Developer
#конкурс #решениезадачи