Dati Raccolti
Dati da google form
Un artista alla volta con punteggio per posizione
Data
Sql
SELECT 1 as position, art1 as artist, 1 as points, gen, scuola, orig, eta FROM risposte UNION ALL SELECT 2 as position, art2 as artist, 1 as points, gen, scuola, orig, eta FROM risposte UNION ALL SELECT 3 as position, art3 as artist, 1 as points, gen, scuola, orig, eta FROM risposte UNION ALL SELECT 4 as position, art4 as artist, 1 as points, gen, scuola, orig, eta FROM risposte UNION ALL SELECT 5 as position, art5 as artist, 1 as points, gen, scuola, orig, eta FROM risposte
Votanti per scuola
Data
Sql
SELECT scuola, count(1) as votanti, count(1) * 1 as max_points FROM risposte r GROUP BY scuola ORDER BY scuola
I più ascoltati in totale
Data
Sql
SELECT sum(a.points) as points, a.artist FROM all_artist a GROUP BY artist ORDER BY points desc
I più ascoltati per scuola
Data
Sql
SELECT c.school, ( SELECT count(1) + 1 FROM ( SELECT scuola as school, sum(points) as points, artist FROM all_artist GROUP BY artist, scuola ) r WHERE c.points < r.points AND r.school = c.school ) as position, c.points, c.artist FROM ( SELECT scuola as school, sum(points) as points, artist FROM all_artist GROUP BY artist, scuola ORDER BY school, points desc ) c
Ascolti relativi per scuola
Data
Sql
SELECT r.*, round(r.points * 10000 / v.max_points) / 100 as relative FROM school_rank r JOIN votanti v ON v.scuola = r.school ORDER BY school, points desc
Se ascolta Vasco allora ascolta gli U2
Data
Sql
SELECT scuola, art1, art2, art3, art4, art5, ('U2' in ( art1, art2, art3, art4, art5 )) as ok FROM risposte WHERE 'Vasco' in ( art1, art2, art3, art4, art5 )
Se ascolta i Beatles allora ascolta i Pink Floyd
Data
Sql
SELECT scuola, art1, art2, art3, art4, art5, ('Pink Floyd' in ( art1, art2, art3, art4, art5 )) as ok FROM risposte WHERE 'Beatles' in ( art1, art2, art3, art4, art5 )
Calcola tutte le regole con la loro affidabilità
Data
Sql
SELECT se.artist as se_ascolta, allora.artist as allora_ascolta, (SELECT count(1) FROM risposte where se.artist in (art1, art2, art3, art4, art5) ) as sup_se, ( SELECT count(1) FROM risposte where se.artist in (art1, art2, art3, art4, art5) and allora.artist in (art1, art2, art3, art4, art5) ) as sup_rule, round( 100 * ( SELECT count(1) FROM risposte where se.artist in (art1, art2, art3, art4, art5) and allora.artist in (art1, art2, art3, art4, art5) ) / ( SELECT count(1) FROM risposte where se.artist in (art1, art2, art3, art4, art5) ) ) / 100 as confidenza FROM global_rank se CROSS JOIN global_rank allora WHERE se.artist <> allora.artist ORDER BY confidenza desc
Informazioni cronologiche,Il tuo gruppo musicale o cantante preferito è:,Il tuo secondo gruppo musicale o cantante è:,Il terzo gruppo / cantante ?,il 4°,e infine il 5°,Frequenti la scuola ...,La tua età:,Sei ....,Sei di origini .... 02/08/2017 11.26.55,Folkabbestia,Fiorella Mannoia,Persiana Jones,Beethoven,MCR,O. Belluzzi,23 o +,M,Italia 02/08/2017 11.26.06,Beatles,Fiorella Mannoia,Branduardi,De Andrè,Banda Osiris,O. Belluzzi,22,M,Estero 02/08/2017 11.27.04,Beatles,Persiana Jones,Fiorella Mannoia,Beethoven,MCR,O. Belluzzi,23 o +,M,Italia 02/08/2017 11.267.50,Giorgia,Pink Floyd,Persiana Jones,De Andrè,Beethoven,O. Belluzzi,22,M,Estero 02/08/2017 11.28.12,Pink Floyd,U2,Beatles,Beethoven,MCR,O. Belluzzi,23 o +,M,Italia 02/08/2017 11.29.34,Beatles,Pink Floyd,Beethoven,MCR,De Andrè,O. Belluzzi,22,M,Estero 02/08/2017 11.30.31,Pink Floyd,Beatles,MCR,Persiana Jones,Fiorella Mannoia,O. Belluzzi,23 o +,M,Italia 02/08/2017 11.31.23,U2,Vasco,Beatles,Beethoven,Banda Osiris,O. Belluzzi,22,M,Estero 02/08/2017 11.34.56,U2,Vasco,Fiorella Mannoia,Beatles,Banda Osiris,O. Belluzzi,23 o +,F,Italia 02/08/2017 11.36.45,Pink Floyd,U2,Persiana Jones,Beethoven,Beatles,O. Belluzzi,22,M,Estero 02/08/2017 11.36.55,De Andrè,MCR,Fiorella Mannoia,Beatles,Pink Floyd,O. Belluzzi,23 o +,M,Italia 02/08/2017 11.37.04,De Andrè,Persiana Jones,Beatles,Beethoven,U2,O. Belluzzi,22,M,Estero 02/08/2017 11.37.12,MCR,Beatles,Beethoven,Banda Osiris,U2,O. Belluzzi,23 o +,M,Italia 02/08/2017 11.37.20,Beatles,Persiana Jones,Banda Osiris,Fiorella Mannoia,U2,O. Belluzzi,22,F,Estero 02/08/2017 11.37.28,Beatles,Pink Floyd,Persiana Jones,U2,Vasco,O. Belluzzi,23 o +,M,Italia 02/08/2017 11.37.36,Beatles,U2,Pink Floyd,Fiorella Mannoia,Vasco,O. Belluzzi,22,M,Estero 02/08/2017 11.37.44,MCR,Beatles,U2,Persiana Jones,Pink Floyd,Salvemini,23 o +,M,Italia 02/08/2017 11.37.52,De Andrè,MCR,Beatles,U2,Pink Floyd,Salvemini,22,M,Estero 02/08/2017 11.37.59,U2,De Andrè,MCR,Beatles,Pink Floyd,Salvemini,23 o +,F,Italia 02/08/2017 11.38.08,U2,Vasco,De Andrè,Pink Floyd,Beatles,Salvemini,22,F,Estero 02/08/2017 11.39.16,U2,Ligabue,MCR,Beatles,De Andrè,Salvemini,23 o +,F,Italia 02/08/2017 11.42.48,Beatles,Ligabue,MCR,De Andrè,Beethoven,Salvemini,22,M,Estero