Trova il primo elemento in un sottogruppo

Ti è mai capitato di dover dover suddividere in sottogruppi un insieme di record e poi dover trovare il primo oppure l’ultimo elemento di questa sottolista?

Ti faccio un esempio pratico, vediamo se riesci a ricondurlo al tuo caso…

• * ci sono 10 cantanti e 10 concorsi musicali
• * ogni concorso può essere di un genere musicale differente: Rock, Pop, Jazz, Rap
• * ogni cantante partecipa a tutti i concorsi

Adesso, prima di leggere oltre, prova a pensare di scrivere una query che restituisce, per ognuna delle categorie citate, il cantante che ha ottenuto il miglior punteggio.

Proviamo a ragiornarci insieme…

Allora…ogni cantante, partecipa ad ogni concorso…quindi provando ad immaginare una tabella rappresentativa, potremmo abbozzarla in questo modo:

Notiamo subito come un semplice raggruppamento non sia sufficiente. Non possiamo ad esempio raggruppare semplicemente per Cantante, Genere musicale e recuperare la somma dei Punteggi (vedi sotto).

La tabella sopra va ulteriormente elaborata per ottenere le sole righe evidenziate. A mio avviso il quesito, seppur base, nasconde un minimo di complessità che si risolve molto agilmente con un paio di strumenti messi a disposizione da Sql Server: Common Table Expression e ROW_NUMBER.

Passiamo al codice

Come al solito, facciamo la creazione passo passo delle tabelle che ci serviranno per la demo.

create table Cantanti (
	Id int identity(1,1),
	NomeCantante nvarchar(125),
);
create table Concorsi (
	Id int identity(1, 1),
	NomeConcorso nvarchar(125),
	Categoria nvarchar(32) -- Rock, Pop, Jazz, Rap
);
create table PunteggiConcorsi (
	Id int identity(1, 1),
	IdConcorso int,
	IdCantante int,
	Punteggio tinyint -- punteggio da 1 a 100
);

Le tabelle Cantanti e Concorsi non credo necessitino di spiegazioni; la tabella PunteggiConcorsi conterrà il punteggio ottenuto da un cantante per uno specifico concorso.

Creiamo un piccolo script per generare randomicamente dei dati all’interno delle tabelle.

declare @numeroCantanti int = 10;
declare @numeroConcorsi int = 100;

insert into Cantanti
select top (@numeroCantanti)
	'Cantante ' + convert(nvarchar, row_number() over(order by o1.name))
from
	sys.objects o1, sys.objects o2

;with cte as (select top (@numeroConcorsi)
	'Concorso ' + convert(nvarchar, row_number() over(order by o1.name)) as Name,
	convert(int, (ABS(CHECKSUM(NEWID())) % 4) + 1) Idx
from
	sys.objects o1, sys.objects o2
)
insert into Concorsi
select Name,
	case idx when 1 then 'Rock'
	when 2 then 'Pop'
	when 3 then 'Jazz'
	when 4 then 'Rap'
	end
from cte

Il miglior cantante per genere musicale

Ora siamo pronti per interrogare la tabella.

select
	pcon.IdCantante, cnt.NomeCantante, con.Categoria, sum(pcon.Punteggio) as TotalePunteggioCategoria 
from
	Cantanti cnt
	join PunteggiConcorsi pcon on cnt.Id = pcon.IdCantante
	join Concorsi con on con.Id = pcon.IdConcorso
group by
	pcon.IdCantante, cnt.NomeCantante, con.Categoria

Questa prima query ci permetterà di sapere il punteggio complessivo di ogni cantante per le varie categorie dei concorsi sostenuti; abbiamo praticamente ottenuto la prima tabella del nostro esempio sopra.

Possiamo migliorare questa query inserendola in una CTE ed utilizzando il ROW_NUMBER per ottenere il primo elemento utile di ogni sottoinsieme (nel nostro caso le categorie musicali).

;with PunteggiCantantiPerCategoria as (
select
	pcon.IdCantante, cnt.NomeCantante, con.Categoria, sum(pcon.Punteggio) as TotalePunteggioCategoria 
from
	Cantanti cnt
	join PunteggiConcorsi pcon on cnt.Id = pcon.IdCantante
	join Concorsi con on con.Id = pcon.IdConcorso
group by
	pcon.IdCantante, cnt.NomeCantante, con.Categoria
), OrdinatiPerCategoria as (
select 
	*,
	row_number() over(partition by Categoria order by TotalePunteggioCategoria desc) IndiceOrdinePunteggio
from 
	PunteggiCantantiPerCategoria
)
select * 
from
	OrdinatiPerCategoria 
where
	IndiceOrdinePunteggio = 1

La colonna IndiceOrdinePunteggio enumera da 1 ad N tutte le righe, “partizionate” per categoria musicale ed ordinate per il totale del punteggio ottenuto sulla specifica categoria in ordine decrescente.

Filtrando sulla riga con IndiceOrdinePunteggio = 1, otterremo finalmente il cantante con il miglior punteggio per categoria musicale.

Il peggior cantante

Se volessimo ottenere il peggiore? Con la nostra precedente query chi ha ottenuto il punteggio più basso sarà in fondo alla lista; la domanda che sorge spontanea probabilmente è: come faccio a filtrare ora che l’indice di filtro potrebbe variare? Non mi basta più quell’uguale ad 1?

In realtà basta eccome, servirà piuttosto rivedere il modo in cui calcoliamo IndiceOrdinePunteggio; l’ordinamento nella funzione di enumerazione delle righe (ROW_NUMBER) non dovrà più essere decrescente ma crescente; quindi:

;with PunteggiCantantiPerCategoria as (
select
	pcon.IdCantante, cnt.NomeCantante, con.Categoria, sum(pcon.Punteggio) as TotalePunteggioCategoria 
from
	Cantanti cnt
	join PunteggiConcorsi pcon on cnt.Id = pcon.IdCantante
	join Concorsi con on con.Id = pcon.IdConcorso
group by
	pcon.IdCantante, cnt.NomeCantante, con.Categoria
), OrdinatiPerCategoria as (
select 
	*,
	row_number() over(partition by Categoria order by TotalePunteggioCategoria asc) IndiceOrdinePunteggio
from 
	PunteggiCantantiPerCategoria
)
select * 
from
	OrdinatiPerCategoria 
where
	IndiceOrdinePunteggio = 1

Non ho cambiato praticamente nulla rispetto alla query precedente, ho modificato il “desc” in “asc”.

Spero che l’articolo vi sia piaciuto e magari vi sia stato utile per risolvere qualche problema simile; in tal caso, fatemelo sapere nei commenti.