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

Все темы 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: Возвращает декартово произведение двух таблиц, то есть все возможные комбинации строк из обеих таблиц.