SQL - обработка данных

Базовая структура sql запроса

[WITH] --доп расчетная таблица (когда нужно сжать логи)
SELECT column1, column2, …   
	[CASE WHEN] -- расчетный столбец по условию 
FROM table1
JOIN table2 ON table1.id = table2.id
[WHERE condition]  -- фильтрация
	column1 > ([SELECT]) -- подзапрос скалярный (для сравнения единичного результата)
	[WINDOW] --оконная функция, которая используется в SELECT
[GROUP BY column1, column2, ...]   -- группировка результата
[HAVING condition]   -- фильтрация после группировки
[ORDER BY column1 [ASC|DESC], column2 [ASC|DESC] -- сортировка по столбцу
[LIMIT 10] -- ограничение выдачи

Оператор SELECT

SELECT в языке SQL — это оператор запроса, который возвращает набор данных (выборку) из базы данных. Он позволяет извлекать нужные строки и столбцы, а также выполнять различные операции над данными. ru.wikipedia.org*tproger.rusky.pro

Подзапросы SELECT

https://struchkov.dev/blog/ru/select-subquery/ Подзапрос представляет собой оператор SELECT, вложенный в тело другого оператора.

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

По количеству возвращаемых значений подзапросы разделяются на два типа:

  • скалярные подзапросы, которые возвращают единственное значение;

  • табличные подзапросы, которые возвращают множество значений.

По способу выполнения выделяют два типа подзапросов:

  • простые подзапросы;

  • сложные подзапросы. Пример 1. Определить наименования деталей, цена которых больше цены детали ‘болт’.

SELECT dname
FROM D
WHERE dprice > (SELECT dprice
                FROM D
                WHERE dname = ’болт’)

Пример 2. Определить номера деталей, цена которых меньше средней цены деталей.

SELECT dname
FROM D
WHERE dprice < (SELECT AVG(dprice)
                FROM D)

Пример 3. Определить номер поставщика, выполнившего поставку с минимальным объемом.

SELECT pnum
FROM PD
WHERE volume = (SELECT min(volume)
                FROM PD)

Пример 4. Определить номера деталей, которых поставляется больше, чем деталей с номером 2.

SELECT pnum
FROM PD
GROUP BY dnum
HAVING sum(volume) > (SELECT sum(volume)
                      FROM PD
                      WHERE dnum = 2)

Пример 5. Вывести следующую информацию о деталях: наименование, цена, отклонение от средней цены.

SELECT dname, dprice, dprice - (SELECT AVG(dprice) FROM PD) AS dif
FROM PD

JOIN

Этот режим объединения результатов поиска в базах данных SQL включается автоматически. Если вы не укажете намеренно тип Join, то сработает именно Inner Join. С помощью него можно указать сразу два критерия (две таблицы) и по ним отсеять контент. INNER JOIN

SELECT *
FROM table-1
INNER JOIN table-2 ON table-1.parameter=table-2.parameter
WHERE table-1.parameter IS ‘myData’

CROSS JOIN (Напрямую объединить две таблицы с дублированием записей по ID)

SELECT *
FROM table-1
CROSS JOIN table-2

Функция Join - все разновидности

Агрегирующие функции

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

Агрегирующие функции часто используют в сочетании с оператором GROUP BY, который позволяет группировать данные по определённым полям. Например:

  • Расчёт общей суммы продаж:

SELECT SUM(quantity * price) AS total_revenue FROM sales

  • Средняя цена товара:

SELECT AVG(price) AS average_price FROM sales

  • Минимальная и максимальная цена:

SELECT MIN(price) AS min_price, MAX(price) AS max_price FROM sales

WHERE и HAVING

WHERE используется для фильтрации записей до группировки, отсеивая те, которые не удовлетворяют заданному критерию. В свою очередь, HAVING применяется после операции GROUP BY и позволяет отфильтровать группы записей по агрегированным значениям, получаемым с помощью функций SUM, AVG или COUNT.

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

SELECT department, SUM(sales) AS total_sales
FROM orders
WHERE department <> 'Returns'  -- Исключаем негатив, связанный с возвратами.
GROUP BY department
HAVING SUM(sales) > 5000       -- В отчет включаем только значительные суммы.

Операторы WHERE

Оператор
Пример
Описание

= - равный

Author = 'Alcott'

Равный

<> - не равно (во многих диалектах также допускается !=))

Dept <> 'Sales'

>

Hire_Date > '2012-01-31'

Больше , чем

<

Bonus < 50000.00

Меньше , чем

>=

Dependents >= 2

Больше или равно

<=

Rate <= 0.05

Меньше или равно

[NOT] BETWEEN [SYMMETRIC] В пределах включенного диапазона. СИММЕТРИЧНО инвертирует границы диапазона, если первая граница выше второй.

Cost BETWEEN 100.00 AND 500.00

[NOT] LIKE [ESCAPE] Начинается с набора символов

Full_Name LIKE 'Will%'

Содержит символьный шаблон

Full_Name LIKE '%Will%'

[NOT] IN

DeptCode IN (101, 103, 209)

Равно одному из нескольких возможных значений

IS [NOT] NULL

Address IS NOT NULL

Сравнить с нулевым значением (отсутствующие данные)

IS [NOT] TRUE или IS [NOT] FALSE

PaidVacation IS TRUE

Проверка логического значения истинности

IS NOT DISTINCT FROM

Debt IS NOT DISTINCT FROM - Receivables

Равно значению или оба значения равны нулю (отсутствуют данные)

AS

SELECT employee AS department1

Используется для изменения названия столбца при просмотре результатов

Операции с датами

Текущая дата: SQLite: DATE(current_date) либо date('now') MySQL: NOW() либо Getdate()

Расчет разницы между датами в днях: SQLite:

Вычитание дат

julianday(DATE(current_date)) - julianday('2024-07-20') as 'date'

STR_TO_DATE(string, format)

  • string — строка, которую нужно отформатировать как дату. mysqltutor.comschoolsw3.comru.basicit.org

  • format — формат входной строки, с помощью спецификаторов формата обозначаются компоненты даты (день, месяц, год и др.). Некоторые распространённые спецификаторы:

    • %d — день месяца в виде числового значения (от 01 до 31);

    • %m — месяц в виде числового значения (от 01 до 12);

    • %Y — год в виде числового значения из 4 цифр;

    • %H — час (от 00 до 23);

    • %i — минуты (от 00 до 59);

    • %s — секунды (от 00 до 59).

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

SELECT STR_TO_DATE('31-01-2024', '%d-%m-%Y')
WHERE strto_date(qs1.CALL_DT, '%Y/%m') = '2021/08'

Добавить или вычесть количество дней от даты: datetime(DATE(current_date), '+1 day') Вычитание дат в SQLite

Задача из Яндекса: У вас есть таблица quality_score с результатами внутренней проверки качества звонков операторов. В случае несогласия с оценкой, оператор может обжаловать её, тогда новая оценка по звонку запишется в эту же таблицу новой строкой. Таким образом, по одному звонку может быть сохранено несколько записей об оценке. Поля таблицы: • CALL_ID – id звонка • CALL_DT - дата совершения звонка • OPERATOR – имя оператора • SCORE_DT - дата оценки • SCORE - оценка Рассчитайте итоговую среднюю оценку по всем звонкам, которые были совершены в августе 2021. Если записей с оценками по звонку несколько, то берем самую свежую оценку.

WITH max_date AS(
    SELECT MAX(qs2.SCORE_DT) AS max_d,
    CALL_ID
    FROM quality_score AS qs2
    GROUP BY qs2.CALL_ID
    )    
SELECT AVG(qs1.SCORE) AS avg_score
  FROM quality_score AS qs1
       LEFT JOIN max_date AS md
       ON md.CALL_ID = qs1.CALL_ID
 WHERE strto_date(qs1.CALL_DT, '%Y/%m') = '2021/08'
   AND qs1.SCORE_DT = md.max_d;

WINDOW - оконные функции

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

Оконные функции полезны для отчётов, анализа данных, финансового моделирования и других задач, где нужно видеть результаты в контексте исходных данных. skillbox.ru

Виды оконных функций

Оконные функции можно разделить на три группы:

  1. Агрегатные. Выполняют арифметические вычисления и возвращают итоговое значение. Например, SUM() подсчитывает общую сумму значений, AVG() рассчитывает среднее арифметическое. Агрегация — это когда мы считаем суммарные или средние показатели (агрегаты). Например, среднюю зарплату по каждому региону или количество золотых медалей у каждой страны в зачете Олимпийских игр.

  2. Ранжирующие. Присваивают ранги строкам в окне. К ним относятся ROW_NUMBER() (возвращает номер строки) и RANK() (возвращает ранг каждой строки). Ранжирование — это всевозможные рейтинги, начиная от призеров чемпионата мира по плаванию и заканчивая Forbes 500.

  3. Функции смещения. Позволяют перемещаться и обращаться к разным строкам в окне относительно текущей строки, а также к значениям в начале или в конце окна. Например, LAG() обращается к данным из предыдущих строк, LEAD() — из следующих. Смещение - Сравнение со смещением — это когда мы смотрим, в чем разница между соседними значениями. Например, сравниваем страны, которые занимают 5 и 6 место в мировом рейтинге ВВП — сильно ли отличаются? А если сравнить 1 и 6 место? Скользящие агрегаты - Скользящие агрегаты — это те же сумма, среднее и прочие сводные показатели. Только рассчитывают их не по всем элементам набора, а более хитрым способом.

Пример расчета скользящего среднего за 3 месяца

select year, month, expense, 
round(avg(expense) over w) as roll_avg 
	from expenses 
	where year = 2020 and month <= 9 
	window w as ( 
	order by year, month rows between 1 preceding and 1 following ) 
order by year, month;

Сумма нарастающим итогом

select year, month, income, expense, 
sum(income) over w as t_income, 
sum(expense) over w as t_expense, 
(sum(income) over w) - (sum(expense) over w) as t_profit 
from expenses 
where year = 2020 and month <= 9 
window w as 
	( order by year, month 
	rows between unbounded preceding and current row 
	) 
order by year, month;

Коротко примеры:

SELECT
salary,
month,

ROW_NUMBER() OVER (ORDER BY salary) AS row_num -- пронумеровать сотрудников
RANK() OVER (ORDER BY salary) AS rank --ранг по зарплатае (одинаковый если зп равны)
NTILE(2) OVER (ORDER BY salary) AS bucket -- разбить на 2 группы по ЗП
SUM(salary) OVER (ORDER BY salary) AS run_total -- накопленная сумма по ЗП
LAG(salary,1) OVER (ORDER BY salary) AS last_salary -- предыдущая ЗП
LEAD(salary,1) OVER (ORDER BY salary) AS next_salary -- следующая ЗП
AVG(salary) OVER (order by month rows between 1 preceding and 1 following) -- скользящее среднее за 3 месяца

FROM employees;

Компоненты оконной функции:

  • PARTITION BY — разделяет строки на группы (по аналогии с GROUP BY).

  • ORDER BY — задаёт порядок строк внутри каждой группы.

  • ROWS BETWEEN или RANGE BETWEEN — указывает диапазон строк для расчёта (например, от текущей строки до предыдущей или следующей строки).

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

  • SUM() — вычисляет сумму значений.

SUM(column) OVER (PARTITION BY group_column ORDER BY order_column)

  • AVG() — вычисляет среднее значение.

AVG(column) OVER (PARTITION BY group_column ORDER BY order_column)

  • COUNT() — возвращает количество строк.

 COUNT(column) OVER (PARTITION BY group_column ORDER BY order_column)

  • MIN() — возвращает минимальное значение.

MIN(column) OVER (PARTITION BY group_column ORDER BY order_column)

  • MAX() — возвращает максимальное значение.

MAX(column) OVER (PARTITION BY group_column ORDER BY order_column)

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

  • ROW_NUMBER() — присваивает уникальный порядковый номер каждой строке в пределах окна.

ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY order_column)

  • RANK() — присваивает ранг строкам в пределах окна с возможностью "разделения" рангов при одинаковых значениях.

RANK() OVER (PARTITION BY group_column ORDER BY order_column)

  • DENSE_RANK() — присваивает ранг строкам, но без пропуска значений ранга (в отличие от RANK).

DENSE_RANK() OVER (PARTITION BY group_column ORDER BY order_column)

  • NTILE(N) — разбивает строки на N частей и присваивает номер части каждой строке.

NTILE(4) OVER (PARTITION BY group_column ORDER BY order_column)

3. Функции смещения Эти функции позволяют получать значения из предыдущих или последующих строк в пределах окна.

  • LAG() — возвращает значение из предыдущей строки относительно текущей строки.

LAG(column, offset, default_value) OVER (PARTITION BY group_column ORDER BY order_column)

offset — смещение назад (по умолчанию 1). default_value — значение по умолчанию, если смещение выходит за пределы окна.

  • LEAD() — возвращает значение из следующей строки относительно текущей строки.

 LEAD(column, offset, default_value) OVER (PARTITION BY group_column ORDER BY order_column)

4. Функции для работы с рамками (frames) Эти функции позволяют вычислять итоговые значения на основе определённых диапазонов строк в пределах окна.

  • FIRST_VALUE() — возвращает первое значение в окне.

FIRST_VALUE(column) OVER (PARTITION BY group_column ORDER BY order_column)

  • LAST_VALUE() — возвращает последнее значение в окне.

 LAST_VALUE(column) OVER (PARTITION BY group_column ORDER BY order_column)

  • NTH_VALUE() — возвращает n-е значение в окне.

 NTH_VALUE(column, n) OVER (PARTITION BY group_column ORDER BY order_column)

Синтаксис оконных функций

CASE WHEN

(Условный оператор)

Оператор CASE WHEN в языке SQL — условное выражение, аналогичное логике if-else в языках программирования. Позволяет выполнять условные проверки в SQL-запросах, манипулировать данными на основе определённых условий. nuancesprog.rusky.pro

SELECT
CASE WHEN data1 = 1 AND table.data2 >500 THEN data3 * 0.9
ELSE data3
END AS row_new

Особенности:

  • Каждое условие — выражение, возвращающее результат типа boolean.

  • Если результатом выражения оказывается true, значением выражения CASE становится результат, следующий за условием, а остальная часть выражения не вычисляется.

  • Если условие не выполняется, за ним таким же образом проверяются все последующие предложения WHEN.

  • Если не выполняется ни одно из условий WHEN, значением CASE становится результат, записанный в предложении ELSE. Если при этом предложение ELSE отсутствует, результатом выражения будет NULL.

SELECT CustomerID, PurchaseAmount, 
	CASE WHEN CustomerStatus = 'VIP' AND PurchaseAmount > 1000 THEN PurchaseAmount * 0.8 
	WHEN CustomerStatus = 'Regular' AND PurchaseAmount > 1000 THEN PurchaseAmount * 0.9 
	ELSE PurchaseAmount 
	END AS FinalAmount 
FROM Customers

nuancesprog.ru

GROUP BY

Оператор GROUP BY в SQL позволяет распределять строки (результат запроса) по группам, в которых значения некоторого столбца, по которому происходит группировка, одинаковы. Группировку можно производить как по одному столбцу, так и по нескольким. function-x.ruotus.ru

Часто оператор GROUP BY применяется вместе с агрегатными функциями (COUNT, SUM, AVG, MAX, MIN). В этих случаях агрегатные функции вычисляют значение ко всему набору строк, для которых некоторый столбец — общий.

SELECT max(id) as id,
asker
FROM questions
GROUP by asker
ORDER by id DESC

ORDER BY

Упорядочить выдачу

Базовый синтаксис оператора ORDER BY:

SELECT column1, column2, ... 
FROM table_name 
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC]

Объяснение:

  • column1, column2, ... — столбцы, по которым необходимо отсортировать результаты.

  • ASC — порядок по возрастанию (по умолчанию).

  • DESC — порядок по убыванию.

WITH

временная таблица

Обобщённое табличное выражение или CTE (Common Table Expressions) - это временный результирующий набор данных, к которому можно обращаться в последующих запросах. Для написания обобщённого табличного выражения используется оператор WITH.

-- Пример использования конструкции WITH
WITH Aeroflot_trips AS
    (SELECT TRIP.* FROM Company
        INNER JOIN Trip ON Trip.company = Company.id WHERE name = "Aeroflot")

SELECT plane, COUNT(plane) AS amount FROM Aeroflot_trips GROUP BY plane;

Выражение с WITH считается «временным», потому что результат не сохраняется где-либо на постоянной основе в схеме базы данных, а действует как временное представление, которое существует только на время выполнения запроса, то есть оно доступно только во время выполнения операторов SELECT, INSERT, UPDATE, DELETE или MERGE. Оно действительно только в том запросе, которому он принадлежит, что позволяет улучшить структуру запроса, не загрязняя глобальное пространство имён.

Синтаксис оператора WITH

WITH название_cte [(столбец_1 [, столбец_2 ] …)] 
AS (подзапрос) [, название_cte [(столбец_1 [, столбец_2 ] …)] 
AS (подзапрос)]

Порядок использования оператора WITH:

  1. Ввести оператор WITH

  2. Указать название обобщённого табличного выражения

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

  4. Ввести AS и далее подзапрос, результат которого можно будет использовать в других частях SQL запроса, используя имя, определённое на 2 этапе

  5. Опционально: если необходимо более одного табличного выражения, то ставится запятая и повторяются шаги 2-4

Комментирование кода (-- или /* * /)

  • Однострочные комментарии начинаются с двух дефисов -- и продолжаются до конца строки. Всё, что после

   -- на этой строке, игнорируется базой данных.

  • Многострочные комментарии начинаются с /* и заканчиваются */. Всё, что находится между этими маркерами, является комментарием и игнорируется СУБД.

/*
  Вывод количества обработанных звонков
  по дням для каждого сотрудника
*/

ОПЕРАТОРЫ

Оператор CREATE

--создать таблицу dogs с 5 полями разных типов 
CREATE TABLE rations ( 
id INT AUTO_INCREMENT PRIMARY KEY, 
name VARCHAR(20) NOT NULL, 
weight INT DEFAULT 0, 
color VARCHAR(20), 
portion INT 
);

Оператор ALTER

Не всегда получается создать идеальную таблицу с первого раза. Не бойтесь вносить изменения, добавлять, удалять или изменять существующие поля:

# переименовать таблицу rations в portions
ALTER TABLE rations RENAME TO portions;
# добавить в таблицу portions числовое поле age
ALTER TABLE portions ADD age INT;
# удалить из таблицы portions поле color
ALTER TABLE portions DROP COLUMN color;
# переименовать поле name в dog_name
ALTER TABLE portions CHANGE name dog_name VARCHAR(20) NOT NULL;

Операторы DROP и TRUNCATE

Оператор DROP удаляет таблицу из базы целиком:

# удалить таблицу portions 
DROP TABLE portions;

Если вам нужно удалить только записи, сохранив саму таблицу, воспользуйтесь оператором TRUNCATE:

# очистить таблицу portions 
TRUNCATE TABLE portions;

Оператор INSERT

Оператор INSERT в SQL — команда, которая позволяет добавлять новые записи (строки) в таблицу базы данных, заполняя их значениями. 

INSERT INTO employees (id, имя, фамилия) 
VALUES ('4', 'Алексей', 'Попов')

Оператор MERGE

MERGE  
    target_table [AS target]  
    USING  
    source_table [AS source]  
    ON  
    merge_condition  
    WHEN  
        MATCHED THEN  
            update_statement  
        WHEN  
        NOT MATCHED THEN  
            insert_statement  
        WHEN  
        NOT MATCHED BY SOURCE THEN  
            delete_statement  
    ;

Оператор DELETE

Удаление записей из таблицы. Важно: Без WHERE удалятся все данные!

Пример:

-- Удалить пользователя с id = 10
DELETE FROM users 
WHERE id = 10;

Оператор UPDATE

Изменение существующих записей. Важно: Всегда указывайте WHERE, чтобы не обновить всю таблицу!

Пример:

-- Изменить email пользователя с id = 5
UPDATE users 
SET email = '[email protected]' 
WHERE id = 5;

Источники и справка

ПредыдущаяOSINT - сервисы для конкурентной разведкиСледующаяНедопеременеджеринг

Последнее обновление