Комментарии 57
Сохранил. Когда-нибудь мне это обязательно пригодится..
Поверь на слово - не пригодится. То, что тут написано - написано человеком, ну очень плохо понимающим, что и о чём он пишет. И даже написано всё это не потому, что он что-то знает или хотя бы думает, что знает, а потому, что кто-то (например, начальник или преподаватель) потребовал создать публикацию, и при этом вариант "отказаться" в принципе не возможен.
четыре частых вопроса по SQL джуну-аналитику
Как оптимизировать SQL запросы?
Никто никогда и нигде не спрашивает у джуна! аналитика!! про оптимизицию запросов. Ему это и не надо. И даже матерые лиды аналитики сами ничего не оптимизируют (да их никто к оптимизации и не подпускает), это задача дата-инженеров.
Никто никогда и нигде не спрашивает у джуна! аналитика!! про оптимизицию запросов.
Мы всегда спрашиваем больше, чем нужно на конкретной позиции. Ибо если кандидат вдруг знает больше, то мы можем рассмотреть его на соседнюю, более высокую, позицию.
И даже матерые лиды аналитики сами ничего не оптимизируют (да их никто к оптимизации и не подпускает), это задача дата-инженеров.
Точной границы нет. Если могут, то оптимизируют. Например, лучший аналитик в нашей команде очень хорошо умеет оптимизировать. И у нас это важный скилл, так как иначе результата запроса для анализа можно и несколько суток прождать.
так может еще и ООП спрашиваеть? а заодно и линукс-администрирование? Вдруг можно будет рассмотреть на соседнюю позицию
Ну тогда почему бы не спросить какие-нибудь задачки из вариационного исчисления, например? )
Если бы это использовалось в нашем подразделении и если видно, что кандидат тянет, то почему бы и не спросить?
У нас почти всегда масса вакансий разного уровня от джунов до техлидов. Если в моменте мы собеседуем на джуна, а кандидат имеет хороший опыт и годится для найма, то мы или попытаемся у руководства поднять нашу позицию, или предложим ему соседнюю более высокую.
Меня спрашивали в двух разных местах на мидла+, и на одном месте работы сразу после устройства призвали послушать внутренний курс по основам оптимизации запросов, так что я ничему не удивлюсь (что где-то и от джуна ждут)
Ощущение, что нейросеть писала. Во второй задаче вообще зачем добавлена группировка по дням? Достаточно сделать group by по клиенту из orders и sum(cos) с отбором по периоду.
Нормализация - это оптимизация? И что она оптимизирует, кроме хранилища? Мне сейчас приходится тесно работать с БД на сотни миллионов записей с глубокой нормализацией, и единственный способ оптимизировать скорость работы этого монстра - введение денормализованных таблиц, автоматически пополняемых по триггерам из основного хранилища. Иначе альтернатива - 10этажные джоины по нескольку секунд, а то и минут. В то время как денормализованный буфер позволяет делать выборку простым селектом за милисекунды, размазывая сложность по атомарным мгновенным операциям вставки по мере заполнения "основы".
Это что касается просто приложений. А о какой оптимизации для аналитика-джуна идет речь? С каких пор аналитику нужна оптимизация прям? Ок, допустим, индексы. С каких пор джун имеет права в продакшене, достаточные для создания индексов, например? Не страшно? ДБА как спит по ночам, после выдачи таких привелегий?
да у них там просто сразу джуну на выбор предлагают все позиции что есть от аналитика, до сторожа
Materialized view не подходят?
Матвьюхи не тащат там, где есть требования к рилтайму. Они же обновляются по запросу, то бишь это эдакий крон раз в N времени. При этом, каждое обновление, даже если оно неблокирующее, занимает столько же времени, сколько создание новой вьюхи. Даже больше, если речь идет именно о неблокирующем рефреше. Это совсем грустно в конкретном случае.
А когда важно, чтобы данные падали мгновенно, тут триггеры без вариантов.
""Иначе альтернатива - 10этажные джоины по нескольку секунд, а то и минут. В то время как денормализованный буфер позволяет делать выборку простым селектом за милисекунды, размазывая сложность по атомарным мгновенным операциям вставки по мере заполнения "основы".
Да, уж.... крутой smart подход ;-)
Вместе с нормализацией рядом идет и денормализация. В зависимости от задачи и конкретных условий может понадобиться или то, или другое.
Ну, я бы не сказал, что это задачи для джуна. Честно говоря, если аналитики, которые "живут" в базе и да, они такое решат. Как минимум в части написания запросов. Оптимизация - уже не факт, часто там техника хромает.
Но остальные банально заваляться на
Находим клиентов, которые тратят больше среднего по всем клиентам.
Рекурсивный запрос - это очень редко и специфично, спрашивать такое - это уже перебор, как по мне.
Вторая задача
Напишите запрос для нахождения клиента с наибольшей общей стоимостью заказов за период с февраля 2019 по май 2024 года, суммируя затраты за каждый день, если клиент сделал несколько заказов в один день
Идиотская постановка задачи. Вот неидиотская:
Напишите запрос для нахождения клиента с наибольшей общей стоимостью заказов за период с февраля 2019 по май 2024 года
Возможно, целью идиотской задачи было выяснить, умеет ли соискатель во вложенные запросы, но зачем для этого именно идиотская задача? Если у вас скудоумие - возьмите пример из оф.документации, там с этим получше.
Вы что там курите?
умеет ли соискатель во вложенные запросы
А вообще, вот, интересно, есть ли ситуация, когда вложенный запрос (исключая клинический случай коррелированного) может быть в каком-либо отношении лучше чем CTE?
ну тот же постгресс например всегда материализует CTE. А вложенные запросы нет. Так что можно подобрать случай когда это хуже.
Иногда вложенный запрос работает быстрее чем СТЕ. У меня такое случалось.
Следовало бы упомянуть, что речь идет (похоже) о Postgres, потому что, например, в MSSQL partitioning делается не так.
Так, как показано в статье, точно делается в MySQL/MariaDB и их клонах.
ну по скольку как бы собес на дата-аналитика! то будем считать что это аналитическая субд, пусть будет greenplum
Если речь о gp то какие нафиг индексы? Вообще оптимизация запросов она не про индексы это совсем другой уровень решений. Оптимизация это про потоки данных в запросе и их наиболее быстрое колапсирование и фильтрацию на более ранних этапах. На разделение запроса на более простые и использование временных таблиц и т.д.
Ну и аналитики бывают очень разными. Я бы больше спрашивал как например найти в бд какую то сущность что то про поиск таблиц по колонкам, про поиск по комментариям к таблицам/колонкам. Про какое то логическое мышление. Написать запрос для этого гугл есть он синтаксис подскажет.
Держите более компактный вариант решения 3 задачи (план выполнения, думаю, аналогичный).
SELECT
s.department_id,
department_name,
total_employees,
high_earners,
s.high_earners * 100.0 / s.total_employees AS percent_high_earners
FROM (
SELECT
department_id,
COUNT(*) AS total_employees,
SUM(CASE WHEN salary > 100000 THEN 1 ELSE 0 END) AS high_earners
FROM employees
GROUP BY department_id
HAVING (SUM(CASE WHEN salary > 100000 THEN 1 ELSE 0 END) * 100.0 / COUNT(*)) > 10
) s JOIN departments d ON s.department_id = d.department_id
ORDER BY total_employees DESC
LIMIT 3;
Больше похоже на вопросы на должность data engineer.
Во-первых, на вопрос об оптимизации я бы ожидал услышать ответ о получении плана запроса и его анализе. Но уж никак не об индексах или партиционировании, которые могут вылезти боком на других запросах.
Во-вторых, нормализация почти всегда снижает производительность. Её цели совершенно другие. И наоборот, для повышения производительности не редко приходится использовать денормализацию.
>Как оптимизировать SQL запросы
Ну может самый первый шаг собственно оптимизировать сам запрос, чем обвешивать таблицу индексами?
>SELECT * FROM customers WHERE name = 'Иван Иванов';
Я помню однажды собеседовался с микрософт-инженером (одно из самых приятных и классных собеседований). Так вот у него был почти аналогичный пример вот этому и вопрос простой - скажи 3 разных вещи, почему такой запрос может быть не очень и что тут неправильного и можно улучшить.
>Использование подзапросов
После запроса отсюда у меня начал дёргаться глаз и я дальше не читал.
PS в некоторых случаях вместо простого джойна лучше использовать outer apply / join lateral, они явно укажут плану с каких таблиц надо стартовать, это вопрос к плохой статистике / битым индексам. Обслуживание базы это конечно же исправит, но подстраховаться стоит кодом.
подскажите правильные ответы)
Звездочка
Не указана схема таблицы
Возможно не указана таблица в where?
Collation
Я не знаю что он хотел услышать, но мои ответы его удовлетворили. Понятно, что name = 'Alex John' было или что-то типа того. Я сказал звёздочка, схема (возможно дефолтная везде потому не такая проблема) и Фамилия с именем в одном поле нейм. FTS тут не очень подойдёт, по многим специфичным признакам. Например я знаю парня Anton Madalin и его имя Madalin, а не то что подумали многие. И его достаточно сильно рассуждения про это порадовали. В сторону колейшная можно ещё было бы подумать, но дефолтный CI_AS используется в подобных запросах, потому что я вижу поиск по имени а не по ИД, значит запрос скорее всего пользовательский. А если так то непонятно почему влепили всё в одно поле. В общем эту часть рассуждали вокруг этого, после того как я переписал на нечто вроде:
select id, name, birthday, fulladdress from customers where lastname = 'John' and firstname = 'Alex'
Мы начали говорить про индексы какие лучше тут создать и почему, это вопрос уже к density и расчёту уникальности.
Задача на одних и тех же данных должна давать одинаковый ответ по одной и той же выборке. Во второй задаче Order BY + LIMIT приводит к неопределённости в плане выбора клиента, если таковых с наибольшей суммой не один - лучше при словах наибольший/наименьший having sum = min/max sum. Не так быстро, но по смыслу правильнее - а если нужен случайный из подходящей выборки - лучше об этом писать.
Статья очень плохая.
Какой-то винегрет из неудачных вопросов, не очень корректных ответов и почти случайных (опять же не всегда корректных) фактов непонятно про какую СУБД.
Очень странный в принципе вопрос аналитику на собеседовании про оптимизацию запросов (ну ок, наверное можно его задать, если кандидат явно на несколько ступеней оверквал - но это всё равно дичь).
Очень странные "способы оптимизации". Не основанные ни на анализе планов, ни на анализе ресурсов, ни на анализе хотя бы времени выполнения. Ладно, первый способ я еще могу представить на собеседовании аналитика, но второй и третий вызывают у меня вопросы к квалификации интервьюера.
Про вторую оптимизацию. Просто из любопытства - попробуйте сделать такое заполнение секционированной таблицы из примера и запрос к ней, в которой секционирование будет стабильно лучше любого индекса, приведите пример с временем, io и планом. Если получилось, то скорее всего вы создали неудачный индекс.
Про нормализацию отметили выше, что обычно она понижает скорость выборки, а не ускоряет.
В разделе про джойны:
Фильтр по
orders.order_id IS NULLпозволяет идентифицировать клиентов без заказов.
Так делать иногда можно, но по умолчанию лучше использовать exists.
В разделе про оконные функции в запросе со скользящим средним может быть очень неожиданным, если поле order_date неуникально. Но это уже мелкая придирка.
CTE. В postgresql, например, CTE имеют тенденцию к материализации. До версии 12 с этим было вообще плохо, сейчас получше, но всё равно можно выстрелить себе в ногу. Тут уж в статье надо было выбрать - либо удобство аналитика, либо акцент на оптимизации.
Но это всё были цветочки, если бы вся статья была этого уровня, я бы не стал комментировать. Но когда я добрался до задач, то увидел
HAVING SUM(CASE WHEN impression = 'Bored' THEN 1 ELSE 0 END) = 0
За 25 лет работы с БД я, конечно, встречал такой код. И даже смогу вспомнить сколько-то раз, когда это не приводило к ухудшению плана запроса. И даже, наверное, вспомню пару раз, когда это решение было хорошим. Но не в этот раз. Не делайте так. Просуммировать выражение с CASE от поля только чтобы убедиться что таких значений нет - очень плохая идея. После этого ситуацию уже индексами, например, исправить нельзя.
вот поэтому в нормальных компаниях никто аналитиков к оптимизации не подпускает, ни джунов, ни сеньеров
Ну этот подход тоже не работает. Вот есть аналитик, у него есть отдельная БД аналитики (sqlite, pg, mysql, vertica, clickhouse или еще что-то), ему надо вытащить данные, он пишет запрос, запускает и внезапно понимает, что прошло уже 20 минут, а сервер думает. Было бы неплохо, если бы этот аналитик умел сам переделать запрос, чтобы запрос данные таки вывел. Каждый запрос к программистам не набегаешься же.
Но это точно не про джуна (да он бы хоть какой-то запрос написал) и точно не решающий вопрос на собеседовании.
Относительно задачи 3: какой вопрос - такой ответ
select d.department_name, count(*) total_employees
from employees e
inner join departments d on d.id = e.department.id
group by d.department_name
having sum(case when salary > 100000) / count(*) > 0.1
order by 2 desc
limit 3Насчёт джоинов - это не объединение (union), а соединение. Это более сложная операция, у которой нет полноценных аналогов в диаграммах Венна. Я бы рассматривал inner join как декартово произведение всех записей из двух таблиц с дальнейшим отбрасыванием кортежей, не прошедших фильтрацию по условию соединения. Понятно, что сама СУБД работает по-другому, но именно для объяснения работы inner join-а имхо подойдёт. Тогда вам будет понятно, почему, например, джоиня юзеров с адресами, где на одного юзера может быть несколько адресов, вы будете получать несколько записей для некоторых юзеров.
пример того, как НЕ надо писать на SQLNeverBored AS ( SELECT user_id FROM campaigns GROUP BY user_id HAVING SUM(CASE WHEN impression = 'Bored' THEN 1 ELSE 0 END) = 0)
"Нужно разработать запрос для идентификации пользователей, которые никогда не испытывали скуку (Bored) в любой рекламной кампании, но испытали восторг (Excited) в последней кампании. " Если эмоции всего 2 - Bored и Excited, то надо же всего навсего найти пользователей, которые всегда испытывали Excited, т.е достаточно такого запроса:
SELECT [user_id]
FROM Campaign
GROUP BY [user_id]
HAVING SUM(CASE WHEN impression = 'Bored' THEN 1 ELSE 0 END) = 0
Разве нет?
Я в тексте не нашёл упоминание для какой именно версии SQL (SQLite, Oracle, Postgresql, MsSql) эти вопросы заданы, а ОСОБЕННО приведены ответы.
Считаю необходимым обязательно указывать на это ограничение. Хотя и ТындексПрактикум, тоже не страдает
Чувство, будто в примере с right join что-то напутали. Не "если какие-то товары не были в заказах", а скорее "если заказ без товара" или "если заказ ссылается на несуществующий товар"
Все что касается приведённых запросов можно посмотреть в документации все эти операторы и функции. Лично я бы при собеседовании просто узнал, понимает ли человек вообще вообще sql как язык обработки и взаимодействия множеств данных в СУБД. Все остальное нарабатывается в зависимости от стуктуры конкретной БД. А на разработчика и уровень понимания транзакций. Все остальное нарабатывается на конкретной БД и рабочих задачах
Четыре частых вопроса по SQL джуну-аналитику и три задачи на собеседовании. Часть 1