C, PHP, VB, .NET

Дневникът на Филип Петров


* Групиране на данни

Публикувано на 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)

 



23 коментара


  1. Първо се изчисляват групите на първата таблица и към към тях се прави декартово произведение с резултата от JOIN на втората таблица.

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

  3. Аз си го обяснявам някакси, че се изпулнява парво частта която е след WHERE след това частта която е след ON и след това групирането и залепването.

  4. т.е. парво се изпълнява частта която е след WHERE и след това частта която е след ON (условието за JOIN)?

  5. Защо в пример 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;" ?

  6. В пример 4 вадим данни от таблици banks и accounts (FROM banks LEFT JOIN accounts...), а искаме да ограничим банките по условието "да имат буква M в името на банков КЛОН (т.е. от таблица branches). Тъй като branches не участва в условието FROM, то директно условие "WHERE branches.name LIKE '%M%'" само по себе си не би направило нищо. Трябва преди това да се укаже връзката към банките (таблица banks) и чак тогава да се прави ограничението.

    В пример 6 таблица employees участва в условието FROM и съответно можем спокойно да използваме ограничение по нея.

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

    WHERE banks.code IN(
    SELECT bank_code
    FROM branches
    WHERE name LIKE '%M%'

    а в пример 6 можем директно да напишем ' WHERE employees.name LIKE 'M%' ' без да тръгваме от banks надолу към employees ?

  8. Възможно ли е да се направи агрегатна функция SUM на няколко полета от еднакъв числен тип и по какъв начин?

  9. В пример 4 пиша заявката точно както е показана, но в резултата за Societe General средната сума не ми я изкарва същата.Тъй като Societe General има два клона по някакъв начин избира сметката само в един клон, а в другите не ги изчислява.

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

  11. В пример 4 заявката, която е дадена не съответства на резултата, даден под нея.

  12. В точка 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;

    Каква е разликата? Кое е най-добре да използваме?

  13. Dimitar - стар и нов стандарт за писане на SQL код. Твоя пример е по стария, с JOIN е по "новия" стандарт (който всъщност е на 20+ години - SQL92). Прието е свързването на таблици да става във FROM, а ограничаващите условия да стават в WHERE.

  14. В пример 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

  15. Относно "Филип Петров казва:
    04 март 2014 в 17:33
    Dimitar – стар и нов стандарт за писане на SQL код. Твоя пример е по стария, с JOIN е по „новия“ стандарт (който всъщност е на 20+ години – SQL92). Прието е свързването на таблици да става във FROM, а ограничаващите условия да стават в WHERE.
    "

    Понеже прочетох в по-горните статии че наслагването на JOIN води до бавно изпълнение, ше трябва ли да го избягваме (питам за изпита) или да използваме това , което ни е по-лесно и ще бъде вярно ?

  16. Между другото - аз пробвах със

    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;
    и ми изкарва верни резултати

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

Добави коментар

Адресът на електронната поща няма да се публикува


*