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

Преди SQL ServerРешенията, базирани на набор от 2012 г., използвани за изчисляване на текущите суми, бяха изключително ресурсоемки. Следователно хората обикновено се обръщат към итеративни решения, които са по-бавни, но все пак по-бързи от решенията, базирани на набор, в някои ситуации. С подобряването на поддръжката на прозорци в SQL Server 2012, текущите суми могат да бъдат изчислени с помощта на прост код, базиран на набори, който се представя много по-добре от по-старите решения, базирани на T-SQL, както базирани на набори, така и итеративни. Мога да покажа ново решение и да премина към следващия раздел; но за да разбера наистина мащаба на промяната, ще опиша старите начини и ще сравня представянето им с новия подход. Естествено, можете да прочетете само първата част, която описва новия подход, и да пропуснете останалата част от статията.

За демонстрация различни решенияЩе използвам салдата по сметката. Ето кода, който създава и попълва таблицата Transactions с малко количество тестови данни:

ЗАДАВАЙТЕ NOCOUNT ON; ИЗПОЛЗВАЙТЕ TSQL2012; АКО OBJECT_ID ("dbo.Transactions", "U") НЕ Е НУЛА ДРОП ТАБЛИЦА dbo.Transactions; CREATE TABLE dbo.Transactions (actid INT NOT NULL, - колона за разделяне на tranid INT NOT NULL, - подреждане на колона val MONEY NOT NULL, - мярка CONSTRAINT PK_Transactions PRIMARY KEY (actid, tranid)); GO - малък набор от тестови данни INSERT INTO dbo.Transactions (actid, tranid, val) VALUES (1, 1, 4.00), (1, 2, -2.00), (1, 3, 5.00), (1, 4, 2.00 ), (1, 5, 1.00), (1, 6, 3.00), (1, 7, -4.00), (1, 8, -1.00), (1, 9, -2.00), (1, 10, -3.00), (2, 1, 2.00), (2, 2, 1.00), (2, 3, 5.00), (2, 4, 1.00), (2, 5, -5.00), (2, 6, 4,00), (2, 7, 2,00), (2, 8, -4,00), (2, 9, -5,00), (2, 10, 4,00), (3, 1, -3,00), (3, 2 , 3.00), (3, 3, -2.00), (3, 4, 1.00), (3, 5, 4.00), (3, 6, -1.00), (3, 7, 5.00), (3, 8 , 3.00), (3, 9, 5.00), (3, 10, -3.00);

Всеки ред в таблицата представлява банкова транзакция в сметка. Депозитите се маркират като транзакции с положителна стойност в колоната val, а тегленията като отрицателна стойност на транзакцията. Нашата задача е да изчислим салдото по сметката във всеки момент от времето, като натрупаме сумите от операции в реда val, сортирани по колоната tranid и това трябва да се направи за всяка сметка поотделно. Желаният изход трябва да изглежда така:

Необходими са повече данни за тестване на двете решения. Това може да стане с такава заявка:

ОБЯВЯВАЙТЕ @брой_раздели AS INT = 10, @rows_per_partition AS INT = 10000; TRUNCATE TABLE dbo.Transactions; INSER IN IN dbo.Transactions WITH (TABLOCK) (actid, tranid, val) SELECT NP.n, RPP.n, (ABS (CHECKSUM (NEWID ())% 2) * 2-1) * (1 + ABS (CHECKSUM { НОВО ()

Можете да посочите вашите входни данни, за да промените броя на разделите (сметки) и редовете (транзакции) в раздел.

Решение, базирано на множество, използващо прозоречни функции

Ще започна с решение, базирано на набори, което използва агрегираната функция на прозореца SUM. Дефиницията на прозорец е доста ясна тук: трябва да разделите прозореца по actid, да го подредите по tranid и да филтрирате линиите в рамката от крайното дъно (НЕОБРАНЕНО ПРЕДВАРИТЕЛНО) до текущото. Ето съответната заявка:

ИЗБЕРЕТЕ actid, tranid, val, SUM (val) НАД (РАЗДЕЛ ПО ACTID ПОРЪЧКА ПО TRANID РЕДОВЕ МЕЖДУ НЕОГРАНИЧЕНИ ПРЕДВАРИТЕЛНИ И ТЕКУЩИ РЕД) КАТО баланс ОТ dbo.Transactions;

Този код е не само прост и ясен - той също се изпълнява бързо. Планът за тази заявка е показан на фигурата:

Таблицата има клъстерен индекс, който отговаря на изискванията на POC и може да се използва от прозоречни функции. По -конкретно, списъкът с ключове на индекса се основава на разделящ елемент (actid), последван от подреждащ елемент (tranid), и също така включва всички други колони в заявката (val), за да осигури покритие. Планът съдържа подредено сканиране, последвано от изчисляване на номера на реда за вътрешна употреба и след това обобщението на прозореца. Тъй като има POC индекс, оптимизаторът не трябва да добавя оператор за сортиране към плана. Това е много ефективен план. Плюс това, той се мащабира линейно. По -късно, когато покажа резултатите от сравнението на производителността, ще видите колко по -ефективен е този метод в сравнение със старите решения.

Преди SQL Server 2012 са използвани или вложени заявки, или съединения. Когато използвате подзаявка, текущите суми се изчисляват чрез филтриране на всички редове със същата активна стойност като външния ред и стойност на tranid, която е по -малка или равна на стойността на външния ред. След това агрегирането се прилага към филтрираните редове. Ето съответната заявка:

Подобен подход може да се приложи с помощта на съединения. Използва се същият предикат, както в клаузата WHERE на подзаявката в клаузата ON на съединението. В този случай за N -та транзакция на същата сметка A в екземпляра, определен като T1, ще намерите N съвпадения в екземпляра T2, докато номерата на транзакциите текат от 1 до N. В резултат на сравнението редовете в T1 се повтарят, така че се нуждаете от групови редове от всички елементи с T1, за да получите информация за текущата транзакция и да обобщите атрибута val от T2, за да изчислите текуща сума. Готовата заявка изглежда така:

ИЗБЕРЕТЕ T1.actid, T1.tranid, T1.val, SUM (T2.val) КАТО баланс ОТ dbo. Транзакции КАТО T1 ПРИСЪЕДИНЯВАНЕ dbo. Транзакции AS T2 ON T2.actid = T1.actid И T2.tranid<= T1.tranid GROUP BY T1.actid, T1.tranid, T1.val;

Фигурата по -долу показва плановете за двете решения:

Имайте предвид, че и в двата случая се извършва пълно сканиране на индекса на инстанция T1. След това за всеки ред в плана има операция за търсене в индекса на началото на раздела по текущата сметка на последната страница на индекса и всички транзакции, при които T2.tranid е по -малък или равен на T1.tranid се четат. Точката на агрегиране на редове е малко по -различна в плановете, но броят на четените редове е един и същ.

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

Във всеки раздел планът предвижда четене на 1 + 2 + ... + r редове, което общо е (r + r * 2) / 2. Общият брой редове, обработени в плановете, е p * r + p * (r + r2) / 2. Това означава, че броят на операциите в плана нараства на квадрат с увеличаване на размера на секцията, тоест ако размерът на секцията се увеличи с f пъти, количеството работа ще се увеличи с около f 2 пъти. Това е лошо. Например 100 реда съответстват на 10 хиляди реда, а хиляда реда съответстват на милион и т.н. Просто казано, това води до силно забавяне на изпълнението на заявка с доста голям размер на секцията, тъй като квадратичната функция расте много бързо. Такива решения работят задоволително с няколко десетки линии на секция, но не повече.

Курсорни решения

Решенията, базирани на курсори, се прилагат челно. Курсорът се декларира въз основа на заявка за подреждане на данни от actid и tranid. След това се извършва итеративно обхождане на курсорните записи. Когато се намери нов акаунт, променливата, съдържаща съвкупността, се нулира. Във всяка итерация сумата на новата транзакция се добавя към променливата, след което редът се съхранява в таблична променлива с информация за текущата транзакция плюс текущата стойност на кумулативната сума. След итеративен проход резултатът се връща от променливата на таблицата. Ето кода за цялостното решение:

ОБЯВЯВАЙТЕ @Резултат КАТО ТАБЛИЦА (активен INT, tranid INT, val MONEY, баланс MONEY); ОБЯВЯВАЙТЕ @actid AS INT, @prvactid AS INT, @tranid AS INT, @val КАТО ПАРИ, @баланс КАТО ПАРИ; ОБЯВЯВАЙТЕ C CURSOR FAST_FORWARD FOR SELECT actid, tranid, val FROM dbo.Transactions ORDER BY actid, tranid; ОТВОРИ C ВЗЕМЕТЕ СЛЕДВАЩО ОТ C В НА @actid, @tranid, @val; SELECT @prvactid = @actid, @balance = 0; ДОКАТО @@ fetch_status = 0 ЗАПОЧНЕТЕ, АКО @actid<>@prvactid SELECT @prvactid = @actid, @balance = 0; SET @баланс = @баланс + @val; ИНСЕРТИРАЙТЕ В @Резултатни стойности (@actid, @tranid, @val, @balance); ВЗЕМЕТЕ СЛЕДВАЩО ОТ C В НА @actid, @tranid, @val; КРАЙ ЗАТВОРИ С; ДЕАЛОКИРАНЕ С; SELECT * FROM @Result;

Планът за заявка с помощта на курсор е показан на фигурата:

Този план се мащабира линейно, тъй като данните от индекса се сканират само веднъж в определен ред. Също така, всяка операция за получаване на ред от курсор има приблизително същата цена на ред. Ако вземем натоварването, генерирано чрез обработка на един ред от курсора, равно на g, цената на това решение може да бъде оценена като p * r + p * r * g (както си спомняте, p е броят на секциите, а r е броя редове в раздела). Така че, ако увеличим броя редове на секция с f пъти, натоварването на системата ще бъде p * r * f + p * r * f * g, тоест ще расте линейно. Цената на обработка на ред е висока, но поради линейния характер на мащабирането, от определен размер на дяла това решение ще покаже по -добра мащабируемост от вложените и присъединителни решения поради квадратното мащабиране на тези решения. Измерване на производителността, което направих, показа, че броят, когато разтворът на курсора е по -бърз, е няколкостотин реда на секция.

Въпреки увеличенията на производителността, осигурени от решенията, базирани на курсора, като цяло те трябва да се избягват, тъй като не са относителни.

Решения, базирани на CLR

Едно възможно решение, базирано на CLR (изпълнение на общ език)по същество е форма на решение, базирано на курсора. Разликата е, че вместо да използвате курсор T-SQL, който губи много ресурси за извличане на следващия ред и повторение, използвайте .NET SQLDataReader и .NET итерации, които са много по-бързи. Една от характеристиките на CLR, която прави тази опция по -бърза, е, че полученият ред във временната таблица не е необходим - резултатите се изпращат директно към извикващия процес. Логиката за вземане на решения, базирана на CLR, е подобна на курсорно базираната логика на решение T-SQL. Ето кода на C #, който определя съхранената процедура за решение:

Използване на системата; използване на System.Data; използване на System.Data.SqlClient; използване на System.Data.SqlTypes; използване на Microsoft.SqlServer.Server; публичен частичен клас StoredProcedures (public static void AccountBalances () (използвайки (SqlConnection conn = new SqlConnection ("context connection = true;")) (SqlCommand comm = new SqlCommand (); comm.Connection = conn; comm.CommandText = @ " "+" SELECT actid, tranid, val "+" FROM dbo.Transactions "+" ORDER BY actid, tranid; "; SqlMetaData колони = нови SqlMetaData; колони = нови SqlMetaData (" actid ", SqlDbType.Int); SqlMetaData ("tranid", SqlDbType.Int); колони = нови SqlMetaData ("val", SqlDbType.Money); колони = нови SqlMetaData ("баланс", SqlDbType.Money); ; conn.Open (); SqlDataReader четец = comm.ExecuteReader (); SqlInt32 prvactid = 0; SqlMoney баланс = 0; GetSqlMoney (2); if (actid == prvactid) (balance + = val;) else (balance = val;) prvactid = actid; rec ord.SetSqlInt32 (0, reader.GetSqlInt32 (0)); record.SetSqlInt32 (1, reader.GetSqlInt32 (1)); record.SetSqlMoney (2, val); record.SetSqlMoney (3, баланс); SqlContext.Pipe.SendResultsRow (запис); ) SqlContext.Pipe.SendResultsEnd (); )))

За да можете да изпълните тази съхранена процедура в SQL Server, първо трябва да изградите сборка с име AccountBalances от този код и да я разгърнете в базата данни TSQL2012. Ако сте нов за внедряване на сборки в SQL Server, можете да прочетете Съхранените процедури и раздела CLR на статията Съхранени процедури.

Ако сте кръстили сборката AccountBalances и пътят към файла за сглобяване е "C: \ Projects \ AccountBalances \ bin \ Debug \ AccountBalances.dll", можете да заредите сборката в базата данни и да регистрирате съхранената процедура със следния код:

CREATE ASSEMBLY AccountBalances ОТ "C: \ Projects \ AccountBalances \ bin \ Debug \ AccountBalances.dll"; ОТПЪЛНЕТЕ ПРОЦЕДУРА dbo.AccountBalances КАТО ВЪНШНО ИМЕ AccountBalances.StoredProcedures.AccountBalances;

След разгръщане на сборката и регистриране на процедурата, можете да я изпълните със следния код:

EXEC dbo.AccountBalances;

Както казах, SQLDataReader е просто друга форма на курсор, но в тази версия цената за четене на редове е значително по-малка от използването на традиционен T-SQL курсор. Също така в .NET итерациите са много по-бързи, отколкото в T-SQL. По този начин решенията, базирани на CLR, също се мащабират линейно. Тестването показа, че производителността на това решение става по -висока от производителността на решения, използващи подзаявки и съединения, когато броят на редовете в раздел надвишава 15.

Когато приключите, изпълнете следния код за почистване:

DROP ПРОЦЕДУРА dbo.AccountBalances; DROP ASSEMBLY AccountBalances;

Вложени итерации

До този момент съм показвал итеративни и базирани на множество решения. Следното решение се основава на вложени итерации, което е хибрид от итеративни и базирани на множество подходи. Идеята е предварително да копирате редовете от изходната таблица (в нашия случай това са банкови сметки) във временна таблица заедно с нов атрибут, наречен rownum, който се изчислява с помощта на функцията ROW_NUMBER. Номерата на редовете са разделени по actid и подредени от tranid, така че на първата транзакция във всяка банкова сметка е присвоен номер 1, на втората транзакция е присвоен номер 2 и т.н. След това на временната таблица се създава клъстериран индекс със списък с ключове (rownum, actid). След това се използва рекурсивен CTE израз или специално проектиран цикъл за обработка на един ред на итерация във всички фактури. След това текущата сума се изчислява чрез добавяне на стойност, съответстваща на текущия ред, със стойността, свързана с предишния ред. Ето реализация на тази логика, използваща рекурсивен CTE:

SELECT actid, tranid, val, ROW_NUMBER () OVER (PARTITION BY actid ORDER BY tranid) AS rownum INTO #Transactions FROM dbo.Transactions; CREATE UNIQUE CLUSTERED INDEX idx_rownum_actid ON #Transactions (rownum, actid); С C AS (SELECT 1 AS rownum, actid, tranid, val, val AS sumqty FROM #Transactions WHERE rownum = 1 UNION ALL SELECT PRV.rownum + 1, PRV.actid, CUR.tranid, CUR.val, PRV.sumqty + CUR.val ОТ C C PRV ПРИСЪЕДИНЯВАНЕ #Транзакции като CUR ON CUR.rownum = PRV.rownum + 1 И CUR.actid = PRV.actid) SELECT actid, tranid, val, sumqty ОТ ОПЦИЯ C (MAXRECURSION 0); ДРОП ТАБЛИЦА #Транзакции;

И това е реализация, използваща изричен цикъл:

SELECT ROW_NUMBER () OVER (PARTITION BY actid ORDER BY tranid) AS rownum, actid, tranid, val, CAST (val AS BIGINT) AS sumqty INTO #Transactions FROM dbo.Transactions; CREATE UNIQUE CLUSTERED INDEX idx_rownum_actid ON #Transactions (rownum, actid); ОБЯВЯВАЙТЕ @rownum AS INT; SET @rownum = 1; ДОКАТО 1 = 1 ЗАПОЧНЕТЕ SET @rownum = @rownum + 1; АКТУАЛИЗИРАНЕ НА CUR SET sumqty = PRV.sumqty + CUR.val ОТ #Transactions AS CUR JOIN #Transactions AS PRV ON CUR.rownum = @rownum И PRV.rownum = @rownum - 1 И CUR.actid = PRV.actid; IF @@ rowcount = 0 BREAK; END SELECT actid, tranid, val, sumqty ОТ #Транзакции; ДРОП ТАБЛИЦА #Транзакции;

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

Многоредова актуализация с променливи

Техниките за изчисляване на кумулативните суми, показани до този момент, гарантирано ще дадат правилния резултат. Методологията, описана в този раздел, е двусмислена, тъй като се основава на наблюдаваното, а не документирано поведение на системата, и също така противоречи на принципите на относителността. Високата му привлекателност се дължи на високата скорост на работа.

Този метод използва инструкция UPDATE с променливи. Оператор UPDATE може да присвоява изрази на променливи въз основа на стойността на колона, както и да присвоява стойности в колони на израз с променлива. Решението започва със създаване на временна таблица с име Транзакции с атрибутите actid, tranid, val и balance и клъстерен индекс със списък с ключове (actid, tranid). След това временната таблица се попълва с всички редове от оригиналната база данни за транзакции, като 0.00 е въведена в колоната за баланс на всички редове. След това се извиква оператор UPDATE с променливите, свързани с временната таблица, за да се изчислят текущите суми и да се вмъкне изчислената стойност в колоната на баланса.

Използват се променливите @prevaccount и @prevbalance, а стойността в колоната на баланса се изчислява с помощта на следния израз:

SET @prevbalance = balance = CASE WHEN actid = @prevaccount THEN @prevbalance + val ELSE val END

Изразът CASE проверява дали идентификаторите на текущите и предишните сметки са еднакви и, ако са равни, връща сумата от предишните и текущите стойности в колоната на баланса. Ако идентификаторите на акаунта са различни, се връща текущата сума на транзакцията. Резултатът от израза CASE след това се вмъква в колоната на баланса и се присвоява на променливата @prevbalance. В отделен израз променливата © prevaccount получава идентификатор на текущата сметка.

След инструкцията UPDATE решението представя редовете от временната таблица и изтрива последната. Ето кода за цялостното решение:

CREATE TABLE #Transactions (actid INT, tranid INT, val MONEY, баланс MONEY); CREATE CLUSTERED INDEX idx_actid_tranid ON #Transactions (actid, tranid); INSERT INTO #Transactions WITH (TABLOCK) (actid, tranid, val, balance) SELECT actid, tranid, val, 0.00 FROM dbo.Transactions ORDER BY actid, tranid; ОБЯВЯВАЙТЕ @prevaccount КАТО INT, @prevbalance КАТО ПАРИ; UPDATE #Transactions SET @prevbalance = balance = CASE WHEN actid = @prevaccount THEN @prevbalance + val ELSE val END, @prevaccount = actid FROM #Transactions WITH (INDEX (1), TABLOCKX) OPTION (MAXDOP 1); SELECT * FROM #Transactions; ДРОП ТАБЛИЦА #Транзакции;

Планът за това решение е показан на следващата фигура. Първата част е оператор INSERT, втората е UPDATE, а третата е SELECT:

Това решение предполага, че подреденото сканиране на клъстерния индекс винаги ще се извършва при оптимизиране на изпълнението на UPDATE и решението предоставя редица подсказки за предотвратяване на обстоятелства, които биха могли да попречат това да се случи, като паралелност. Проблемът е, че няма официална гаранция, че оптимизаторът винаги ще изглежда в реда на клъстерирания индекс. Не можете да разчитате на спецификата на физическите изчисления, за да наложите логическата коректност на кода си, освен ако в кода има логически порти, които по дефиниция могат да гарантират това поведение. В този код няма логически функции, които да гарантират точно това поведение. Естествено, изборът дали да използвате този метод или не, зависи изцяло от вашата съвест. Мисля, че е безотговорно да го използвате, дори ако сте го проверявали хиляди пъти и „изглежда, че всичко работи както трябва“.

За щастие, в SQL Server 2012 този избор става почти ненужен. С изключително ефективно решение за агрегиране с прозорец, не е нужно да мислите за други решения.

измерване на резултатите

Измервал съм и сравнявал ефективността на различни техники. Резултатите са показани на фигурите по -долу:

Разделих резултатите на две графики, защото вложеният метод или методът на присъединяване е толкова по -бавен от останалите, че трябваше да използвам различна скала за него. Във всеки случай, имайте предвид, че повечето решения показват линейна зависимост на количеството работа от размера на дяла и само решението, основано на подзаявка или съединение, показва квадратична зависимост. Ясно е също колко по -ефективно е новото решение за агрегиране на прозорци. Решението UPDATE с променливи също е много бързо, но поради вече описаните причини не препоръчвам да го използвате. Решението CLR също е доста бързо, но трябва да напишете целия този .NET код и да разположите сборката в базата данни. Както и да го погледнете, решението, базирано на множество, използващо агрегати за прозорци, остава предпочитаното решение.

Как мога да разбера броя на моделите компютри, произведени от конкретен доставчик? Как да определим средната цена за компютри със същите спецификации? На тези и много други въпроси, свързани с някои статистически данни, може да се отговори с помощта обобщени (съвкупни) функции... Стандартът предвижда следните съвкупни функции:

Всички тези функции връщат една стойност. В този случай функциите COUNT, MINи МАКСса приложими за всеки тип данни, докато SUMи AVGсе използват само за числови полета. Разлика между функциите БРОЯ (*)и БРОЯ (<имя поля>) е, че вторият не взема предвид NULL стойностите при изчисляване.

Пример. Намерете минималната и максималната цена за персонални компютри:

Пример. Намерете броя налични компютри от производител А:

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

Пример. Намерете броя на различните модели, налични от производителя А. Заявката е подобна на предишната, при която беше необходимо да се определи общият брой модели, произведени от производителя А. Тук също трябва да намерите броя на различните модели в PC маса (т.е. налична в търговската мрежа).

За да се гарантира, че при получаване на статистически показатели се използват само уникални стойности, кога аргумент на съвкупни функцииможе да се използва DISTINCT параметър... Друг параметър ALLе по подразбиране и приема, че всички връщани стойности в колоната се отчитат. Оператор,

Ако трябва да получим броя на произведените модели компютри всекипроизводител, ще трябва да използвате Клауза GROUP BYследва синтактично КЪДЕ клаузи.

Клауза GROUP BY

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

Има няколко конкретни правила за изпълнение на съвкупни функции:

• Ако в резултат на заявката не са получени редове(или повече от един ред за дадена група), тогава първоначалните данни за изчисляване на някоя от съвкупните функции липсват. В този случай резултатът от изпълнението на функциите COUNT ще бъде нула, а резултатът от всички останали функции ще бъде NULL.
• Аргументагрегираща функция не може да съдържа агрегирани функции(функция от функция). Тези. в една заявка не можете, да речем, да получите максималните средни стойности.
• Резултатът от изпълнението на функцията COUNT е цяло число(INTEGER). Други съвкупни функции наследяват типовете данни на обработваните стойности.
• Ако при изпълнение на функцията SUM е получен резултат, който надвишава максималната стойност на използвания тип данни, a грешка.

Така че, ако заявката не съдържа Клаузи GROUP BY, тогава агрегатни функциивключен в Клауза SELECT, се изпълняват върху всички получени редове на заявка. Ако искането съдържа Клауза GROUP BY, всеки набор от редове, който има същата колона или група колони, посочени в Клауза GROUP BY, съставлява група и агрегатни функциисе извършват за всяка група поотделно.

HAVING клауза

Ако Клаузата WHEREдефинира предикат за филтриране на редове, след това ИМА ОФЕРТАприложен след групиранеза дефиниране на подобни групи за филтриране на предикати по стойности агрегатни функции... Тази клауза е необходима за тестване на стойностите, получени с помощта агрегираща функцияне от отделни редове на източника на запис, дефиниран в ОТ клауза, и от групи от такива редове... Следователно такава проверка не може да се съдържа в Клаузата WHERE.

Този урок ще ви покаже как да използвате функцията SUMв SQL Server (Transact-SQL) със синтаксис и примери.

Описание

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

Синтаксис

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

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

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

expression1, expression2,… expression_n са изрази, които не са включени във функцията SUM и трябва да бъдат включени в клаузата GROUP BY в края на SQL израза.
aggregate_expression е колоната или изразът, който трябва да бъде обобщен.
таблици - таблиците, от които искате да получите записи. В клаузата FROM трябва да има поне една таблица.
КЪДЕ условията не са задължителни. Това са условията, които трябва да бъдат изпълнени за избраните записи.

Приложение

Функцията 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) КАТО „Обща комисионна“ ОТ поръчки;

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

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

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

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


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


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


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


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

SELECT MIN (цена), MAX (цена), AVG (цена) ОТ цените;

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

1. Първо изберете от таблицата „Доставки“ (входящи) идентификаторите (id_incoming) на онези доставки, които са направени от доставчика на „Печатница“ (id = 2):

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

3. Сега трябва да добавим към получената таблица цените за намерените продукти, които се съхраняват в таблицата с цени. Тоест, трябва да обединим таблиците Magazine_incoming и Цени от колоната id_product:

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

SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity * prices.price AS сума FROM от magazine_incoming, цени WHERE magazine_incoming.id_product = prices.id_product И id_incoming = (SELECT id_incoming ОТ входящ WHERE 2 id_vendor

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

SELECT SUM (column_name) FROM table_name;

Знаем името на колоната - 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 сума от FROM_incoming, цени WHERE magazine_incoming.id_product = prices.id_product И id_incoming = (FROMECT id_incoming = (FROMECT id_incoming) ;

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

SELECT SUM (summa) FROM report_vendor;

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

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

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

Вторият нюанс е ключовата дума 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, цени AS B WHERE A.id_product = B.id_product И id_incoming = (SELECT id_incoming ОТ входящ КЪДЕ 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.

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

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

SQL MAX функция

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

Пример 5.

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

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

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

Пример 6.Работим отново с две маси - персонал и организация. Покажете името на отдела и максималните комисионни, спечелени от един служител в отдел, принадлежащ към Източния отдел. Използвайте 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) от персонала

Резултатът е 11.

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

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

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

Резултатът е 17.

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

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

Обобщени функции с SQL GROUP BY (групиране)

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

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

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

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

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

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

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

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

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

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

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