今年配信のアルバム数
select count(*) from (
select al.id as id, ap.name as name
from apple_music_albums as ap inner join albums as al on ap.album_id = al.id
where al.status = 1 and ap.release_date >= '2022-01-01' and ap.release_date <= '2022-12-31'
) t1
今年の人気アルバム
-- 1. 以下を実行する
SET @rownum=0;
-- 2. 次に以下を実行する
select concat(rank, "位 ", ttt1.name, "
https://music.apple.com/jp/album/", ttt1.id, "?at=1001lxy6") from (
select @rownum:=@rownum+1 as rank, tt2.apple_music_id as id, tt2.name from
(
select t1.id as id, t1.popularity + t2.popularity as popularity from (
select REPLACE(page_location, '/albums/', '') as id, count(*) * 100 as popularity
from page_view_logs
where page_location like '/albums/%'
group by page_location
having count(*) > 5
order by count(*) desc
) t1 inner join (
select favorable_id as id, count(*) * 1000 as popularity from favorites
where favorable_type = 'Album' and created_at >= '2023-01-01' and created_at <= '2023-06-30'
group by favorable_id
) t2 on t1.id = t2.id
) tt1 inner join (
select al.id as id, ap.name as name, ap.apple_music_id as apple_music_id
from apple_music_albums as ap inner join albums as al on ap.album_id = al.id
where al.status = 1 and ap.release_date >= '2023-01-01' and ap.release_date <= '2023-06-30'
) tt2 on tt1.id = tt2.id
) ttt1
order by ttt1.rank desc;;
select t1.id as id, t1.popularity + t2.popularity as popularity from (
select REPLACE(page_location, '/albums/', '') as id, count(*) * 100 as popularity
from page_view_logs
where page_location like '/albums/%'
group by page_location
having count(*) > 5
order by count(*) desc
) t1 inner join (
select favorable_id as id, count(*) * 1000 as popularity from favorites
where favorable_type = 'Album' and created_at >= '2023-01-01' and created_at <= '2023-06-31'
group by favorable_id
) t2 on t1.id = t2.id
;;;
select @rownum:=@rownum+1 as rank, tt2.apple_music_id as id, tt2.name from
(
select t1.id as id, t1.popularity + t2.popularity as popularity from (
select REPLACE(page_location, '/albums/', '') as id, count(*) * 100 as popularity
from page_view_logs
where page_location like '/albums/%'
group by page_location
having count(*) > 5
order by count(*) desc
) t1 inner join (
select favorable_id as id, count(*) * 1000 as popularity from favorites
where favorable_type = 'Album' and created_at >= '2023-01-01' and created_at <= '2023-06-31'
group by favorable_id
) t2 on t1.id = t2.id
) tt1 inner join (
select al.id as id, ap.name as name, ap.apple_music_id as apple_music_id
from apple_music_albums as ap inner join albums as al on ap.album_id = al.id
where al.status = 1 and ap.release_date >= '2023-01-01' and ap.release_date <= '2023-06-31'
) tt2 on tt1.id = tt2.id
今年の人気トラック
select concat("位 ", ttt1.name, "
https://music.apple.com/jp/album/", ttt1.album_apple_music_id, "?i=", ttt1.track_apple_music_id, "&at=1001lxy6") from (
select tt2.album_apple_music_id, tt1.popularity + tt3.popularity as popularity, tt4.name, tt4.track_apple_music_id from (
select favorable_id as id, count(*) * 100 as popularity from favorites
where favorable_type = 'Track' and created_at >= '2022-01-01' and created_at <= '2022-12-31'
group by favorable_id
) tt1 inner join (
select track_id as id, album_apple_music_id from album_has_tracks at inner join
(
select al.id as id, ap.apple_music_id as album_apple_music_id
from apple_music_albums as ap inner join albums as al on ap.album_id = al.id
where al.status = 1 and ap.release_date >= '2022-01-01' and ap.release_date <= '2022-12-31'
) t1
on at.album_id = t1.id
) tt2 inner join (
select REPLACE(page_location, '/tracks/', '') as id, count(*) * 10 as popularity
from page_view_logs
where page_location like '/tracks/%'
group by page_location
having count(*) > 1
order by count(*) desc
) tt3 inner join (
select track_id as id, name, apple_music_id as track_apple_music_id from apple_music_tracks
) tt4
on tt1.id = tt2.id and tt1.id = tt3.id and tt1.id = tt4.id
) ttt1
order by ttt1.popularity asc