🥥SQL (язык запросов)

Чем отличается where и having?

Различие между операторами WHERE и HAVING в SQL заключается в их функциональности и контексте использования:

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

  • HAVING: Оператор HAVING используется для фильтрации данных после их группировки. Он применяется к группам данных, созданным с помощью оператора GROUP BY. HAVING позволяет фильтровать результаты агрегации по заданным условиям. Он работает с агрегатными функциями, такими как SUM, COUNT, AVG, MIN, MAX, и применяется после группировки данных.

Таким образом, WHERE используется для фильтрации отдельных строк данных, в то время как HAVING применяется к группам данных после их агрегации. WHERE применяется до операции GROUP BY, а HAVING - после.

Виды join запросов в SQL

Вот краткое описание видов JOIN запросов:

  1. INNER JOIN:

    • Возвращает только те строки, которые имеют соответствующие значения в обеих таблицах, участвующих в объединении.

    • INNER JOIN используется для объединения данных из разных таблиц на основе связанных значений в них[1][3].

  2. LEFT JOIN:

    • Возвращает все строки из левой таблицы и только соответствующие строки из правой таблицы. Если в правой таблице нет соответствующей строки, то возвращается NULL для значений правой таблицы.

    • LEFT JOIN позволяет сохранить все строки из левой таблицы, даже если в правой таблице нет соответствия[2][4].

  3. RIGHT JOIN:

    • Возвращает все строки из правой таблицы и только соответствующие строки из левой таблицы. Если в левой таблице нет соответствующей строки, то возвращается NULL для значений левой таблицы.

    • RIGHT JOIN позволяет сохранить все строки из правой таблицы, даже если в левой таблице нет соответствия[2][4].

  4. OUTER JOIN:

    • OUTER JOIN включает все строки из одной таблицы и только совпадающие строки из другой таблицы.

    • Существует три типа OUTER JOIN: LEFT OUTER JOIN, RIGHT OUTER JOIN и FULL OUTER JOIN[5].

Таким образом, различные виды JOIN запросов в SQL, такие как INNER JOIN, LEFT JOIN, RIGHT JOIN и OUTER JOIN, предоставляют различные способы объединения данных из разных таблиц, в зависимости от требуемых результатов и условий соединения.

Citations: [1] https://ru.hexlet.io/courses/complex-sql-queries/lessons/join/theory_unit [2] https://antonz.ru/sql-join/ [3] https://elbrusboot.camp/blog/join-sql/ [4] https://timeweb.com/ru/community/articles/kak-rabotaet-sql-join [5] https://tproger.ru/articles/sql-join

Перечислить агрегатные функции

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

  • COUNT: Подсчитывает количество строк или значений в выборке. Может быть использован с аргументом или без него для подсчета всех строк.

  • SUM: Вычисляет сумму чисел в столбце или наборе значений.

  • AVG: Вычисляет среднее арифметическое чисел в столбце или наборе значений.

  • MIN: Находит минимальное значение в столбце или наборе значений.

  • MAX: Находит максимальное значение в столбце или наборе значений.

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

Что такое триггер и примеры использования (в PostgreSQL)

Триггеры в PostgreSQL - это специальные функции, которые автоматически выполняются при наступлении определенного события в базе данных, таких как INSERT, UPDATE, DELETE или TRUNCATE.

Основные особенности триггеров:

  • Триггер может быть отмечен как FOR EACH ROW, тогда он будет вызываться для каждой измененной строки, или FOR EACH STATEMENT, тогда он будет вызываться только один раз для всего оператора.

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

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

Примеры использования триггеров:

  1. Триггер для аудита изменений в таблице:

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

Этот триггер сохраняет информацию о пользователе и времени последнего изменения строки.

  1. Триггер для обновления агрегированной таблицы:

CREATE TRIGGER update_data_agg
AFTER INSERT ON data_raw
EXECUTE PROCEDURE update_data_agg();

Этот триггер обновляет агрегированную таблицу data_agg при добавлении новых записей в data_raw.

  1. Триггер для вставки данных в представление:

CREATE TRIGGER view_insert INSTEAD OF INSERT ON my_view
FOR EACH ROW EXECUTE FUNCTION view_insert_row();

Этот триггер выполняет функцию view_insert_row() для вставки данных в таблицы, лежащие в основе представления my_view.

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

Что такое оконные функции?

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

Основные характеристики оконных функций в SQL включают:

  • Работу с выделенным набором строк (окном) для выполнения вычислений.

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

  • Использование операторов OVER, PARTITION BY, ORDER BY и ROWS для настройки оконных функций в SQL.

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

Citations: [1] https://thisisdata.ru/blog/uchimsya-primenyat-okonnyye-funktsii/ [2] https://webonto.ru/klassifikatsiya-baz-dannyih/ [3] https://habr.com/ru/articles/664000/ [4] https://tproger.ru/translations/sql-window-functions [5] http://cs.petrsu.ru/studies/filatova_information/CMD_1996566_M/my_files/Inform/DataBase/a-2.htm

Пример кода на GoLang с использованием оконных функций SQL

Вот пример кода на GoLang, который использует оконные функции SQL для выполнения запроса к базе данных:

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/lib/pq" // драйвер для PostgreSQL
)

func main() {
	// Установка соединения с базой данных
	db, err := sql.Open("postgres", "user:password@localhost/database")
	if err != nil {
		fmt.Println(err)
		return
	}
	defer db.Close()

	// Выполнение запроса с использованием оконной функции
	rows, err := db.Query(`
		SELECT 
			order_id, 
			order_date, 
			SUM(order_total) OVER (PARTITION BY customer_id ORDER BY order_date) AS total_spent
		FROM 
			orders
		ORDER BY 
			customer_id, 
			order_date;
	`)
	if err != nil {
		fmt.Println(err)
		return
	}
	defer rows.Close()

	// Обработка результатов
	for rows.Next() {
		var (
			orderID    int
			orderDate string
			totalSpent float64
		)
		err = rows.Scan(&orderID, &orderDate, &totalSpent)
		if err != nil {
			fmt.Println(err)
			return
		}
		fmt.Printf("Order ID: %d, Order Date: %s, Total Spent: %.2f\n", orderID, orderDate, totalSpent)
	}
}

В этом примере мы используем драйвер lib/pq для подключения к базе данных PostgreSQL. Затем мы выполняем запрос, который использует оконную функцию SUM с параметрами PARTITION BY и ORDER BY для расчета общей суммы заказов (total_spent) для каждого клиента (customer_id) в порядке даты заказа (order_date).

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

Когда необходимо использовать оконные функции, а когда можно без них обойтись?

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

Когда использовать оконные функции:

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

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

  • Вычисление накопительных сумм: Для расчета накопительных сумм или других агрегированных значений в пределах определенного окна.

Когда можно обойтись без оконных функций:

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

  • Ограниченные требования: Если задача может быть решена стандартными SQL-операторами без необходимости работы с окнами данных.

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

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

Citations: [1] https://webonto.ru/klassifikatsiya-baz-dannyih/ [2] https://inftis.narod.ru/db/db-2.htm [3] https://selectel.ru/blog/databases-types/ [4] https://otus.ru/nest/post/587/ [5] http://cs.petrsu.ru/studies/filatova_information/CMD_1996566_M/my_files/Inform/DataBase/a-2.htm

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

  1. Расчет рейтинга или ранга для каждой строки в пределах группы: Например, необходимо присвоить ранг каждому сотруднику в зависимости от его зарплаты внутри отдела. Это можно сделать с помощью оконной функции ROW_NUMBER() или RANK() с параметром PARTITION BY для группировки по отделам[2][4].

  2. Вычисление накопительных сумм или средних значений: Если требуется рассчитать накопительную сумму продаж для каждого клиента в хронологическом порядке, можно использовать оконную функцию SUM() с параметром ORDER BY[1][5].

  3. Определение n-го значения в пределах группы: Например, необходимо найти для каждого клиента третий по величине заказ. Это можно сделать с помощью оконной функции NTH_VALUE() или LEAD()/LAG()[4].

  4. Вычисление разницы между текущей строкой и предыдущей/следующей строкой в пределах группы: Если требуется найти разницу в цене между текущим и предыдущим товаром для каждой категории, можно использовать оконные функции LAG() или LEAD()[4].

  5. Вычисление процента или доли от общего итога в пределах группы: Например, необходимо рассчитать долю продаж каждого продукта в общих продажах по каждому региону. Это можно сделать с помощью оконной функции SUM() в сочетании с OVER(PARTITION BY)[1][5].

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

Citations: [1] https://professorweb.ru/my/sql-server/window-functions/level1/1_1.php [2] https://hackernoon.com/ru/%D0%A0%D1%83%D0%BA%D0%BE%D0%B2%D0%BE%D0%B4%D1%81%D1%82%D0%B2%D0%BE-%D0%B4%D0%BB%D1%8F-%D0%BD%D0%B0%D1%87%D0%B8%D0%BD%D0%B0%D1%8E%D1%89%D0%B8%D1%85-%D0%BF%D0%BE-%D0%BF%D0%BE%D0%BD%D0%B8%D0%BC%D0%B0%D0%BD%D0%B8%D1%8E-%D0%BE%D0%BA%D0%BE%D0%BD%D0%BD%D1%8B%D1%85-%D1%84%D1%83%D0%BD%D0%BA%D1%86%D0%B8%D0%B9-sql,-%D1%87%D0%B0%D1%81%D1%82%D1%8C-2 [3] https://inftis.narod.ru/db/db-2.htm [4] https://vc.ru/dev/130856-poleznye-okonnye-funkcii-sql [5] http://www.sql-tutorial.ru/ru/book_other_examples_of_using_window_functions.html

Что такое рекурсивные SQL запросы?

Рекурсивные SQL запросы - это особый вид запросов, которые используют производную таблицу для многократного выполнения одного и того же запроса до тех пор, пока не будет выполнено определенное условие[3][4]. Они позволяют эффективно решать задачи, требующие рекурсивной обработки данных, такие как обход иерархических структур.

Общий синтаксис рекурсивного SQL запроса выглядит следующим образом[3][4]:

WITH <имя> (<список столбцов>)
AS (
  SELECT -- анкорная часть
  UNION ALL -- рекурсивная часть
  SELECT FROM <имя>
  WHERE <условие продолжения интерации>
)

Рекурсивный запрос состоит из двух частей[1][2]:

  1. Анкорная часть: Начальный запрос, который выбирает данные для первой итерации.

  2. Рекурсивная часть: Запрос, который ссылается на производную таблицу и выполняется до тех пор, пока не будет выполнено условие остановки.

Рекурсивные запросы широко используются для[5]:

  • Построения иерархических структур данных

  • Обхода деревьев и графов

  • Вычисления последовательностей и рядов

Однако, несмотря на свою мощь, рекурсивные запросы требуют осторожности при использовании, так как могут привести к увеличению времени выполнения и нагрузки на базу данных[5].

Citations: [1] https://sky.pro/wiki/sql/rekursivniy-sql-zapros-dlya-polucheniya-vsekh-predkov-elementa/ [2] https://habr.com/ru/articles/27439/ [3] https://stefaniuk.website/all/recursion-in-sql/ [4] http://www.sql-tutorial.ru/ru/book_recursive_cte.html [5] https://быстрыеотчеты.рф/ru/blog/show/recursion-mssql/

Пример:

Для вычисления чисел Фибоначчи с помощью рекурсивного SQL запроса можно использовать общее табличное выражение (Common Table Expression, CTE). Вот пример такого запроса в SQL:

WITH RECURSIVE Fibonacci (n, fib_n, next_fib_n) AS (
    -- Инициализация первых двух чисел Фибоначчи
    SELECT 1 AS n, 0 AS fib_n, 1 AS next_fib_n
    UNION ALL
    -- Рекурсивное вычисление последующих чисел
    SELECT n + 1, next_fib_n, fib_n + next_fib_n
    FROM Fibonacci
    WHERE n < 20 -- Ограничение на количество чисел Фибоначчи
)
SELECT n, fib_n FROM Fibonacci;

Объяснение запроса:

  1. WITH RECURSIVE: Это ключевое слово начинает определение рекурсивного CTE.

  2. Fibonacci (n, fib_n, next_fib_n): Здесь определяется CTE с именем Fibonacci. Оно содержит три колонки: n (порядковый номер числа Фибоначчи), fib_n (значение n-го числа Фибоначчи), next_fib_n (значение (n+1)-го числа Фибоначчи).

  3. Инициализация: В первой части (до UNION ALL) задаются начальные значения: первое число Фибоначчи (0) и второе число Фибоначчи (1).

  4. Рекурсивное вычисление: Во второй части, после UNION ALL, происходит рекурсивное вычисление. Для каждой следующей строки n увеличивается на 1, fib_n принимает значение next_fib_n предыдущей строки, а next_fib_n вычисляется как сумма fib_n и next_fib_n предыдущей строки.

  5. WHERE n < 20: Это условие ограничивает количество рекурсивных вызовов, чтобы избежать бесконечного цикла. В данном случае, запрос выведет первые 20 чисел Фибоначчи.

  6. SELECT n, fib_n FROM Fibonacci: Финальный SELECT возвращает значения n и fib_n, то есть порядковый номер и соответствующее число Фибоначчи.

Этот запрос можно выполнить в системах управления базами данных, которые поддерживают рекурсивные CTE, таких как PostgreSQL, SQL Server и других.

PreviousБазы данныхNextИндексы

Last updated