SQL 데이터 집계, 순위, 조인, 뷰

 

241205 

 

 

데이터 집계 

 

SELECT 열, 집계함수

FROM 테이블

WHERE 조건

GROUP BY 열 

ORDER BY 열 ASC|DESC;

 

• sum: 전체 데이터의 합 반환. NULL 제외
• avg: 전체 데이터의 평균 반환. NULL 제외, 데이터 개수도 NULL제외한 개수
• max/min: 최대/최솟값 반환. NULL 제외  
• count: 주어진 조건에 맞는 데이터 항목 수 반환. NULL 제외  

SELECT SUM(sales) FROM product;
SELECT AVG(veg) FROM product;
SELECT MAX(sales) FROM product;
SELECT MIN(sales) FROM product;
SELECT COUNT(*) FROM product WHERE product_id = 123;

 

 

순위

 

데이터를 특정 기준으로 정렬하여 행에 순위를 부여

순위함수는 OVER () 필수 

 

• RANK(): 지정한 기준에 따라 순위 부여, 동점 있는 경우 같은 번호를 주어 순서를 건너 뜀 
• DENSE_RANK(): 지정한 기준에 따라 순위 부여, 동점 있는 경우 같은 번호를 주고, 이후 연속적으로 번호를 부여함
• ROW_NUMBER(): 행에 고유한 순위 부여, 동점 있어도 순서를 건너뛰지 않음. 순차적 번호 부여  
• NTILE(N): 데이터를 지정된 개수의 구간으로 나누고 각 행에 해당 구간 번호를 부여

 

RANK() OVER (ORDER BY sales DESC) AS rank
DENSE_RANK() OVER (ORDER BY sales DESC) AS rank
ROW_NUMBER() OVER (ORDER BY sales DESC) AS rank
NTILE(4) OVER (ORDER BY sales DESC) AS quartile

 

 

*  OVER()  순위함수나 집계함수를 사용할 때 데이터를 그룹화하지 않고 지정된 윈도우 범위 안에서 순위와 집계 값을 계산. 

 

전체 데이터에 순위를 부여함 

OVER (ORDER BY sales DESC) AS rank

 

 

 

조인함수 

 

여러 테이블을 연결하여 하나의 테이블을 만드는 방법

 

• INNER JOIN: 두 테이블에서 일치하는 행만 결합
• OUTER JOIN: 한 테이블에 있는 모든 행을 결합. 다른 테이블과 일치하는 데이터가 없으면 NULL 값을 채워서 반환
  • LEFT JOIN: 왼쪽 테이블의 모든 행 결합. 오른쪽 테이블에서 일치하는 값이 있으면 결합하고, 없으면 NULL을 반환
  • RIGHT JOIN: 오른쪽 테이블의 모든 행 결합. 왼쪽 테이블에서 일치하는 값이 있으면 결합하고, 없으면 NULL을 반환
  • FULL JOIN: 양쪽 테이블의 모든 행을 반환하며, 한 테이블에 일치하는 데이터가 없으면 NULL 값을 반환
• CROSS JOIN: 두 테이블의 카테시안 곱 계산하여 모든 가능한 조합 반환

 

SELECT e.emp_id, e.emp_name, e.dept_id, d.dept_name, e.phone, e.email
FROM employee AS e
JOIN department AS d ON e.dept_id = d.dept_id;

SELECT e.emp_id, e.emp_name, e.dept_id, d.dept_name, e.phone
FROM employee AS e
INNER JOIN department AS d ON e.dept_id = d.dept_id
WHERE e.hire_date BETWEEN '2020-01-01' AND '2020-12-31'
AND e.retire_date IS NULL;

 

 

 

뷰 

 

 

CREATE VIEW view_name AS
SELECT 열
FROM 테이블명
WHERE 조건;

 

쿼리문을 이용하여 가상 테이블을 만드는 방법. 실제로 저장되는 테이블이 아님. 

쿼리 단순화, 보안 강화, 재사용성의 장점이 존재함. 

 

CREATE VIEW top_customers AS
SELECT customer_id, customer_name, total_spent
FROM customers
WHERE total_spent > 1000;

 

생성 이후 조건을 만족하는 고객 데이터 반환하는 뷰를 테이블처럼 사용 가능 

 

SELECT * FROM top_customers;