[SQL] SolveSQL 난이도2 문제 풀이 2 - REGEXP, DATE, STRFTIME, 열 추출

안녕하세요! 하마코입니다. 😊

 

SolveSQL 난이도2 문제 풀이를 전편에 이어서 진행해보려고 합니다!

정규표현식도 써보고, 셀프 조인도 써보고,

난이도 1보다 훨씬 더 많은 함수들과 조인을 쓰면서 풀어본 것 같아요!

 

총 7개 문제 풀이 시작하겠습니다! 

---

 

외부 사이트에 Solvesql 문제를 올릴 수 없어서 링크만 남겨놓겠습니다!

 

문제1 - 다음날도 서울숲의 미세먼지 농도는 나쁨 😢

링크 | 처음에 LEAD()로 풀었는데 실패해서 .. 셀프 조인으로 풀었다. DATE()로 '-1 day'라고 명시할 수 있는 sqlite 함수를 처음 알았다. MySQL에서는 DATEADD() 같은거로 연산을 했는데 함수가 조금씩 다른 것 같다.

SELECT
    m1.measured_at AS today,
    m2.measured_at AS next_day,
    m1.pm10,
    m2.pm10 AS next_pm10
FROM
    measurements AS m1
JOIN
    measurements AS m2
    ON
    m1.measured_at = DATE(m2.measured_at, '-1 day')
WHERE
    m2.pm10 > m1.pm10;

틀렸던 코드

SELECT
    DATE(measured_at) as today,
    DATE(measured_at, '+1 day') as next_day,
    pm10,
    LEAD(pm10) OVER (ORDER BY DATE(measured_at)) as next_pm10
FROM measurements;

 

문제2 - 제목이 모음으로 끝나지 않는 영화

링크 | 맨 마지막이 모음으로 끝나는 걸 '%[AEIOU]' 써서 like 비교하려고 했는데 실패.. like 쓸거면 성실하게 '%A' 이런식으로 모두 따로 써야했다. 그래서 REGEXP를 사용해서 다시 풀었다.

SELECT title
FROM film
WHERE
    rating IN ('R', 'NC-17')
  AND
    title NOT REGEXP ('A$|E$|I$|O$|U$');

 

문제3 - 언더스코어(_)가 포함되지 않은 데이터 찾기

링크 | 여기도 where ㅁㅁ not like '%_%'로 했는데 데이터 추출이 이상해서 REGEXP로 작성했다.

SELECT DISTINCT page_location
FROM ga
WHERE page_location not REGEXP('_')
ORDER BY page_location;

 

문제4 - 게임을 10개 이상 발매한 게임 배급사 찾기

링크 | 조인해서 Group by, Having 사용해야했던 문제! 어렵지 않게 풀었다.

SELECT c.name
FROM games g
JOIN companies c ON g.publisher_id=c.company_id
GROUP BY g.publisher_id
HAVING COUNT(g.publisher_id) >= 10;

 

문제5 - 기증품 비율 계산하기

링크 | 풀었던 문제 중 제일 뿌듯했던 문제! With로 깔끔하게 구문 분리하고 비율도 계산했다. 특히 With에서 바로 COUNT()를 뽑으면 SELECT절에서 뽑기 어렵다는 것을 저번 문제풀이로 배워서 여기서도 적용했다.

WITH gifts AS (
    SELECT *
    FROM artworks
    WHERE credit like '%gift%'
)
SELECT
    ROUND(((SELECT COUNT(*) FROM gifts) * 100.0 / COUNT(*)),3) as ratio
FROM artworks;

 

문제6 - 최대값을 가진 행 찾기

링크 | With를 2개 써야하나 했는데, 쉽게 Where절에서 or로 풀 수 있었다. 다시 풀어보기 위해 다른 분의 답도 한 번 보고 다른 날에 다시 처음부터 풀어서 맞췄다.

SELECT
    id
FROM points
WHERE x = (SELECT MAX(x) FROM points) or y = (SELECT MAX(y) FROM points)
ORDER BY id;

 

문제7 - 3년간 들어온 소장품 집계하기

링크 | 내가 제일 약한 열에서 여러개 뽑기..! 몇몇 블로그를 찾아보고 적용했다. 1) MySQL은 YEAR() 같은 함수가 있는데 sqlite는 STRFTIME()으로 뽑아야했다. 언어 여러개를 하니까 조금 헷갈린다. 2) case when 문을 통해 열을 추출하는 게 어려웠다.

SELECT
    classification,
    COUNT(CASE WHEN STRFTIME('%Y', acquisition_date) = '2014' THEN 1 END) AS '2014',
    COUNT(CASE WHEN STRFTIME('%Y', acquisition_date) = '2015' THEN 1 END) AS '2015',
    COUNT(CASE WHEN STRFTIME('%Y', acquisition_date) = '2016' THEN 1 END) AS '2016'
FROM artworks
GROUP BY 1
ORDER BY 1;