* Групиране на данни
Публикувано на 17 март 2009 в раздел Бази от Данни.
Когато имаме повтарящи се данни в дадена колона, можем да ги “групираме”. За целта се използва клаузата “GROUP BY”. Този оператор обикновено се прилага в комбинация с агрегатни функции. Целта е агрегатната функция да се приложи върху множество по-малки резултатни таблици, резултатите от които накрая да се обединят в една.
1. Да се изведе сумата на акаунти от тип 1 и сумата на акаунти от тип 2:
mysql> SELECT type, SUM(amount) FROM accounts GROUP BY type; +------+-------------+ | type | SUM(amount) | +------+-------------+ | 1 | 49468.67 | | 2 | 8419.00 | +------+-------------+ 2 rows in set (0.00 sec)
2. Изкарайте името на клиентите и сумата от техните сметки в системата:
SELECT customers.name, SUM(accounts.amount) FROM customers LEFT JOIN accounts ON customers.id = accounts.customer_id GROUP BY customers.name; +-------------------+----------------------+ | name | SUM(accounts.amount) | +-------------------+----------------------+ | Alain Delrick | 3910.50 | | Alexander Smith | 1536.91 | | Atanas Petrov | 1200.00 | | Devry Henry | 231.37 | | Elica Zaharieva | 211.98 | | Fontaine Rupert | 500.00 | | George Harison | 991.63 | | George Lucas | 19036.07 | | Ivailo Ivanov | 99.18 | | Ivan Ivanov | 133.48 | | Lenard Renne | 9463.23 | | Maria Smith | 14921.43 | | Mariana Zaharieva | 1236.33 | | Michael Jackson | 559.32 | | Mihail Ivchev | 1331.50 | | Neno Nenov | 42.98 | | Petko Stoianov | 136.22 | | Todor Ivanov | 156.38 | | Todor Shtilianov | 317.79 | | Tony Martin | 1212.70 | | Tony McCarter | 402.26 | | Zlatomir Petrov | 256.41 | +-------------------+----------------------+ 22 rows in set (0.00 sec)
В последния пример групирането е по име. Трябва да отбележим, че това по принцип е грешно, защото е възможно да има двама клиенти с едно и също име. Затова е по-добре да групираме по уникалния ключ:
SELECT customers.name, SUM(accounts.amount) FROM customers LEFT JOIN accounts ON customers.id = accounts.customer_id GROUP BY customers.id ORDER BY customers.name;
За да видите разликата, въведете още един клиент с име „Ivan Ivanov“ и направете горните заявки още веднъж. Тук трябва да забележим, че ANSI стандарта изисква задължително в условието GROUP BY да присъства някоя от колоните изредени след SELECT. В MySQL това не е задължително.
3. Изведете имената на банките и средната сума на акаунтите в тях:
mysql> SELECT banks.name, AVG(accounts.amount)
FROM banks JOIN accounts
ON accounts.branch_id IN(
SELECT branches.id
FROM branches
WHERE branches.bank_code = banks.code
)
GROUP BY banks.name;
+-----------------+-------------+
| name | AVG(amount) |
+-----------------+-------------+
| Bank of America | 8229.170000 |
| Bulbank | 426.854167 |
| Societe General | 2821.020000 |
| Wells Fargo | 2775.247500 |
+-----------------+-------------+
4 rows in set (0.00 sec)
4. За да демонстрираме по-сложни заявки, нека усложним предишния пример малко – изведете името на банката и средната сума на акаунтите в нея, но само за банките, които имат буквата „M“ някъде в името на банковия клон:
mysql> SELECT banks.name, AVG(accounts.amount)
FROM banks JOIN branches ON branches.bank_code = banks.code
JOIN accounts ON accounts.branch_id = branches.id
WHERE branches.name LIKE '%M%'
GROUP BY banks.name;
+-----------------+-------------+
| name | AVG(amount) |
+-----------------+-------------+
| Societe General | 2821.020000 |
| Wells Fargo | 2775.247500 |
+-----------------+-------------+
2 rows in set (0.00 sec)
Тук добавихме и таблицата branches в JOIN между таблиците, защото в WHERE използвахме информация от нея.
5. Изведете името на банката, клона, името на служителя и общата сума в акаунтите, на чийто клиенти той е мениджър. Полученият резултат да се сортира в реда име на банка, име на клон и име на служител:
SELECT banks.name, branches.name, employees.name, SUM(accounts.amount)
FROM banks JOIN branches ON banks.code = branches.bank_code
JOIN employees ON branches.id = employees.branch_id
JOIN accounts ON accounts.customer_id IN(
SELECT customers.id
FROM customers
WHERE customers.bank_mgr = employees.id
)
GROUP BY employees.id
ORDER BY banks.name, branches.name, employees.name;
+-----------------+------------+-----------------+-------------+
| name | name | name | SUM(amount) |
+-----------------+------------+-----------------+-------------+
| Bank of America | LA | James Pitt | 16458.34 |
| Bulbank | Serdica | Ivan Ivanov | 292.60 |
| Bulbank | Serdica | Ivan Stoianov | 42.98 |
| Bulbank | Serdica | Mihail Zahariev | 1448.31 |
| Bulbank | Slatina | Ilian Stoianov | 317.79 |
| Bulbank | Slatina | Milen Stoilov | 1589.89 |
| Bulbank | Slatina | Petar Petrov | 99.18 |
| Bulbank | Slatina | Svilen Petrov | 1331.50 |
| Societe General | Marseilles | Alfonso Levi | 500.00 |
| Societe General | Paris | Francoa Dupres | 13605.10 |
| Wells Fargo | Manhattan | Jimmy Carter | 21799.72 |
| Wells Fargo | Manhattan | Mary Jane | 402.26 |
+-----------------+------------+-----------------+-------------+
12 rows in set (0.00 sec)
Както виждате GROUP BY, в комбинация с агрегатни функции, ни дава добри възможности за обобщаване на данни и извършване на прости статистически изчисления.
6. Изведете името на банката, клона, името на служителят и общата сума в акаунтите, на чийто клиенти той е мениджър. Получения резултат да се сортира в реда име на банка, име на клон и име на служител, но само за служителите, чието име започва с „M“:
SELECT banks.name, branches.name, employees.name, SUM(accounts.amount)
FROM banks JOIN branches ON banks.code = branches.bank_code
JOIN employees ON branches.id = employees.branch_id
JOIN accounts ON accounts.customer_id IN(
SELECT customers.id
FROM customers
WHERE customers.bank_mgr = employees.id
)
WHERE employees.name LIKE 'M%'
GROUP BY employees.id
ORDER BY banks.name, branches.name, employees.name;
+-------------+-----------+-----------------+-------------+
| name | name | name | SUM(amount) |
+-------------+-----------+-----------------+-------------+
| Bulbank | Serdica | Mihail Zahariev | 1448.31 |
| Bulbank | Slatina | Milen Stoilov | 1589.89 |
| Wells Fargo | Manhattan | Mary Jane | 402.26 |
+-------------+-----------+-----------------+-------------+
3 rows in set (0.00 sec)
Първо се изчисляват групите на първата таблица и към към тях се прави декартово произведение с резултата от JOIN на втората таблица.
в заявка 4 какъв е реда на изпълнението, защото ми стана доста объркано.
Аз си го обяснявам някакси, че се изпулнява парво частта която е след WHERE след това частта която е след ON и след това групирането и залепването.
т.е. парво се изпълнява частта която е след WHERE и след това частта която е след ON (условието за JOIN)?
Да, така е – аз написах същото :)
Защо в пример 3 свързващтото условие в ON е accounts.customer_id „ON accounts.customer_id IN(„?
И къде има връзка между customers и branch_id „SELECT id FROM customers WHERE branch_id“, при условие че
„CREATE TABLE `banks`.`customers` (
`id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 255 ) NOT NULL ,
`address` VARCHAR( 255 ) NULL DEFAULT NULL ,
`bank_mgr` INT NULL ,
PRIMARY KEY ( `id` ) ,
FOREIGN KEY ( `bank_mgr` )
REFERENCES `banks`.`employees`( `id` )
) ENGINE = InnoDB;“ ?
Пример 3 е грешен. Сега ще го поправя.
Благодаря!
В пример 4 вадим данни от таблици banks и accounts (FROM banks LEFT JOIN accounts…), а искаме да ограничим банките по условието „да имат буква M в името на банков КЛОН (т.е. от таблица branches). Тъй като branches не участва в условието FROM, то директно условие „WHERE branches.name LIKE ‘%M%'“ само по себе си не би направило нищо. Трябва преди това да се укаже връзката към банките (таблица banks) и чак тогава да се прави ограничението.
В пример 6 таблица employees участва в условието FROM и съответно можем спокойно да използваме ограничение по нея.
може би задавам глупав въпрос, но защо в пример 4 трябва да кажем:
WHERE banks.code IN(
SELECT bank_code
FROM branches
WHERE name LIKE ‘%M%’
а в пример 6 можем директно да напишем ‘ WHERE employees.name LIKE ‘M%’ ‘ без да тръгваме от banks надолу към employees ?
Благодаря…това обяснява много неща :D
Възможно ли е да се направи агрегатна функция SUM на няколко полета от еднакъв числен тип и по какъв начин?
В пример 4 пиша заявката точно както е показана, но в резултата за Societe General средната сума не ми я изкарва същата.Тъй като Societe General има два клона по някакъв начин избира сметката само в един клон, а в другите не ги изчислява.
Пример 4 е за съвсем друго.
Значи в базата данни има записани допълнителни, изтрити или променени данни. Няма никакъв проблем. Важно е заявката да е написана правилно, а това какви резултати ще излезнат зависи от това какво е съхранено.
В пример 4 заявката, която е дадена не съответства на резултата, даден под нея.
В точка 3 а и като цяло където е използвано JOIN ако се напише така:
SELECT banks.name, AVG(accounts.amount)
FROM banks, accounts
WHERE accounts.branch_id IN(
SELECT branches.id
FROM branches
WHERE branches.bank_code = banks.code
)
GROUP BY banks.name;
Каква е разликата? Кое е най-добре да използваме?
Dimitar – стар и нов стандарт за писане на SQL код. Твоя пример е по стария, с JOIN е по „новия“ стандарт (който всъщност е на 20+ години – SQL92). Прието е свързването на таблици да става във FROM, а ограничаващите условия да стават в WHERE.
В пример 4 може ли заявката да се напише така, за да не правим JOIN и на branches?
SELECT banks.name, AVG(amount)
FROM banks JOIN accounts
ON banks.code IN
(SELECT bank_code FROM branches
WHERE id=accounts.branch_id AND name LIKE „%M%“)
GROUP BY banks.name
Може, но не бих го направил така (поне аз).
Относно „Филип Петров казва:
04 март 2014 в 17:33
Dimitar – стар и нов стандарт за писане на SQL код. Твоя пример е по стария, с JOIN е по „новия“ стандарт (който всъщност е на 20+ години – SQL92). Прието е свързването на таблици да става във FROM, а ограничаващите условия да стават в WHERE.
“
Понеже прочетох в по-горните статии че наслагването на JOIN води до бавно изпълнение, ше трябва ли да го избягваме (питам за изпита) или да използваме това , което ни е по-лесно и ще бъде вярно ?
Между другото – аз пробвах със
select banks.name,avg(accounts.amount)
from banks join accounts
on accounts.branch_id IN(
select id
from branches
where branches.bank_code=banks.code and branches.name like ‘%M%’
)
group by banks.name;
и ми изкарва верни резултати
Предпочитаме… да кажем даже настояваме да използвате синтаксиса с JOIN, защото той е общоприетия в практиката. И не, не е по-бавен от другия.