2024-02-07

SQL 코드카타

 

Ollivander's Inventory(SQL)(left join, where exists, min, group by, having, in으로도 가능함) (특정 조건들끼리해서 가장 최소값인 데이터만 가져와서 내림차순하기)

 

Ollivander's Inventory | HackerRank

사악하지 않은 지팡이 중 나이와 power가 같은 지팡이의 경우 가장 가격이 싼 경우만 가져와서

지팡이의 id, age, 필요한 코인수, power를 power를 기준으로 내림차순하며 power가 같을 경우 나이를 기준으로 내림차순하는 문제이다.

 아래는 먼저 시도해봤다가 예상치 못하게 에러가 난 쿼리인데 좀더 확인해보고 후에 질문을 통해 확인해볼 예정이다

SELECT
    w.id, wp.age, w.coins_needed, w.power
FROM 
    wands w
LEFT JOIN 
    wands_property wp ON w.code = wp.code
WHERE 
    wp.is_evil = 0
    AND EXISTS (
        SELECT
            w_s.id, wp_s.age, MIN(w_s.coins_needed), w_s.power
        FROM 
            wands w_s
        LEFT JOIN 
            wands_property wp_s ON w_s.code = wp_s.code
        WHERE 
            wp_s.is_evil = 0
        GROUP BY 
            w_s.id, wp_s.age)
ORDER BY
    w.power DESC, wp.age DESC

이렇게 했더니

#ERROR 1055 (42000) at line 4: Expression #4 of SELECT list is not in 
#GROUP BY clause and contains nonaggregated column 'run_ad9jzdqunff.w_s.power' 
#which is not functionally dependent on columns in GROUP BY clause; 
#this is incompatible with sql_mode=only_full_group_by

SELECT
    w.id, wp.age, w.coins_needed, w.power
FROM 
    wands w
LEFT JOIN 
    wands_property wp ON w.code = wp.code
WHERE wp.is_evil = 0
    AND EXISTS 
    (
        SELECT
            wp_s.age, w_s.power, MIN(w_s.coins_needed)
        FROM 
            wands w_s
        LEFT JOIN 
            wands_property wp_s ON w_s.code = wp_s.code
        WHERE 
            wp_s.age = wp.age
        GROUP BY 
            wp_s.age, w_s.power
        HAVING 
            MIN(w_s.coins_needed) = w.coins_needed)
ORDER BY
    w.power DESC, wp.age DESC

간단히 쿼리에 대해 설명하자면 우선 wands테이블과 wadas_property테이블을 code를 공통컬럼으로 하여 left join해주었으며 그중 사악하지 않은 지팡이에 대한 데이터만 가져왔다 그런 뒤 지팡이의 id, 나이, 필요한 코인수, power를 출력하는데 이 때 각 행에 대하여 EXISTS문 서브쿼리에 행이 존재할 경우에만 출력한다. 이때 EXISTS문에 들어갈 서브쿼리을 설명하자면 동일하게 wands와 wands_property 테이블을 code로 left join하고 메인쿼리의 age와 exists서브쿼리의 age가 같은 경우의 데이터만 가져와서 지팡이의 나이와 power를 기준으로 그룹지어서 그 그룹의 최소 필요코인 수와 메인쿼리의 필요 코인 수가 같을 때만 지팡이의 나이, power, 최소 필요 코인 수가 출력되는 쿼리이다(사실 특별한 컬럼을 출력할 필요없이 1이나 *로 하여 값이 출력되게만 하여도 충분히 동작한다)

이번 문제는적당히 어렵다고 생각할 수 있을 것 같다 문제의 의미가 설명을 부실하게 해둔 것인지, 내가 이해를 잘못했던 것인지, 아니면 피곤한 상태로 풀어서 그런지 의미 이해하는데 꽤 오래 걸려 헤맸지만 풀이 자체는 그리 엄청 복잡하거나 하지는 않고 꾸준히 풀어주는 실력보존용 가볍게 푸는 문제로는 이정도가 적당하지 않나라는 생각을 했다(비록 최근 계속 약간 무리해서 하다가 늦게 자는 바람에 매우 피곤한 상태라 어제도 급하게 밤늦게 풀어보다가 안풀려서 중간에 그만두고 오늘 마저 풀었지만..)

 아래는 다른사람 쿼리로 in으로 푼 방식이다

SELECT w.id,
       wp.age,
       w.coins_needed,
       w.power
FROM wands w LEFT JOIN wands_property wp ON w.code = wp.code
WHERE wp.is_evil = 0 
    AND (wp.age, w.power, w.coins_needed) in (
        SELECT wp_1.age,
               w_1.power,
               MIN(w_1.coins_needed)                 
        FROM wands w_1 LEFT JOIN wands_property wp_1 ON w_1.code = wp_1.code
        GROUP BY wp_1.age, w_1.power)
ORDER BY w.power DESC, wp.age DESC;

 

Challenges(SQL)(hackerrank에서 with를!?, group by, not exists, count, having, max, 최대가 아닐때에만 적용되게 조건 걸어주기, A와 B가 있으면 A에 대하여 그룹하고 B갯수, 그것을 또 B갯수로 그룹지어서 해당되는 A갯수확인하기)(좀 복잡한듯)

Challenges | HackerRank

가장 많은 수의 challenge를 만든 경우가 아니라면 동일한 수의 challenge를 만든 사람은 다 제외하고 challenge를 제출한 수에 대하여 그 수에 해당하는 사람이 한명인 경우와 가장 많은 challenge를 만든 경우에 포함되는 hacker의 id와 그 사람이 만든 challenge의 수를 출력하는데, 만든 수로 내림차순하고 이름으로 오름차순하는 문제이다.

SELECT 
    c.hacker_id, COUNT(challenge_id) #확인결과 중복 X
FROM
    challenges c
GROUP BY 
    c.hacker_id

SELECT 
    c.hacker_id, COUNT(c.challenge_id) cnt_chall, COUNT(COUNT(c.challenge_id))
FROM 
    challenges c
GROUP BY 
    c.hacker_id, cnt_chall
ORDER BY 
    cnt_chall DESC

# ERROR 1111 (HY000) at line 4: Invalid use of group function

문법적으로 안될 것 같기는 했는데 혹시나 하여 해보았는데 역시나 안 되었다

아래는 not exists문의 서브쿼리로 쓰기위한 사전작업으로 만든 쿼리이다

SELECT 
    sub.cnt_chall, COUNT(sub.hacker_id)
FROM 
    (SELECT 
        c_s.hacker_id, COUNT(c_s.challenge_id) cnt_chall
    FROM 
        challenges c_s
    GROUP BY 
	        c_s.hacker_id
    ) sub
GROUP BY 
    sub.cnt_chall

먼저 hacker_id당 challenge를 몇 개씩 만들었는지 세어주고 그것을 from 서브쿼리로 써서 다시 세어준 갯수를 그룹지어서 hacker 몇 명이 그 갯수만큼 제출했는지 확인해주는 쿼리이다.

SELECT 
    nc_s.cnt_chall, COUNT(nc_s.hacker_id) cnt_hack, MAX(nc_s.cnt_chall)
FROM 
    num_challenge_per_hacker nc_s
GROUP BY 
    nc_s.cnt_chall
HAVING 
    cnt_hack>1
    -- AND nc_s.cnt_chall != MAX(nc_s.cnt_chall)

이렇게하면 왜 nc_s.cnt_chall를 전체적으로 볼 때 최대값이 아닌 nc_s.cnt_chall행마다 최대값으로 판정되어 행마다 다르게 최대값이 표시되는건지 알듯 모를듯하다..ㅠ

WITH num_challenge_per_hacker AS(
    SELECT 
        c_s.hacker_id, COUNT(c_s.challenge_id) cnt_chall
    FROM 
        challenges c_s
    GROUP BY 
        c_s.hacker_id
)
SELECT 
    h.hacker_id, h.name, nc.cnt_chall
FROM 
    hackers h, num_challenge_per_hacker nc
WHERE 
    h.hacker_id = nc.hacker_id
    AND NOT EXISTS(
                SELECT 
                    nc_s.cnt_chall, COUNT(nc_s.hacker_id) cnt_hack
                FROM 
                    num_challenge_per_hacker nc_s
                WHERE 
                    h.hacker_id = nc_s.hacker_id
                GROUP BY 
                    nc_s.cnt_chall
                HAVING 
                    cnt_hack>1
                    AND nc_s.cnt_chall != (SELECT MAX(sub.cnt_chall) FROM num_challenge_per_hacker sub)
                    )
ORDER BY 
    nc.cnt_chall DESC, h.hacker_id;

이렇게도 안된다 확인해보니 왜인지는 모르겠지만 그 갯수를 제출한 사람이 여러명이여도 1명이라고 표시가 된다

EXISTS 쿼리문을 따로 떼어내어 확인해보니 연결하는 조건인

WHERE h.hacker_id = nc_s.hacker_id가 원인이였다 조건에 만족하는 데이터는 1개일 수 밖에 없기에 해당하는 데이터는 항상 1개만 오게 되었고 따라서 쿼리의 결과는 항상 갯수가 1이기에 아무것도 존재하지 않아 항상 not exists에 대하여 참이되어 모든 데이터가 not exists의 조건에 대해 만족하는 없는 것과 마찬가지인 상황이였던 것이다

그래서 연결 조건을 다르게 걸어주어 만들어낸 challenge수인 cnt_chall을 조건으로 걸었다

그랬더니 잘 해결되었으며 쿼리는 아래와 같다

WITH num_challenge_per_hacker AS(
    SELECT 
        c_s.hacker_id, COUNT(c_s.challenge_id) cnt_chall
    FROM 
        challenges c_s
    GROUP BY 
        c_s.hacker_id
)
SELECT 
    h.hacker_id, h.name, nc.cnt_chall
FROM 
    hackers h, num_challenge_per_hacker nc
WHERE 
    h.hacker_id = nc.hacker_id
    AND NOT EXISTS(
                SELECT 
                    nc_s.cnt_chall, COUNT(nc_s.hacker_id) cnt_hack
                FROM 
                    num_challenge_per_hacker nc_s
                WHERE 
                    nc.cnt_chall = nc_s.cnt_chall
                GROUP BY 
                    nc_s.cnt_chall
                HAVING 
                    cnt_hack>1
                    AND nc_s.cnt_chall != (SELECT MAX(sub.cnt_chall) FROM num_challenge_per_hacker sub)
                    )
ORDER BY 
    nc.cnt_chall DESC, h.hacker_id;

쿼리에 대해 간단히 설명하자면 우선 임시테이블로 challenges테이블로 부터 해커아이디로 그룹을 지어서 해커아이디와 해커아이디의 사람이 만든 challenge 수((cnt_chall이라고 하겠음))를 출력하는 쿼리로 임시테이블num_challenge_per_hacker를 만들었다

그리고 hackers 테이블과 num~테이블을 가져와 where절로 둘의 hacker_id가 같은 경우에 대해서만 데이터를 가져와서 hacker_id와 이름, cnt_chall을 출력하도록 하는데 각 행에 대하여 EXISTS절에 데이터가 존재하지 않는 경우만 출력하도록하며 정렬은 cnt_chall로 내림차순, hacker_id로 오름차순 정렬해준다

그리고 EXISTS절 서브쿼리에 대해 설명하면 우선 num~테이블로 부터 데이터를 가져오는데 단 메인쿼리의 cnt_chall과 같은 경우만 가져온다 그리고 cnt_chall에 대해 그룹지어서 cnt_chall 그 그룹이 cnt_hack(cnt_chall에 해당되는 hacker 수)가 1보다 크고 cnt_chall이 최대가 아닌 경우에만 cnt_chall과 그에 해당하는 hacker_id의 수를 출력하는 쿼리이다.

따라서 만약 가장 큰 수를 제외한 같은 수의 challenge를 만든 학생은 EXISTS 쿼리에 데이터가 존재하게 되기 때문에 해당되는 데이터는 메인쿼리에서 출력되지 않게 된다

 

Contest Leaderboard(SQL)(이중 from 서브쿼리, sum, max, group by)

Contest Leaderboard | HackerRank

각 챌린지 별로 최대점수로 계산하여 hacker_id별로 총 점수의 합을 구하는 문제인데, hacker_id, 이름, 총점수를 출력하는데 총점이 0점인 사람은 출력하지 않고, 또 정렬은 총점기준으로 내림차순하고 id기준으로 오름차순하는 문제이다.

SELECT 
    h.hacker_id, h.name, s.tot_score
FROM 
    hackers h,
    (SELECT 
        s2.hacker_id, SUM(s2.max_score) tot_score
    FROM
        (SELECT 
            s1.hacker_id, MAX(s1.score) max_score
        FROM 
            submissions s1
        GROUP BY 
            s1.hacker_id, s1.challenge_id
        ) s2
     GROUP BY 
        s2.hacker_id
    ) s
WHERE 
    h.hacker_id = s.hacker_id
    AND s.tot_score >0
ORDER BY 
    s.tot_score DESC, h.hacker_id

우선 어제 시간이 급하여 적지 못한 프로젝트 관련 내용부터 간단히 말하고 시작하도록 하겠다

어제 오전에 프로젝트 관련해서 발제를 하고 주제를 선택할 수있게 7가지의 주제 선택지를 주어졌는데
아래에 접은 글로 관련하여 내 개인적인 사전 생각을 적어두겠다

프로젝트 주제관련 내 개인적인 생각

• 우선 머신러닝 실제로 적용해서 써보고 싶어서 가능한 예측을 하고싶음
  • 와인가격 예측 예측이라서 일단 어느정도는 관심, 하지만 와인자체는 배경지식도 전무하다 싶이하고 관심도가 좀 떨어짐
  • 서울시 전세가격 예측 →어려워보이긴 한데 새로운 것 많이 해볼 수 있고 주제도 실생활에 쓰일 법한 실용적인 것이라 관심이 감
  • 상품 판매 데이터 분석 여태 했던게 계속 마케팅 쪽이라서 다른 것을 해보고 싶기 때문에 별로 안하고 싶음 예측하는 부분도 없다 생각될 수 있어서 머신 러닝 적용을 할려면 다하고 추가로 해주는 정도가 가능할 것 같아서 비선호
  • 금융 css 진짜 대중적으로 간략하게나마 알만한 정도만 알고 자세히는 몰라서 정하는데 어려움이 있을 듯하지만 개인적으로 도메인들중 상대적으로 더 흥미가 가는 듯한 분야이기도하고 이번 개인과제했던 것과도 어느정도 연관지어서 해볼 수 있을 듯하여 꽤 괜찮다고 생각됨
  • GA4 비록 예측은 없어 머신러닝을 써보지는 못할 것으로 예상되나 주제가 한번쯤은 체험해보고 싶었던 그런 유저분석에 관한 내용인듯하여 흥미는 감
  • 위의 금융쪽 마찬가지로 css와 유사한 듯한데 대출등급을 평가하여 매기는 쪽 내용 같은데 마찬가지로 해석이 어려울 수는 있으나 데이터를 수집하지 않아도 된다는 점에서 마음에 들고, 한번해보면 재미있을 듯하여 꽤 괜찮은듯보임
  • 제주 특산물 가격 예측 예측이라 어느정도는 관심이 가나 내용에 대해 간단하게 해보고 싶다면 오히려 이부분이 실제 머신러닝 모델을 학습하는 것에는 더 도움이 될듯함

예측, ML 주제 흥미도 난이도 쉬움 배경지식 최종 두가지예측, ML 주제 흥미도 난이도 쉬움 배경지식 최종 두가지

	예측, ML	주제 흥미도	난이도 쉬움	배경지식	최종 두가지
와인가격예측	ㅇ	x	△	x	🐨
서울시전세예측	ㅇ	ㅇ	x	x	🧐
상품판매데이터분석	x	x	ㅇ	△	☠
금융신용평가모형	ㅇ	ㅇ	x	△	✅
GA4유저분석	x	ㅇ	△	△	🧐
고객 대출 등급 분류	ㅇ	ㅇ	x	△	✅
제주 특산물 가격예측	ㅇ	x	ㅇ	x	🧐

처음에 이렇게 생각하여 이중 금융신용평가모형이나 고객 대출 등급 분류 문제를 가장 하고 싶었다

나머지도 굳이 와인이랑 상품판매데이터분석만 아니면 다 호기심이 어느정도 들기는 했다(제주 특산물은 아예 없진 않았지만 다른 것들에 비하면 사실상 없는 쪽에 분류하는게 더 가까울 것 같기는 하였다)

(그리고 물론 평가는 내 주관적인 당시의 생각으로 내린 평가이므로 정확하지 않을 확률이 매우 높다)

하지만 내 희망과는 다르게 투표 결과 우선 크게 와인가격예측, 서울시 전세 가격 예측 모델링, 고객 대출 등급 분류 이렇게 3개로 추려지게 되었는데 시간적으로 조금 여유있는 상대적으로 간단한 내용을 다들 하길 선호하는 느낌이 있어서(다들 ADsP자격증 공부도 겸할 시간, 체력도 남겨둬야하는 상황이라) 비교적 간단해 보였는 와인 가격 예측을 팀원 모두가 관련하여 배경지식이 아무도 없었지만 고르게 되었다.
하지만 반나절쯤 계속 찾아보고 생각해보던 결과 나는 팀원들과 나의 예상과 달리 와인가격 예측이 그리 간단하지 않을 수 있다고 판단하였고, 이유는 예전에 다른 어떤 교수분이 와인가격을 예측해놓은 것을 리뷰?같이 써두신 블로그 내용을 봤었는데 대략적으로 변수를 여름철 평균온도와 평균강수량에 영향을 받는 것으로 예상되고(포도가 재료이다 보니 포도의 생산량과 관계되어서 그렇지 않을까 예상했다), 또 와인의 숙성기간에 영향 받음을 변수로 하여 선형회귀모델을 사용하여 예측하였는데 약간의 bias를 감안하면 거의 일치하는 예측을 보였기 때문에 간단하지 않을까 생각했던 것도 있었는데 data set을 검색하여 찾아보니 숙성기간정도는 포함된게 많았지만, 생각과 달리 주로 생산지역, 알코올농도?, 산도, 떫은 정도 과 같은 이런 정보들이 data set의 주이고 온도나 강수량 관련 데이터는 포함된 것이 없어서 따로 가격 정보가 담긴 data set에서 해당되는 지역의 강수량과 온도(이것들을 변수로 쓸려면)을 대응되는 것을 따로 다 찾아서 정리한 뒤 시작할 수 있는데 지역이 적은 것도 아니였고 data set의 연도와 시기 등에 맞는 데이터를 찾아야하기 때문에 난이도가 더 높을 것으로 예상되었고 우리나라의 온도 및 강수량이 아닌 세계 여러지역의 온도, 강수량 등의 자료를 찾아야하기 때문에 쉽지 않을 것으로 예상했다, 물론 굳이 변수를 그것으로 안하고 다른 data set에 있는 데이터들로 변수로 하여 학습한 뒤 예측을 할 수 도있겠으나, 사실 와인의 이름, 종류와 관련이 있다면 모를까 그렇게 까지 가격과 관련이 있을까라는 생각이 드는 데이터가 많은 것 같아 이것들을 변수로 하여 학습하여 좋은 성능의 예측을 하기엔 힘들지 않을까라는 생각을 했다. 덧붙여 다른 사람들도 많이들 해보았던 주제일듯하여 레퍼런스를 찾기위해 서칭해보았으나 대부분 머신러닝보다는 그중에서도 딥러닝, 텐서 플로우로 많이들 해둔 것 같았는데 현재 우리는 딥러닝은 제대로 배운 내용이 없어서 처음부터 딥러닝을 공부하여 하기에는 많이 부담이 된다고 생각하였다. 따라서 나는 아직 얼마 지나지 않았으니 지금이라도 다른 주제로 변경하여 진행하는 것이 좋지 않을지 조심스럽게 건의하였고 팀원분들도 와인가격예측이 그렇게 희망적인 예측이 안서는 듯하여서 그럼 우선 다른분들은 와인 관련된 정보들을 더 찾아보는 것으로하고 나는 다른 주제에 대해서 한번 전체적인 그림 등을 그려보고 정리하여 저녁시간에 다시 모였을 때 말해보기로 하였다.
저녁시간에 다시 모였을 때 여전히 다른 팀원분들도 와인에 관하여 더 진행된 정보를 얻지 못하셨던 것으로 보였고, 나는 그 찾아보는 시간동안 마지막으로 추려졌던 3가지 중 나머지 2가지를 위주로 더 보았는데 그중에서도 눈에 들어왔던 것이 고객 대출 등급 분류였다. 무엇보다도 데이터 수집을 따로 할 필요없이 주어진 데이터를 통해 높은 예측을 하는 것이 목적이기에 해야할 것도 분명하고, 이전 강의나 개인과제에서 했던 부분도 다시 활용해서 할 수 있을 듯 보였고, 또 머신러닝을 진짜 제대로 집중해서 사용해 볼 수 있을 것이라고 생각 되었기 때문에 더 관심이 갔다 그래서 개인과제 했을 때처럼 전체적인 틀과 내용들을 코드로 한 40~60%정도 해두고(함수 등도 개인 과제등에서 썼던 것을 다시 수정해서 활용할 수 있을 것 같다면 수정해서 추가해주었다) 그래서 내가 준비하고 생각하는 방향과 길을 보여드리니 다른 분들도 그럼 주제를 고객 대출 등급 분류로 바꾸는 것도 좋을 것이라고 동의해주시고 주제를 변경하게 되었다
추가로 내가 전체적인 틀과 그림 등을 설계해 나갔기 때문에 어제 저녁부터 오늘 오전까지해서 좀 더 준비해서 점심먹고 다시 모여서 설명을 드리고 역할을 어느정도 나누어서 메인 역활과 나머지 서브는 프리포지션을 각자에게 부여해서 진행하는 방향으로 하기로 결정되었다(어쩌다 보니 내가 거의 다 주도해서 진행하게 된 부분이 많았던 것 같다) 그래서 얼추 전체 과정의 40%정도 이상을 해두고 공유를 해드린 다음 관련하여 좀 더 공부하거나 따로 코드 등으로 시도해보는 것을 하고 내일 점심먹기전 12시에 모여서 공유하고 얘기를 나눠보기로 하였다 중간중간 세세한 내용은 더있지만 다적기에는 너무 많은 듯하여 블로그에는 이정도로만 적도록 하겠다

마지막으로 SQL 코드카타는 프로젝트한다고 별로 시간을 못 쓰다가 저녁 먹고 나서 조금 쉬엄해 가는 타이밍과 정규 시간인 9시 이후로 남아서 좀더 풀어보았다(중간에 github관련해서 질문하러 다녀오기도 하였다(관련 내용은 슬랙에 팀원분들께 내가 이해한 내용을 토대로 공유해 두었는데 차후 질문하는 내용등에 대해서 조금 더 모이면 정리해서 올리는 것이 좋을 것 같다는 생각이들어 다음에 올릴까한다(사실 요근래 계속 너무 늦게 자서 매우 피곤한 관계로 귀찮아져서일수도 있다...ㅎㅎ)))

그리고 살짝 시간 여유 남을 때 매번 SQL관련해서 질문드렸던 정현석 튜터님께 질문드릴려했는데(프로젝트 기간동안 질문드리러 오는 사람이 매우 많아서 바쁘실듯했기에 빨리 미리 질문 모아둔 것 여쭤봐서 해치워버릴려했다) 아쉽게도 타이밍도 조금 어긋나고 사람들이 너무 많아서 질문을 드리지 못하고 시간이 다되어 퇴근하셨기에 어쩔 수 없이 다음에 여유가 있을 때 질문을 드릴 예정이다.(어차피 급한 질문이거나 하지는 않아서 프로젝트에 좀더 집중하고 여유날 때 질문드려도 크게 무리는 없을듯 하다)