Функции и операции

Все темы Data Analyst

В чем разница между предложениями HAVING и WHERE?

В SQL для фильтрации данных в запросе используются как предложения HAVING, так и WHERE. Однако между ними есть некоторые различия.

Предложение WHERE используется для фильтрации данных перед их группировкой или агрегированием и применяется в операторах SELECT, UPDATE и DELETE. Оно фильтрует данные на основе условий, которые применяются к отдельным строкам.

Например, если вы хотите получить данные обо всех сотрудниках, чья зарплата превышает 50 000 долларов, вы можете использовать предложение WHERE в операторе SELECT:

SELECT *
FROM employees
WHERE salary > 50000;

Предложение HAVING используется для фильтрации данных после их группировки или агрегирования и применяется только с оператором SELECT. Оно фильтрует данные на основе условий, которые применяются к группам строк.

Допустим, вы хотите получить среднюю зарплату сотрудников в каждом отделе и показать только те отделы, где средняя зарплата превышает 50 000 долларов. Вы можете использовать предложение HAVING в операторе SELECT:

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

В этом примере предложение GROUP BY группирует данные по отделам, а функция AVG вычисляет среднюю зарплату для каждого отдела. Предложение HAVING фильтрует результаты, показывая только те отделы, в которых средняя зарплата превышает 50 000 долларов.

Что такое оконная функция и как она используется?

Оконная функция – это тип функции в SQL, которая выполняет вычисления для набора строк в определенном “окне” или диапазоне. Она используется для решения сложных аналитических задач, которые не могут быть легко решены с помощью простых агрегатных функций.

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

Пример ниже считает скользящее среднее по текущей и двум предыдущим строкам для каждой пары регион/продукт. Это соответствует трём месяцам только при одной строке на каждый последовательный месяц.

SELECT
date,
region,
product,
sales_amount,
AVG(sales_amount) OVER (
PARTITION BY region, product
ORDER BY date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) as rolling_avg_sales

FROM
sales_data

В этом запросе функция AVG() используется в качестве оконной функции для расчета скользящего среднего значения продаж. Предложение PARTITION BY делит данные на разделы или группы по регионам и продуктам, а предложение ORDER BY сортирует данные по дате внутри каждого раздела. Предложение ROWS BETWEEN задает диапазон строк, которые должны быть включены в оконную рамку, в данном случае текущая строка и две предыдущие.

В чем разница между скалярной и табличной функцией?

В SQL функция – это набор инструкций, которые могут быть использованы для выполнения определенной задачи. Существует два типа функций: скалярные и табличные.

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

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

Одно из ключевых различий между скалярными и табличными функциями заключается в том, что скалярные функции можно вызывать внутри запроса, то есть использовать их как часть предложений SELECT, WHERE или ORDER BY. Табличные функции должны вызываться как часть предложения FROM, поскольку они возвращают таблицу.

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

Что такое представление SQL и как оно используется?

Представление (VIEW) — это виртуальная таблица, которая является производной от одной или нескольких таблиц в базе данных. Такие виртуальные таблицы используются для упрощения сложных запросов, представляя данные в более организованном и осмысленном виде. Они также могут использоваться для соблюдения политики безопасности, скрытия конфиденциальной информации и сокращения объема данных, которые необходимо извлекать из БД.

Для создания представления можно использовать оператор CREATE VIEW. Вот пример представления, которое извлекает информацию из двух таблиц:

CREATE VIEW employee_salary AS
SELECT employee_name, salary
FROM employee_info
JOIN salary_info ON employee_info.employee_id = salary_info.employee_id;

В этом примере представление “employee_salary” объединяет данные из двух таблиц “employee_info” и “salary_info”, соединяя их по столбцу “employee_id”. Представление возвращает только два столбца, “employee_name” и “salary”, и скрывает остальные столбцы из исходных таблиц.

Что такое оператор UNION и для чего он используется?

UNION объединяет результаты SELECT и удаляет повторяющиеся строки. Число столбцов должно совпадать, а типы в соответствующих позициях должны быть совместимыми, необязательно идентичными. UNION ALL сохраняет повторы.

Как SQL MERGE объединяет вставку, обновление и удаление?

MERGE сопоставляет source и target по условию, затем выполняет ветви WHEN MATCHED и WHEN NOT MATCHED, а некоторые диалекты позволяют удалить строки. Точная семантика различается между PostgreSQL, SQL Server, Oracle и облачными DWH. Источник должен однозначно сопоставляться с target: дубли могут вызвать ошибку или неоднозначное обновление. Для повторного запуска нужна идемпотентная бизнес-логика, стабильные ключи и транзакционные гарантии конкретной СУБД. Перед использованием проверяют план и concurrency-семантику.

Чем отличаются COUNT(*), COUNT(column) и COUNT(DISTINCT key)?

COUNT(*) считает строки результата, включая строки с NULL. COUNT(column) считает только строки, где эта колонка не NULL. COUNT(DISTINCT key) считает разные ненулевые значения ключа. Подсчет уникальных комбинаций нескольких колонок имеет разный синтаксис по диалектам, переносимый вариант строят через SELECT DISTINCT в подзапросе. Всегда уточняют гранулярность после join: размноженные строки меняют обычный count. Точный distinct на больших данных может требовать дорогого sort или hash; приближения используют только с допустимой ошибкой.

Можно ли использовать псевдоним SELECT в HAVING?

Переносимого правила «можно» нет. Логически HAVING обрабатывается до SELECT, поэтому alias еще не обязан существовать. MySQL и некоторые другие СУБД разрешают ссылку на alias как расширение, PostgreSQL обычно не разрешает ее в HAVING. Надежный вариант повторяет агрегатное выражение либо выносит вычисление в CTE или подзапрос и фильтрует во внешнем запросе. При ответе надо назвать диалект, а не переносить поведение одной СУБД на весь SQL.