База SQLite и основы SQL - 9. Встроенные функции

16-11-2021Время чтения ~ 6 мин.SQL 3048

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

Как пользоваться SQLite-функциями? Они указываются прямо в sql-запросе. В некоторых случаях можно вообще использовать только функции в SELECT.

SELECT datetime('now'); -- текущее время
SELECT pi(); -- число пи
SELECT sqrt(25); -- корень из числа
SELECT sqlite_version(); -- версия SQLite 

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

SELECT 10*2 / 3 + 94;

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

Простой пример, демонстрирующий, как произвести какие-то операции с данными из базы.

-- создадим таблицу
CREATE TABLE tSum(
   a INTEGER,
   b INTEGER
); 
 
-- добавим данные
INSERT INTO tSum VALUES
(1, 2),
(3, 4),
(10, 30),
(5, 6),
(23, 54);
 
-- вывод суммы всех чисел попарно
SELECT a, b, a + b FROM tSum;
 
a  b  a + b
-----------
1  2  3
3  4  7
и т.д.

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

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

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

Функции агрегирования

Есть несколько функций, который вычисляют значения для группы записей. Их также называют групповые функции. К ним относятся:

• avg(X) — среднее арифметическое
• count(X) и count(*) — количество записей
• max(X) — максимальное значение
• min(X) — минимальное значение
• sum(X) или total(X) — сумма

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

SELECT sum(a), sum(b) FROM tSum; -- суммы по колонкам
SELECT avg(a) FROM tSum; -- среднее по всем значениям
SELECT count(*) FROM tSum; -- всего записей в таблице

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

Что такое группа?

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

CREATE TABLE tGr1 (
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
   a INTEGER,
   b INTEGER
);
  
INSERT INTO tGr1(a, b) VALUES
(1, 10),
(1, 20),
(1, 30),
(2, 100),
(2, 200),
(3, 50),
(3, 100),
(3, 30);
 
SELECT a, b FROM tGr1;
 
a    b
-------
1    10
1    20
1    30
2   100
2   200
3    50
3   100
3    30

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

a    b
-------
1    10
1    20
1    30
 
2   100
2   200
 
3    50
3   100
3    30

Чтобы запрос работал с группами, используется команда GROUP BY:

SELECT a FROM tGr1 GROUP BY a;
 
a
-
1
2
3

Данный запрос выводит все найденные группы по полю a. Не важно по какому полю происходит группировка, команда GROUP BY найдёт все уникальные значения по полю:

SELECT b FROM tGr1 GROUP BY b;
 
b
-
10
20
30
50
100
200

Теперь поставим задачу посчитать суммы значений b в каждой группе a. Делается это с помощью функции агрегирования sum():

SELECT a, sum(b) FROM tGr1 GROUP BY a;
 
a  sum(b)
---------
1  60
2  300
3  180

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

С группами бывает много задач. Наша таблица очень простая, но представьте себе, что поле a указывает на номер клиента или сотрудника. Поле b — это какая-то сумма, или количество. Тогда у нас может возникнуть ряд задач:

• сколько всего было выдано денег;
• сколько раз это происходило;
• какова средняя стоимость;
• какова минимальная/максимальная стоимость.

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

SELECT a, count(*) FROM tGr1 GROUP BY a;
 
a   count(*)
------------
1   3
2   2
3   3

Сортировка в GROUP BY

Может возникнуть вопрос как применить сортировку в sql-запросе для вычисляемого поля. Делается это точно также, как и для любого другого запроса — в ORDER BY указывается используемая функция:

SELECT a, sum(b)
FROM tGr1 
GROUP BY a 
ORDER BY sum(b) DESC;

Либо можно использовать AS:

SELECT a, sum(b) AS summa
FROM tGr1 
GROUP BY a 
ORDER BY summa DESC; 

Также нужно понимать, что ORDER BY срабатывает уже после GROUP BY. То есть вначале группировка и получение результата, а ORDER BY — это уже сортировка результата.

Условие HAVING

Теперь предположим, что нам нужно ограничить результат группы, каким-то условием. Логично было бы использовать WHERE как-то так:

SELECT a, sum(b) AS summa
FROM tGr1 
WHERE summa > 100
GROUP BY a 
ORDER BY summa DESC;

Однако такой запрос выдаст ошибку «misuse of aggregate: sum()». Это из-за того, что WHERE не будет работать с группами. Поэтому для условий групп нужно использовать команду HAVING. По смыслу она как WHERE, только работает с группами:

SELECT a, sum(b) AS summa
FROM tGr1 
GROUP BY a 
HAVING summa > 100
ORDER BY summa DESC;
 
a  summa
--------
2  300
3  180

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

Сравним два запроса, где поставим условие b>=50:

SELECT a, sum(b) AS summa
FROM tGr1 
WHERE b >= 50
GROUP BY a 
ORDER BY summa DESC;
 
a  summa
--------
2  300
3  150

Посмотрите на исходную таблицу, чтобы убедиться что группа «3» посчиталась верно (50+100, а 30 не удовлетворяет условию). Теперь тоже самое с HAVING:

SELECT a, sum(b) AS summa
FROM tGr1 
GROUP BY a 
HAVING b >= 50
ORDER BY summa DESC;
 
a  summa
--------
2  300
3  180

По сути условие HAVING сработало неожиданным образом: группа «1» также не прошла, как и положено, но сумма группы «3» оказалось ошибочным (условие не сработало). Обращайте внимание на такие вещи.

Если нам нужно добавить условие по полю и группе, то используем WHERE для полей и HAVING для групп.

SELECT a, sum(b) AS summa
FROM tGr1 
WHERE b >= 30
GROUP BY a 
HAVING summa < 300
ORDER BY summa ASC; 
 
a  summa
--------
1  30
3  180

Если у вас возникают трудности с пониманием групп, то «поиграйте» с этими примерами.

Порядок команд в SQL-запросе

Когда вы составляете SQL-запрос, то всегда придерживайтесь такой последовательности:

SELECT [distinct] поля
FROM таблицы
[LEFT] JOIN таблица ON условия
WHERE условия
GROUP BY поля
HAVING условия
ORDER BY порядок [asc | desc]
LIMIT смещение, кол-во;

Постарайтесь запомнить эту последовательность, поскольку она имеет логический смысл.

SELECT — это поля, которые мы хотим увидеть в результате запроса. Также здесь можно задать синонимы с помощью AS, которые будут работать во всех частях запроса.

FROM — таблица или таблицы с которыми мы работаем.

JOIN — команда на присоединение к FROM других таблиц.

WHERE — в полученном массиве данных можно применить фильтрацию по полям.

GROUP BY — в полученном результате можно выделить группы.

HAVING — для групп можно ввести какие-то условия.

ORDER BY — полученные данные можно отсортировать.

LIMIT — заключительная операция — ограничение количества строк для вывода.