Pull to refresh

Comments 57

Сохранил. Когда-нибудь мне это обязательно пригодится..

Поверь на слово - не пригодится. То, что тут написано - написано человеком, ну очень плохо понимающим, что и о чём он пишет. И даже написано всё это не потому, что он что-то знает или хотя бы думает, что знает, а потому, что кто-то (например, начальник или преподаватель) потребовал создать публикацию, и при этом вариант "отказаться" в принципе не возможен.

четыре частых вопроса по SQL джуну-аналитику

Как оптимизировать SQL запросы?

Никто никогда и нигде не спрашивает у джуна! аналитика!! про оптимизицию запросов. Ему это и не надо. И даже матерые лиды аналитики сами ничего не оптимизируют (да их никто к оптимизации и не подпускает), это задача дата-инженеров.

Никто никогда и нигде не спрашивает у джуна! аналитика!! про оптимизицию запросов.

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

И даже матерые лиды аналитики сами ничего не оптимизируют (да их никто к оптимизации и не подпускает), это задача дата-инженеров.

Точной границы нет. Если могут, то оптимизируют. Например, лучший аналитик в нашей команде очень хорошо умеет оптимизировать. И у нас это важный скилл, так как иначе результата запроса для анализа можно и несколько суток прождать.

так может еще и ООП спрашиваеть? а заодно и линукс-администрирование? Вдруг можно будет рассмотреть на соседнюю позицию

У меня однажды на собеседовании на C# backend разработчика спросили про модель OSI. Душа собеседующего - потемки (а иногда так вообще полный сумрак).

UFO just landed and posted this here

Ну тогда почему бы не спросить какие-нибудь задачки из вариационного исчисления, например? )

Если бы это использовалось в нашем подразделении и если видно, что кандидат тянет, то почему бы и не спросить?

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

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

Ощущение, что нейросеть писала. Во второй задаче вообще зачем добавлена группировка по дням? Достаточно сделать group by по клиенту из orders и sum(cos) с отбором по периоду.

Задачу решали, но недорешали.
Исходное условие подразумевает получение данных по датам, а по факту начали было выбирать, даты растеряли и получили общий результат по клиентам

Нормализация - это оптимизация? И что она оптимизирует, кроме хранилища? Мне сейчас приходится тесно работать с БД на сотни миллионов записей с глубокой нормализацией, и единственный способ оптимизировать скорость работы этого монстра - введение денормализованных таблиц, автоматически пополняемых по триггерам из основного хранилища. Иначе альтернатива - 10этажные джоины по нескольку секунд, а то и минут. В то время как денормализованный буфер позволяет делать выборку простым селектом за милисекунды, размазывая сложность по атомарным мгновенным операциям вставки по мере заполнения "основы".

Это что касается просто приложений. А о какой оптимизации для аналитика-джуна идет речь? С каких пор аналитику нужна оптимизация прям? Ок, допустим, индексы. С каких пор джун имеет права в продакшене, достаточные для создания индексов, например? Не страшно? ДБА как спит по ночам, после выдачи таких привелегий?

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

до гендира.

вдруг вывезет, тогда самим и делать ни хрена не надо :)

Materialized view не подходят?

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

А когда важно, чтобы данные падали мгновенно, тут триггеры без вариантов.

""Иначе альтернатива - 10этажные джоины по нескольку секунд, а то и минут. В то время как денормализованный буфер позволяет делать выборку простым селектом за милисекунды, размазывая сложность по атомарным мгновенным операциям вставки по мере заполнения "основы".

Да, уж.... крутой smart подход ;-)

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

Ну, я бы не сказал, что это задачи для джуна. Честно говоря, если аналитики, которые "живут" в базе и да, они такое решат. Как минимум в части написания запросов. Оптимизация - уже не факт, часто там техника хромает.

Но остальные банально заваляться на

Находим клиентов, которые тратят больше среднего по всем клиентам.

Рекурсивный запрос - это очень редко и специфично, спрашивать такое - это уже перебор, как по мне.

Вторая задача

Напишите запрос для нахождения клиента с наибольшей общей стоимостью заказов за период с февраля 2019 по май 2024 года, суммируя затраты за каждый день, если клиент сделал несколько заказов в один день​

Идиотская постановка задачи. Вот неидиотская:

Напишите запрос для нахождения клиента с наибольшей общей стоимостью заказов за период с февраля 2019 по май 2024 года

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

Вы что там курите?

умеет ли соискатель во вложенные запросы

А вообще, вот, интересно, есть ли ситуация, когда вложенный запрос (исключая клинический случай коррелированного) может быть в каком-либо отношении лучше чем CTE?

ну тот же постгресс например всегда материализует CTE. А вложенные запросы нет. Так что можно подобрать случай когда это хуже.

PostgreSQL материализует CTE когда явно указано MATERIALIZED. В остальных случаях, теоретически, необходимость материализации определяет оптимизатор. А практически, я не припомню ни одного случая, чтобы оптимизатор материализовал запрос.

ясно, новшевства появилиь в 12-м постгресе.

Так 12-я - самая старая версия на поддержке. Причем скоро и она с поддержки слетит, с выходом 17-й версии.

Иногда вложенный запрос работает быстрее чем СТЕ. У меня такое случалось.

Следовало бы упомянуть, что речь идет (похоже) о 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, они явно укажут плану с каких таблиц надо стартовать, это вопрос к плохой статистике / битым индексам. Обслуживание базы это конечно же исправит, но подстраховаться стоит кодом.

подскажите правильные ответы)

  1. Звездочка

  2. Не указана схема таблицы

  3. Возможно не указана таблица в where?

Или юникод:

SELECT * FROM customers WHERE name = N'Иван Иванов';

Я не знаю что он хотел услышать, но мои ответы его удовлетворили. Понятно, что 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-а имхо подойдёт. Тогда вам будет понятно, почему, например, джоиня юзеров с адресами, где на одного юзера может быть несколько адресов, вы будете получать несколько записей для некоторых юзеров.

сама субд иногда так и работает - nested loop как раз и делает декартово проиведение, но проверяют условие после каждого соединения строк - если true - идет в выборку, нет - нет

пример того, как НЕ надо писать на SQL
NeverBored 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
Разве нет?

with ...
...
Bored AS (select unique user_id from campaigns where impression = 'Bored')
SELECT e.user_id
FROM ExcitedLast e
WHERE e.user_id not in (select user_id from Bored);

такой запрос, например, может использовать индекс по impression и вообще лучше оптимизируется

Я в тексте не нашёл упоминание для какой именно версии SQL (SQLite, Oracle, Postgresql, MsSql) эти вопросы заданы, а ОСОБЕННО приведены ответы.

Считаю необходимым обязательно указывать на это ограничение. Хотя и ТындексПрактикум, тоже не страдает

Чувство, будто в примере с right join что-то напутали. Не "если какие-то товары не были в заказах", а скорее "если заказ без товара" или "если заказ ссылается на несуществующий товар"

да, там нужен простой inner join

Все что касается приведённых запросов можно посмотреть в документации все эти операторы и функции. Лично я бы при собеседовании просто узнал, понимает ли человек вообще вообще sql как язык обработки и взаимодействия множеств данных в СУБД. Все остальное нарабатывается в зависимости от стуктуры конкретной БД. А на разработчика и уровень понимания транзакций. Все остальное нарабатывается на конкретной БД и рабочих задачах

UFO just landed and posted this here
Sign up to leave a comment.