Соединения и запросы

Все темы Data Analyst

Что такое выражение GROUP BY и как оно используется?

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

GROUP BY группирует строки по указанным выражениям. Агрегатные функции часто используются вместе с ним, но не обязательны: SELECT Product FROM Sales GROUP BY Product возвращает уникальные группы.

Например, если у вас есть таблица “Sales” (“Продажи”) со столбцами “Product” (“Продукт”), “Date” (“Дата”) и “Sales Amount” (“Сумма продаж”), и вы хотите найти общую сумму продаж для каждого продукта, вы можете использовать следующий SQL-запрос:

SELECT Product, SUM([Sales Amount]) as TotalSales
FROM Sales
GROUP BY Product;

В результате будет получена таблица с двумя столбцами: “Product” и “TotalSales”, где каждая строка представляет собой уникальный продукт и его общий объем продаж.

Оператор GROUP BY также можно использовать с несколькими столбцами, что позволит сгруппировать данные по каждой уникальной комбинации столбцов. Например:

SELECT Product, Date, SUM([Sales Amount]) as TotalSales
FROM Sales
GROUP BY Product, Date;

В результате будет получена таблица с тремя столбцами: “Product”, “Date” и “TotalSales”, где каждая строка представляет собой уникальную комбинацию продукта и даты, а также общее количество продаж.

Что такое self-join и когда используется?

Self-join в SQL – это тип операции соединения, при которой таблица объединяется сама с собой. Это полезно, когда у вас есть таблица со связанными данными в разных строках, которые вы хотите объединить на основе общего поля.

Например, рассмотрим таблицу “employees” (“Сотрудники”) со столбцами для ID и имени сотрудника, а также ID менеджера. Столбец ID менеджера содержит ID менеджера сотрудника. Чтобы получить список всех сотрудников с именем их менеджера, можно использовать self-join.

Вот пример запроса:

SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;

В этом запросе мы соединяем таблицу “employees” с самой собой, используя столбец “manager_id”, чтобы сопоставить каждого сотрудника с его руководителем. В результирующей таблице будут столбцы с именем сотрудника и именем его менеджера.

Self-join также можно использовать для поиска связей между данными в одной таблице. Например, если у вас есть таблица продуктов со столбцами для ID продукта, названия и ID “родительского” продукта, соединение внутри таблицы может быть использовано для поиска всех “дочерних” продуктов данного “родительского” продукта.

В чем разница между внутренним и внешним соединением?

Cоединения (joins, джоины) используются для комбинации данных из нескольких таблиц на основе общего столбца или их взаимоотношений. Два наиболее распространенных типа соединений – это внутреннее (inner join) и внешнее (outer join) соединения.

Внутреннее соединение возвращает только совпадающие строки из обеих таблиц на основе условия соединения. Например, если у нас есть две таблицы A и B, и мы выполняем внутреннее объединение с использованием общего столбца C, будут возвращены только те строки, в которых C совпадает в обеих таблицах.

Вот пример запроса для внутреннего соединения:

SELECT A.column1, B.column2
FROM A
INNER JOIN B
ON A.C = B.C;

С другой стороны, внешнее соединение возвращает все строки из одной таблицы и совпадающие строки из другой таблицы. Если во второй таблице нет совпадающих строк, результат будет содержать NULL-значения для всех столбцов этой таблицы. Внешние соединения также делятся на левое внешнее (left outer join), правое внешнее (right outer join) и полное внешнее соединение (full outer join).

Вот пример запроса для левого внешнего соединения:

SELECT A.column1, B.column2
FROM A
LEFT OUTER JOIN B
ON A.C = B.C;

Этот запрос вернет все строки из таблицы A и совпадающие строки из таблицы B на основе столбца C. Если в таблице B нет совпадающих строк, результат будет содержать NULL-значения для column2.

В чем разница между коррелированным и некоррелированным подзапросом?

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

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

SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Подзапрос (SELECT AVG(salary) FROM employees) является некоррелированным подзапросом, поскольку может выполняться независимо от внешнего запроса. Он извлекает среднюю зарплату всех сотрудников, а результат используется в операторе WHERE внешнего запроса, чтобы отфильтровать сотрудников, чья зарплата больше средней.

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

SELECT first_name, last_name,
(SELECT SUM(amount) FROM sales WHERE sales.employee_id = employees.employee_id) AS total_sales
FROM employees;

Подзапрос (SELECT SUM(amount) FROM sales WHERE sales.employee_id = employees.employee_id) является коррелированным, поскольку ссылается на столбец employee_id из внешнего запроса. Он оценивается для каждого сотрудника во внешнем запросе, и результат используется для расчета общего объема продаж для каждого сотрудника.

Что такое обобщенное табличное выражение (CTE) и как оно используется?

CTE представляет именованный подзапрос, доступный в рамках одной SQL-команды. Он не обязательно хранится в памяти: способ вычисления и материализации зависит от СУБД и плана.

CTE определяются с помощью ключевого слова WITH, за которым следует имя CTE и подзапрос, определяющий его. Синтаксис CTE выглядит следующим образом:

WITH cte_name AS (
SELECT column1, column2, ...
FROM table_name
WHERE condition
)

SELECT *
FROM cte_name

На CTE можно ссылаться в основной команде, к которой относится WITH, и в допустимых последующих CTE этой же команды. В следующей отдельной SQL-команде он уже недоступен.

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

Какие виды JOINов вы знаете?

В SQL существует несколько типов JOIN:

1. INNER JOIN: Возвращает строки, которые имеют соответствия в обеих таблицах.

2. LEFT JOIN (или LEFT OUTER JOIN): Возвращает все строки из левой таблицы и соответствующие строки из правой таблицы. Если нет соответствия, то возвращается NULL для столбцов правой таблицы.

3. RIGHT JOIN (или RIGHT OUTER JOIN): Возвращает все строки из правой таблицы и соответствующие строки из левой таблицы. Если нет соответствия, то возвращается NULL для столбцов левой таблицы.

4. FULL JOIN (или FULL OUTER JOIN): Возвращает все строки из обеих таблиц. Если нет соответствия, то возвращается NULL для соответствующих столбцов.

5. CROSS JOIN: Возвращает декартово произведение двух таблиц, то есть все возможные комбинации строк из обеих таблиц.

Как меняется смысл строки после GROUP BY?

До GROUP BY строка обычно представляет исходное событие или сущность. После него одна строка представляет уникальную комбинацию ключей группировки, а остальные значения должны быть агрегированы или функционально зависеть от этих ключей по правилам СУБД. Например, после группировки по user_id строка означает пользователя, а COUNT(*) число его исходных строк. Ошибка возникает, когда аналитик считает строки сделками после many-to-many join или выбирает неагрегированную колонку без однозначного смысла. Гранулярность надо проговаривать явно.

Что произойдет, если скалярный подзапрос вернет несколько строк?

Подзапрос в скалярном контексте должен вернуть не более одной строки. Если строк несколько, большинство СУБД завершают запрос ошибкой; если строк нет, результатом обычно становится NULL. Добавлять LIMIT 1 без порядка и бизнес-правила опасно, потому что он скрывает нарушение уникальности. Если допустимо несколько значений, используют IN, EXISTS или join в зависимости от смысла. Если значение должно быть одно, обеспечивают уникальный ключ или осмысленную агрегацию.