Група за преброяване на Sql чрез примери. SELECT Команда GROUP BY Секция

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

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

Използването им обаче причинява много проблеми на начинаещите автори на софтуерни творения. Те не съвсем правилно интерпретират получените резултати и самия механизъм за групиране на данни. Затова нека да разберем на практика какво се случва и как.

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

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

user_name - потребителско име

forum_group - име на групата

mess_count - брой съобщения

is_have_social_profile - дали профилът във форума съдържа линк към страница в социална мрежа

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

Чисто групиране чрез групиране по

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

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

Посочете полета и изчислени колони изберете forum_group, avg(raiting) като avg_raiting, sum(mess_count) като total_mess_count -- Посочете таблицата от userstat -- Посочете групиране по поле група по forum_group

Моля, обърнете внимание, че след като сте използвали конструкцията group by в заявка, можете да използвате само тези полета в select, които са били посочени след group by, без да използвате агрегатни функции. Останалите полета трябва да бъдат посочени вътре в агрегатните функции.

Използвах и две агрегатни функции. AVG - изчислява средната стойност. И SUM - изчислява сумата.

форум_група	среден_рейтинг	total_mess_count
администратор	4	50
модер	3	50
потребител	3	150

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

2. След това към всяка група бяха приложени агрегатни функции. Например, за групата администратор средната оценка е изчислена както следва (2 + 5 + 5)/3 = 4. Броят на съобщенията е изчислен като (10 + 15 + 25) = 50.

Както можете да видите, нищо сложно. Приложихме обаче само едно условие за групиране и не филтрирахме по група. Така че нека преминем към следващия пример.

Групиране чрез групиране по и филтриране на групи с наличие

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

Първо ще опишем устно какво трябва да се направи в SQL заявката. Трябва да разделим всички редове на оригиналната таблица userstat според следните критерии: име на група и наличие на социален профил. Съответно е необходимо данните от таблицата да се групират по полетата forum_group и is_have_social_profile. Ние обаче не се интересуваме от онези групи, в които има само един човек. Следователно такива групи трябва да бъдат филтрирани.

Забележка: Струва си да знаете, че този проблем може да бъде решен чрез групиране само по едно поле. Ако използвате конструкцията case. В рамките на този пример обаче са показани възможностите за групиране.

Бих искал също незабавно да изясня един важен момент. Можете да филтрирате само като използвате, когато използвате агрегатни функции, а не по отделни полета. С други думи, това не е конструкция where, това е филтър за групи от редове, а не за отделни записи. Въпреки че условията вътре са посочени по подобен начин с помощта на "или" и "и".

Ето как би изглеждала SQL заявката

Посочете полета и изчислени колони изберете forum_group, is_have_social_profile, count(*) като общ -- Посочете таблицата от userstat -- Посочете групиране по полета група по forum_group, is_have_social_profile -- Посочете груповия филтър с count(*) > 1

Моля, имайте предвид, че полетата след конструкцията group by са разделени със запетаи. Указването на полета в select става по същия начин, както в предишния пример. Използвах и агрегатната функция count, която изчислява броя на редовете в групите.

Ето резултата:

форум_група	е_има_социален_профил	обща сума
администратор	1	2
модер	1	2
потребител	0	3

Нека да разгледаме стъпка по стъпка как се получи този резултат.

1. Първоначално се получават 6 групи. Всяка от групите forum_group беше разделена на две подгрупи въз основа на стойностите на полето is_have_social_profile. С други думи, групи: , , , , , .

Забележка: Между другото, не е задължително да има 6 групи. Така че, например, ако всички администратори са попълнили профил, тогава ще има 5 групи, тъй като полето is_have_social_profile ще има само една стойност за потребителите на администраторската група.

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

3. След това се изчисляват необходимите данни и се получава резултатът.

Както можете да видите, няма нищо трудно за използване.

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

Вече знаете как да групирате данни с group by, както и как да филтрирате групи с помощта на have.

Един от важните отбори в SQL е GROUP BY. Тази конструкция е създадена за избиране на отделни групи редове от таблица, към всяка от които функциите, посочени в ИЗБЕРЕТЕ(Например, БРОЯ(), MIN()и така нататък). Нека да разгледаме конкретни примери.

Да кажем, че имаме маса със супермаркети:

документ за самоличност- уникален идентификатор.
shop_id- уникален идентификатор на супермаркета.
цена- цена на млякото.

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

Оригиналната таблица изглежда така:

Така разбрахме средната цена в определена верига супермаркети (или в отделен магазин).

Друга много често срещана употреба е извличането на уникални записи от таблици. В предишния пример забелязахте, че няма дубликати в получената проба shop_id, докато в оригиналната таблица бяха.

Да кажем, че имаме таблица с потребители:

документ за самоличност- уникален идентификатор.
електронна поща - електронна пощапотребител.
хеш- уникален потребителски хеш.

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

Така че трябва да изберем всички записи с уникален хеш. За това отново се използва ГРУПИРАЙ ПО:

SELECT * FROM `table` GROUP BY `hash`

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

Ето две практични пример за използване на GROUP BY в SQLподредихме го.

Прегледахме много материали по SQL, по-специално Transact-SQL, но не засегнахме толкова проста тема като групиране на данни GROUP BY. Така че днес ще научим как да използваме групата по оператор за групиране на данни.

Много начинаещи програмисти, когато се сблъскват с SQL, не знаят за такава възможност като групиране на данни с помощта на оператора GROUP BY, въпреки че тази функция се изисква доста често на практика, в това отношение днешният ни урок, както обикновено с примери, е посветен до За вас беше по-лесно и по-лесно да научите как да използвате този оператор, тъй като определено ще го срещнете. Ако се интересувате от темата за SQL, тогава, както казах по-рано, сме го засягали повече от веднъж, например в статиите SQL език - JOIN или Union and union all, така че можете да се запознаете с този материал .

И за въведение, малко теория.

Какво представлява операторът GROUP BY

ГРУПИРАЙ ПОе операторът ( или дизайн, което ви е по-удобно) SQL за групиране на данни по поле при използване на агрегатни функции като sum, max, min, count и други в заявка.

Както знаете, агрегатните функции работят с набор от стойности, например sum сумира всички стойности. Но да кажем, че трябва да сумирате по някакво условие или по няколко условия наведнъж, затова се нуждаем от оператора за група по, за да групираме всички данни по полета с изхода на резултатите от агрегатните функции.

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

Забележка! Ще напишем всички примери в Management Studio SQL Server 2008.

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

И първо, нека създадем и попълним тестова таблица с данни, към които ще изпратим нашите заявки за избор, като използваме group by. Таблицата и данните, разбира се, са измислени, чисто примерни.

Създайте таблица

СЪЗДАВАНЕ НА ТАБЛИЦА.( NULL, (50) NULL, NULL, NULL) В ДВИЖЕНИЕ

Попълних го със следната информация:

Id – идентификатор на запис;
Име – фамилията на служителя;
Summa - пари в брой;
Наградата е знак за средства (например 1-Заплата; 2-Бонус).

Групиране на данни с помощта на група по заявка

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

Синтаксис:

Изберете агрегатни функции

от източник

Където Условия за избор

Групирай по полета за групиране

Имайки Условия за агрегатни функции

Подредени по полета за сортиране

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

ИЗБЕРЕТЕ SUM(сума)като сума ОТ test_table WHERE name="Ivanov"

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

ИЗБЕРЕТЕ SUM(сума)като сума, име ОТ test_table ГРУПИРАНЕ ПО име

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

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

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

ИЗБЕРЕТЕ SUM(summa)като [Общи средства], COUNT(*) като [Брой разписки], Име [Служител] ОТ test_table ГРУПИРАНЕ ПО име

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

ИЗБЕРЕТЕ SUM(summa)като [Общо средства], COUNT(*) като [Брой разписки], Име [Служител] , Награда [Източник] FROM test_table GROUP BY name, priz ORDER BY name

Сега всичко се показва, т.е. колко пари е получил служителят, колко пъти и от какъв източник.

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

ИЗБЕРЕТЕ SUM(summa) AS [Общи пари], COUNT(*) AS [Брой разписки], Име [Служител], CASE WHEN награда = 1 след това "Заплата" WHEN priz = 2 след това "Бонус" ELSE "Няма източник" END AS [Източник] FROM test_table GROUP BY име, priz ORDER BY име

Сега всичко е съвсем ясно и не е толкова трудно, дори и за начинаещи.

Нека се докоснем и до условията за крайните резултати на агрегатните функции ( имайки). С други думи, добавяме условие не за избор на самите редове, а за крайната стойност на функциите, в нашия случай това е sum или count. Например, трябва да покажем едно и също нещо, но само тези с "общо средства"повече от 200. За да направите това, добавете условието за наличие:

ИЗБЕРЕТЕ SUM(summa)като [Общи пари], COUNT(*) като [Брой разписки], Име [Служител], СЛУЧАЙ КОГАТО награда = 1 след това "Заплата" WHEN награда = 2 след това "Бонус" ИНАЧЕ "Няма източник" КРАЙ AS [Източник] ОТ test_table GROUP BY name, priz --group HAVING SUM(summa) > 200 --select ORDER BY name --sort

Сега показахме всички стойности на sum(summa), които са по-големи от 200, всичко е просто.

Надявам се след днешния урок да разберете как и защо да използвате структурата Групирай по. Късмет! Ще продължим да изучаваме SQL в следващите статии.

Последна актуализация: 19.07.2017

T-SQL използва операторите GROUP BY и HAVING за групиране на данни, използвайки следния формален синтаксис:

ИЗБЕРЕТЕ колони ОТ таблицата

ГРУПИРАЙ ПО

Клаузата GROUP BY определя как ще бъдат групирани редовете.

Например, нека групираме продуктите по производител

ИЗБЕРЕТЕ Производител, БРОЙ(*) КАТО МоделиБрой ОТ Продукти, ГРУПИРАНИ ПО Производител

Първата колона в оператора SELECT - Manufacturer представлява името на групата, а втората колона - ModelsCount представя резултата от функцията Count, която изчислява броя на редовете в групата.

Струва си да се има предвид, че всяка колона, която се използва в оператор SELECT (без да се броят колоните, които съхраняват резултата от агрегатни функции), трябва да бъде указана след клаузата GROUP BY. Така например в случая по-горе колоната Производител е посочена и в клаузите SELECT и GROUP BY.

И ако операторът SELECT избира една или повече колони и също така използва агрегатни функции, тогава трябва да използвате клаузата GROUP BY. По този начин следният пример няма да работи, защото не съдържа израз за групиране:

ИЗБЕРЕТЕ Manufacturer, COUNT(*) AS ModelsCount FROM Products

Друг пример, нека добавим групиране по брой продукти:

ИЗБЕРЕТЕ Manufacturer, ProductCount, COUNT(*) AS ModelsCount FROM Products GROUP BY Manufacturer, ProductCount

Клаузата GROUP BY може да групира по множество колони.

Ако колоната, по която групирате, съдържа NULL стойност, редовете с NULL стойност ще образуват отделна група.

Имайте предвид, че клаузата GROUP BY трябва да идва след клаузата WHERE, но преди клаузата ORDER BY:

ИЗБЕРЕТЕ Производител, БРОЙ(*) КАТО Брой модели ОТ Продукти WHERE Цена > 30000 ГРУПИРАНЕ ПО Производител ORDER BY Брой модели DESC

Групово филтриране. ИМАЩ

Оператор ИМАЩ определя кои групи ще бъдат включени в изходния резултат, тоест филтрира групите.

Използването на HAVING е в много отношения подобно на използването на WHERE. Само WHERE се използва за филтриране на редове, HAVING се използва за филтриране на групи.

Например, нека намерим всички продуктови групи по производител, за които е дефиниран повече от 1 модел:

ИЗБЕРЕТЕ Производител, БРОЯ (*) КАТО Брой модели ОТ ГРУПИРАНЕ НА ПРОДУКТИ ПО Производител ИМАЩ БРОЯ (*) > 1

В този случай в една команда можем да използваме WHERE и HAVING изрази:

ИЗБЕРЕТЕ производител, БРОЙ(*) КАТО ModelsCount FROM Продукти WHERE Цена * Продуктов брой > 80000 ГРУПИРАНЕ ПО производител HAVING COUNT(*) > 1

Тоест в този случай първо се филтрират редовете: избират се продуктите, чиято обща цена е повече от 80 000, след което избраните продукти се групират по производител. След това се филтрират самите групи - избират се тези групи, които съдържат повече от 1 модел.

Ако е необходимо сортиране, тогава изразът ORDER BY идва след израза HAVING:

ИЗБЕРЕТЕ Производител, БРОЙ(*) AS модели, SUM(ProductCount) AS Units FROM Products WHERE Price * ProductCount > 80000 GROUP BY Manufacturer HAVING SUM(ProductCount) > 2 ORDER BY Units DESC

В този случай групирането е по производител, като също така се избира броя на моделите за всеки производител (Models) и общия брой на всички продукти за всички тези модели (Units). Накрая групите се сортират по брой продукти в низходящ ред.

В този урок ще научите как да използвате SQL израза ГРУПИРАЙ ПОсъс синтаксис и примери.

Описание

Клаузата SQL GROUP BY може да се използва в оператор SELECT за събиране на данни от множество записи и групиране на резултатите в една или повече колони.

Синтаксис

Синтаксисът за оператора GROUP BY в SQL е:

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

израз1, израз2, … израз_n Изрази, които не са капсулирани в агрегатна функция и трябва да бъдат включени в GROUP BY в края на SQL заявката. aggregate_function Това е агрегатна функция като SUM, COUNT, MIN, MAX или AVG. aggregate_expression Това е колоната или изразът, за които ще се използва агрегатната_функция. таблици Таблиците, от които искате да извлечете записи. Клаузата FROM трябва да указва поне една таблица. Условия WHERE По избор. Това са условията, които трябва да бъдат изпълнени, за да изберете записи. ORDER BY израз По избор. Изразът, използван за сортиране на записите в резултатния набор. Ако е посочен повече от един израз, стойностите трябва да бъдат разделени със запетаи. ASC По избор. ASC сортира резултатния набор във възходящ ред по израз. Това е поведението по подразбиране, ако не е зададен модификатор. DESC По избор. DESC сортира резултатния набор в низходящ ред по израз.

Пример - Използване на GROUP BY с функцията SUM

Нека да видим как да използваме GROUP BY с функцията SUM в SQL.
В този пример имаме таблица на служителите със следните данни:

dept_id	общи_заплати
500	119500
501	113000

В този пример използвахме функцията SUM, за да съберем всички заплати за всеки dept_id, и дадохме на резултатите SUM(salary) псевдоним на „total_salaries“. Тъй като dept_id не е капсулиран във функцията SUM, той трябва да бъде указан в клаузата GROUP BY.

Пример - Използване на GROUP BY с функцията COUNT

Нека да видим как да използваме клаузата GROUP BY с функцията COUNT в SQL.

В този пример имаме таблица с продукти със следните данни:

В този пример използвахме функцията COUNT, за да изчислим броя total_products за всеки category_id, и посочихме псевдонима „total_products“ като резултати от функцията COUNT. Изключихме всички стойности на category_id, които са NULL, като ги филтрирахме в клаузата WHERE. Тъй като category_id не е капсулиран във функцията COUNT, той трябва да бъде указан в клаузата GROUP BY.

Пример - Използване на GROUP BY с функцията MIN

Нека сега да видим как да използваме клаузата GROUP BY с функцията MIN в SQL.

В този пример отново ще използваме таблицата на служителите със следните данни:

Ще бъдат избрани 2 записа. Ето резултатите, които ще получите:

dept_id	най-ниска_заплата
500	57500
501	42000

В този пример използвахме функцията MIN, за да върнем най-ниската стойност на заплатата за всеки dept_id, и използвахме псевдоним на резултатите от функцията MIN „lowest_salary“. Тъй като dept_id не е капсулиран във функцията MIN, той трябва да бъде посочен в клаузата GROUP BY.

Пример - Използване на GROUP BY с функцията MAX

И накрая, нека да разгледаме как да използваме клаузата GROUP BY с функцията MAX.

Нека отново използваме таблицата на служителите, но този път да намерим най-високата заплата за всеки dept_id:

номер_на_служител	първо име	фамилия	заплата	dept_id
1001	Джъстин	Бийбър	62000	500
1002	Селена	Гомес	57500	500
1003	Мила	Кунис	71000	501
1004	Том	Круиз	42000	501

Въведете следния SQL оператор.