[2주차] 24.01.15 월요일

SQL

 

/*
[group by {column_name | expr | position}]
특정 컬럼에 대해 동일한 값을 갖는 행동을 하나의 행으로 처리하는 것
통계 작업에 보편적으로 사용된다
*/

 

USE sqldb;

-- 사용자별 구매 총액을(sum) 확인하고 싶다면?

SELECT		userID,	-- group by절에 명세된 컬럼은 select절에 정의가 가능하다
		SUM(price * amount) AS 'total_price'
FROM		buytbl
GROUP BY	userID
ORDER BY	total_price DESC;

-- 사용자별 평균 구매 개수를 확인하고 싶다면?

SELECT		userID,
		AVG(amount)
FROM		buytbl
GROUP BY	userID;

 

USE encore;

-- 부서번호가 50번이거나 부서가 없는 사원의 이름, 급여를 출력하되 급여가 많은 사원부터 조회하겠다
SELECT		emp_name,
		salary
FROM		employee
WHERE		dept_id = 50 || dept_id IS NULL
ORDER BY	salary DESC;

-- 사원테이블에서 입사일이 2003년 이후인 사원의 이름, 입사일, 부서를 조회
-- 부서번호를 기준으로 내림차순 정렬
-- 부서번호가 동일하면 입사일을 기준으로 오름차순 정렬
-- 입사일이 동일하면 이름을 기준으로 오름차순 정렬

SELECT		emp_name, hire_date, dept_id
FROM		employee
WHERE 		YEAR(hire_date) > 2003
ORDER BY	dept_id DESC, hire_date ASC, emp_name ASC; -- 기준 컬럼을 열거할 수 있다

-- 부서별 평균 급여를 조회하고 싶다면?

SELECT		dept_id,
		ROUND(AVG(salary), -4) -- 정수자리 4자리에 맞춰 반올림
FROM		employee
GROUP BY	dept_id
ORDER BY	2 desc;

-- 성별에 따른 평균 급여를 조회하고 싶다면?

SELECT		case
			when SUBSTRING(emp_no, 8, 1) = 1 then '남'
			when SUBSTRING(emp_no, 8, 1) = 2 then '여'
		END AS '성별',
		ROUND(AVG(salary), -4) AS '급여'
FROM		employee
GROUP BY	성별
ORDER BY	급여 DESC;

 

-- 서브쿼리(쿼리를 포함하는 쿼리)
SELECT		*,
		MIN(salary)
FROM		employee;

SELECT		*
FROM		employee
WHERE		salary = (SELECT MIN(salary) FROM employee);

USE sqldb;

SELECT		*
FROM		usertbl;

-- 키가 가장 작은 사원과 가장 큰 사원의 정보를 조회하고 싶다면?
SELECT		*
FROM		usertbl
WHERE		height = (SELECT MIN(height) FROM usertbl) || height = (SELECT MAX(height) FROM usertbl);

 

-- having : 그룹에 대한 조건
-- where : 테이블에 대한 조건. where절은 그룹함수를 사용할 수 없다.

USE encore;

-- 부서별 급여 총합이 9000000 이상인 부서만 조회
SELECT		dept_id,
		SUM(salary)
FROM		employee
GROUP BY	dept_id;

SELECT		dept_id,
		SUM(salary)
FROM		employee
GROUP BY	dept_id
HAVING 		SUM(salary) >= 9000000;

/* -- error
where절에는 그룹함수를 사용할 수 없다
SELECT		dept_id,
		SUM(salary)
FROM		employee
WHERE 		SUM(salary) >= 9000000
GROUP BY	dept_id;
*/

USE sqldb;

/* -- error
SELECT		userID AS '사용자',
		SUM(price * amount) AS '총 구매액'
FROM		buytbl
WHERE		SUM(price * amount) > 100
GROUP BY	userID;
*/

SELECT		userID AS '사용자',
		SUM(price * amount) AS '총 구매액'
FROM		buytbl
GROUP BY	userID
HAVING		SUM(price * amount) > 100;

 

-- rollup() 함수 : 중간 합계와 총합을 출력. group by 절에 가장 먼저 작성한 컬럼을 기준으로 삼는다.

SELECT		*
FROM		buytbl;

-- 구매한 목록 중 그룹이름별로 구매비용을 조회하고 싶다면?
SELECT		num,
		groupName,
		SUM(price * amount) AS '구매비용'
FROM		buytbl
GROUP BY	groupName, num
WITH ROLLUP;

SELECT		groupName,	-- 중간합계는 출력되지 않고 누적 총합만 출력된다.
		SUM(price * amount) AS '구매비용'
FROM		buytbl
GROUP BY	groupName
WITH ROLLUP;

 

-- 순위함수 : 결과에 순번 또는 순위를 정의하는 함수(rank(), row_number(), dense_rank() etc.....)
-- row_number() : 행이 만들어진 순번(order by에 의한 순서)

SELECT		*
FROM		usertbl;

SELECT		*
FROM		usertbl
ORDER BY	height DESC;

SELECT		ROW_NUMBER() OVER(ORDER BY height DESC, NAME ASC) AS '행 번호',
		NAME,
		addr,
		height
FROM		usertbl;

-- 지역별로 키 큰 순위를 정의하고 싶다면?

SELECT		ROW_NUMBER() OVER(PARTITION BY addr ORDER BY height DESC, NAME ASC) AS '행 번호',
		NAME,
		addr,
		height
FROM		usertbl;

-- dense_rank() : 동률이 있는 경우 1개로 간주하고 다음 등수를 매기는 함수

SELECT		DENSE_RANK() OVER(ORDER BY height DESC) AS '행 번호',
		NAME,
		addr,
		height
FROM		usertbl;

-- rank() : 동률이 있는 경우 그 수만큼 다음 등수

SELECT		RANK() OVER(ORDER BY height DESC) AS '행 번호',
		NAME,
		addr,
		height
FROM		usertbl;