ИЗЧИСЛЕНИЕ

Обобщаващи функции

Изразите на SQL заявка често изискват предварителна обработка на данни. За тази цел се използват специални функциии изрази.

Доста често трябва да разберете колко записа отговарят на конкретна заявка,каква е сумата от стойностите на определена цифрова колона, нейните максимални, минимални и средни стойности. За целта се използват така наречените крайни (статистически, агрегатни) функции. Функциите за обобщение обработват набори от записи, определени например от клауза WHERE. Ако ги включите в списъка с колони след оператор SELECT, получената таблица ще съдържа не само колоните на таблицата на базата данни, но и стойностите, изчислени от тези функции. Следното есписък с обобщени функции.

• БРОЙ (параметър ) връща броя на записите, посочени в параметъра. Ако искате да получите броя на всички записи, трябва да посочите символа звездичка (*) като параметър. Ако зададете име на колона като параметър, функцията ще върне броя на записите, в които тази колона има стойности, различни от NULL. За да разберете колко различни стойности съдържа една колона, предшествайте името на колоната с ключовата дума DISTINCT. Например:

SELECT COUNT(*) FROM Clients;

SELECT COUNT(Order_Amount) FROM Customers;

SELECT COUNT(DISTINCT Order_Amount) FROM Customers;

Опитът за изпълнение на следната заявка ще доведе до съобщение за грешка:

ИЗБЕРЕТЕ Регион, БРОЙ(*) ОТ Клиенти;

• SUM (параметър ) връща сумата от стойностите на колоната, посочена в параметъра. Параметърът може да бъде и израз, съдържащ името на колоната. Например:

ИЗБЕРЕТЕ СУМА (Сума_поръчка) ОТ Клиенти;

Този SQL израз връща таблица с една колона и един запис, съдържаща сумата от всички дефинирани стойности за колоната Order_Amount от таблицата Customers.

Да кажем, че в изходната таблица стойностите на колоната Order_Amount са изразени в рубли и трябва да изчислим общата сума в долари. Ако текущият обменен курс е например 27,8, тогава можете да получите необходимия резултат, като използвате израза:

ИЗБЕРЕТЕ СУМА (Сума_поръчка*27.8) ОТ Клиенти;

• AVG (параметър ) връща средната аритметична стойност на всички стойности на колоната, посочена в параметъра. Параметърът може да бъде израз, съдържащ името на колоната. Например:

ИЗБЕРЕТЕ СРЕДНА (Сума_на_поръчка) ОТ Клиенти;

ИЗБЕРЕТЕ СРЕДНА (Сума_на_поръчка*27,8) ОТ Клиенти

КЪДЕ Регион<>"Север_3запад";

• MAX (параметър ) връща максималната стойност в колоната, посочена в параметъра. Параметърът може да бъде и израз, съдържащ името на колоната. Например:

SELECT MAX(Order_Amount) FROM Clients;

ИЗБЕРЕТЕ MAX(Order_Amount*27.8) FROM Clients

КЪДЕ Регион<>"Север_3запад";

• МИН (параметър ) връща минималната стойност в колоната, посочена в параметъра. Параметърът може да бъде израз, съдържащ името на колоната. Например:

SELECT MIN(Order_Amount) FROM Customers;

ИЗБЕРЕТЕ МИН. (Сума на поръчката*27,8) ОТ Клиенти

КЪДЕ Регион<>"Север_3запад";

На практика често е необходимо да се получи финална таблица, съдържаща общите, средните, максималните и минималните стойности на цифровите колони. За да направите това, трябва да използвате функциите за групиране (GROUP BY) и сумиране.

ИЗБЕРЕТЕ регион, SUM (Сума_на_поръчка) ОТ Клиенти

ГРУПИРАНЕ ПО Регион;

Таблицата с резултати за тази заявка съдържа имената на регионите и общите (общи) суми на поръчките от всички клиенти от съответните региони (фиг. 5).

Сега разгледайте заявка за получаване на всички обобщени данни по региони:

ИЗБЕРЕТЕ Регион, СУМА (Сума_на_поръчка), СР (Сума_на_поръчка), MAX(Сума_на_поръчка), MIN (сума_на_поръчката)

ОТ Клиенти

ГРУПИРАНЕ ПО Регион;

Оригиналните таблици и таблиците с резултати са показани на фиг. 8. В примера само северозападният регион е представен в изходната таблица с повече от един запис. Следователно в таблицата с резултати за него различните обобщаващи функции дават различни стойности.

ориз. 8. Окончателна таблица на сумите на поръчките по региони

Когато използвате функции за обобщаване в списък с колони в оператор SELECT, заглавките на съответните им колони в таблицата с резултати са Expr1001, Expr1002 и т.н. (или нещо подобно, в зависимост от изпълнението на SQL). Можете обаче да зададете заглавки за стойностите на обобщените функции и други колони по ваша преценка. За да направите това, точно след колоната в израза SELECT, задайте израз от формата:

AS колона_заглавие

Ключовата дума AS (as) означава, че в таблицата с резултати съответната колона трябва да има заглавие, указано след AS. Присвоеното заглавие също се нарича псевдоним. Следният пример (Фигура 9) задава псевдоними за всички изчислени колони:

ИЗБЕРЕТЕ регион,

SUM (Сума_на_поръчката) КАТО [Обща сума на поръчката],

СР (Сума_на_поръчка) КАТО [Средна сума на поръчка],

MAX(Сума_на_поръчка) КАТО максимум,

МИН (Сума_на_поръчка) КАТО минимум,

ОТ Клиенти

ГРУПИРАНЕ ПО Регион;

ориз. 9. Окончателна таблица на сумите на поръчките по региони, използвайки псевдоними на колони

Псевдоними, състоящи се от няколко думи, разделени с интервали, се поставят в квадратни скоби.

Функциите за обобщение могат да се използват в клаузи SELECT и HAVING, но не могат да се използват в клаузи WHERE. Операторът HAVING е подобен на оператора WHERE, но за разлика от WHERE той избира записи в групи.

Да приемем, че искате да определите кои региони имат повече от един клиент. За тази цел можете да използвате следната заявка:

ИЗБЕРЕТЕ регион, брой(*)

ОТ Клиенти

ГРУПИРАНЕ ПО Регион С БРОЯ (*) > 1;

Функции за обработка на стойности

Когато работите с данни, често трябва да ги обработвате (преобразувате в правилния тип): изберете подниз в низ, премахнете началните и крайните интервали, закръглете число, изчислете квадратния корен, определете текущо времеи т.н. В SQL има три типа функции:

• низови функции;
• числови функции;
• функции за дата-час.

Стрингови функции

Функциите за низове приемат низ като параметър и връщат низ или NULL след обработката му.

• ПОДНИЗ (ред ОТ начало)връща подниз, произтичащ от низа, указан като параметърлиния . Подниз започва със знака, чийто сериен номер е посочен в началния параметър и има дължината, посочена в параметъра за дължина. Знаците в низа са номерирани отляво надясно, започвайки от 1. Квадратните скоби тук само показват, че изразът, заграден в тях, е незадължителен. Ако изразътЗА дължина не се използва, тогава подниз отзапочнете и до края на оригиналния ред. Стойности на параметритеначало и дължина трябва да бъде избран така, че търсеният подниз действително да е вътре в оригиналния низ. В противен случай функцията SUBSTRING ще върне NULL.

Например:

SUBSTRING ("Скъпа Маша!" ОТ 9 ЗА 4) връща "Маша";

SUBSTRING ("Скъпа Маша!" ОТ 9) връща "Маша!";

SUBSTRING("Скъпа Маша!" FROM 15) връща NULL.

Можете да използвате тази функция в SQL израз, например по този начин:

ИЗБЕРЕТЕ * ОТ Клиенти

WHERE SUBSTRING(Регион ОТ 1 ЗА 5) = "Север";

• ГОРЕН(низ ) преобразува всички символи от низа, зададен в параметъра, в главни букви.
• LOWER(низ ) преобразува всички знаци от низа, зададен в параметъра, в малки букви.
• TRIM (ВОДЕЩ | КРАЕН | И ДВАТА ["символ"] ОТ низ ) премахва началния (LEADING), завършващия (TRAILING) или и двата (BOTH) знака от низ. По подразбиране знакът за премахване е интервал (" "), така че може да бъде пропуснат. Най-често тази функция се използва за премахване на интервали.

Например:

TRIM (ВОДЕЩ " " ОТ "град Санкт Петербург") завърта "град Санкт Петербург";

TRIM(TRALING " " FROM "град Санкт Петербург") връща "град Санкт Петербург";

TRIM (BOTH " " FROM " град Санкт Петербург ") връща "град Санкт Петербург";

TRIM(BOTH FROM " град Санкт Петербург ") връща "град Санкт Петербург";

TRIM(BOTH "g" FROM "град Санкт Петербург") връща "град Санкт Петербург".

Сред тези функции най-често използваните са SUBSTRING() И TRIM().

Числови функции

Числовите функции могат да приемат данни не само като параметър числов тип, но винаги връща число или NULL (недефинирана стойност).

• ПОЗИЦИЯ ( targetString IN низ) търси срещане на целевия низ в посочения низ. Ако търсенето е успешно, връща номера на позицията на първия си знак, в противен случай 0. Ако целевият низ има нулева дължина (например низът " "), тогава функцията връща 1. Ако поне един от параметрите е NULL , тогава се връща NULL. Знаците на реда са номерирани отляво надясно, започвайки от 1.

Например:

POSITION("e" IN "Здравейте всички") връща 5;

POSITION ("всички" В "Здравейте всички") връща 8;

POSITION(" " Здравейте на всички") връща 1;

POSITION("Здравейте!" IN "Здравейте всички") връща 0.

В таблицата Клиенти (виж фиг. 1) колоната Адрес съдържа освен името на града, пощенски код, име на улица и други данни. Може да се наложи да изберете записи за клиенти, които живеят в определен град. Така че, ако искате да изберете записи, свързани с клиенти, живеещи в Санкт Петербург, можете да използвате следния израз на SQL заявка:

ИЗБЕРЕТЕ * ОТ Клиенти

WHERE POSITION (" Санкт Петербург " В адрес ) > 0;

Имайте предвид, че тази проста заявка за извличане на данни може да бъде формулирана по различен начин:

ИЗБЕРЕТЕ * ОТ Клиенти

WHERE Адрес като "%Петербург%";

• ЕКСТРАКТ (параметър ) извлича елемент от стойност за дата-час или от интервал. Например:

ЕКСТРАКТ (МЕСЕЦ ОТ ДАТА "2005-10-25")връща 10.

• CHARACTER_LENGTH(низ ) връща броя знаци в низа.

Например:

CHARACTER_LENGTH("Здравейте на всички") връща 11.

• OCTET_LENGTH(низ ) връща броя октети (байтове) в низа. Всеки знак на латиница или кирилица е представен с един байт, а символът на китайската азбука е представен с два байта.
• КАРДИНАЛНОСТ (параметър ) приема колекция от елементи като параметър и връща броя на елементите в колекцията (кардинално число). Колекцията може да бъде например масив или мултимножество, съдържащо елементи от различни типове.
• ABS (номер ) връща абсолютната стойност на число. Например:

ABS (-123) връща 123;

ABS (2 - 5) връща 3.

• MO D (номер1, номер2 ) връща остатъка от целочислено деление на първото число на второто. Например:

MOD(5, h) връща 2;

MOD(2, h) връща 0.

• LN (номер ) връща естествения логаритъм на число.
• EXP (число) връща числото (основата на естествения логаритъм на степен число).
• МОЩНОСТ (номер1, номер2 ) връща номер1номер2 (число 1 на степен число 2).
• SQRT (число ) връща корен квадратен от число.
• ЕТАЖ (номер ) връща най-голямото цяло число, което не надвишава зададеното от параметъра (закръгляване надолу). Например:

FLOOR (5.123) връща 5.0.

• CEIL (номер) или CEILING (номер ) връща най-малкото цяло число, което не е по-малко от стойността, указана от параметъра за закръгляване нагоре). Например:

CEIL(5.123) връща 6.0.

• WIDTH_BUCKET (number1, number2, number3, number4) връща цяло число в диапазона между 0 и number4 + 1. Параметрите number2 и number3 определят числов интервал, разделен на равни интервали, чийто брой се определя от параметъра number4 номерът на интервала, в който попада стойността number1. Ако number1 е извън определения диапазон, тогава функцията връща 0 или число 4 + 1. Например:

WIDTH_BUCKET(3.14, 0, 9, 5) връща 2.

Функции дата-час

IN SQL езикИма три функции, които връщат текущата дата и час.

• CURRENT_DATE връща текущата дата (тип ДАТА).

Например: 2005-06-18.

• CURRENT_TIME (номер ) връща текущия час (тип TIME). Целочисленият параметър определя точността на представянето на секундите. Например стойност 2 ще представлява секунди до най-близката стотна (два знака след десетичната запетая):

12:39:45.27.

• CURRENT_TIMESTAMP (число ) връща датата и часа (тип TIMESTAMP). Например 2005-06-18 12:39:45.27. Целочисленият параметър определя точността на представянето на секундите.

Имайте предвид, че датата и часът, върнати от тези функции, не са символен тип. Ако искате да ги представите като символни низове, тогава трябва да използвате функцията за преобразуване на типа CAST(), за да направите това.

Функциите за дата-час обикновено се използват в заявки за вмъкване, актуализиране и изтриване на данни. Например, когато записвате информация за продажбите, въвеждате текуща датаи време. След обобщаване на резултатите за месец или тримесечие данните за продажбите за отчетния период могат да бъдат изтрити.

Изчислени изрази

Изчислените изрази се изграждат от константи (числови, низови, логически), функции, имена на полета и други типове данни чрез свързването им с аритметични, низови, логически и други оператори. От своя страна изразите могат да се комбинират с помощта на оператори в по-сложни (съставни) изрази. Скобите се използват за контролиране на реда, в който се оценяват изразите.

Логически операториИ, ИЛИ и НЕ и функции са обсъдени по-рано.

Аритметични оператори:

• + допълнение;
• - изваждане;
• * умножение;
• / разделение.

Стрингов операторсамо един оператор за конкатенация или конкатенация на низ (| |). Някои реализации на SQL (като Microsoft Access) използват знака (+) вместо (| |). Операторът за конкатенация добавя втория низ към края на първия пример, изразът:

"Саша" | | "обича" | | "размахване"

ще върне низа "Sasha loves Masha" като резултат.

Когато съставяте изрази, трябва да се уверите, че операндите на операторите са от валидни типове. Например изразът: 123 + "Саша" не е валиден, защото операторът за аритметично събиране се прилага към операнд от низ.

Изчислените изрази може да се появят след Изявление SELECT, както и в условни изрази на изрази WHERE и HAVIН.Г.

Нека да разгледаме няколко примера.

Нека таблицата Sales съдържа колоните ProductType, Quantity и Price и искаме да знаем приходите за всеки тип продукт. За да направите това, просто включете израза Количество*Цена в списъка с колони след командата SELECT:

ИЗБЕРЕТЕ Product_type, Quantity, Price, Quantity*Price AS

Общо ОТ Продажби;

Това използва ключовата дума AS (as) за указване на псевдоним за колоната с изчислени данни.

На фиг. Фигура 10 показва оригиналната таблица Sales и таблицата с резултати от заявката.

ориз. 10. Резултат от заявката с изчисление на приходите за всеки вид продукт

Ако искате да разберете общите приходи от продажбата на всички стоки, просто използвайте следната заявка:

ИЗБЕРЕТЕ СУМА (Количество*Цена) ОТ Продажби;

Следната заявка съдържа изчислени изрази както в списъка с колони, така и в условието на клаузата WHERE. Той избира от таблицата с продажби онези продукти, чиито приходи от продажби са повече от 1000:

ИЗБЕРЕТЕ Product_type, Quantity*Price AS Total

ОТ Продажби

WHERE Количество*Цена > 1000;

Да приемем, че искате да получите таблица, която има две колони:

Продукт, съдържащ вид и цена на продукта;

Общо съдържащи приходи.

Тъй като се предполага, че в оригиналната таблица за продажби колоната Product_Type е символна (тип CHAR), а колоната Price е числова, когато се сливат (слепват) данни от тези колони, е необходимо числовият тип да се преобразува в символен тип, като се използва Функция CAST(). Заявката, която изпълнява тази задача, изглежда така (фиг. 11):

ИЗБЕРЕТЕ Product_Type | | " (Цена: " | | CAST(Цена КАТО CHAR(5)) | | ")" КАТО Продукт, Количество*Цена КАТО Общо

ОТ Продажби;

ориз. 11. Резултат от заявка, комбинираща различни типове данни в една колона

Забележка. В Microsoft Access подобна заявка ще изглежда така:

ИЗБЕРЕТЕ Product_type + " (Цена: " + Cул (Цена) + ")" КАТО продукт,

Количество*Цена КАТО Общо

ОТ Продажби;

Условни изрази с оператор CASE

Конвенционалните езици за програмиране имат оператори за условно прескачане, които ви позволяват да контролирате изчислителния процес в зависимост от това дали дадено условие е вярно или не. В SQL този оператор е CASE (случай, обстоятелство, случай). В SQL:2003 този оператор връща стойност и следователно може да се използва в изрази. Има две основни форми, които ще разгледаме в този раздел.

CASE израз със стойности

Изявлението CASE със стойности има следния синтаксис:

CASE проверена_стойност

WHEN стойност1 THEN резултат1

WHEN стойност2 THEN резултат2

. . .

WHEN стойността на N THEN резултатът от N

ИНАЧЕ резултат X

В случай проверена_стойносте равно на стойност1 , изразът CASE връща стойносттарезултат1 , посочено след ключова думаТОГАВА (това). В противен случай checked_value се сравнява сстойност2 и ако са равни, тогава се връща стойността result2. В противен случай стойността, която се тества, се сравнява със следващата стойност, посочена след ключовата дума WHEN и т.н. Ако tested_value не е равна на нито една от тези стойности, тогава стойността се връщарезултат X , посочени след ключовата дума ELSE (друго).

Ключовата дума ELSE не е задължителна. Ако липсва и никоя от стойностите, които се сравняват, не е равна на стойността, която се тества, тогава операторът CASE връща NULL.

Да речем, въз основа на таблицата Клиенти (вижте Фиг. 1), искате да получите таблица, в която имената на регионите са заменени с техните кодови номера. Ако няма твърде много различни региони в изходната таблица, тогава за решаване на този проблем е удобно да използвате заявка с оператора CASE:

ИЗБЕРЕТЕ име, адрес,

Регион CASE

КОГАТО "Москва" ТОГАВА "77"

КОГАТО "Тверска област" ТОГАВА "69"

. . .

ДРУГО Регион

AS Регионален код

ОТ Клиенти;

CASE оператор с условия за търсене

Втората форма на оператора CASE включва използването му при търсене в таблица за онези записи, които отговарят на определено условие:

СЛУЧАЙ

WHEN условие1 THEN резултат1

WHEN catch2 THEN result2

. . .

WHEN условие N THEN резултат N

ИНАЧЕ резултат X

Операторът CASE тества дали условие1 е вярно за първия запис в набора, дефиниран от клаузата WHERE, или за цялата таблица, ако WHERE не присъства. Ако да, тогава CASE връща резултат1. В противен случай условие2 се проверява за този запис. Ако е вярно, тогава се връща стойността резултат2 и т.н. Ако нито едно от условията не е вярно, тогава се връща стойността резултат X , посочени след ключовата дума ELSE.

Ключовата дума ELSE не е задължителна. Ако липсва и нито едно от условията не е вярно, операторът CASE завърта NULL. След като изразът, съдържащ CASE, се изпълни за първия запис, той преминава към следващия запис. Това продължава, докато целият набор от записи бъде обработен.

Да предположим, че в таблица с книги (Заглавие, Цена), колона е NULL, ако съответната книга е изчерпана. Следната заявка връща таблица, която показва „Изчерпано“ вместо NULL:

ИЗБЕРЕТЕ Заглавие,

СЛУЧАЙ

КОГАТО ЦЕНАТА Е НУЛЕВА, ТОГАВА „Няма наличност“

ELSE CAST (Цена КАТО CHAR(8))

AS Цена

ОТ Книги;

Всички стойности в една и съща колона трябва да са от един и същи тип. Следователно, в това исканеизползва функцията за преобразуване на типа CAST за кастинг числови стойностиколона Цена към тип знак.

Имайте предвид, че винаги можете да използвате втората форма на израза CASE вместо първата:

СЛУЧАЙ

WHEN test_value = value1 THEN result1

WHEN test_value = value2 THEN result2

. . .

WHEN тествана_стойност = стойност N ТОГАВА резултатN

ИНАЧЕ резултат

Функции NULLIF и COALESCE

В някои случаи, особено при заявки за актуализиране на данни (оператор UPDATE), е удобно да се използват по-компактните функции NULLIF() (NULL if) и COALESCE() (комбиниране) вместо тромавия оператор CASE.

Функция NULLIF ( стойност1, стойност2) връща NULL, ако стойността на първия параметър съвпада със стойността на втория параметър, в случай на несъответствие, стойността на първия параметър се връща непроменена. Тоест, ако равенството value1 = value2 е вярно, тогава функцията връща NULL, в противен случай стойност value1.

Тази функцияе еквивалентен на израза CASE в следните две форми:

• CASE стойност1

WHEN стойност2 THEN NULL

ИНАЧЕ стойност1

• СЛУЧАЙ

WHEN стойност1 = стойност2 THEN NULL

ИНАЧЕ стойност1

Функция COALESCE( стойност1, стойност2, ... , N стойност) приема списък със стойности, които могат да бъдат NULL или NULL. Функцията връща определена стойност от списък или NULL, ако всички стойности са недефинирани.

Тази функция е еквивалентна на следния оператор CASE:

СЛУЧАЙ

КОГАТО стойност 1 НЕ Е NULL, ТОГАВА стойност 1

КОГАТО стойност 2 НЕ Е NULL, ТОГАВА стойност 2

. . .

КОГАТО стойността N НЕ Е NULL, ТОГАВА стойността N

ИНАЧЕ NULL

Да предположим, че в таблицата Книги (Заглавие, цена) колоната Цена е NULL, ако съответната книга е изчерпана. Следната заявка връща таблица, където вместо NULL Показва се текстът „Изчерпано“:

ИЗБЕРЕТЕ Име, COALESCE (CAST(цена AS CHAR(8)),

„Изчерпано“) AS Цена

ОТ Книги;

В този урок ще научите как да използвате Функция SUM V SQL сървър(Transact-SQL) със синтаксис и примери.

Описание

В SQL Server (Transact-SQL) Функция SUMвръща общата стойност на израз.

Синтаксис

Синтаксисът за функцията SUM в SQL Server (Transact-SQL) е:

ИЛИ синтаксисът за функцията SUM при групиране на резултати по една или повече колони е:

Параметри или аргументи

израз1, израз2, ... израз_n са изрази, които не са включени във функцията SUM и трябва да бъдат включени в клаузата GROUP BY в края на SQL оператора.
aggregate_expression е колоната или изразът, който ще бъде агрегиран.
таблици - таблици, от които искате да получите записи. Трябва да има поне една таблица, посочена в клаузата FROM.
WHERE условия - по желание. Това са условията, които трябва да бъдат изпълнени за избраните записи.

Приложение

Функцията SUM може да се използва в следните версии на SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Пример с едно поле

Нека да разгледаме някои примери за функция SUM на SQL Server, за да разберем как да използваме функцията SUM в SQL Server (Transact-SQL).

Например, можете да разберете общото количество на всички продукти, чието количество е по-голямо от 10.

В този пример на функцията SUM сме надали псевдоним на израза SUM(количество) „Общо количество“. При връщане на набор от резултати - "Общо количество" ще се появи като име на полето.

Пример за използване на DISTINCT

Можете да използвате оператора DISTINCT във функцията SUM. Например този по-долу SQL изразвръща общата заплата с уникални стойности на заплатата, където заплатата е под $29 000 на година.

Ако двете заплати бяха $24 000 на година, само една от тези стойности щеше да се използва във функцията SUM.

Пример за използване на формулата

Изразът, съдържащ се във функцията SUM, не трябва да бъде едно поле. Можете също да използвате формулата. Например можете да изчислите общата комисионна.

Transact-SQL

ИЗБЕРЕТЕ СУМА (продажби * 0,03) КАТО „Обща комисионна“ ОТ поръчки;

ИЗБЕРЕТЕ СУМА (продажби * 0,03) КАТО „Обща комисионна“ ОТ поръчки;

Пример за използване на GROUP BY

В някои случаи ще трябва да използвате оператора GROUP BY с функцията SUM.

SQL - Урок 11. Общи функции, изчислени колони и изгледи

Общите функции се наричат ​​още статистически, агрегатни или сумарни функции. Тези функции обработват набор от низове, за да преброят и върнат една стойност. Има само пет такива функции:

• Функцията AVG() връща средната стойност на колона.


• Функцията COUNT() връща броя редове в колона.


• Функцията MAX() връща най-голямата стойност в колона.


• Функцията MIN() връща най-малката стойност в колоната.


• SUM() Функцията връща сумата от стойностите на колоните.

Вече се запознахме с един от тях - COUNT() - в урок 8. Сега нека се запознаем с останалите. Да кажем, че искаме да знаем минималната, максималната и средната цена на книгите в нашия магазин. След това от таблицата с цените трябва да вземете минималните, максималните и средните стойности за ценовата колона. Заявката е проста:

ИЗБЕРЕТЕ МИН.(цена), МАКС.(цена), СР.(цена) ОТ цени;

Сега искаме да разберем колко стоки са ни донесени от доставчика "Печатница" (id=2). Отправянето на такова искане не е толкова лесно. Нека помислим как да го съставим:

1. Първо от таблицата Supplies (incoming) изберете идентификаторите (id_incoming) на тези доставки, извършени от доставчика „Печатница“ (id=2):

2. Сега от таблицата Supply Journal (magazine_incoming) трябва да изберете стоките (id_product) и техните количества (quantity), които са извършени в доставките, намерени в точка 1. Тоест заявката от точка 1 става вложена:

3. Сега към получената таблица трябва да добавим цените за намерените продукти, които се съхраняват в таблицата Цени. Тоест ще трябва да се присъединим към таблиците Supply Magazine (magazine_incoming) и Prices с помощта на колоната id_product:

4. В получената таблица явно липсва колоната Сума, т.е изчислена колона. Възможността за създаване на такива колони е предоставена в MySQL. За да направите това, просто трябва да посочите в заявката името на изчисляемата колона и какво трябва да изчисли. В нашия пример такава колона ще се нарича summa и ще изчислява произведението на колоните за количество и цена. Името на новата колона е разделено с думата AS:

ИЗБЕРЕТЕ magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price КАТО сума ОТ magazine_incoming, цени WHERE magazine_incoming.id_product= prices.id_product И id_incoming= (SELECT id_incoming FROM incoming WHERE id_vendor=2);

5. Чудесно, остава само да съберем колоната сума и накрая да разберем за каква цена ни е донесъл стоката доставчикът „Печатница“. Синтаксисът за използване на функцията SUM() е както следва:

SELECT SUM(име_на_колона) FROM име_на_таблица;

Знаем името на колоната - summa, но нямаме името на таблицата, тъй като е резултат от запитване. какво да правя За такива случаи MySQL има Views. Изгледът е заявка за избор, която получава уникално име и може да се съхранява в база данни за по-късна употреба.

Синтаксисът за създаване на изглед е както следва:

CREATE VIEW view_name AS заявка;

Нека запазим нашата заявка като изглед с име report_vendor:

CREATE VIEW report_vendor AS SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND id_incoming= (SELECT id_incoming FROM incoming WHERE id_vendor=2 );

6. Сега можете да използвате крайната функция SUM():

ИЗБЕРЕТЕ SUM(сума) ОТ report_vendor;

Така постигнахме резултата, въпреки че за това трябваше да използваме вложени заявки, съединения, изчислени колони и изгледи. Да, понякога трябва да мислите, за да получите резултат, без това не можете да стигнете доникъде. Но засегнахме две много важни теми - изчислени колони и изгледи. Нека поговорим за тях по-подробно.

Изчисляеми полета (колони)

Използвайки пример, днес разгледахме математическо изчислено поле. Тук бих искал да добавя, че можете да използвате не само операцията умножение (*), но и изваждане (-), събиране (+) и деление (/). Синтаксисът е както следва:

SELECT име_на_колона 1, име_на_колона 2, име_на_колона 1 * име_на_колона 2 КАТО име_на_изчислена_колона ОТ име_на_таблица;

Вторият нюанс е ключовата дума AS, използвахме я, за да зададем името на изчислената колона. Всъщност тази ключова дума се използва за задаване на псевдоними за всякакви колони. Защо е необходимо това? За намаляване на кода и четливост. Например нашият изглед може да изглежда така:

CREATE VIEW report_vendor AS SELECT A.id_product, A.quantity, B.price, A.quantity*B.price AS summa FROM magazine_incoming AS A, prices AS B WHERE A.id_product= B.id_product AND id_incoming= (SELECT id_incoming FROM incoming WHERE id_vendor=2);

Съгласете се, че това е много по-кратко и по-ясно.

Изявления

Вече разгледахме синтаксиса за създаване на изгледи. След като изгледите са създадени, те могат да се използват по същия начин като таблиците. Тоест, изпълнявайте заявки срещу тях, филтрирайте и сортирайте данни и комбинирайте някои изгледи с други. От една страна, това е много удобен начин за съхранение на често използвани сложни заявки(както в нашия пример).

Но не забравяйте, че изгледите не са таблици, тоест те не съхраняват данни, а само ги извличат от други таблици. Следователно, първо, когато данните в таблиците се променят, резултатите от представянето също ще се променят. И второ, когато се направи заявка към изглед, се търсят необходимите данни, тоест производителността на СУБД се намалява. Затова не трябва да злоупотребявате с тях.

Да се ​​научим да обобщаваме. Не, това не са резултатите от изучаването на SQL, а резултатите от стойностите на колоните на таблиците на базата данни. Агрегат SQL функциидействайте върху стойностите на колоните, за да произведете една резултатна стойност. Най-често използваните SQL агрегатни функции са SUM, MIN, MAX, AVG и COUNT. Необходимо е да се прави разлика между два случая на използване на агрегатни функции. Първо, агрегатните функции се използват самостоятелно и връщат една резултатна стойност. Второ, агрегатните функции се използват с клаузата SQL GROUP BY, тоест групиране по полета (колони), за да се получат получените стойности във всяка група. Нека първо разгледаме случаите на използване на агрегатни функции без групиране.

SQL SUM функция

Функцията SQL SUM връща сумата от стойностите в колона на таблица на база данни. Може да се прилага само към колони, чиито стойности са числа. SQL заявките за получаване на резултантната сума започват така:

ИЗБЕРЕТЕ СУМА (COLUMN_NAME) ...

Този израз е последван от FROM (TABLE_NAME) и след това може да се посочи условие с помощта на клаузата WHERE. Освен това името на колоната може да бъде предшествано от DISTINCT, което означава, че ще се броят само уникални стойности. По подразбиране се вземат предвид всички стойности (за това можете конкретно да посочите не DISTINCT, а ALL, но думата ALL не се изисква).

Ако искате да изпълнявате заявки за база данни от този урок на MS SQL Server, но тази СУБД не е инсталирана на вашия компютър, тогава можете да я инсталирате, като използвате инструкциите на тази връзка .

Първо ще работим с фирмената база данни - Company1. Скриптът за създаване на тази база данни, нейните таблици и попълване на таблиците с данни е във файла на този линк .

Пример 1.Има фирмена база данни с данни за нейните поделения и служители. В таблицата Персонал има и колона с данни за заплатите на служителите. Изборът от таблицата изглежда така (за да увеличите снимката, кликнете върху нея с левия бутон на мишката):

За да получим сумата на всички заплати, използваме следната заявка (на MS SQL Server - с предходната конструкция USE company1;):

ИЗБЕРЕТЕ СУМА (Заплата) ОТ Персонал

Тази заявка ще върне стойността 287664.63.

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

Функция SQL MIN

Функцията SQL MIN работи и с колони, чиито стойности са числа, и връща минималната от всички стойности в колоната. Тази функция има синтаксис, подобен на този на функцията SUM.

Пример 3.Базата данни и таблицата са същите като в пример 1.

Трябва да разберем минималната заплата за служителите на отдел номер 42. За да направите това, напишете следната заявка (на MS SQL Server - с префикса USE company1;):

Заявката ще върне стойността 10505.90.

И отново упражнение за независимо решение . В това и някои други упражнения ще ви трябва не само таблицата Staff, но и таблицата Org, съдържаща данни за подразделенията на компанията:

Пример 4.Таблицата Org се добавя към таблицата Staff, съдържаща данни за отделите на компанията. Отпечатайте минималния брой години, отработени от един служител в отдел, разположен в Бостън.

Функция SQL MAX

Функцията SQL MAX работи по подобен начин и има подобен синтаксис, който се използва, когато трябва да определите максималната стойност сред всички стойности в колона.

Пример 5.

Трябва да разберем максималната заплата на служителите в отдел номер 42. За да направите това, напишете следната заявка (на MS SQL Server - с префикс USE company1;):

Заявката ще върне стойността 18352.80

Времето дойде упражнения за самостоятелно решение.

Пример 6.Отново работим с две таблици - Staff и Org. Покажете името на отдела и максималната стойност на комисионната, получавана от един служител в отдела, принадлежащ към групата отдели (Division) East. Използвайте JOIN (съединяване на таблици) .

Функция SQL AVG

Това, което е заявено относно синтаксиса за предишните описани функции, е вярно и за функцията SQL AVG. Тази функция връща средната стойност на всички стойности в колона.

Пример 7.Базата данни и таблицата са същите като в предишните примери.

Да речем, че искате да разберете средния стаж на служителите в отдел номер 42. За да направите това, напишете следната заявка (на MS SQL Server - с предходната конструкция USE company1;):

Резултатът ще бъде 6,33

Пример 8.Работим с една маса - Персонал. Покажете средната заплата на служители с опит от 4 до 6 години.

Функция SQL COUNT

Функцията SQL COUNT връща броя на записите в таблица на база данни. Ако укажете SELECT COUNT(COLUMN_NAME) ... в заявката, резултатът ще бъде броят на записите, без да се вземат предвид тези записи, в които стойността на колоната е NULL (недефинирана). Ако използвате звездичка като аргумент и започнете SELECT заявка COUNT(*) ..., тогава резултатът ще бъде броят на всички записи (редове) на таблицата.

Пример 9.Базата данни и таблицата са същите като в предишните примери.

Искате да знаете броя на всички служители, които получават комисионни. Броят служители, чиито стойности на колона Comm не са NULL, ще бъде върнат от следната заявка (на MS SQL Server - с префикс USE company1;):

ИЗБЕРЕТЕ БРОЯ (Comm) FROM Staff

Резултатът ще бъде 11.

Пример 10.Базата данни и таблицата са същите като в предишните примери.

Ако трябва да разберете общия брой записи в таблицата, тогава използвайте заявка със звездичка като аргумент на функцията COUNT (на MS SQL Server - с предходната конструкция USE company1;):

ИЗБЕРЕТЕ БРОЯ (*) ОТ персонала

Резултатът ще бъде 17.

В следващия упражнение за самостоятелно решениеще трябва да използвате подзаявка.

Пример 11.Работим с една маса - Персонал. Покажете броя на служителите в отдела за планиране (Plains).

Агрегирани функции с SQL GROUP BY

Сега нека разгледаме използването на агрегатни функции заедно с оператора SQL GROUP BY. Операторът SQL GROUP BY се използва за групиране на стойностите на резултатите по колони в таблица на база данни. Уеб сайтът има урок, посветен отделно на този оператор .

Ще работим с базата данни "Ads Portal 1". Скриптът за създаване на тази база данни, нейната таблица и попълване на таблицата с данни е във файла на тази връзка .

Пример 12.И така, има база данни на портала за реклами. Има таблица с реклами, съдържаща данни за реклами, изпратени за седмицата. Колоната Категория съдържа данни за големи рекламни категории (например Недвижими имоти), а колоната Части съдържа данни за по-малки части, включени в категориите (например частите Апартаменти и Вили са част от категорията Недвижими имоти). Колоната Единици съдържа данни за броя на подадените реклами, а колоната Пари съдържа данни за размера на парите, получени за подаване на реклами.

Категория	Част	единици	Пари
транспорт	автомобили	110	17600
недвижими имоти	Апартаменти	89	18690
недвижими имоти	Дачи	57	11970
транспорт	Мотоциклети	131	20960
Строителни материали	Дъски	68	7140
Електротехника	телевизори	127	8255
Електротехника	Хладилници	137	8905
Строителни материали	Regips	112	11760
свободно време	Книги	96	6240
недвижими имоти	у дома	47	9870
свободно време	Музика	117	7605
свободно време	игри	41	2665

Използвайки оператора SQL GROUP BY, намерете сумата пари, спечелени от публикуване на реклами във всяка категория. Пишем следната заявка (на MS SQL Server - с предходната конструкция USE adportal1;):

ИЗБЕРЕТЕ категория, СУМА (пари) КАТО пари ОТ РЕКЛАМИ, ГРУПИРАНИ ПО категория

Пример 13.Базата данни и таблицата са същите като в предишния пример.

Използвайки оператора SQL GROUP BY, разберете коя част от всяка категория има най-много списъци. Пишем следната заявка (на MS SQL Server - с предходната конструкция USE adportal1;):

ИЗБЕРЕТЕ категория, част, МАКС. (единици) КАТО максимум ОТ ГРУПА РЕКЛАМИ ПО категория

Резултатът ще бъде следната таблица:

Общите и индивидуалните стойности могат да бъдат получени в една таблица комбиниране на резултатите от заявката с помощта на оператора UNION .

Релационни бази данниданни и SQL език