* # *DB FINAL PROJECT*
## Time schedule:
## datasets
https://www.kaggle.com/nadintamer/datasets //3 years
https://www.kaggle.com/leonardopena/top-spotify-songs-from-20102019-by-year //2010-2019
https://www.kaggle.com/leonardopena/top-50-spotify-songs-by-each-country //top 50 by country
https://www.kaggle.com/pieca111/music-artists-popularity // singer to country
https://www.kaggle.com/theworldbank/world-bank-gdp-ranking // country's GDP
https://www.kaggle.com/ritesh2000/spotify-top-50-most-streamed-songs // top 1 song attributes
https://www.kaggle.com/imdevskp/corona-virus-report // country to covid-19
## 5/27
#### top2017>>>top17
#### top2018>>>top18
#### top2019>>>top19
#### top2010~2019>>>top10_19

#### top 50 and its country>>>country
#### gdp with ppp>>>gdp_ppp
millions_of_Ranking_Economy_international_dollars

africa ZAF 南非
argentina ARG 阿根廷
australia AUS 澳洲
belgium BEL 比利時
bolivia BOL 玻利維亞
brazil BRA 巴西
canada CAN 加拿大
germany DEU 德國
colombia COL 哥倫比亞
chile CHL 智利
spain ESP 西班牙
usa USA 美國
france FRA 法國
india IND 印度
indonesia IDN 印尼
israel ISR 以色列
italy ITA 義大利
japan JPN 日本
malasya MYS 馬來西亞
#### top 1 song attributes>>>attributes
average_streams(Millions)

# 5/30
SQL

隨機產生top10

Weight: 前35%>>強 35~70%>>中 後70%>>弱

top50的特徵

音樂類別的推薦

該國家的top

# 6/2
country和top10_19的
### 除100
dnce(danceability)
nrgy(energy)
live(liveness)
val(valence)
acous(acousticness)
spch(speechiness)
### *1000
dur(duration_ms)
### 不用變
bpm(tempo)
dB(loudness)

ex:
SELECT title as name,artists,dnce/100 as danceability,nrgy/100 as energy,dB as loudness,spch/100 as speechiness,acous/100 as acousticness,live/100 as liveness,val/100 as valence,bpm as tempo,dur*1000 as duration_ms FROM top10_19
### ------------------------------
各項平均
SELECT AVG(danceability),AVG(energy),AVG(loudness),AVG(speechiness),AVG(acousticness),AVG(liveness),AVG(valence),AVG(tempo),AVG(duration_ms)
FROM (SELECT name,artists,danceability,energy,loudness,speechiness,acousticness,liveness,valence,tempo,duration_ms FROM top17
UNION DISTINCT
SELECT name,artists,danceability,energy,loudness,speechiness,acousticness,liveness,valence,tempo,duration_ms FROM top18
UNION DISTINCT
SELECT name,artists,danceability,energy,loudness,speechiness,acousticness,liveness,valence,tempo,duration_ms FROM top19
UNION DISTINCT
SELECT title as name,artists,dnce/100 as danceability,nrgy/100 as energy,dB as loudness,spch/100 as speechiness,acous/100 as acousticness,live/100 as liveness,val/100 as valence,bpm as tempo,dur*1000 as duration_ms FROM top10_19
UNION DISTINCT
SELECT title as name,artists,dnce/100 as danceability,nrgy/100 as energy,dB as loudness,spch/100 as speechiness,acous/100 as acousticness,live/100 as liveness,val/100 as valence,bpm as tempo,dur*1000 as duration_ms FROM country) S

top曲風
SELECT S.top_genre
FROM (SELECT title,top_genre FROM country UNION SELECT title,top_genre FROM top10_19) S
GROUP BY S.top_genre
ORDER BY COUNT(*) DESC
LIMIT 10

隨機10首
SELECT s.name, s.artists
FROM (SELECT name, artists FROM top17
UNION DISTINCT
SELECT name, artists FROM top18
UNION DISTINCT
SELECT name, artists FROM top19
UNION DISTINCT
SELECT title as name, artists FROM top10_19
UNION DISTINCT
SELECT title as name, artists FROM country) s
ORDER BY RAND() LIMIT 10

加到最愛

從最愛刪除