Home-theater-designers
Gran part del poder de les bases de dades relacionals prové de filtrar dades i unir taules. Per això representem aquestes relacions en primer lloc. Però els sistemes de bases de dades moderns proporcionen una altra tècnica valuosa: l’agrupament.
L’agrupació permet extreure informació resum d’una base de dades. Us permet combinar resultats per crear dades estadístiques útils. L’agrupació no us permet escriure codi per a casos habituals, com ara fer una mitjana de llistes de xifres. I pot proporcionar sistemes més eficients.
Què fa la clàusula GROUP BY?
GRUPAR PER, com el seu nom indica, agrupa els resultats en un conjunt més petit. Els resultats consisteixen en una fila per a cada valor diferent de la columna agrupada. Podem mostrar-ne l’ús mirant algunes dades de mostra amb files que comparteixen alguns valors comuns.
sincronitzar els contactes de l'iPhone a l'Android
La següent és una base de dades molt senzilla amb dues taules que representen àlbums de discs. Podeu configurar aquesta base de dades mitjançant escrivint un esquema bàsic per al sistema de base de dades escollit. El àlbums La taula té nou files amb una clau principal identificador columna i columnes per al nom, l'artista, l'any de llançament i les vendes:
+----+---------------------------+-----------+--------------+-------+
| id | name | artist_id | release_year | sales |
+----+---------------------------+-----------+--------------+-------+
| 1 | Abbey Road | 1 | 1969 | 14 |
| 2 | The Dark Side of the Moon | 2 | 1973 | 24 |
| 3 | Rumours | 3 | 1977 | 28 |
| 4 | Nevermind | 4 | 1991 | 17 |
| 5 | Animals | 2 | 1977 | 6 |
| 6 | Goodbye Yellow Brick Road | 5 | 1973 | 8 |
| 7 | 21 | 6 | 2011 | 25 |
| 8 | 25 | 6 | 2015 | 22 |
| 9 | Bat Out of Hell | 7 | 1977 | 28 |
+----+---------------------------+-----------+--------------+-------+El artistes taula és encara més senzilla. Té set files amb columnes d'identificador i de nom:
+----+---------------+
| id | name |
+----+---------------+
| 1 | The Beatles |
| 2 | Pink Floyd |
| 3 | Fleetwood Mac |
| 4 | Nirvana |
| 5 | Elton John |
| 6 | Adele |
| 7 | Meat Loaf |
+----+---------------+Podeu entendre diversos aspectes de GROUP BY amb només un conjunt de dades senzill com aquest. Per descomptat, un conjunt de dades de la vida real tindria moltes, moltes més files, però els principis continuen sent els mateixos.
Agrupació per una sola columna
Diguem que volem esbrinar quants àlbums tenim per a cada artista. Comenceu amb un típic SELECCIONA consulta per obtenir la columna artist_id:
SELECT artist_id FROM albumsAixò retorna les nou files, com s'esperava:
+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 2 |
| 5 |
| 6 |
| 6 |
| 7 |
+-----------+Afegiu la frase per agrupar aquests resultats per artista GRUP PER ID_artista :
SELECT artist_id FROM albums GROUP BY artist_idEl que dóna els resultats següents:
+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+-----------+Hi ha set files al conjunt de resultats, reduïdes del total de nou a àlbums taula. Cada un únic ID_artista té una sola fila. Finalment, per obtenir els recomptes reals, afegiu COMPTE (*) a les columnes seleccionades:
SELECT artist_id, COUNT(*)
FROM albums
GROUP BY artist_id
+-----------+----------+
| artist_id | COUNT(*) |
+-----------+----------+
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 2 |
| 7 | 1 |
+-----------+----------+Els resultats agrupen dos parells de files per als artistes amb identificadors 2 i 6 . Cadascun té dos àlbums a la nostra base de dades.
Relacionat: El full de trucs Essential SQL Commands per a principiants
Com accedir a dades agrupades amb una funció agregada
És possible que hàgiu utilitzat el fitxer COMPTE funció anterior, particularment a COMPTE (*) forma tal com es va veure més amunt. Obté el nombre de resultats en un conjunt. Podeu utilitzar-lo per obtenir el nombre total de registres d'una taula:
SELECT COUNT(*) FROM albums
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+COUNT és una funció agregada. Aquest terme fa referència a funcions que tradueixen valors de diverses files en un sol valor. Sovint s’utilitzen juntament amb la declaració GROUP BY.
En lloc de comptar només el nombre de files, podem aplicar una funció agregada als valors agrupats:
SELECT artist_id, SUM(sales)
FROM albums
GROUP BY artist_id
+-----------+------------+
| artist_id | SUM(sales) |
+-----------+------------+
| 1 | 14 |
| 2 | 30 |
| 3 | 28 |
| 4 | 17 |
| 5 | 8 |
| 6 | 47 |
| 7 | 28 |
+-----------+------------+Les vendes totals que es mostren més amunt per als artistes 2 i 6 són les vendes de diversos àlbums combinades:
SELECT artist_id, sales
FROM albums
WHERE artist_id IN (2, 6)
+-----------+-------+
| artist_id | sales |
+-----------+-------+
| 2 | 24 |
| 2 | 6 |
| 6 | 25 |
| 6 | 22 |
+-----------+-------+Agrupació per diverses columnes
Podeu agrupar per més d'una columna. Només cal incloure diverses columnes o expressions, separades per comes. Els resultats s’agruparan segons la combinació d’aquestes columnes.
SELECT release_year, sales, count(*)
FROM albums
GROUP BY release_year, salesNormalment es produiran més resultats que agrupar per una sola columna:
+--------------+-------+----------+
| release_year | sales | count(*) |
+--------------+-------+----------+
| 1969 | 14 | 1 |
| 1973 | 24 | 1 |
| 1977 | 28 | 2 |
| 1991 | 17 | 1 |
| 1977 | 6 | 1 |
| 1973 | 8 | 1 |
| 2011 | 25 | 1 |
| 2015 | 22 | 1 |
+--------------+-------+----------+Tingueu en compte que, en el nostre petit exemple, només dos àlbums tenen el mateix any de llançament i el nombre de vendes (28 el 1977).
Funcions agregades útils
A part de COUNT, diverses funcions funcionen bé amb GROUP. Cada funció retorna un valor basat en els registres pertanyents a cada grup de resultats.
- COUNT () retorna el nombre total de registres coincidents.
- SUM () retorna el total de tots els valors de la columna donada sumats.
- MIN () retorna el valor més petit d'una columna determinada.
- MAX () retorna el valor més gran d'una columna determinada.
- AVG () retorna la mitjana mitjana. És l’equivalent a SUM () / COUNT ().
També podeu utilitzar aquestes funcions sense una clàusula GROUP:
mou totes les aplicacions a la targeta sd
SELECT AVG(sales) FROM albums
+------------+
| AVG(sales) |
+------------+
| 19.1111 |
+------------+Ús de GROUP BY amb una clàusula WHERE
Igual que amb un SELECT normal, encara podeu utilitzar WHERE per filtrar el conjunt de resultats:
SELECT artist_id, COUNT(*)
FROM albums
WHERE release_year > 1990
GROUP BY artist_id +-----------+----------+
| artist_id | COUNT(*) |
+-----------+----------+
| 4 | 1 |
| 6 | 2 |
+-----------+----------+Ara només teniu els àlbums publicats després del 1990, agrupats per artistes. També podeu utilitzar una unió amb la clàusula WHERE, independentment de GROUP BY:
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND release_year > 1990
GROUP BY artist_id +---------+--------+
| name | albums |
+---------+--------+
| Nirvana | 1 |
| Adele | 2 |
+---------+--------+Tingueu en compte, però, que si intenteu filtrar segons una columna agregada:
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND albums > 2
GROUP BY artist_id;Rebràs un error:
ERROR 1054 (42S22): Unknown column 'albums' in 'where clause'Les columnes basades en dades agregades no estan disponibles per a la clàusula WHERE.
Utilitzant la clàusula HAVING
Llavors, com es filtra el conjunt de resultats després que s'hagi realitzat una agrupació? El TENIR la clàusula tracta aquesta necessitat:
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
GROUP BY artist_id
HAVING albums > 1;Tingueu en compte que la clàusula HAVING ve després del GROUP BY. En cas contrari, és bàsicament un simple reemplaçament del WHERE per HAVING. Els resultats són:
+------------+--------+
| name | albums |
+------------+--------+
| Pink Floyd | 2 |
| Adele | 2 |
+------------+--------+Encara podeu utilitzar una condició WHERE per filtrar els resultats abans de l'agrupació. Funcionarà juntament amb una clàusula HAVING per filtrar després de l'agrupació:
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND release_year > 1990
GROUP BY artist_id
HAVING albums > 1;Només un artista de la nostra base de dades va publicar més d’un àlbum després del 1990:
+-------+--------+
| name | albums |
+-------+--------+
| Adele | 2 |
+-------+--------+Combinació de resultats amb GROUP BY
La sentència GROUP BY és una part increïblement útil del llenguatge SQL. Pot proporcionar informació resum de dades, per exemple, per a una pàgina de contingut. És una alternativa excel·lent per obtenir grans quantitats de dades. La base de dades gestiona bé aquesta càrrega de treball addicional, ja que el seu disseny el fa òptim per al treball.
Un cop hàgiu entès l’agrupació i com unir-vos a diverses taules, podreu utilitzar la major part del poder d’una base de dades relacional.
Compartir Compartir Tweet Correu electrònic Com es poden consultar diverses taules de bases de dades alhora amb SQL JoinsObteniu informació sobre com s'utilitzen les combinacions SQL per racionalitzar les consultes, estalviar temps i fer-vos sentir com un usuari avançat de SQL.
com esborra la línia de la paraulaLlegiu a continuació Temes relacionats
- Programació
- SQL
Bobby és un entusiasta de la tecnologia que va treballar com a desenvolupador de programari durant gairebé dues dècades. És un apassionat dels jocs, treballa com a editor de ressenyes a Switch Player Magazine i està immers en tots els aspectes de la publicació en línia i el desenvolupament web.
Més de Bobby JackSubscriu-te al nostre butlletí
Uniu-vos al nostre butlletí per obtenir consells tècnics, ressenyes, llibres electrònics gratuïts i ofertes exclusives.
Feu clic aquí per subscriure-us