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 запросов:

INNER JOIN:
- Возвращает только те строки, которые имеют соответствующие значения в обеих таблицах, участвующих в объединении.
- INNER JOIN используется для объединения данных из разных таблиц на основе связанных значений в них[1][3].
LEFT JOIN:
- Возвращает все строки из левой таблицы и только соответствующие строки из правой таблицы. Если в правой таблице нет соответствующей строки, то возвращается NULL для значений правой таблицы.
- LEFT JOIN позволяет сохранить все строки из левой таблицы, даже если в правой таблице нет соответствия[2][4].
RIGHT JOIN:
- Возвращает все строки из правой таблицы и только соответствующие строки из левой таблицы. Если в левой таблице нет соответствующей строки, то возвращается NULL для значений левой таблицы.
- RIGHT JOIN позволяет сохранить все строки из правой таблицы, даже если в левой таблице нет соответствия[2][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, тогда он будет вызываться только один раз для всего оператора.
Триггеры могут быть определены для таблиц, представлений, событий и других объектов базы данных.
Триггеры могут использоваться для обеспечения целостности данных, аудита, обновления связанных таблиц и других задач.

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

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

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

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

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

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

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

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

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. Вот несколько конкретных примеров:

Расчет рейтинга или ранга для каждой строки в пределах группы: Например, необходимо присвоить ранг каждому сотруднику в зависимости от его зарплаты внутри отдела. Это можно сделать с помощью оконной функции ROW_NUMBER() или RANK() с параметром PARTITION BY для группировки по отделам[2][4].
Вычисление накопительных сумм или средних значений: Если требуется рассчитать накопительную сумму продаж для каждого клиента в хронологическом порядке, можно использовать оконную функцию SUM() с параметром ORDER BY[1][5].
Определение n-го значения в пределах группы: Например, необходимо найти для каждого клиента третий по величине заказ. Это можно сделать с помощью оконной функции NTH_VALUE() или LEAD()/LAG()[4].
Вычисление разницы между текущей строкой и предыдущей/следующей строкой в пределах группы: Если требуется найти разницу в цене между текущим и предыдущим товаром для каждой категории, можно использовать оконные функции LAG() или LEAD()[4].
Вычисление процента или доли от общего итога в пределах группы: Например, необходимо рассчитать долю продаж каждого продукта в общих продажах по каждому региону. Это можно сделать с помощью оконной функции 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]:

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

Рекурсивные запросы широко используются для[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;

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

WITH RECURSIVE: Это ключевое слово начинает определение рекурсивного CTE.
Fibonacci (n, fib_n, next_fib_n): Здесь определяется CTE с именем Fibonacci. Оно содержит три колонки: n (порядковый номер числа Фибоначчи), fib_n (значение n-го числа Фибоначчи), next_fib_n (значение (n+1)-го числа Фибоначчи).
Инициализация: В первой части (до UNION ALL) задаются начальные значения: первое число Фибоначчи (0) и второе число Фибоначчи (1).
Рекурсивное вычисление: Во второй части, после UNION ALL, происходит рекурсивное вычисление. Для каждой следующей строки n увеличивается на 1, fib_n принимает значение next_fib_n предыдущей строки, а next_fib_n вычисляется как сумма fib_n и next_fib_n предыдущей строки.
WHERE n < 20: Это условие ограничивает количество рекурсивных вызовов, чтобы избежать бесконечного цикла. В данном случае, запрос выведет первые 20 чисел Фибоначчи.
SELECT n, fib_n FROM Fibonacci: Финальный SELECT возвращает значения n и fib_n, то есть порядковый номер и соответствующее число Фибоначчи.

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

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

Last updated 8 months ago

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) } }

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

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;