2024-02-09~2024-02-12(설날연휴)

(일단 글만 적어두고 나중에 수정)

 

SQL 코드카타

 

아직 해결 못한 문제는 제외하고 작성

 

Interviews(SQL) (해결은 했는데 제출이 안되서 못함)

Interviews | HackerRank

각 contest_id별로 contest_id와 hacker_id, name, 각 전체 submissions, accepted_submissions, views, unique_views를 출력하는데 contests_id의 오름차순으로 정렬하여 출력하는 문제이다

이번 문제도 뭔가 테이블이 많이 주어진 것으로 봐서 조금 복잡할 수 있겠다는 생각이 들었다.

이전 시도한 내용들

WITH total AS (
    SELECT 
        v.challenge_id, 
        SUM(v.total_views) total_views, 
        SUM(v.total_unique_views) total_unique_views, 
        SUM(s.total_submissions) total_submissions, 
        SUM(s.total_accepted_submissions) total_accepted_submissions
    FROM 
        view_stats v
    LEFT JOIN 
        submission_stats s ON v.challenge_id = s.challenge_id
    GROUP BY 
        v.challenge_id
)
SELECT 
    con.contest_id, con.hacker_id, con.name,
    SUM(total_views), 
    SUM(total_unique_views),
    SUM(total_submissions),
    SUM(total_accepted_submissions)
FROM 
    contests con
LEFT JOIN 
    colleges col ON con.contest_id = col.contest_id
LEFT JOIN 
    challenges ch ON col.college_id = ch.col.college_id
LEFT JOIN 
    total t ON ch.challenge_id = t.challenge_id
GROUP BY 
    con.contest_id
ORDER BY 
    con.contest_id

WITH total_values AS (
    SELECT 
        v.challenge_id, 
        COALESCE(SUM(v.total_views), 0) total_views, 
        COALESCE(SUM(v.total_unique_views), 0)                                                       total_unique_views, 
        COALESCE(SUM(s.total_submissions), 0) total_submissions, 
        COALESCE(SUM(s.total_accepted_submissions), 0)                                                 total_accepted_submissions
    FROM 
        view_stats v
    LEFT JOIN 
        submission_stats s ON v.challenge_id = s.challenge_id
    GROUP BY 
        v.challenge_id
)
SELECT 
    con.contest_id, con.hacker_id, con.name,
    SUM(total_views), 
    SUM(total_unique_views),
    SUM(total_submissions),
    SUM(total_accepted_submissions)
FROM 
    contests con
LEFT JOIN 
    colleges col ON con.contest_id = col.contest_id
LEFT JOIN 
    challenges ch ON col.college_id = ch.col.college_id
LEFT JOIN 
    total_values t ON ch.challenge_id = t.challenge_id
GROUP BY 
    con.contest_id
ORDER BY 
    con.contest_id

SELECT 
    con.contest_id, con.hacker_id, con.name,
    SUM(total_views), 
    SUM(total_unique_views),
    SUM(total_submissions),
    SUM(total_accepted_submissions)
FROM 
    contests con
LEFT JOIN 
    colleges col ON con.contest_id = col.contest_id
LEFT JOIN 
    challenges ch ON col.college_id = ch.college_id
LEFT JOIN 
    (
    SELECT 
        v.challenge_id, 
        COALESCE(SUM(v.total_views), 0) total_views, 
        COALESCE(SUM(v.total_unique_views), 0) total_unique_views, 
        COALESCE(SUM(s.total_submissions), 0) total_submissions, 
        COALESCE(SUM(s.total_accepted_submissions), 0) total_accepted_submissions
    FROM 
        view_stats v
    LEFT JOIN 
        submission_stats s ON v.challenge_id = s.challenge_id
    GROUP BY 
        v.challenge_id
    ) t ON ch.challenge_id = t.challenge_id
GROUP BY 
    con.contest_id
ORDER BY 
    con.contest_id

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

SELECT 
    con.contest_id, 
    con.hacker_id, 
    con.name,
    SUM(total_views), 
    SUM(total_unique_views),
    SUM(total_submissions),
    SUM(total_accepted_submissions)
FROM 
    contests con
LEFT JOIN
    (
    SELECT 
        col.contest_id,
        SUM(t.total_views) total_views, 
        SUM(t.total_unique_views) total_unique_views,
        SUM(t.total_submissions) total_submissions,
        SUM(t.total_accepted_submissions) total_accepted_submissions
    FROM 
        colleges col
    LEFT JOIN 
        challenges ch ON col.college_id = ch.college_id
    LEFT JOIN 
        (
        SELECT 
            v.challenge_id, 
            COALESCE(SUM(v.total_views), 0) total_views, 
            COALESCE(SUM(v.total_unique_views), 0) total_unique_views, 
            COALESCE(SUM(s.total_submissions), 0) total_submissions, 
            COALESCE(SUM(s.total_accepted_submissions), 0) total_accepted_submissions
        FROM 
            view_stats v
        LEFT JOIN 
            submission_stats s ON v.challenge_id = s.challenge_id
        GROUP BY 
            v.challenge_id
        ) t ON ch.challenge_id = t.challenge_id
    GROUP BY 
        col.contest_id
    ) t2 ON con.contest_id = t2.contest_id
ORDER BY 
    con.contest_id

SELECT 
    col.contest_id,
    COALESCE(SUM(t.total_views), 0) total_views, 
    COALESCE(SUM(t.total_unique_views), 0) total_unique_views, 
    COALESCE(SUM(t.total_submissions), 0) total_submissions, 
    COALESCE(SUM(t.total_accepted_submissions), 0) total_accepted_submissions
FROM 
    colleges col
LEFT JOIN 
    challenges ch ON col.college_id = ch.college_id
LEFT JOIN 
    (
    SELECT 
        v.challenge_id, 
        COALESCE(SUM(v.total_views), 0) total_views, 
        COALESCE(SUM(v.total_unique_views), 0) total_unique_views, 
        COALESCE(SUM(s.total_submissions), 0) total_submissions, 
        COALESCE(SUM(s.total_accepted_submissions), 0) total_accepted_submissions
    FROM 
        view_stats v
    LEFT JOIN 
        submission_stats s ON v.challenge_id = s.challenge_id
    GROUP BY 
        v.challenge_id
    ) t ON ch.challenge_id = t.challenge_id
GROUP BY 
    col.contest_id

이부분 까지는 에러가 안나고 잘되는 듯함(중간에 null값 잔뜩있길래 0으로 바꿔주었다)

SELECT 
    con.contest_id, 
    con.hacker_id, 
    con.name,
    t3.total_submissions,
    t3.total_accepted_submissions,
    t3.total_views, 
    t3.total_unique_views
FROM
    contests con
LEFT JOIN
    (
    SELECT
        con2.contest_id, 
        COALESCE(SUM(t2.total_submissions), 0) total_submissions, 
        COALESCE(SUM(t2.total_accepted_submissions), 0) total_accepted_submissions,
        COALESCE(SUM(t2.total_views), 0) total_views, 
        COALESCE(SUM(t2.total_unique_views), 0) total_unique_views
    FROM 
        contests con2
    LEFT JOIN
        (SELECT 
            col.contest_id,
            col.college_id,
            COALESCE(SUM(t.total_submissions), 0) total_submissions, 
            COALESCE(SUM(t.total_accepted_submissions), 0) total_accepted_submissions,
            COALESCE(SUM(t.total_views), 0) total_views, 
            COALESCE(SUM(t.total_unique_views), 0) total_unique_views
        FROM 
            colleges col
        LEFT JOIN 
            (
            SELECT
                ch.college_id,
                ch.challenge_id, 
                COALESCE(SUM(s.total_submissions), 0) total_submissions, 
                COALESCE(SUM(s.total_accepted_submissions), 0) total_accepted_submissions,
                COALESCE(SUM(v.total_views), 0) total_views, 
                COALESCE(SUM(v.total_unique_views), 0) total_unique_views
            FROM 
                challenges ch
            LEFT JOIN
                view_stats v ON ch.challenge_id = v.challenge_id
            LEFT JOIN 
                submission_stats s ON ch.challenge_id = s.challenge_id
            GROUP BY 
                ch.college_id, ch.challenge_id
            ) t ON col.college_id = t.college_id
        GROUP BY 
            col.contest_id, col.college_id
        ) t2 ON con2.contest_id = t2.contest_id
    GROUP BY 
        con2.contest_id
    ) t3 ON con.contest_id = t3.contest_id
WHERE t3.total_views + t3.total_unique_views 
        + t3.total_submissions + t3.total_accepted_submissions >0
ORDER BY 
    con.contest_id

여기서 wrong answer뜸

일단 간단하게 해서 파악한 잘못된 결과가 나온 이유는

제일 처음 JOIN해줄 때 각 challenge_id가 중복되는 것들이 있는데 만약 같은 challege_id에 대하여 view가 2번있고 submission은 3번 있었다하면 join하면 2*3하여 challenge에 대하여 6번 나타나게 된다 그래서 view는 원래 총 합의 *3이 되고, submission은 원래 총 합의 *2가되어 나왔기 때문에 잘못된 결과가 나온 것이다

그래서 올바른 결과를 얻기위해서는 join하기전 challenge_id를 고유하게 해주기 위해서 각 테이블에서 미리 group by sum을 해줘야할 것으로예상된다

SELECT 
    con.contest_id, 
    con.hacker_id, 
    con.name,
    t3.total_submissions,
    t3.total_accepted_submissions,
    t3.total_views, 
    t3.total_unique_views
FROM
    contests con
LEFT JOIN
    (
    SELECT
        con2.contest_id, 
        COALESCE(SUM(t2.total_submissions), 0) total_submissions, 
        COALESCE(SUM(t2.total_accepted_submissions), 0) total_accepted_submissions,
        COALESCE(SUM(t2.total_views), 0) total_views, 
        COALESCE(SUM(t2.total_unique_views), 0) total_unique_views
    FROM 
        contests con2
    LEFT JOIN
        (SELECT 
            col.contest_id,
            col.college_id,
            COALESCE(SUM(t.total_submissions), 0) total_submissions, 
            COALESCE(SUM(t.total_accepted_submissions), 0) total_accepted_submissions,
            COALESCE(SUM(t.total_views), 0) total_views, 
            COALESCE(SUM(t.total_unique_views), 0) total_unique_views
        FROM 
            colleges col
        LEFT JOIN 
            (
            SELECT 
                ch.college_id,
                ch.challenge_id, 
                COALESCE(SUM(s.total_submissions), 0) total_submissions, 
                COALESCE(SUM(s.total_accepted_submissions), 0) total_accepted_submissions,
                COALESCE(SUM(v.total_views), 0) total_views, 
                COALESCE(SUM(v.total_unique_views), 0) total_unique_views
            FROM 
                challenges ch
            LEFT JOIN
                (
                SELECT 
                    vs.challenge_id,
                    SUM(vs.total_views) total_views,
                    SUM(vs.total_unique_views) total_unique_views
                FROM 
                    view_stats vs
                GROUP BY 
                    vs.challenge_id
                ) v ON ch.challenge_id = v.challenge_id
            LEFT JOIN 
                (
                SELECT 
                    ss.challenge_id ,
                    SUM(ss.total_submissions) total_submissions,
                    SUM(ss.total_accepted_submissions) total_accepted_submissions
                FROM 
                    submission_stats ss
                GROUP BY 
                    ss.challenge_id
                    ) s ON ch.challenge_id = s.challenge_id
            GROUP BY 
                ch.college_id, ch.challenge_id
            ) t ON col.college_id = t.college_id
        GROUP BY 
            col.contest_id, col.college_id
        ) t2 ON con2.contest_id = t2.contest_id
    GROUP BY 
        con2.contest_id
    ) t3 ON con.contest_id = t3.contest_id
WHERE t3.total_views + t3.total_unique_views 
        + t3.total_submissions + t3.total_accepted_submissions >0
ORDER BY 
    con.contest_id;

run code는 성공하였지만 제출할 때 시간이 너무 오래 걸려서 인지 계속 실패했고, 따라서 더 줄일 수 있을지 여부를 확인을 해보았다

우선 문제에서 주어진 전제조건으로 contes는 여러개의 college에서 후보자를 심사할 수 있으나, 각 college에서는 단 1개의 심사 공모전만 개최한다는 조건이 있었다

이게 의미상 contests, colleges, challenges까지는 다 고유한 값을 가진다고 생각 할 수 있을 것 같아 나머지 테이블은 한번에 join해주어도 위에서 언급한 중복해서 여러번 포함되는 경우를 가지지 않을 것 같다고 생각되어 서브쿼리 수를 좀 더 줄이고 한번에 시도해보았다

SELECT 
    con.contest_id, 
    con.hacker_id, 
    con.name,
    COALESCE(SUM(s.total_submissions), 0) total_submissions,
    COALESCE(SUM(s.total_accepted_submissions), 0) total_accepted_submissions,
    COALESCE(SUM(v.total_views), 0) total_views, 
    COALESCE(SUM(v.total_unique_views), 0) total_unique_views
FROM
    contests con 
LEFT JOIN
    colleges col ON con.contest_id = col.contest_id
LEFT JOIN 
    challenges ch ON col.college_id = ch.college_id
LEFT JOIN
    (
    SELECT 
        vs.challenge_id,
        SUM(vs.total_views) total_views,
        SUM(vs.total_unique_views) total_unique_views
    FROM 
        view_stats vs
    GROUP BY 
        vs.challenge_id
    ) v ON ch.challenge_id = v.challenge_id
LEFT JOIN 
    (
    SELECT 
        ss.challenge_id ,
        SUM(ss.total_submissions) total_submissions,
        SUM(ss.total_accepted_submissions) total_accepted_submissions
    FROM 
        submission_stats ss
    GROUP BY 
        ss.challenge_id
    ) s ON ch.challenge_id = s.challenge_id
GROUP BY 
    con.contest_id, con.hacker_id, con.name
HAVING 
	total_submissions + total_accepted_submissions
                + total_views + total_unique_views > 0
ORDER BY 
    con.contest_id;

줄였는데도 제출이 안된다..ㅠ

 -> 결과적으로 문제는 맞게 푼 듯한데 제출이 안되서 멈춰있는 상태이다

추가적으로 다른 사람의 쿼리를 적어주면 아래와 같다

select 
	con.contest_id, con.hacker_id, con.name, 
	sum(total_submissions),
	sum(total_accepted_submissions), 
	sum(total_views), 
	sum(total_unique_views) 
from contests con,colleges col,challenges ch, 
		(
		select 
				challenge_id,
				sum(total_views) total_views, 
				sum(total_unique_views) total_unique_views 
		from view_stats group by challenge_id) v, 
		(
		select 
			challenge_id,
			sum(total_submissions) total_submissions,
			sum(total_accepted_submissions) total_accepted_submissions 
		from Submission_Stats group by challenge_id) s 
where 
	con.contest_id=col.contest_id 
	and col.college_id=ch.college_id 
	and ch.challenge_id=v.challenge_id(+) 
	and ch.challenge_id=s.challenge_id(+) 
group by con.contest_id, con.hacker_id, con.name 
HAVING 
	sum(total_views)+ sum(total_unique_views)
	+sum(total_submissions)+sum(total_accepted_submissions)>0 
order by con.contest_id;

이건 오라클에서 한 것 인듯 하다

 

select con.contest_id,
        con.hacker_id, 
        con.name, 
        sum(total_submissions), 
        sum(total_accepted_submissions), 
        sum(total_views), sum(total_unique_views)
from contests con 
join colleges col on con.contest_id = col.contest_id 
join challenges cha on  col.college_id = cha.college_id 
left join
(select 
		challenge_id, sum(total_views) as total_views, 
		sum(total_unique_views) as total_unique_views
from view_stats group by challenge_id) vs on cha.challenge_id = vs.challenge_id 
left join
(select 
	challenge_id, sum(total_submissions) as total_submissions, 
	sum(total_accepted_submissions) as total_accepted_submissions 
from submission_stats 
group by challenge_id
) ss on cha.challenge_id = ss.challenge_id
group by con.contest_id, con.hacker_id, con.name
having sum(total_submissions)!=0 or 
       sum(total_accepted_submissions)!=0 or
       sum(total_views)!=0 or
       sum(total_unique_views)!=0
order by contest_id;

 

Draw The Triangle 1(SQL) (recursive 재귀, with, union all, repeat, 패턴 반복해서 넣기)

Draw The Triangle 1 | HackerRank

P(n)이라고 하면 ‘* ‘를 n개부터 시작하여 1개까지 표시해서 행으로 이어서 패턴 그리는 것을 의미한다고 하면 P(20)를 그리는 문제였다

WITH RECURSIVE cte AS (
    SELECT 20 AS n
    UNION ALL
    SELECT n - 1 FROM cte WHERE n > 1
)
SELECT REPEAT('* ',n) FROM cte

재귀를 사용하여 20부터 시작해서 1까지(n>1일때까지 n-1하니 1이 되면 더이상 n-1하지 않아서) 모든 결과 행 하나로 합치는 재귀의 형태이다

그런뒤 * 를 n번 반복하여 적도록 하기 위해 repeat를 사용했다

(일단 문제가 있어 하긴 했지만 SQL에서 솔직히 이런건 왜하는지 모르겠다는 생각은 들었다)

[MySQL] REPEAT()

 

Draw The Triangle 2(SQL) (recursive 재귀, with, union all, repeat, 패턴 반복해서 넣기)

Draw The Triangle 2 | HackerRank

1번 문제와 반대로 그리는 문제이다

WITH RECURSIVE cte AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM cte WHERE n < 20
)
SELECT REPEAT('* ',n) FROM cte

 

Print Prime Numbers(SQL) (recursive 재귀, with, union all, not in, where 서브쿼리, mod, group_concat)(소수 구해서 출력하기)

Print Prime Numbers | HackerRank

1000이하의 소수를 모두 나타내는데 각 소수간에 &로 연결해서 표시하는 문제이다

WITH RECURSIVE numbers AS (
    SELECT 2 AS num
    UNION ALL
    SELECT num + 1 FROM numbers WHERE num < 1000
)
SELECT 
    GROUP_CONCAT(n.num SEPARATOR '&')
FROM 
    numbers n
WHERE 
    n.num NOT IN (
            SELECT
                DISTINCT n2.num
            FROM 
                numbers n1
            JOIN 
                numbers n2 ON n1.num < n2.num
                AND MOD(n2.num, n1.num) = 0
            ORDER BY 
                n2.num
            );

우선 WITH를 통해 재귀를 사용하여 2~1000까지 행으로써 표시된 테이블을 생성해주고

그 테이블을 가져오는데 조건으로 num의 값이 서브쿼리의 목록(1열1~여러행인 테이블)에 포함되지 않는 경우에 대해서만 가져오는데 서브쿼리에 대해 설명하면 위에서 생성한 numbers 테이블을 n1과 n2로 2개 가져와서 n2>n1를 만족하고 n2를 n1으로 나눴을 때 나머지가 0인 경우에 대해서만 join해준다(1000이하에서 어떤 수의 약수인 값들을 테이블로써 출력하기 위해서) 그런뒤 중복없이 n2의 숫자들을 출력하는데 오름차순으로 정렬해주는 서브쿼리이다 이러한 조건을 만족하는 값들(모든 행)을 group_concat으로 합쳐주는데 구분자는 &를 사용하여 합쳐주는 쿼리이다

아마 EXISTS를 써서도 할 수 있을 것으로 생각되는데 지금은 굳이해바야할 까라는 생각이 들어서 나중에 복습할 때 해보면 되지 않을까 생각한다

아래의 쿼리들은 참고한 다른 사람들의 쿼리 레퍼런스이다

WITH RECURSIVE Numbers AS (
    SELECT 2 AS num
    UNION ALL
    SELECT num + 1 FROM Numbers WHERE num < 1000
)
select GROUP_CONCAT(num SEPARATOR '&') from numbers 
where num not in (
								SELECT distinct(n2.num) as rem 
								FROM Numbers n1 
								join Numbers n2 on n1.num<n2.num 
										and MOD(n2.num,n1.num)=0 order by n2.num);

 

 2/11 아이디어 추가로 적어둔 부분

 

미리 전처리 다하고 train, test 데이터 분리 고려

이상치 제거 부분 option 선택가능하게 기능 추가

(현재 그냥 한개만 범위 골라서 제거, IQR로 한번 확인하여 제거 있고

여러번 돌려서 없애는 방식 추가 고려 중

그리고 보통 제거할 때만 그전에 그 방식으로 확인시 어느정도인지 확인하는게 필요할 듯하여 굳이 함수 두개로 분리해서 따로 하지 말고 합쳐도 되지 않나 생각이 듦)

그리고 옵션 전체로 해서 어떤 옵션으로 할지 선택하면 자동으로 옵션 어떻게 선택했는지 가지고 전처리&학습&평가해서 보여주게 하면 나중에 이렇게 했을 경우는 이랬는데 이렇게 할경우 이렇다 보여주기 좋을듯

함수(옵션1)

함수(옵션2)

이런식으로 한번에도 보여 줄 수 있을 것이라 생각되는데

중간에 초기화를 어떻게 시켜줄지 등은 좀 더 고려할 필요가 있을듯함