10 продвинутых SQL вопросов с собеседований, на которые вы должны знать ответ.
t.me/data_analysis_ml
Вступление
SQL (Structured Query Language) – это стандартный язык программирования, используемый для управления базами данных и манипулирования ими. Это необходимый навык для любого специалиста по обработке данных, поскольку он позволяет им эффективно извлекать и анализировать данные, хранящиеся в базе данных. SQL является распространённой темой на технических собеседованиях на должности, связанные с работой с данными, подобно аналитикам данных, инженерам по обработке данных и администраторам баз данных.
Продолжаем серию статей по вопросам с SQL собеседований для аналитика данных.
1. SQL-запрос для поиска n-й по величине зарплаты/платежа или 3-й по величине зарплаты/платежа
Чтобы найти n-ую самую высокую зарплату, вы можете использовать подзапрос с функцией DENSE_RANK() для вычисления ранга каждой зарплаты, а затем отфильтровать результаты, чтобы включить только строку с рейтингом, равным n:
SELECT
*
FROM
(
SELECT
name,
salary,
DENSE_RANK() OVER (
ORDER BY
salary DESC
) as salary_rank
FROM
employees
) subquery
WHERE
salary_rank = n;
Вы также можете использовать условия LIMIT и OFFSET , чтобы найти n-ую самую высокую зарплату, следующим образом:
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET (n - 1);
Например, чтобы найти 3-ю по величине зарплату, вы могли бы использовать следующий запрос:
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2;
2. Как вы оптимизируете SQL-запросы для повышения производительности?
Существует несколько способов оптимизации SQL-запросов для повышения производительности, в том числе:
Индексация
Создание индекса для столбца или группы столбцов может значительно повысить скорость запросов.
Секционирование
Секционирование большой таблицы на более мелкие части может повысить производительность запросов, которым требуется доступ только к подмножеству данных.
Нормализация
Нормализация включает в себя организацию данных в базе данных таким образом, чтобы каждая часть данных хранилась только в одном месте, уменьшая избыточность и улучшая целостность данных.
Использование соответствующих типов данных
Использование правильного типа данных для каждого столбца может повысить производительность запросов, которые фильтруют или сортируют по этим столбцам.
Использование соответствующих типов JOIN
Использование правильного типа JOIN (например: INNER JOIN, OUTER JOIN, CROSS JOIN) может повысить производительность запросов, объединяющих несколько таблиц.
Использование соответствующих агрегатных функций
Использование соответствующих агрегатных функций (например: SUM, AVG, MIN, MAX) может повысить производительность запросов, которые выполняют вычисления на больших наборах данных. Некоторые агрегатные функции, подобные COUNT, более эффективны, чем другие, поэтому важно выбрать подходящую функцию для вашего запроса.
3. Как вы используете функции LAG и LEAD в SQL? Можете ли вы привести пример их использования?
Функции LAG() и LEAD() – это оконные функции в SQL, которые позволяют сравнивать значения в строке со значениями в предыдущей или следующей строке соответственно. Они полезны для вычисления текущих итогов или для сравнения значений в таблице со значениями в предыдущей или последующей строке.
Функция LAG() принимает два аргумента: столбец, который нужно вернуть, и количество строк, которые нужно вернуть. Например:
SELECT
name,
salary,
LAG(salary, 1) OVER (
ORDER BY
salary DESC
) as prev_salary
FROM
employees;
Функция LEAD() работает аналогичным образом, но принимает значения, находящиеся после текущей строки. Например:
SELECT
name,
salary,
LEAD(salary, 1) OVER (
ORDER BY
salary DESC
) as next_salary
FROM
employees
4. Объясните концепцию ETL и ELT в SQL
ETL (Извлечение, преобразование, загрузка) – это процесс, используемый в SQL для извлечения данных из одного или нескольких источников, преобразования данных в формат, подходящий для анализа или других целей, а затем загрузки данных в целевую систему, такую как хранилище данных.
ELT (Извлечение, загрузка, преобразование) аналогичен ETL, но фаза преобразования выполняется после загрузки данных в целевую систему, а не до этого. Это позволяет целевой системе выполнять преобразования, которые могут быть более эффективными и масштабируемыми, чем выполнение преобразований в инструменте ETL. ELT часто используется в современных инфраструктурах обработки данных, которые используют мощные механизмы обработки данных (такие как Apache Spark или Apache Flink) для выполнения фазы преобразования.
5. Можете ли вы объяснить разницу между предложениями WHERE и HAVING в SQL
Предложения WHERE и HAVING используются для фильтрации строк из инструкции SELECT. Основное различие между ними заключается в том, что предложение WHERE используется для фильтрации строк перед операцией group by, в то время как предложение HAVING используется для фильтрации строк после операции group by.
SELECT department, SUM(salary) FROM employees GROUP BY department HAVING SUM(salary) > 100000;
В этом примере предложение HAVING используется для фильтрации любых отделов, где сумма зарплат сотрудников меньше 100 000. Это делается после операции group by, поэтому это влияет только на строки, представляющие каждый отдел.
SELECT * FROM employees WHERE salary > 50000;
В этом примере предложение WHERE используется для фильтрации любых сотрудников с зарплатой менее 50000. Это делается перед любой операцией group by, поэтому это влияет на все строки в таблице employees.
6. Объясните разницу между операциями TRUNCATE, DROP и DELETE в SQL
TRUNCATE
Операция TRUNCATE удаляет все строки из таблицы, но не влияет на структуру таблицы. Процесс происходит быстрее, чем при использовании DELETE, потому что оно не генерирует никаких журналов отмены или повтора и не запускает никаких триггеров удаления.
Вот пример использования оператора TRUNCATE:
TRUNCATE TABLE employees;
Эта инструкция удаляет все строки из таблицы employees, но структура таблицы, включая имена столбцов и типы данных, остаётся неизменной.
DROP
Операция DROP удаляет таблицу из базы данных и удаляет все данные в таблице. Она также удаляет все индексы, триггеры и ограничения, связанные с таблицей.
Вот пример использования инструкции DROP:
DROP TABLE employees;
Эта инструкция удаляет таблицу employees из базы данных, и все данные в таблице удаляются безвозвратно. Структура таблицы также будет удалена.
DELETE
Операция DELETE удаляет одну или несколько строк из таблицы. Это позволяет вам указать предложение WHERE для выбора строк для удаления. Она также генерирует журналы отмены и повтора и запускает триггеры отмены.
Вот пример использования инструкции DELETE:
DELETE FROM employees WHERE salary & lt; 50000;
Эта инструкция удаляет все строки из таблицы employees, где зарплата меньше 50 000. Структура таблицы остается неизменной, а удалённые строки можно восстановить с помощью журналов отмены.
7. Что более эффективно – соединение или подзапрос?
Как правило, более эффективно использовать соединение, а не подзапрос при объединении данных из нескольких таблиц. Это связано с тем, что метод JOIN позволяет базе данных выполнять запрос более эффективно за счёт использования индексов в объединённых таблицах.
Например, рассмотрим следующие два запроса, которые возвращают одни и те же результаты:
SELECT
*
FROM
orders o
WHERE
o.customer_id IN (
SELECT
customer_id
FROM
customers
WHERE
country = 'US'
);
SELECT
*
FROM
orders o
WHERE
o.customer_id IN (
SELECT
customer_id
FROM
customers
WHERE
country = 'US'
);
Первый запрос использует соединение для объединения таблиц orders и customers, а затем фильтрует результаты с помощью предложения WHERE. Второй запрос использует подзапрос для выбора соответствующих идентификаторов клиентов из таблицы customers, а затем использует оператор IN для фильтрации таблицы orders на основе этих идентификаторов.
8. Как вы используете оконные функции в SQL?
В SQL оконная функция – это функция, которая работает с выделенным набором строк (окном, партицией) и выполняет вычисление для этого набора строк в отдельном столбце. Оконная функция используется для выполнения вычислений по строкам, и их можно использовать в операторах SELECT, UPDATE и DELETE, а также в предложениях WHERE и HAVING инструкции SELECT.
Вот пример использования оконной функции в инструкции SELECT:
SELECT name, salary, AVG(salary) OVER (PARTITION BY department_id) as avg_salary_by_department FROM employees
Этот оператор возвращает результирующий набор с тремя столбцами: имя, зарплата и средняя заработная плата в отделе. Столбец avg_salary_by_department рассчитывается с использованием функции AVG window, которая вычисляет среднюю заработную плату для каждого отдела. Предложение PARTITION BY указывает, что окно разделено по department_id, что означает, что средняя зарплата рассчитывается отдельно для каждого отдела.
9. Объясните нормализацию
Нормализация – это процесс организации базы данных таким образом, чтобы уменьшить избыточность и зависимость. Это системный подход к декомпозиции таблиц для устранения избыточности данных и улучшения их целостности. Существует несколько обычных форм, которые можно использовать для нормализации базы данных. Наиболее распространенными нормальными формами являются:
Первая нормальная форма (1NF)
- Каждая ячейка в таблице содержит одно значение, а не список значений.
- Каждый столбец в таблице имеет уникальное имя.
- Таблица не содержит никаких повторяющихся групп столбцов.
Вторая нормальная форма (2NF)
- Находится в первой нормальной форме.
- У него нет никаких частичных зависимостей.
Третья нормальная форма (3NF)
- Находится во второй нормальной форме.
- У него нет никаких переходных зависимостей.
Нормальная форма Бойса-Кодда (BCNF)
- Находится в третьей нормальной форме.
- Каждый определитель (атрибут, который определяет значение другого атрибута) является ключом-кандидатом (столбец или набор столбцов, которые можно использовать в качестве первичного ключа).
10. Объясните эксклюзивную блокировку и блокировку обновления в SQL
Эксклюзивная блокировка – это блокировка, которая предотвращает чтение или запись другими транзакциями в заблокированные строки. Этот тип блокировки обычно используется, когда транзакции необходимо изменить данные в таблице, и она хочет гарантировать, что никакие другие транзакции не смогут получить доступ к таблице одновременно.
Блокировка обновления – это блокировка, которая позволяет другим транзакциям считывать заблокированные строки, но не позволяет им обновлять или записывать в них. Этот тип блокировки обычно используется, когда транзакции необходимо прочитать данные в таблице, но она хочет гарантировать, что данные не будут изменены другими транзакциями до завершения текущей транзакции.
sqlhub – разбор реальных задач SQL в нашем телеграм канале.