Управление полученными данными и агрегирующие функции

Управление полученными данными

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

Пример операций:

CREATE TABLE tbl (a, b, c, id INTEGER PRIMARY KEY);
INSERT INTO tbl (a, b, c) VALUES (10, 10, 10);
INSERT INTO tbl (a, b, c) VALUES (11, 15, 20);
INSERT INTO tbl (a, b, c) VALUES (12, 20, 30);

SELECT a*b, c-a from tbl;

a*b  c-a
---  ---
100  0  
165  9  
240  18 

Если вам не нравится полученное имя столбца, то ему можно дать псевдоним с помощью <выражение> as <имя>. Обратите внимание, что в клиенте название кириллицей сработало, но это может вызвать проблемы в коде:

SELECT a*b as "Умножение", c-a as "Вычитание" from tbl;

Умножение  Вычитание
---------  ---------
100        0        
165        9        
240        18 

В SQLite есть свой набор работы со строками, но SQLite умеет работать только с латинскими символами, поэтому операции обработки строк не работают с кириллическими символами:

sqlite> select upper('hello human');

upper('hello human')
--------------------
HELLO HUMAN         

sqlite> SELECT upper(title) FROM groceries where id=1;

upper(title)
------------
Морковка

Документация по доступным функциям в SQLite.

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

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

Самые часто используемые функции:

• COUNT - подсчитать общее количество записей
• MAX - поиск максимального значения столбца
• MIN - минимальное значение
• AVG - среднее арифметическое
• SUM - сумма

Общий синтаксис работы таких функций выглядит так:

SELECT 
    <ФУНКЦИЯ>(<поле>) [AS <имя>]
FROM
    <таблица>
[WHERE
   ... остальные возможные условия];

Пример подсчета количества товаров в списке:

SELECT 
    COUNT(*)
FROM
    groceries;

COUNT(*)
--------
8      

Группировка по полю

Обсуждая агрегирующие функции важно обратить внимание на еще одну возможность баз: группировка по значению поля с помощью GROUP BY. В этом пункте надо перечислить поля, по которым будет идти группировка.

Синтаксис GROUP BY:

SELECT 
    <ФУНКЦИЯ>(<поле>) [AS <имя>]
FROM
    <таблица>
[WHERE
   <условия>]
GROUP BY
    <столбец1>,
    <столбец2>
;

Например, если надо узнать сколько уже куплено и сколько еще надо купить товаров, то можно сгруппировать их по полю is_done:

SELECT 
    is_done, 
    COUNT(*) as count 
FROM 
    groceries
GROUP BY is_done;

is_done  count
-------  -----
0        4    
1        4