[프로그래머스 SQL/DB 스터디 1기 3주차] TIL

1. GROUP BY / HAVING 절

🙄 문제

주문에서 상태(status)별 주문수를 검색하세요.

단, 주문일(orderDate)의 7일 내에 배송(shippedDate)이 이루어진 주문만 주문수에 포함합니다.

출력 컬럼은 status, 주문수 순으로 합니다.

 

⛔ 나의 코드 

SELECT status , COUNT(*) 주문수
FROM orders
GROUP BY status 
HAVING (shippedDate - orderDate) < 7;

정답 SELECT 절을 작성하지 않아 오답 처리되었다. 

 

✅ 피드벡 

SELECT status , COUNT(orderNo) 주문수
FROM orders
WHERE shippedDate <= orderDate + INTERVAL 7 DAY
GROUP BY status; 

-- 해설 : INTERVAL을 사용하지 않은 경우
SELECT status, shippedDate, orderDate,shippedDate - orderDate AS diff
FROM orders
ORDER BY diff DESC;
-- 2003-12-26	2003-10-22	204
-- 200312026 - 20031022 이렇게 정수로 계산되어서 해당 diff에서 204 가 출력된다.

-- 또 다른 정답
SELECT status , COUNT(orderNo) 주문수
FROM orders
WHERE TIMESTAMPDIFF(DAY, orderDate, shippedDate) < 7 
GROUP BY status; 
-- TIMESTAMPDIFF(unit, begin, end) 함수
-- 두 날짜형 값의 차이를 주어진 단위로 계산한다. / (end – begin)

---

🙄 문제 

주문에 포함된 상품수의 평균, 그리고 주문에 포함된 모든 상품 주문액 합계의 평균을 계산하세요.

출력 컬럼은 주문수, 상품수의 평균, 주문액 합계의 평균 순으로 합니다.

단, 주문에 포함된 각 상품의 주문액은 주문단가(priceEach)와 주문개수(quantity)의 곱으로 계산합니다.

또한, 상품수의 평균과 주문액 합계의 평균은 모두 소수 세째자리에서 반올림하여 소수 둘째자리까지 구합니다.

 

⛔ 나의 코드 

-- 작성코드 1
WITH temp AS (
	SELECT COUNT(*) 주문수, productCode
	FROM products
	GROUP BY productLine
)
SELECT SUM(주문수) 주문수, ROUND(AVG(quantity), 2) 
	'상품수의 평균', ROUND(AVG(priceEach * quantity),2) '주문액 합계의 평균'
FROM orderdetails , temp
where temp.productCode = orderdetails.productCode
GROUP BY orderNo;

-- 작성코드 2
SELECT COUNT(*) 주문수, ROUND(AVG(quantity), 2) 
	'상품수의 평균', ROUND((SUM(priceEach * quantity)/AVG(quantity)),2) '주문액 합계의 평균'
FROM orderdetails
GROUP BY orderNo;

주문수, 상품수, 주문액 합계의 평균에 대한 의미를 제대로 파악하기 어려웠다. @.@

 

✅ 피드벡 

WITH temp AS
(
	SELECT	orderNo, COUNT(productCode) 상품수, SUM(priceEach * quantity) 주문액햡계
	FROM	orderDetails
	GROUP 	BY orderNo
)
SELECT	COUNT(*) 주문수, ROUND(AVG(상품수),2) '상품수의 평균', 
	ROUND(AVG(주문액합계),2) '주문액합계의 평균'
FROM	temp;

---

2. ORDER BY 절

🙄 문제

상품라인(productLine) 별로 평균 구매단가(buyPrice)와 평균 권장소비자가(MSRP)를 검색하세요.

출력 컬럼은 productLine, 평균 구매단가, 평균 권장소비자가 순으로 합니다.

이 때 결과는 평균 구매단가와 평균 권장소비자가의 내림차순으로 정렬합니다.

 

⛔ 나의 코드 

SELECT productLine, AVG(buyPrice) '평균 구매단가', AVG(MSRP) '평균 권장소비자가'
FROM products
GROUP BY productLine
ORDER BY '평균 구매단가' DESC, '평균 권장소비자가' DESC;

 

✅ 피드벡 

SELECT	productLine, AVG(buyPrice) '평균 구매단가', AVG(MSRP) '평균 권장소비자가'
FROM	products
GROUP 	BY productLine
ORDER	BY 2 DESC, 3 DESC;
-- 출력 컬럼 번호로 대체하여 작성 가능하다.

---

3. LIMIT 절

🙄 문제

권장소비자가(MSRP)가 가장 큰 25개 상품을 검색하세요.

단, MSRP는 소수점 이하를 버리고 비교 및 출력하세요.

출력 컬럼은 행번호, productCode, name, MSRP 순으로 합니다.

이 때 결과는 MSRP의 내림차순, name의 오름차순으로 정렬합니다.

 

⛔ 나의 코드 

WITH temp AS
(
	SELECT productCode, name, TRUNCATE(MSRP, 0) MSRP
	FROM products
    ORDER BY MSRP DESC, name
    LIMIT 25
)
SELECT ROW_NUMBER() OVER (ORDER BY MSRP DESC) AS rowNo, productCode, name, MSRP
FROM temp;

 

✅ 피드벡 

SELECT ROW_NUMBER() OVER (ORDER BY TRUNCATE(MSRP, 0) DESC, name) AS rowNo, 
productCode, name, TRUNCATE(MSRP, 0) MSRP
FROM products
LIMIT 25;

-- 불필요한 코드작성을 제거한 정답 코드

---

4. 단일행 내장함수

🙄 문제

scale이 가장 큰 상품을 검색 검색하세요.

출력 컬럼은 productCode, name, scale 순으로 합니다.

결과는 scale의 내림차순, name의 오름차순으로 정렬합니다.

 

⛔ 나의 코드 

WITH temp AS (
	SELECT productCode, name, scale, CAST(SUBSTRING(scale, 3, 5) AS SIGNED), 
		RANK() OVER (ORDER BY CAST(SUBSTRING(scale, 3, 5) AS SIGNED)) AS scaleRank
    FROM products
)
SELECT productCode, name, scale
FROM temp
WHERE scaleRank = 1
ORDER BY scale DESC, name;

-- scale : 정확히 어떤 수치를 의미하는지 모르겠다.

 

✅ 피드벡 

SELECT	productCode, name, scale
FROM 	products
ORDER 	BY CAST(SUBSTRING(scale, LOCATE(':', scale) + 1, 10) AS UNSIGNED) DESC, name;

---

🙄 문제

오늘부터 1달 안에 생일(birthDate)을 맞을 직원을 검색하세요. (오늘이 09월 14일이면, 10월 13일까지 포함함)

출력 컬럼은 성명, birthDate 순으로 합니다.

단, 성명은 firstName과 lastName으로 구성하며, 사이에 공백 문자(space)가 하나 들어갑니다.

이때 결과는 생일의 오름차순으로 정렬합니다.

 

⛔ 나의 코드 

SELECT *
FROM employees;

SELECT DATE(SYSDATE()), MONTH(SYSDATE()), DAY(SYSDATE());

SELECT CONCAT(firstName, ' ', lastName) 성명, birthDate
FROM employees
WHERE (MONTH(SYSDATE()) = MONTH(birthDate) AND DAY(SYSDATE()) <= DAY(birthDate)) 
	OR (MONTH(SYSDATE())+1 = MONTH(birthDate) AND DAY(SYSDATE()) > DAY(birthDate)) 
ORDER BY birthDate;

 

✅ 피드벡 

SELECT	CONCAT(firstName, ' ', lastName) 성명, birthDate
FROM	employees
WHERE	( MONTH(NOW()) = MONTH(birthDate) AND DAY(NOW()) <= DAY(birthDate) ) OR
        ( MONTH(NOW() + INTERVAL 1 MONTH) = MONTH(birthDate) 
        AND DAY(NOW() + INTERVAL 1 MONTH) > DAY(birthDate) )
ORDER   BY birthDate;

-- MONTH 가 같은 경우 
-- MONTH 가 다른 경우 
-- 나눠서 알고리즘을 구상해야 한다.

---

 

🙄 문제

'Germany'에 있는 고객을 검색하세요.

출력 컬럼은 name, country, 담당직원(salesRepId) 순으로 합니다.

단, 담당직원이 없는 고객의 경우, 직원아이디 1165인 직원을 판매담당 직원으로 출력하세요.

출력 컬럼은 name, country, salesRepId 순으로 합니다.

이때 결과는 name의 오름차순으로 정렬합니다.

 

⛔ 나의 코드 

SELECT *
FROM customers;

SELECT name, country, 
	CASE 
		WHEN salesRepId IS NULL THEN 1165
        ELSE salesRepId
    END AS 담당직원
FROM customers
WHERE country = 'Germany'
ORDER BY name;

 

✅ 피드벡 

SELECT	name, country, COALESCE(salesRepId, 1165) salesRepId
FROM	customers
WHERE	country = 'Germany'
ORDER   BY name; 

-- COALESCE() 함수 활용하면 더 간결한 코드 작성 가능

 

---

🙄 문제

직원 이메일에서 아이디, 회사명, 도메인명을 분리하세요.

예를 들어, id@company.com에서 아이디는 id, 회사명은 company, 도메인명은 com입니다.

출력 컬럼은 직원의 성명, email, 아이디, 회사명, 도메인명 순으로 합니다.

성명은 firstName과 lastName으로 구성되며, 사이에 공백 문자(space)가 하나 들어갑니다.

결과는 성명의 오름차순으로 정렬합니다.

 

⛔ 나의 코드 

SELECT CONCAT(firstName, ' ', lastName) 성명, email, REGEXP_SUBSTR(email, '[^@]+') 아이디,
	REGEXP_SUBSTR(REGEXP_SUBSTR(email, '[^@]+', 1, 2), '[^\\.]+') 회사명, 
    'com' 도메인명	-- 원래는 이렇게 작성하면 안된다.
FROM employees
ORDER BY 성명;

 

✅ 피드벡 

SELECT	CONCAT(firstName, ' ', lastName) 성명, email,
		REGEXP_SUBSTR(email, '[^@]+') 아이디,
        REGEXP_SUBSTR(REGEXP_SUBSTR(email, '[^@]+', 1, 2), '[^\\.]+') 회사명,
        REGEXP_SUBSTR(email, '[^\\.]+', 1, 2) 도메인명
FROM 	employees
ORDER  	BY 1;

-- REGEXP_SUBSTR(expr, pattern[, pos[, occurrence]]) : 
-- exp)의 문자열 내에 정규식 pattern과 일치하는 부분 문자열을 리턴함. 
-- pos는 검사의 시작 위치, occurrence는 일치하는 몇 번째 부분 문자열인지를 나타냄.

---

프로그래머스 스쿨 [스터디/1기] 학교 밖에서 듣는 전공필수 SQL/DB Essential 바로가기

[스터디/1기] 학교 밖에서 듣는 전공필수 SQL/DB Essentials