Top 10 Showclosers van concerten

Door Eelco Klomp op 26-08-2014 20:32

1.410 views

Ik heb een website www.bosstime.nl gemaakt in de database voornamelijk data over concerten en setlijsten van Bruce Springsteen concerten, waar leuke statistieken en overzichten van gemaakt kan worden. Nu wil ik de statistieken uitbreiden met een top 10 van nummers waar de show mee geopend wordt en waar de show mee gesloten wordt. De showopener top 10 is geen probleem, die query is me vrij eenvoudig gelukt, maar de showclosers is lastiger. Ik heb een gevoel dat ik iets over het hoofd zie maar zie niet wat.

Ik heb o.a. de volgende tabellen:

bt_songs: de nummers (id, titel, songtekst, releasedatum etc)
bt_live: de concerten (id, datumconcert, plaats etc)
bt_concertsongs: de setlijst -> koppeling tussen het concert en de gespeelde nummers (id, concertid, volgorde, songid)

Het veld 'volgorde' geeft aan het hoeveelste nummer het van de setlijst is. Nummer 1 is het eerste nummer van dat concert, het laatste nummer van een concert varieert. De ene keer worden er 26 nummers gespeeld, de andere keer 34.

Voorbeeld van een een stukje van de bt_concertsongs (id, concertid, volgorde, songid):
1 12 1 41
2 12 2 267
3 12 3 206
4 12 4 30
5 12 5 8
6 12 6 45
7 12 7 271
8 12 8 33
9 12 9 34
10 12 10 67
11 12 11 80
12 12 12 269
13 12 13 216
14 12 14 70
15 12 15 20
16 12 16 218
17 12 17 275
18 12 18 276
19 12 19 25
20 12 20 272
21 12 21 279
22 12 22 174
23 12 23 280
24 12 24 21
25 12 25 72
26 12 26 75
27 12 27 263

Showopeners:

SELECT bt_songs.titel, songid, count(songid) FROM `bt_concertsongs` Inner Join bt_songs On bt_songs.id = bt_concertsongs.songid WHERE `volgorde` = 1 GROUP BY songid Order By count(songid) DESC LIMIT 0 , 10

Ik wil dus een top 10 maken vanuit deze tabel waar per concert de waarde van 'volgorde' het hoogste getal is bij dat concertid.

Ik weet even niet hoe ik dit zou moeten doen. Heeft iemand suggesties?

Peter K

26-08-2014 20:37

Volgens mij moet je de volgorde op '27' zetten? Dat is het laatste nummer?

Eelco Klomp

26-08-2014 20:42

Ik heb meer dan 2000 setlijsten in de database en de lengte van elke show is anders. In die ene setlijst is 27 inderdaad het laatste nummer, bij andere shows niet....

Ger van Steenderen

26-08-2014 20:53 gewijzigd op 26-08-2014 20:57


SELECT
	song_id,
	s.song_title,
	COUNT(*) aantal
FROM
	(SELECT
		concert_id,
		MAX(volgorde) volgorde
	FROM
		bt_concertsongs
	GROUP_BY
		concert_id
	) t
JOIN
	bt_concertsongs 
	USING (concert_id, volgorde)
JOIN
	bt_songs s
	USING (song_id)
GROUP BY
	song_id, s.song_title
ORDER BY
	aantal DESC LIMIT 10

Edit:
Oops, group by vergeten in de subquery

Eelco Klomp

27-08-2014 20:28 gewijzigd op 27-08-2014 20:32

Ger bedankt voor je hulp alleen kom ik er nog niet uit. Ik heb PHP / MySQL mezelf wat aangeleerd, zodra de query wat complexer wordt gaat he me soms (nog) wat te ver...

Hier nog mijn script voor het eerste nummer per concert:

SELECT bt_songs.titel, songid, count(songid) 
FROM `bt_concertsongs` Inner Join bt_songs On bt_songs.id = bt_concertsongs.songid 
WHERE `volgorde` = 1 
GROUP BY songid Order By count(songid) 
DESC LIMIT 0 , 10

Als ik het goed begrijp probeer je via een soort "subquery" eerst alle nummers te achterhalen per "concertid" die de hoogste waarde voor "volgorde" hebben?

In je script had ik concert_id al gewijzigd naar concertid en song_id naar songid.

Ger van Steenderen

27-08-2014 20:53

>> Als ik het goed begrijp probeer je via een soort "subquery" eerst alle nummers te achterhalen per "concertid" die de hoogste waarde voor "volgorde" hebben?

Zo ongeveer, in de subquery wordt bepaalt wat het hoogste volgnummer per concert is.
Daarna join ik nogmaals dezelfde tabel op concertid én volgnummer om te achterhalen welke song dat is.
Daarna join ik songs om de title etc. te achterhalen.

Echter ik gebruik USING (song_id), maar dat kan alleen als de kolommen in beide tabellen dezelfde naam hebben, wat volgens mij bij jou niet het geval is.
Dit moet je dan wijzigingen in ON .... = ....

Eelco Klomp

28-08-2014 14:34

Ger, heel erg bedankt! Het is gelukt! Weer een mooi leermoment voor mezelf!

Reageren

Inloggen om te reageren