llenguatge sql. consultes amb agrupació de files
TRANSCRIPT
Group By
Miquel Boada25-11-2016
Desenvolupament aplicacions multiplataforma (DAM)
Mòdul 2 – Bases de dades - UF2 - llenguatges SQL: DML i DDL
Tema 2 – Introducció . Integritat referencial
Objectius1. Agrupació de registres
Coneixements previs• Instrucció select
Requisits previs• Ordinador amb MySQL server instal·lat.• Client MySQL server (MySQL Workbench per la representació gràfica).• Connexió al servidor des del client • Una base de dades creada.
Autor: Miquel Boada Pàgina 2 de 10
Desenvolupament aplicacions multiplataforma (DAM)
Mòdul 2 – Bases de dades - UF2 - llenguatges SQL: DML i DDL
Tema 2 – Introducció . Integritat referencial
SQL - Group ByObjectius....................................................................................................................................................2Coneixements previs................................................................................................................................2Requisits previs.........................................................................................................................................2Introducció ...............................................................................................................................................6Group by....................................................................................................................................................6
Funcionament......................................................................................................................................6 Pas 1. Filtratge dels registres (Where)..........................................................................................7 Pas 2. Ordenació de la informació................................................................................................7 Pas 3. Càlcul operacions................................................................................................................8 Pas 4. Projecció de les columnes..................................................................................................8
Sintaxis i exemples...................................................................................................................................9 Exemples group by / group by i where.............................................................................................9
Autor: Miquel Boada Pàgina 3 de 10
Desenvolupament aplicacions multiplataforma (DAM)
Mòdul 2 – Bases de dades - UF2 - llenguatges SQL: DML i DDL
Tema 2 – Introducció . Integritat referencial
Per a realitzar les diferents consultes es partirà del següent model ER i la seva conversió almodel físic.
Superhero Planet
Les instruccions per a crear la base de dades , les taules i les dades es detallen a continuació.Tenir en compte que la força i la intel·ligència es generen mitjançant un número aleatori i, pertant, aquests valors diferiran dels valors mostrats en la taula anterior.
Autor: Miquel Boada Pàgina 4 de 10
Desenvolupament aplicacions multiplataforma (DAM)
Mòdul 2 – Bases de dades - UF2 - llenguatges SQL: DML i DDL
Tema 2 – Introducció . Integritat referencial
create database if not exists dbheroes;use dbheroes;
create table planet ( namep varchar(20), mass decimal(6,2) not null, primary key(namep)) engine=innodb;
create table superheroes ( nameh varchar(40), planet varchar(20), intelligence decimal(4,2) not null, strong decimal(4,2) not null, primary key(nameh)) engine=innodb;
insert into planet values ("Mercury",0.06),("Venus",0.82),("Earth",1),("Mars",0.11),("Jupiter",317.8),("Saturn",95.2),("Uranus",14.6),("Neptune",17.2);
alter table superheroe add constraint fk_superhero_planet foreign key (planet) references planet(namep) on delete restrict on update cascade;
insert into superhero values("Superman","earth",round(rand()*100,2),round(rand()*100,2)),("Batman","earth",round(rand()*100,2),round(rand()*100,2)),("Spiderman","mars",round(rand()*100,2),round(rand()*100,2)),("Thor","earth",round(rand()*100,2),round(rand()*100,2)),("Hal Jordan","Venus",round(rand()*100,2),round(rand()*100,2)),("Wonder Woman","earth",round(rand()*100,2),round(rand()*100,2)),("Captain America","Venus",round(rand()*100,2),round(rand()*100,2)),("Martian Manhunter","Mars",round(rand()*100,2),round(rand()*100,2));
Autor: Miquel Boada Pàgina 5 de 10
Desenvolupament aplicacions multiplataforma (DAM)
Mòdul 2 – Bases de dades - UF2 - llenguatges SQL: DML i DDL
Tema 2 – Introducció . Integritat referencial
Introducció . Quan es volen realitzar consultes del tipus:
✔ Quina és la facturació del client X✔ Quina és la facturació annual entre els anys Y i Z ✔ Quina és la facturació annual del client X entre els anys Y i Z.✔ ….
O consultes relacionades amb les taules del nostre exemple✔ Quina és la força de cada un dels planetes✔ Quina és la intel·ligència mitjana de cada planeta✔ …....
És precís utilitzar la clausula «group by».
Group by.La funció de «group by» és agrupar tots els registres resultants d'una consulta, per un o variscriteris, per tal d'obtenir una o més dades resum (aplicació d'una funció d'agregat) a lescolumnes de la taula.
Funcionament Per a mostrar el funcionament del «group by» utilitzarem l'exemple de superherois suposantque la taula conté la següent informació:
Nameh Planet Strong Intelligence
Superman Earth 50 40
Batman Earth 40 35
Spiderman Mars 35 45
Thor Earth 40 50
Hal Jordan Venus 35 40
Wonder Woman Earth 45 35
Captain America Venus 35 45
Martian Manhunter Mars 40 20
La consulta que es vol obtenir és el total de força i d'intel·ligència acumulada en cada un delsplanetes.
Autor: Miquel Boada Pàgina 6 de 10
Desenvolupament aplicacions multiplataforma (DAM)
Mòdul 2 – Bases de dades - UF2 - llenguatges SQL: DML i DDL
Tema 2 – Introducció . Integritat referencial
Pas 1. Filtratge dels registres (Where)Si la sentència de consulta conté un filtre de registres, s'aplicarà primer aquest filtre reduintd'aquesta forma el total de files sobre la qual s'hauran de realitzar les operacions. En el casque no hi hagi cap clausula «where» especificada, s'utilitzaran tots els registres implicats en laconsulta. En el nostre cas no es vol realitzar cap filtre.
Pas 2. Ordenació de la informacióEl sistema el primer que fa és ordenar les files de la consulta pels diferents criteris que s'haginespecificat. En el nostre cas, com que es vol obtenir la força i intel·ligència acumulada de cadaun dels planetes, s'utilitzarà com a criteri d'ordenació la col·lumna «planet».
Nameh Planet Strong Intelligence
Superman Earth 50 40
Batman Earth 40 35
Thor Earth 40 50
Wonder Woman Earth 45 35
Spiderman Mars 35 45
Martian Manhunter Mars 40 20
Hal Jordan Venus 35 40
Captain America Venus 35 45
Autor: Miquel Boada Pàgina 7 de 10
Desenvolupament aplicacions multiplataforma (DAM)
Mòdul 2 – Bases de dades - UF2 - llenguatges SQL: DML i DDL
Tema 2 – Introducció . Integritat referencial
Pas 3. Càlcul operacionsAmb les files ordenades, la realització dels càlculs implicats és senzill, ja que només cal aplicarl'operació a les files que tenen el criteri d'agrupació desitjat: vindran unes a continuació de lesaltres.
Nameh Planet Strong Total strong Intelligence Total intelligence
Superman Earth 50
175
40
160
Batman Earth 40 35
Thor Earth 40 50
Wonder Woman Earth 45 35
Spiderman Mars 35
75
45
65Martian Manhunter Mars 40 20
Hal Jordan Venus 35
70
40
85Captain America Venus 35 45
Pas 4. Projecció de les columnesEl resultat d'una consulta amb group by només poden ser les columnes resultat d'una funciód'agregat o les columnes per les quals s'ha realitzat l'agrupació. Si s'analitza la informació espot observar com només aquestes columnes aporten sentit a la nostra consulta: la restad'informació és detall que es perd o s'agrupa pel funcionament propi del sistema.Per tant, la nostra consulta final només tindrà tres columnes: Planet, Total strong i Totalintelligence.
Planet Total strong Total intelligence
Earth 175 160
Mars 75 65
Venus 70 85
Autor: Miquel Boada Pàgina 8 de 10
Desenvolupament aplicacions multiplataforma (DAM)
Mòdul 2 – Bases de dades - UF2 - llenguatges SQL: DML i DDL
Tema 2 – Introducció . Integritat referencial
Sintaxis i exemplesLa clausula «group by» s'indica després de la clausula Where.
Exemples group by / group by i where
Select planet, sum(strong), sum(intelligence) from superheroesgroup by planet;
-- Quants superherois hi ha en cada planeta amb una força superior a 50 select planet, count(*) from superheroes
where strong>50group by planet;
Video: group by
create database dbarticle;use dbarticle;-- Creació de la taulaCREATE TABLE article ( code char(10) primary key, name varchar(30) not null, family varchar(20) null, color varchar(20) not null,
width decimal(4,2) not null, height decimal(4,2) not null, length decimal(4,2) not null ) engine=innodb;
insert into article values('wp10x20x30','white plastic box 10x20x30', 'plastic', 'white' , 10, 20, 30);insert into article values('wp10x20x30','white plastic box 10x20x30', 'plastic', 'white' , 10, 20, 30);insert into article values('wp20x20x30','white plastic box 10x20x30', 'plastic', 'white' , 20, 20, 30);insert into article values('bp10x20x30','black plastic box 10x20x30','plastic', 'black' , 10, 20, 30);insert into article values('bp20x20x30','black plastic box 10x20x30','plastic', 'black' , 20, 20, 30);
-- Inserció de dades amb una única instrucció insertinsert into article values
('wc10x20x30','white plastic box 10x20x30','cardboard', 'white' , 10, 20, 30), ('wc20x20x30','white plastic box 10x20x30','cardboard', 'white' , 20, 20, 30), ('bc10x20x30','black plastic box 10x20x30','cardboard', 'black' , 10, 20, 30), ('bc20x20x30','black plastic box 10x20x30','cardboard', 'black' , 20, 20, 30);
-- Exemples consultes amb group by select color, count(*) from article group by color;select color, width, count(*) from article group by color, width;select color, width*height*length AS volume, count(*) from article group by color, volume;
Autor: Miquel Boada Pàgina 9 de 10
Desenvolupament aplicacions multiplataforma (DAM)
Mòdul 2 – Bases de dades - UF2 - llenguatges SQL: DML i DDL
Tema 2 – Introducció . Integritat referencial
select color, width*height*length AS volume, family, count(*) from article group by color, volume, family;select color, width*height*length AS volume, family, count(*) from article group by family, color, volume;select color, width*height*length AS volume, family, count(*) from article group by family, color, volume;select color, width*height*length AS volume, family, count(*) from article
where width>15 group by family, color, volume;
Video: Exemple complet amb group by
Autor: Miquel Boada Pàgina 10 de 10