Inicio‎ > ‎SQL‎ > ‎Lliçons SQL‎ > ‎

T05 Agregació


Es poden utilitzar expressions aritmètiques tant en la clàusula select, per a obtenir una nova columna en la taula resultat, com en la construcció de condicions de selecció de files. 

 Suposant que el curs es divideix en 2 semestres i que 3 crèdits es corresponen amb 1 hora de classe setmanal, nom de les assignatures i nombre d’hores de classe setmanals de cada una en un únic semestre.

select descripcion, (creditos/3)*2 horas
from asignaturas;

descripcionhoras
DISEÑO Y GESTION DE BASES DE DATOS4.00000
FUNDAMENTOS DE LAS BASES DE DATOS4.00000
FUNDAMENTOS DE LA PROGRAMACION6.00000
HISTORIA DE LA INFORMATICA3.00000
PROGRAMACION CONCURRENTE4.00000

Els crèdits de cada assignatura són anuals: dividits entre 3, obtenim les hores a impartir durant un any lectiu cada setmana; si ho reduïm a un únic semestre, cada setmana tindrem el doble d’hores de classe.


Descripció de les assignatures i nombre d’hores setmanals de les assignatures amb menys de 4 hores setmanals de classe

select descripcion, creditos 
from asignaturas 
where (creditos/3)*2 < 4

descripcioncreditos
HISTORIA DE LA INFORMATICA4.5


Funcions


La llista completa de funcions disponibles en MySQL és millor consultar-la en el seu manual(http://dev.mysql.com/doc/refman/5.0/es/functions.html). Tinga’s en compte que cada SGBD pot proporcionar les seues funcions que coincidiran o no amb les que maneja MySQL .

Redondea 15.1297 a dos decimales

select round(15.1297,2) redondeo from dual;

redondeo
15.13

Òbviament, per a realitzar i mostrar un càlcul com aquest no cal cap taula. Dual és una taula virtual que s’utilitza en aquests casos per a mostrar dades no emmagatzemats ni calculats a partir de cap taula. En realitat, es manté per compatibilitat amb altres SGBD (Oracle) ja que també es pot executar select round(15.1297,2) amb el mateix resultat.



Funcions d'agregats

Es disposa d’una sèrie de funcions d’agregats que retornen valors calculats sobre una determinada columna o columnes.

La diferència amb les mostrades en la secció anterior és que aquestes funcions treballen sobre conjunts de valors: tornen un únic valor resultat de processar diverses tuplas seleccionades mitjançant la condició de la clàusula where; si no s’especifica aquesta, el càlcul es realitza sobre la totalitat de la columna.

COUNT( * )número de filas
COUNT( DISTINCT expr )número de valores distintos en expr
SUM( [DISTINCT] expr )suma de todos los valores en expr
AVG( [DISTINCT] expr )promedio de todos los valores en expr
MINexpr )el más pequeño de todos los valores en expr
MAXexpr )el mayor de todos los valores en expr










Les funcions de tipus estadístic precisen que l’expressió que s’avalue es construïsca sobre columnes numèriques. L’expressió expr pot contenir el nom d’una columna o un càlcul sobre una o unes quantes columnes.

Si s’especifica la paraula clau distinct l'expressió obligatòriament ha de ser un nom de columna, i s’assumeix que la funció es calcula únicament sobre valors diferents de l’expressió.


Quants professors hi ha en la nostra base de dades?

select count(*) profes from profesores;

profes
3


Quantes assignatures de més de 4 crèdits tenim?

select count(*) from asignaturas
where creditos > 4
;

count(*)
5

Quants valors de crèdits distints hi ha?

select count(distinct creditos) quecreditos 
from asignaturas;

quecreditos
3

Hi ha 3 valors diferents de crèdits : 6.0, 9.0 y 4.5.

codigodescripcioncreditoscreditosp
DGBDDISEÑO Y GESTION DE BASES DE DATOS6.03.0
FBDFUNDAMENTOS DE LAS BASES DE DATOS6.01.5
FPFUNDAMENTOS DE LA PROGRAMACION9.04.5
HIHISTORIA DE LA INFORMATICA4.5 
PCPROGRAMACION CONCURRENTE6.01.5


Sobre el modificador DISTINCT

Totes les funcions d'agregats (menys count(*)) ignoren els nuls (NULL). Per tant, select count(creditos) from asignaturas tornaria la quantitat de files en la taula assignatures que no tenen un nul en creditos.

select count(*) filas, count(creditosp) valores, count(distinct creditosp) distintos from asignaturas;

filasvaloresdistintos
543

L’ús del modificador distinct implica, a més, que no es tenen en compte els valors duplicats. Suposem una taula amb cinc files i una columna colx i que volem obtenir la mitjana dels valors emmagatzemats en ella (NULL,1,1,1,3): AVG(colx) ens tornaria 1.5, mentres que AVG(distinct colx) ens tornaria 2.

Per exemple:

select avg(creditosp) sinDis, avg(distinct creditosp) conDis from asignaturas;

sinDisconDis
2.625003.00000


Group by

Recordem que l’ús de funcions d’agregats ens permet realitzar càlculs sobre la totalitat de files que compleixen una determinada condició (o sobre totes les files de la taula)

Quantitat de crèdits que s’imparteixen

select sum(creditos) credsImp
from imparte i, asignaturas a
where i.asignatura = a.codigo

credsImp
18.0

No obstant, és molt habitual la necessitat de realitzar aquest càlcul no de forma global sinó particularitzant per algun criteri.
Quantitat de crèdits que imparteix cada professor.

select sum(creditos) credsImp
from imparte i, asignaturas a
where i.asignatura = a.codigo
group by dni

credsImp
12.0
6.0

En realitat, s’entendrà millor si introduïm més informació en l’eixida

select dni, sum(creditos) credsImp
from imparte i, asignaturas a
where i.asignatura = a.codigo
group by dni

dni credsImp
21111222 12.0
21333444 6.0


El criteri definit en el group by(dni), estableix els distints grups de files sobre els quals es realitzarà la suma. Diguem que el sistema busca cada un dels valors diferents de dni dins de la taula imparte i que, per a totes les files que comparteixen cada un d’aquests valors fa una suma de creditos.

A partir d’ací ja podem afegir qualsevol informació que considerem adequada.

 

Nom dels professors i crèdits que imparteixen.

select nombre, sum(creditos) credsPorProf
from imparte i, asignaturas a, profesores p
where i.asignatura = a.codigo
and p.dni = i.dni
group by p.dni

SQL Error: Column 'ejemplo.p.nombre' isn't in GROUP BY

L’error en la interpretació de l’orde anterior ve donat per que és obligatori posar en el group by totes les columnes no calculades que vagen a eixir en el resultat.

Al contrari, no cal que totes les columnes especificades en el group by apareguen en l’eixida.


select nombre, sum(creditos) credsPorProf
from imparte i, asignaturas a, profesores p
where i.asignatura = a.codigo
and p.dni = i.dni
group by p.dni, nombre

nombre credsPorProf
EVA GOMEZ 12.0
RAFAEL ROMERO 6.0

Encara que amb el "nombre" bastava, hem posat també el "DNI" per prevenció, podria donar-se el cas de tenir 2 professors distints que es digueren igual.

Where i group by

Quan s’utilitza la clàusula where, a banda d’enllaçar taules per columnes comunes, com a PROFESORES i IMPARTE per profesores.dni i imparte.dni respectivament, es pot utilitzar per a eliminar certes files del càlcul.

Així, si volem calcular quantes assignatures imparteix cada professor sense comptar FBD, escriurem la següent sentència.:


select nombre, count(*)
from profesores p, imparte i
where p.dni = i.dni
    and asignatura != 'FBD'
group by p.dni, nombre;

nombre count(*)
EVA GOMEZ 1
RAFAEL ROMERO 1

 
Per a entendre’ns, "primer" s’executa el select-from-where sense càlculs i "després", sobre les files resultants, es realitzen els càlculs segons el criteri d’agrupació del group by.

Atributs no clau

Hem estat introduint la clau primària de profesores en els group by per a prevenir càlculs, en principi, erronis. Els únics atributs que asseguren la identificació d’una tupla respecte de les altres són els que formen la clau primària (en general, clau candidata).

Així, si la clau primària d’una taula de persones (profesores o alumnos, per exemple) és el D.N.I., assumim que no hi haurà duplicats en aquest atribut. No obstant això, el nom no serà clau i, per tant, admet duplicats. És a dir, és perfectament possible trobar dues persones distintes que s’anomenen igual.

Nom del coordinador i quantes assignatures coordina.

select nombre, count(*) coordina
from asignaturas a, coordinadores c
where c.asig = a.codigo
group by nombre;

ombre coordina
AGAPITO CIFUENTES 2
ROMUALDO GOMEZ 1

DNI i nom del coordinador i quantes assignatures coordina.

select dni, nombre, count(*) coordina
from asignaturas a, coordinadores c
where c.asig = a.codigo
group by dni, nombre;

dni nombre coordina
10111222 AGAPITO CIFUENTES 1
55777666 AGAPITO CIFUENTES 1
66555444 ROMUALDO GOMEZ 1

Pareix clar que la primera solució no és correcta, més si cap tenint en compte el disseny de la taula i el significat dels seus dades (un coordinador no pot coordinar més d’una assignatura).



Ordenació de l'eixida

Es pot ordenar l’eixida utilitzant les columnes calculades.

Totes aquestes ordes obtenen la mateixa eixida:

  • utilitzant la pròpia expressió  
select nombre, count( * ) asignaturas
from profesores p, imparte i
where p.dni=i.dni
group by p.dni, nombre
order by count( * );

  • utilizant l'etiqueta de columna
select nombre, count( * ) asignaturas
from profesores p, imparte i
where p.dni=i.dni
group by p.dni, nombre
order by asignaturas;

  • utilizant l'orde de la columna
select nombre, count( * ) asignaturas
from profesores p, imparte i
where p.dni=i.dni
group by p.dni, nombre
order by 2;



nombre asignaturas
RAFAEL ROMERO 1
EVA GOMEZ 2