SQL 쿼리 연습문제 2

6. OLD IS NOT GOLD

문제 : Which decades saw the most number of official releases? List the number of official releases in every decade since 1900. Like 1970s|57210.

Print all decades and the number of official releases. Releases with different issue dates or countries are considered different releases. Print the relevant decade in a fancier format by constructing a string that looks like this: 1970s. Sort the decades in decreasing order with respect to the number of official releases and use decade (descending) as tie-breaker. Remember to exclude releases whose dates are NULL.

official release들의 발매 연도를 1900년부터 10년 단위로 끊어서 발매수를 집계하고 집계한 발매수의 내림차순으로 출력하라고 합니다.

일단 official release를 알아보려면 release_status 릴레이션을 살펴봐야 합니다.

Official, Promotion, Bootleg, Pseudo-Release라는 네가지 종류가 있는데 첫번째 Official을 갖고있는 튜플들이 Official Release를 뜻합니다.

그리고 발매 연도를 알기 위해서는 release와 release_info 테이블을 조인해줘야 했습니다. 여기서 조건을 date_year가 1900 이상인 것과 status가 Official을 나타내는 1로 조건을 걸어주면 일단 조인한 테이블에서 필요한 튜플들을 골라낼 수 있습니다.

문제 설명 말미에 NULL인 것을 제외하라고 나와있는데 where 절에서 null과 >=로 비교하는 조건은 false로 취급되기 때문에 단순히 date_year >= 1900 조건만 걸어도 date_year가 NULL인 튜플들은 걸러집니다.

우리가 일반적으로 사용하는 프로그래밍 언어에선 BOOL 값이 True, False로 두가지지만 NULL과의 비교는 이 두가지로 정의할 수가 없기 때문에 Unknown이라는 새로운 값을 정의해서 NULL VALUE와의 비교를 새로 정의해서 관리하게 됩니다. 상세한 내용을 sqlite 문서에서 찾기가 어려워서 정리하기가 힘드네요.

아무튼 위 조건까지 걸었을 때 나오는 것은 각 연도 별 공식 발매 수입니다. 이를 10년단위로 묶어줘야 하고 심지어 출력 형식도 바꿔줘야 합니다.

다행히도 select 구문에서 계산식을 사용할 수 있습니다. 10년 단위로 끊어내기 위해서는 10으로 나눈 뒤에 10을 곱하고 거기에 's'를 붙여주면 됩니다. 앞의 연산은 정수 연산이고 뒤의 연산은 문자열 붙여넣는 연산으로 ||을 사용하면 됩니다.

최종 쿼리문은 아래와 같습니다.

select (date_year/10 * 10) || 's' as decade, count(1) as cnt
from release join release_info on release.id = release_info.release
where date_year >= 1900 and release.status = 1
group by decade
order by cnt desc, decade desc

7. RELEASE PERCENTAGE

문제 : List the month and the percentage of all releases issued in the corresponding month all over the world in the past year. Display like 2020.01|5.95.

The percentage of releases for a month is the number of releases issued in that month devided by the total releases in the past year from 07/2019 to 07/2020, both included. Releases with different issue dates or countries are considered different releases. Round the percentage to two decimal places using ROUND(). Sort by dates in ascending order.

2019년 7월부터 2020년 7월까지의 발매들에 대해서 월별 발매의 퍼센테이지를 날짜의 오름차순으로 출력해야 한다.

일단 2019년 7월부터 2020년 7월까지의 발매들을 걸러내야 한다. 그리고 연도와 월을 결합해서 출력해야되는데 이것도 문자열 concat으로 수행해야 한다.

여기까진 쿼리 하나로 처리하는 것은 지금까지 수행한 문제에서 해왔던 것으로 쉽다. 그런데 문제는 이 릴레이션을 월별로 묶은 뒤에 전체 튜플 수로 나눠야 한다.

여기서 이 릴레이션을 서브쿼리로 처리하려면 골치가 아프다. 여기서 도움이 되는 것이 with 구문이다.

with 구문은 임시 릴레이션을 만들어서 with 구문과 동시에 쓰이는 쿼리에서 변수처럼 사용하게 해준다.

내가 이해한 바로는 임시 변수처럼 릴레이션을 정의할 수 있게 해주는 구문이다. 그래서 위에서 필요한 릴레이션을 뽑고 이를 임시 변수로 저장해서 사용하면 된다. 그리고 이를 Common Table Expression이라고 부르나보다.

작성한 쿼리문은 아래와 같다.

with past_year(year_month) as 
    select date_year || '.' ||
        (case
             when date_month < 10 then '0'
             else ''
         end) || date_month
    from release join release_info on release.id = release_info.release
    where (date_year = 2019 and date_month >= 7) or
            (date_year = 2020 and date_month <= 7)
select year_month, round(count(1) * 100.0 /
    (select count(1) from past_year), 2) as percentage
from past_year
group by year_month
order by year_month

8. COLLABORATE ARTIST

문제 : List the number of artists who have collaborated with Ariana Grande.

Print only the total number of artists. An artist is considered a collaborator if they appear in the same artist_credit with Ariana Grande. The answer should include Ariana Grande herself.

아리아나 그란데와 협력한 아티스트의 수를 출력해야 한다. 일단 artist_credit이 release를 발매한 아티스트에 대한 정보를 담고 있다.

그리고 artist_credit은 여러 명으로 구성되어 있을 수 있으며 구성원의 정보는 artist_credit_name과 관계를 맺음으로 저장하고 있다.

그리고 artist_credit_name이 어떤 아티스트인지는 artist의 ID로 정보를 가지고 있다.

따라서 아리아나 그란데의 ID를 가진 artist_credit_name이 속해 있는 artist_credit의 다른 구성원의 ID를 전부 가져와서 distinct한 수를 출력하면 된다.

이를 쿼리문으로 작성하면 아래와 같다.

select count(distinct artist)
from artist_credit_name
where artist_credit in (
        select artist_credit
        from artist_credit_name
        where artist = 823336
       )

823336은 아리아나 그란데가 artist 릴레이션에서 갖고 있는 ID이다.

9. DRE AND EMINEM

문제 : List the rank, artist names, along with the number of collaborative releases of Dr. Dre and Eminem among other most productive duos (as long as they appear in the same release) both started after 1960 (not included). Display like [rank]|Dr. Dre|Eminem|[# of releases].

For example, if you see a release by A, B, and C, it will contribute to three pairs of duos: A|B|1, A|C|1, and B|C|1. You will first need to calculate a rank of these duos by number of collaborated releases (release with artist_credit shared by both artists) sorted descendingly, and then find the rank of Dr. Dre and Eminem. Only releases in English are considered. Both artists should be solo artists. All pairs of names should have the alphabetically smaller one first. Use artist names (asc) as tie breaker.

내용이 엄청 길지만 원하는 내용은 첫 문장에 나와있다.

release 중에서 1960년 이후로 활동한 듀오들이 발매한 release 중에서 닥터드레와 에미넴의 듀오로 진행한 release가 몇 개 있는지를 찾아야 하고, 이런 듀오들이 진행한 release의 숫자들에 rank를 매긴다 치면 그 rank가 얼만지도 찾아야 한다.

그리고 rank를 매기는 순은 일단 발매 수이고 같다면 이름의 아티스트 이름의 오름차순이다. 그리고 듀오의 이름을 카운팅 하는 방법도 알려주고 있다. 그리고 카운팅할 release는 영어로 발매된 것 한정이다. 거기다 더 듀오 각자는 솔로 아티스트여야 한다.

당연한 얘기지만 artist의 name이 아닌 ID 기준으로 구분을 진행해야 한다. 이제 필요한 정보를 다시 보자.

아티스트의 이름을 봐야 하기 때문에 artist_credit_name이 필요하고 솔로 아티스트를 구분해야 되기 때문에 artist_type도 있어야 하고 이 둘을 연결하기 위해서 artist 릴레이션도 필요하다. 그리고 release를 세줘야 하니 이것도 필요하다. English인 release를 골라야 하니까 또 language 테이블도 필요하다.

차근차근 적어보자. 일단 artist가 두 명 필요하고 그 둘이 같은 artist_credit에 속해야 한다. 그리고 그들이 속한 artist_credit이 작업한 release가 필요하다.

간단하게 이것들을 조인해보자.

select *
from artist_credit_name as acn1
    join artist_credit_name as acn2 on acn1.artist_credit = acn2.artist_credit
    join release as r on acn1.artist_credit = r.artist_credit

그리고 artist_credit_name을 artist의 ID 기준으로 다시 조인해야된다.

select *
from artist_credit_name as acn1
    join artist_credit_name as acn2 on acn1.artist_credit = acn2.artist_credit
    join release as r on acn1.artist_credit = r.artist_credit
    join artist as a1 on acn1.artist = a1.id
    join artist as a2 on acn2.artist = a2.id

그리고 솔로 아티스트여야 하는 조건이 있었다. artist_type이 Person이어야 솔로 아티스트고 이 ID는 1이다. 그리고 release의 Language가 영어여야 한다. 이것들을 종합하자.

select *
from artist_credit_name as acn1
    join artist_credit_name as acn2 on acn1.artist_credit = acn2.artist_credit
    join release as r on acn1.artist_credit = r.artist_credit
    join artist as a1 on acn1.artist = a1.id
    join artist as a2 on acn2.artist = a2.id
    join artist_type as at1 on a1.type = at1.id
    join artist_type as at2 on a2.type = at2.id
    join language as l on r.language = l.id

여기까지가 필요한 릴레이션들의 join이었고 이제 where로 위에서 상기한 조건들을 걸어줘야 한다. 아티스트의 이름은 첫 이름이 사전순으로 더 작아야 하고 솔로 아티스트여야 했다. 그리고 사용된 언어는 영어, 활동 시작이 1960년 이후여야 한다.영어의 ID는 120이다. 그리고 필요한 정보는 두 아티스트의 ID와 그 조합이 발매한 release의 수이다.

다시 적자.

select acn1.artist, acn2.artist, count(1)
from artist_credit_name as acn1
    join artist_credit_name as acn2 on acn1.artist_credit = acn2.artist_credit
    join release as r on acn1.artist_credit = r.artist_credit
    join artist as a1 on acn1.artist = a1.id
    join artist as a2 on acn2.artist = a2.id
    join artist_type as at1 on a1.type = at1.id
    join artist_type as at2 on a2.type = at2.id
    join language as l on r.language = l.id
where a1.name < a2.name
    and at1.id = 1 and at2.id = 1
    and l.id = 120
    and a1.begin_date_year > 1960
    and a2.begin_date_year > 1960

이제 필요한 것은 위 릴레이션을 아티스트 조합으로 그룹화 한 다음에 발매 수의 내림차순으로 정렬하고 닥터드레와 에미넴의 조합이 몇 번째인지까지 출력해야 한다.

위 릴레이션을 with 구문으로 임시 테이블로 만들어주자.

순위의 경우 SQL 자체적으로 지원하는 것은 없지만 정렬 후에 행에 번호를 붙임으로 순위를 매기는 것이 가능하다. 이는 row_number()로 행 번호를 붙이는게 가능하다.

이제 최종 쿼리문을 작성하자.

with comb_list(id1, id2, cnt) as (
    select acn1.artist, acn2.artist, count(1)
    from artist_credit_name as acn1
        join artist_credit_name as acn2 on acn1.artist_credit = acn2.artist_credit
        join release as r on acn1.artist_credit = r.artist_credit
        join artist as a1 on acn1.artist = a1.id
        join artist as a2 on acn2.artist = a2.id
        join artist_type as at1 on a1.type = at1.id
        join artist_type as at2 on a2.type = at2.id
        join language as l on r.language = l.id
    where a1.name < a2.name
        and at1.id = 1 and at2.id = 1
        and l.id = 120
        and a1.begin_date_year > 1960
        and a2.begin_date_year > 1960
    group by acn1.artist, acn2.artist
)
select *
from (select row_number() over (
                order by cnt desc,
                    a1.name,
                    a2.name
            ) as rank,
            a1.name as name1,
            a2.name as name2,
            cnt
      from comb_list
        join artist as a1 on comb_list.id1 = a1.id
        join artist as a2 on comb_list.id2 = a2.id
    )
where name1 = "Dr. Dre" and name2 = "Eminem"

10. AROUND THE WORLD

문제 : Concat all dubbed names of The Beatles using comma-separated values(like "Beetles, fab four").

Find all dubbed names of artist "The Beatles" in artist_alias and order them by id (ascending). Print a single string containing all the dubbed names separated by commas.

비틀즈의 예명을 전부 콤마로 이어 붙인 것을 id의 오름차순으로 출력해야 한다.

그런데 튜플당 예명 하나가 아니라 모든 예명을 id의 오름차순으로 ,로 이어 붙인 뒤에 출력해야 한다. 이걸 대체 어떻게 해야 할까...

힌트로는 CTE를 잘쓰면 된다고 한다. 고민을 해봤는데 모르겠어서 답지를 깠다.

일단 비틀즈의 예명을 ID의 오름차순으로 갖는 CTE를 쓰자.

with alias as(
    select artist_alias.name
    from artist_alias
        join artist on artist.id = artist_alist.artist
    where artist.name = "The Beatles"
    order by artist_alias.id
)

이걸로 어떻게 잘 해봐야 할 거 같다. 그냥 C++이거나 그러면 순회하면서 붙이겠지만 SQL에 그런 것은 존재하지 않는다. 그나마 재귀적으로 CTE를 작성하면 반복문의 효과를 낼 수 있다.

alias 외에 새로운 CTE를 작성해서 쉼표로 이어지는 문자열을 만들어보자. 일단 인덱스처럼 사용하기 위해서 row_number()를 alias CTE에 넣자.

with alias as(
    select row_number() over (
        order by artist_alias.id
    ) as num, artist_alias.name as name
    from artist_alias
        join artist on artist.id = artist_alias.artist
    where artist.name = "The Beatles"
)

이제 위 테이블에서 num이 커지는 순서대로 쉼표와 함께 name을 concat 해주면 된다. 그런데 이걸 재귀적으로 해야 한다.

CTE의 이름을 해당 CTE 내부에 재사용하면 그런 재귀가 가능하다.

with alias as(
    select row_number() over (
        order by artist_alias.id
    ) as num, artist_alias.name
    from artist_alias
        join artist on artist.id = artist_alias.artist
    where artist.name = "The Beatles"
),
comma as (
    select num, name
    from alias
    where num = 1
    union all
    select alias.num, comma.name || ', ' || alias.name
    from alias
        join comma on alias.num = comma.num+1
)
select name
from comma
order by num desc
limit 1;

위처럼 comma CTE를 정의해주는데 그 안에 comma를 사용하여 재귀적으로도 정의가 가능하다. 강의 중에도 이런 식으로 사용했던 거 같은데 이 부분만 다시 살펴보도록 해야겠다.