---
# System prepended metadata

title: '*DB FINAL PROJECT*'

---

* # *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
![](https://i.imgur.com/pyluvm1.png)
#### top2017>>>top17
![](https://i.imgur.com/uhVlFKK.png)
![](https://i.imgur.com/aU3fnDN.png)
#### top2018>>>top18
![](https://i.imgur.com/iLbjU1U.png)
![](https://i.imgur.com/CcqLGa6.png)
#### top2019>>>top19
![](https://i.imgur.com/BUdzoVg.png)
![](https://i.imgur.com/nVpycrM.png)
#### top2010~2019>>>top10_19
![](https://i.imgur.com/f65UNtR.png)
![](https://i.imgur.com/TC2uMBX.png)

#### top 50 and its country>>>country
![](https://i.imgur.com/KJ9hhJ8.png)
![](https://i.imgur.com/ioskaAR.png)
#### gdp with ppp>>>gdp_ppp
![](https://i.imgur.com/E3mFhT7.png)
![](https://i.imgur.com/guQxYaI.png)
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
![](https://i.imgur.com/HKKeOGv.png)
![](https://i.imgur.com/atQcseC.png)
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

加到最愛

從最愛刪除


















