2024-02-05

SQL코드카타

 

New Companies(SQL)(모르겠음 일단 대강 패스하고 질문해서 확인해야 할 듯)(어려웠음(분명하게 테이블 준 의도 등을 모르겠어서))

New Companies | HackerRank

각 회사별로 각 직위에 있는 사람이 몇명씩인지 출력하는 문제이다.

SELECT
    c.company_code, c.founder,
    COUNT(lm.lead_manager_code),
    COUNT(sm.senior_manager_code),
    COUNT(m.manager_code),
    COUNT(e.employee_code)
FROM 
    Company c, Lead_Manager lm, Senior_Manager sm, Manager m, Employee e
GROUP BY 
		company_code

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

SELECT
    c.company_code, c.founder,
    COUNT(lm.lead_manager_code),
    COUNT(sm.senior_manager_code),
    COUNT(m.manager_code),
    COUNT(e.employee_code)
FROM 
    Company c, Lead_Manager lm, Senior_Manager sm, Manager m, Employee e
GROUP BY 
		c.company_code

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

혹시 이런식으로도 되나 해봤는데 역시 Group by부분에서 에러가 났다

SELECT
    c.company_code, c.founder,
    COUNT(lm.lead_manager_code),
    COUNT(sm.senior_manager_code),
    COUNT(m.manager_code),
    COUNT(e.employee_code)
FROM 
    Company c
LEFT JOIN
    Lead_Manager lm ON c.company_code = lm.company_code
LEFT JOIN
    Senior_Manager sm ON c.company_code = sm.company_code
LEFT JOIN
    Manager m ON c.company_code = m.company_code
LEFT JOIN
    Employee e ON c.company_code = e.company_code
GROUP BY 
    c.company_code

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

join 많이 하는 식으로도 해봤는데 에러가 났다

SELECT
    c.company_code, c.founder,
    COUNT(DISTINCT lm.lead_manager_code),
    COUNT(DISTINCT sm.senior_manager_code),
    COUNT(DISTINCT m.manager_code),
    COUNT(DISTINCT e.employee_code)
FROM 
    Company c, Lead_Manager lm, Senior_Manager sm, Manager m, Employee e
WHERE 
    c.company_code = lm.company_code
    AND lm.lead_manager_code = sm.lead_manager_code
    AND sm.senior_manager_code = m.senior_manager_code
    AND m.manager_code = e.manager_code
GROUP BY 
    c.company_code

#동일하게 에러가 났다

select
	c.company_code, c.founder, count(distinct lm.lead_manager_code), 
	count(distinct sm.senior_manager_code), count(distinct m.manager_code), 
	count(distinct e.employee_code)
from 
    Company c
JOIN 
    Lead_Manager lm ON c.company_code = lm.company_code
JOIN 
    Senior_Manager sm 
    ON c.company_code = sm.company_code
    AND lm.lead_manager_code = sm.lead_manager_code
JOIN
    Manager m 
    ON c.company_code = m.company_code
    AND lm.lead_manager_code = m.lead_manager_code
    AND sm.senior_manager_code = m.senior_manager_code
JOIN
    Employee e
    ON c.company_code = e.company_code
    AND lm.lead_manager_code = e.lead_manager_code
    AND sm.senior_manager_code = e.senior_manager_code
    AND m.manager_code = e.manager_code
group by c.company_code
order by c.company_code;

동일해보이는 에러..

group by c.company_code, c.founder에서 c.founder 빼면 왜 안되는 것인지?

그리고 다른 테이블엔 founder자체가 없는데 어떻게 그룹짓는 것인지?

아래는 다른사람들이 한 코드인데 현재는 내가 그대로 가져다 쓴 쿼리도 같이 있다(그래서 후에 다시 더 생각해볼 예정이다)

select c.company_code, c.founder, count(distinct lm.lead_manager_code), 
count(distinct sm.senior_manager_code), count(distinct m.manager_code), 
count(distinct e.employee_code)

from Company c, Lead_Manager lm, Senior_Manager sm, Manager m, Employee e
where c.company_code = lm.company_code
    and lm.lead_manager_code = sm.lead_manager_code
    and sm.senior_manager_code = m.senior_manager_code
    and m.manager_code = e.manager_code
group by c.company_code, c.founder
order by c.company_code

일단 distinct안해주면 그냥 join하는 식으로 다세어버리기 때문에 count에 distinct를 붙여줘야하는 듯하다

select c.company_code, 
    c.founder, 
    count(distinct e.lead_manager_code), 
    count(distinct e.senior_manager_code), 
    count(distinct e.manager_code), 
    count(distinct e.employee_code)
from company c
    inner join employee e on e.company_code = c.company_code
group by c.company_code,c.founder
order by c.company_code;

SELECT cp.company_code,
       cp.founder,
       lmc.cnt_lmc,
       smc.cnt_smc,
       mc.cnt_mc,
       ec.cnt_ec
FROM Company cp LEFT JOIN (
    SELECT company_code,
           COUNT(DISTINCT lead_manager_code) AS cnt_lmc
    FROM Lead_Manager
    GROUP BY company_code
) lmc ON cp.company_code = lmc.company_code LEFT JOIN (
    SELECT company_code,
           COUNT(DISTINCT senior_manager_code) AS cnt_smc
    FROM Senior_Manager
    GROUP BY company_code
) smc ON cp.company_code = smc.company_code LEFT JOIN (
    SELECT company_code,
           COUNT(DISTINCT manager_code) AS cnt_mc
    FROM Manager
    GROUP BY company_code
) mc ON cp.company_code = mc.company_code LEFT JOIN (
    SELECT company_code,
           COUNT(DISTINCT employee_code) AS cnt_ec
    FROM Employee
    GROUP BY company_code
) ec ON cp.company_code = ec.company_code
ORDER BY cp.company_code

뭔가 실무에도 이런식으로 파악하는 일이 쓰일 것은 같은데 실제로 어떤지?

이게 어떤 방식으로 작동하는 것인지 직접 Dbeaver에서 데이터베이스 만들어서 확인해볼 필요가 있을 듯하다

데이터 추가해서 만들어주었는데 그래도 뭔가 이해가 안가는 부분이 많아서 더 오래 해봐야될 것같다

 

Weather Observation Station 20(SQL)(percent_rank, 중위값median)(드디어 새로운 의미있는 문제)

Weather Observation Station 20 | HackerRank

lat_n의 중위값(median)을 구하는 문제이다

SELECT 
    ROUND(p.lat_n,4)
FROM 
    (SELECT 
        s.lat_n, 
        PERCENT_RANK() OVER(ORDER BY s.lat_n) per
     FROM 
        station s) p
WHERE 
    p.per = 0.5

그런데 SQL에서 바로 medien구할 일이 많은지? 궁금해서 여쭤볼 예정이다

 

The Report(SQL)(case when, null 글자 표시(hr에서만 되는 것일수도), from서브쿼리, 서브쿼리와 join, join on between, order정렬문제, 다른 테이블 기준으로 적용하여 분류(세그먼트)하기)

The Report | HackerRank

grades의 내용대로 각 학생의 mark에 따라 등급을 나누고 등급순으로 내림차순, 이름으로 오름차순하는데 7등급이하인 학생은 이름을 null로 표시하는 문제이다.

미리 정렬해두고 그 테이블을 가져다 쓰는 방식으로 했을 때 쿼리이다

# How to pre-order first(미리 정렬해두고 사용하는 방식)
SELECT 
    CASE 
        WHEN g.grade >= 8 THEN s.name
        ELSE NULL
    END name,
    g.grade,
    s.marks
FROM 
    (SELECT 
        s1.name,
        s1.marks
     FROM 
        students s1
    ORDER BY 
        s1.marks DESC, s1.name) s
LEFT JOIN 
		grades g ON s.marks BETWEEN g.min_mark AND g.max_mark
ORDER BY 
		g.grade DESC, name

간단히 설명하자면 먼저 student테이블에 대해 이름과 mark데이터만 가져와서 mark로 먼저 내림차순, 이름을 오름차순 정렬을 한뒤 가져와서 grade와 s.mark가 각 grades의 min_mark와 max_mark사이에 있는 경우로 맞게 join해준다 그 뒤 이름표시에 관련해서 등급이 8이상인 경우만 이름 그대로 표시하고 아닌 경우는 null로 표시해준다 그리고 나머지 등급과 mark를 표시해주고 마지막으로 등급으로 내림차순, 이름(null로 표시해준 컬럼)으로 오름차순해준다

뭔가 먼저 정렬하고 하는 것이 비효율적인 듯하여 안하고 하는 방식으로 해보았는데 잘되었는데

아래가 그 방식으로 한 것으로 일반적인 것처럼 다하고 마지막에 정렬해주었다

SELECT 
    CASE 
        WHEN g.grade >= 8 THEN s.name
        ELSE NULL
    END name,
    g.grade,
    s.marks
FROM 
    students s
LEFT JOIN 
		grades g ON s.marks BETWEEN g.min_mark AND g.max_mark
ORDER BY 
		g.grade DESC, s.name

간단히 설명하자면 학생테이블을 그대로 위의 방식과 동일하게 join하고 이름과, 나머지 표시될 컬럼도 동일하게 해준 다음, 정렬을 등급으로 내림차순, 기존 원래 student이름으로 오름차순 해주었다

where이나 order, 서브쿼리의 경우도 그 노테이션 방식대로 작성하시는지?

전에 order select에 쓰여있어서 표시되는 컬럼에 대해서만 되었던 것 같은데 지금은 또 왜 되는 것인지?

SELECT 
    g.grade,
    s.marks
FROM 
    students s
LEFT JOIN 
    grades g ON s.marks BETWEEN g.min_mark AND g.max_mark
ORDER BY 
    g.grade DESC, s.name

오늘은 갑자기 SQL 코트카타가 어려운? 복잡한? 문제가 쭉 나왔다 그래서 생각보다 시간을 많이 소요했는데

오늘 있었던 머신러닝 개인과제해설 특강에 관련해서도 질문드릴 것도 많았고 혼자서 생각해보고 해볼만한 내용도 바빠서 오늘은 굉장히 바쁜 하루였다 머신러닝 질문은 일단 간략히 질문드릴 부분은 다 드리긴했으나 너무 간략한듯해서 현재 머신러닝 과제 했던 부분 보완해서 추가로 더 해보고 있는 중이라(정확히는 판단하기위해서 시각화하는 부분에서 잘 안보이는 것을 보기 좋게 변경하다가 시간을 많이 썻고 글을 작성하는 현재도 진행중이다) 나중에 다른 질문과 함께 정리해서 올릴 예정이다.

SQL코드카타에 대해 짧게 더 얘기하면 오늘 2,3번째문제 중 2번째는 그냥 새로운 통계적 수치를 구하는 방식을 알게되어 의미있었던 것 같고, 3번째는 다른 테이블 기준으로 적용하여 분류(세그먼트)하기를 해보았다는데 의미가 큰 것 같았다 그리고 order관련해서 좀더 생각해볼 거리가 있는 듯해서 의미가 더 있다고 생각이 든다. 하지만 1번문제는 아직 제대로 이해를 못해서 판단은 이해를 한 다음에 제대로 할 수 있을 것 같아서 패스하도록 하겠다