この Lab では1~2人のクラスメートと協力することは問題ありませんが、そのグループのすべての受講生が等しく実習に貢献することが求められます。
曲のデータベースに関する質問に答えるSQLクエリを記述します。
始め方
VS Codeを開きます。
ターミナルウィンドウ内をクリックすることから始めて、それからcdを実行します。
その後プロンプトは次のようになっていることがわかります。
$ターミナルウィンドウの内側をクリックし、次のように入力します。
wget https://cdn.cs50.net/2022/fall/labs/7/songs.zipその後にEnterを押すと、songs.zipというZIPがあなたのCodespaceにダウンロードされます。wgetと次のURLの間にあるスペースや、その他の文字を見落とさないように注意してください。
次に
unzip songs.zipを実行して、songsというフォルダを作成します。
ZIPファイルは不要になったため、
rm songs.zipを実行し、プロンプトで “y “に続いてEnterで応答すると、ダウンロードしたZIPファイルが削除されます。
次に
cd songsの後にEnterを押して、そのディレクトリに移動する（つまり、開く）。これでプロンプトは以下のようになります。
songs/ $すべて成功した場合は、次のように実行します。
ls8つの.sqlファイル、songs.db、answer.txtが表示されるはずです。
問題が発生した場合は、同じ手順をもう一度実行して、どこが間違っていたかを判断できるかどうかを確認してください。
理解を深める
提供されるのはsongs.dbというファイルで、Spotifyからの曲やアーティストに関するデータを保存するSQLiteデータベースです。このデータセットには、2018年にSpotifyでストリーミングされた上位100曲が含まれています。ターミナルウィンドウでsqlite3 songs.dbを実行し、データベースでクエリの実行を開始できるようにします。
まず、sqlite3がクエリの入力を求めるプロンプトを表示したら、.schemaと入力してEnterキーを押します。これにより、データベース内の各テーブルの生成に使用されたCREATETABLEステートメントが出力されます。
すべてのartistにはidとnameがあります。また、すべての曲には、name、artist_id (曲のアーティストのidに対応) 、ダンス、エネルギー、キー、ラウドネス、スピーチ (トラック内の話し言葉の有無) 、バレンス、テンポ、曲の長さ (ミリ秒単位) などの値があります。
これらのテーブルからデータを選択し、さまざまな質問に答える SQL クエリを作成することが、今後の課題です。その後、Spotifyが毎年行っているSpotify Wrappedキャンペーンで、この同じデータを使ってリスナーの習慣を特徴づける方法を考えてみましょう。
実装の詳細
次の各問題について、各問題で指定された結果を出力する単一のSQL問合せを作成する必要があります。回答は単一のSQL問合せの形式である必要がありますが、クエリ内に他のクエリをネストすることもできます。特定の曲やアーティストのidについて何も仮定しないでください。特定の曲またはアーティストのidが異なっていても、クエリは正確である必要があります。最後に、各クエリは質問に答えるために必要なデータのみを返す必要があります。例えば、問題が曲の名前の出力のみを要求する場合、各曲のテンポなどは出力しないでください。
1.sqlで、データベース内のすべての曲の名前をリストするSQLクエリを記述します。- クエリを実行すると、各曲の名前の列が1つあるテーブルが出力されます。
2.sqlで、すべての曲の名前をテンポの昇順でリストするSQLクエリを記述します。- クエリを実行すると、各曲の名前の列が1つあるテーブルが出力されます。
3.sqlで、最も長い曲の上位5曲の名前を長さの降順でリストするSQLクエリを記述します。- クエリを実行すると、各曲の名前の列が1つあるテーブルが出力されます。
4.sqlで、ダンサビリティ、エネルギー、バレンスが0.75を超える曲の名前をリストするSQLクエリを記述します。- クエリを実行すると、各曲の名前の列が1つあるテーブルが出力されます。
5.sqlで、すべての曲の平均エネルギーを返すSQLクエリを記述します。- クエリは、平均エネルギーを含む単一の列と単一の行を持つテーブルを出力します。
6.sqlで、Post Maloneの曲の名前をリストするSQLクエリを記述します。- クエリを実行すると、各曲の名前の列が1つあるテーブルが出力されます。
- Post Maloneの
artist_idが何であるかについては、仮定を置くべきではありません。
7.sqlで、Drakeの曲の平均エネルギーを返すSQLクエリを記述します。- クエリは、平均エネルギーを含む単一の列と単一の行を持つテーブルを出力します。
- Drakeの
artist_idが何であるかについては、仮定を置くべきではありません。
8.sqlで、他のアーティストをフィーチャーした曲の名前をリストするSQLクエリを記述します。- 他のアーティストをフィーチャーした曲には 「feat」が曲の名前の中に含まれます。
- クエリを実行すると、各曲の名前の列が1つあるテーブルが出力されます。
ウォークスルー
このビデオは、コースがまだコードを書くためにCS50 IDEを使用していたときに記録されたものです。インターフェイスはあなたのCodespaceと異なるように見えますが、2つの環境の動作はほぼ同じであるはずです
ヒント
- 役立つSQL構文については、このSQLキーワードのリファレンスを参照してください。
解決方法がわかりませんか？
Spotify Wrapped
Spotify Wrappedは、Spotifyユーザーが過去1年間に最も再生した100曲を提示する機能です。2021年、Spotify Wrappedは各ユーザーの「オーディオオーラ」を算出しました。”その年のトップソングとアーティストによって決定される、（その人の）2つの最も顕著なムードを読み取る “というものです。Spotifyが、その人の過去1年間のトップ100曲の平均エネルギー、価値、踊りやすさを調べて、オーディオオーラを決定したとします。answers.txtで、以下の質問について考えてみてください。
songs.dbに2018年のあるリスナーの上位100曲が含まれているとしたら、あなたはその人のオーディオ・オーラをどのように特徴付けますか？
このオーラを計算した方法が、なぜリスナーをあまり代表していないのか、仮説を立ててみてください。また、より良いオーラの算出方法を提案してください。
.sqlファイルと一緒にanswer.txtも必ず提出してください。
テスト
check50を使用して以下を実行し、コードの正確性を評価します。
check50 cs50/labs/2023/x/songs提出方法
ターミナルで、以下のコマンドを実行して提出してください。
submit50 cs50/labs/2023/x/songs謝辞
Kaggleのデータセット。