아무리 사소하더라도 배움이 없는 날은 없다.
SQL 문제풀이 복습
Leetcode
문제 링크
where절 안에 not in 조건을 넣고
서브쿼리로 해결하면 간단한 문제.
SELECT seller_name
FROM Seller s
LEFT JOIN Orders o
ON s.seller_id = o.seller_id
WHERE s.seller_id NOT IN (SELECT DISTINCT seller_id
FROM Orders
WHERE Year(sale_date) = '2020')
ORDER BY 1;문제 링크
recursive CTE를 써서 풀어야 하는 문제.
id가 1부터 시작해야 하므로 cte를 짤 때 min(customer_id)로 시작하면 안됨.
WITH recursive cte
AS
(
SELECT 1 AS "id"
FROM Customers
UNION ALL
SELECT id+1
FROM cte
WHERE id <
(
SELECT max(customer_id)
FROM Customers))
SELECT DISTINCT id AS "ids"
FROM cte
LEFT JOIN Customers c
ON cte.id = c.customer_id
WHERE c.customer_id IS NULL;문제 링크
cross join 후에 where절로 조건만 잘 주면 어렵지 않은 문제.
SELECT a.student_name AS "member_A",
b.student_name AS "member_B",
c.student_name AS "member_C"
FROM SchoolA a,
SchoolB b,
SchoolC c
WHERE a.student_name <> b.student_name
AND b.student_name <> c.student_name
AND a.student_name <> c.student_name
AND a.student_id <> b.student_id
AND b.student_id <> c.student_id
AND a.student_id <> c.student_id;오늘부터 프로그래머스 문제도 추가로 풀면서
하루 중 SQL을 다루는 볼륨을 높일 예정.
프로그래머스
문제 링크
Leetcode를 쓸 때에 비해 프로그래머스의 단점
조금만 쿼리가 복잡해져도 코드 실행 자체가 안 된다;
그래서 거꾸로 생각해보면 장점이 머릿속으로 생각을 많이 해야 한다는 것.
recursive CTE를 써야 하는 level5짜리 문제.
WITH RECURSIVE cte
AS
(
SELECT id,
1 AS generation
FROM ECOLI_DATA
WHERE parent_id IS NULL
UNION ALL
SELECT e.id,
generation + 1
FROM cte
JOIN ECOLI_DATA e
ON cte.id = e.parent_id )
SELECT COUNT(*) AS "COUNT",
cte.generation
FROM cte
LEFT JOIN ECOLI_DATA children
ON cte.id = children.parent_id
WHERE children.id IS NULL
GROUP BY 2
ORDER BY 2;정답 쿼리를 단계별로 쪼개보면,
-
먼저 1세대, 즉 위로 부모가 없는 대장균의 id를 찾는다. 여기까지의 연산 결과가 cte에 저장된다.
SELECT id, 1 AS generation FROM ECOLI_DATA WHERE parent_id IS NULL -
그 다음 이 1세대의 id를 부모 id(parent_id)로 갖는 경우를 찾는다. join의 조건은 id = parent_id가 되고, 이 조건에 해당하는 놈들은 2세대이므로 generation+1을 하면 된다.
SELECT e.id, generation + 1 FROM cte JOIN ECOLI_DATA e ON cte.id = e.parent_id -
그리고 이 연산은 더 이상 자식 세대가 안 나타날 때까지 계속 반복되어야 한다. 따라서 1번과 2번의 결과를 union all로 이어주고 반복 연산하라는 뜻에서 recursive cte로 지정해 준다.
WITH RECURSIVE cte AS ( SELECT id, 1 AS generation FROM ECOLI_DATA WHERE parent_id IS NULL UNION ALL SELECT e.id, generation + 1 FROM cte JOIN ECOLI_DATA e ON cte.id = e.parent_id ) -
여기까지의 연산 결과를 실행해 보면 다음과 같다. 각각의 id별로 세대가 잘 분리된 것을 확인할 수 있다.
-
이제 CTE의 결과를 ECOLI_DATA 테이블과 다시 join해서 각 세대별로 자식이 없는 경우를 찾은 후 숫자를 세어 주면 끝.
SELECT COUNT(*) AS "COUNT", cte.generation FROM cte LEFT JOIN ECOLI_DATA children ON cte.id = children.parent_id WHERE children.id IS NULL GROUP BY 2 ORDER BY 2;
프로그래머스 문제를 오랜만에 푸니까
Leetcode랑 UI며 문제 스타일이 너무 달라서 적응이 잘 안된다ㅎㅎ
문제 링크
1트(6/27) 때는 불필요하게 cte를 만들어서 풀었는데
사실 recursive를 쓰는 게 아니라 그냥 '3세대'의 id 찾는 정도라면
그렇게까지 복잡하게 할 필요는 없다.
1세대부터 출발하면 되므로
where절에 e1.parent_id is null을 건 다음
단순히 join을 두 번만 수행하면
출력 결과 마지막 쪽에 3세대의 id가 뜬다.
SELECT e3.id
FROM ECOLI_DATA e1
JOIN ECOLI_DATA e2
ON e1.id = e2.parent_id
JOIN ECOLI_DATA e3
ON e2.id = e3.parent_id
WHERE e1.parent_id IS NULL
ORDER BY 1;문제 링크
window함수 중 percent_rank를 쓰면 간단히 해결되는 문제.
본 쿼리에서 case when을 쓸 때도 between을 쓰면
경계에 걸리는 애매한 값들을 제대로 처리하지 못해 오답이 뜨는 경우가 있으므로,
아예 <= 0.25, <= 0.5 이런 식으로 구분해 주는 게 좋다.
SELECT id,
CASE
WHEN percentile <= 0.25 THEN "CRITICAL"
WHEN percentile <= 0.5 THEN "HIGH"
WHEN percentile <= 0.75 THEN "MEDIUM"
WHEN percentile > 0.75 THEN "LOW"
END AS "COLONY_NAME"
FROM (SELECT id,
size_of_colony,
Percent_rank()
OVER(
ORDER BY size_of_colony DESC) AS "percentile"
FROM ECOLI_DATA) a
ORDER BY 1;문제 링크
오랜만에 만난 비트 연산 문제.
Leetcode에서는 한 번도 만난 적이 없는데,
유독 프로그래머스에서는 이렇게 비트 연산에 관한 부분을 물어올 때가 있다.
비트 연산자(&)
숫자형 컬럼 2개를 &로 연결하면
두 개의 정수 값을 비트(이진수) 단위로 비교하여
양쪽 모두 1이 있을 때에만 1을 반환한다. 예를 들어,
3은 이진수로 011(2)이고
5는 이진수로 101(2)이므로
일의 자리에서만 1이 겹친다. 따라서 3 & 5를 연산하면
이진수로는 001, 즉 십진수로는 1이 나온다.
비트 연산자는 where절, join절, having절 등
숫자형 연산이 들어갈 수 있는 곳이면 어디든 다 쓸 수 있다.
따라서 문제의 조건대로
부모의 형질을 모두 갖고 있는 경우를 찾으려면
(자식의 형질 & 부모의 형질) <- 공통 형질
이놈이 부모의 형질과 같은지 여부를 where절 내에서 검증하면 된다.
SELECT e2.id AS "ID",
e2.genotype AS "GENOTYPE",
e1.genotype AS "PARENT_GENOTYPE"
FROM ECOLI_DATA e1
JOIN ECOLI_DATA e2
ON e1.id = e2.parent_id
WHERE ( e1.genotype & e2.genotype ) = e1.genotype
ORDER BY 1;문제 링크
위에서 언급한 비트 연산을 쓰는 또 다른 문제.
먼저 2번 형질(이진수로 나타내면 10(2)이 된다)을 갖고 있지 않아야 하므로
비트 연산을 붙여봤을 때 0이 나와야 한다.
select
*
from ECOLI_DATA
where GENOTYPE & 2 = 0그리고 다시 이 중에서, 1번이나 3번 형질을 보유해야 하므로
비트 연산을 붙여봤을 때 결과가 1이 나오는 것들을 or 조건으로 연결하면 된다.
이 때 주의해야 할 것은, 3번 형질의 보유 여부를 확인하기 위해
genotype & 3으로 연결하면 안 된다는 점이다.
(그리고 이게 비트 연산자 활용이 헷갈리는 이유이기도 함)
즉, 100(2)와 붙여봐서 1이 들어와야
아무 불순물도 끼어있지 않고 3번 형질만을 갖고 있는 경우를 체크할 수 있는 것.
genotype & 3으로 연결하면 3의 이진수는 011(2)이므로,
1번 형질과 2번 형질을 동시에 갖고 있느냐를 확인하겠다는 의미가 되어
문제에서 요구한 것과는 아예 다른 결과를 도출하게 된다.
비트 연산자를 쓸 일이
다른 문제에서도, 어쩌면 실무에서도 많이 없을 수 있겠지만
개념은 헷갈리지 않게 잘 정리해 둘 것.
SELECT Count(*) AS "COUNT"
FROM ECOLI_DATA
WHERE ( genotype & 2 = 0 )
AND ( genotype & 1 > 0
OR genotype & 4 > 0 );