INTERSECT, GROUP_CONCAT, 집계함수

SQL

카타

 

문제 118. 585. investment in 2016 : where 활용, intersect, 집계함수 문법

LeetCode - The World's Leading Online Programming Learning Platform

 

 

작성한  정답 

select round(sum(tiv_2016) over (), 2) as tiv_2016
from insurance
where tiv_2015 in (select tiv_2015
                   from insurance
                   group by tiv_2015
                   having count(*)>=2)
    and (lat, lon) in (select lat, lon
                       from insurance
                       group by lat, lon
                       having count(*) = 1)
limit 1;

 

 

더 깔끔한 답

select round(sum(tiv_2016), 2) as tiv_2016
from insurance
where tiv_2015 in (select tiv_2015
                   from insurance
                   group by tiv_2015
                   having count(*)>=2)
    and (lat, lon) in (select lat, lon
                       from insurance
                       group by lat, lon
                       having count(*) = 1)

 포인트

• 굳이 윈도우 함수도 limit도 쓸 필요 없음
• 집계함수의 문법에 좀 더 익숙해질 필요가 있음

 

다른 방식의 풀이

WITH temp AS(
    SELECT pid
    FROM Insurance
    WHERE tiv_2015 IN (SELECT DISTINCT tiv_2015 FROM Insurance GROUP BY tiv_2015 HAVING COUNT(tiv_2015) > 1)

    INTERSECT 

    SELECT pid
    FROM Insurance
    WHERE CONCAT(lat, lon) NOT IN (SELECT DISTINCT CONCAT(lat, lon) FROM Insurance GROUP BY CONCAT(lat, lon) HAVING COUNT(CONCAT(lat, lon)) > 1)
)

select round(sum(tiv_2016), 2) as tiv_2016
from insurance
where pid in (select * from temp)

-intercept : 교집합을 출력하는 명령어!(null도 불러오기 가능)

 

 

 

 

 

문제 120. 1667. Fix Names in a Table : 대문자/소문자 변형

LeetCode - The World's Leading Online Programming Learning Platform

 

작성한 답안

with temp as
(select user_id,
       lower(name) as name
from users)

select user_id,
       concat(upper(substr(name, 1, 1)), substr(name, 2)) as name
from temp
order by 1

해결 과정

-문제를 보자마자 '대문자와 소문자가 무질서하게 섞여 있으니 일단 전체를 소문자로 바꿔줘야겠군!'이라고 생각함

-그에 따라 전체를 우선 소문자 만들어주고, 그 뒤에 맨 앞 글자만 대문자로 만드는 과정을 거치기로 결정

 

 

더 간단한 답안

select user_id,
       concat(upper(substr(name, 1, 1)), lower(substr(name, 2))) as name
from users
order by 1

-결론 : 처음에 전체를 lower로 바꿔줄 필요 없음. 그에 따라 서브 쿼리든, with든 사용할 필요도 없었다.

 

 

문제123. null 출력

LeetCode - The World's Leading Online Programming Learning Platform

오답

select salary as SecondHighestSalary
from employee
where salary not in (select max(salary)
                    from employee)
order by salary desc
limit 1

null일 경우 출력이 안 돼서 실패

 

정답

select max(salary) as SecondHighestSalary
from employee
where salary not in (select max(salary)
                    from employee)

 

포인트(추측)

• limit의 경우 말 그대로 첫 행을 출력해줘야하는데, 그 첫 행부터가 존재하질 않으니 아무 것도 출력 되지 않음
• max의 경우 행의 갯수와 상관 없이 최댓값을 출력해줘야하나 그에 해당하는 데이터가 없음
• **사실 어느 맥락에서 limit으로 쓰면 아무 값이 안 나오고, max로 하면 null이 나오는지 대충 감은 오지만, 글로는 당장 정리를 못 하겠다ㅠ

 

 

 

문제 124. 1484. Group Sold Product By The Date : GROUP_CONCAT

LeetCode - The World's Leading Online Programming Learning Platform

SELECT
  sell_date,
  COUNT(DISTINCT product) AS num_sold,
  GROUP_CONCAT(DISTINCT product ORDER BY product) AS products
FROM Activities
GROUP BY sell_date
ORDER BY sell_date;

GROUP_CONCAT() : 그룹화한 데이터를 그대로 나열해주는 함수. GROUP BY와 같이 씀

 

 

*GROUP_CONCAT() 내 변수들 익혀둘 것 : order by, separator ' '

group_concat(distinct product order by product separator ':') as products

 

• 위와 같은 경우, 단어 사이가 ':'으로 구분됨
• 디폴트 : 쉼표(,)로 구분

 

 

문제 125. 1327. List the Products Ordered in a Period

LeetCode - The World's Leading Online Programming Learning Platform

작성한 답

with temp as
(select product_id,
       sum(unit) as unit
from orders
where order_date like '2020-02%'
group by product_id)

select p.product_name,
       unit
from temp t join products p using(product_id)
where unit>=100

 

더 간단한 답

select p.product_name,
       sum(unit) as unit
from orders o join products p using(product_id)
where order_date like '2020-02%'
group by product_id
having unit>=100

group by로 묶어준 다음 having절로 조건을 단다는 것이 포인트인데,

아직 having으로 조건을 지어줄 수 있는 범위가 계속 헷갈린다.