SQL Aggregate 함수 = SUM, COUNT, AVG... AVG, COUNT는 null은 세지 않음
COUNT
--------------------------------------------------------------------------
SELECT COUNT(*) FROM Products
>>> 프로덕트의 행들, 데이터 레코드의 개수 세기, null 값 포함
SELECT COUNT(Price) FROM Products AS COUNT(Price)
>>> 특정 컬럼의 행 갯수
SELECT COUNT(DISTINCT Price) FROM Products AS COUNT(Price)
>>> 중복값은 제외하고 셀 때 + null 값 미포함
SUM, AVG(null 계산에서 제외처리됨 )
--------------------------------------------------------------------------
SELECT SUM(Price) FROM Products
SELECT AVG(Price) FROM Products
평균 구할 때, null 값을 0으로 처리해주고 싶을 때
--------------------------------------------------------------------------
SELECT SUM(Price)/COUNT(*) FROM Products
>>> 전체 로우 수로 나누기
최대값, 최소값
--------------------------------------------------------------------------
SELECT MAX(Price) FROM Products
SELECT MIN(Price) FROM Products
SELECT COUNT(Price), MAX(Price), MIN(Price) FROM Products
--------------------------------------------------------------------------
공급자 기준, 평균가격
--------------------------------------------------------------------------
SELECT SupplierID, AVG(Price) FROM Products
GROUP BY SupplierID
+ 기준 1개 추가
--------------------------------------------------------------------------
SELECT SupplierID, CategoryID, AVG(Price) AS AVG_Price FROM Products
GROUP BY SupplierID, CategoryID
더 나은 코드 스타일 + 코드 가독성
--------------------------------------------------------------------------
SELECT SupplierID
, CategoryID
, AVG(Price) AS AVG_Price
FROM Products
GROUP BY SupplierID, CategoryID
오름차순 기준 정렬, Sorting
--------------------------------------------------------------------------
SELECT SupplierID
, CategoryID
, AVG(Price) AS AVG_Price
FROM Products
GROUP BY SupplierID, CategoryID
ORDER BY AVG(Price)
내림차순 기준 정렬
--------------------------------------------------------------------------
SELECT SupplierID
, CategoryID
, AVG(Price) AS AVG_Price
FROM Products
GROUP BY SupplierID, CategoryID
ORDER BY AVG(Price) DESC
집계 함수가 SELECT 문에 들어가도 상관없음
--------------------------------------------------------------------------
SELECT SupplierID
, CategoryID
, AVG(Price) AS AVG_Price
FROM Products
WHERE 은 GROUP BY 이전에
HAVING 은 이후에
--------------------------------------------------------------------------
100불 이상 평균 가격 내림차순 정렬
SELECT SupplierID
, CategoryID
, AVG(Price) AS AVG_Price
FROM Products
GROUP BY SupplierID, CategoryID
HAVING AVG_Price >= 100
ORDER BY AVG(Price) DESC
Alias 활용
--------------------------------------------------------------------------
SELECT SupplierID
, CategoryID
, AVG(Price) AS avg_price
FROM Products
GROUP BY SupplierID, CategoryID
HAVING avg_price >= 100
소수점 처리
--------------------------------------------------------------------------
CEIL() >>> 올림
FLOOR() >>> 내림
ROUND() >>> 반올림
CASE
--------------------------------------------------------------------------
SELECT CASE
WHEN categoryid = 1 THEN '음료'
WHEN categoryid = 2 THEN '조미료'
ELSE '기타'
END AS 'category name', *
FROM Products
AND 조건 1개 추가
--------------------------------------------------------------------------
SELECT CASE
WHEN categoryid = 1 AND supplierID = 2 THEN '음료'
WHEN categoryid = 2 THEN '조미료'
ELSE '기타'
END AS 'category name', *
FROM Products
CASE + GROUP BY
--------------------------------------------------------------------------
SELECT CASE
WHEN categoryid = 1 AND supplierID = 2 THEN '음료'
WHEN categoryid = 2 THEN '소스'
ELSE '기타'
END AS 'NEW_CATEGORY', *
FROM Products
GROUP BY NEW_CATEGORY
--------------------------------------------------------------------------
SELECT
CASE
WHEN categoryid = 1 AND supplierID = 2 THEN '음료'
WHEN categoryid = 2 THEN '소스'
ELSE '기타'
END AS 'NEW_CATEGORY', AVG(Price) >>> 반올림 포함하는
FROM Products
GROUP BY NEW_CATEGORY
WHEN절의 순서 중요,
정삼각형 데이터 분리된 후 다음 케이스 분리하는 방식
--------------------------------------------------------------------------
SELECT
CASE
WHEN A=B AND B=C THEN 'Equilateral'
WHEN A+B<=C OR A+C<=B OR B+C<=A THEN 'Not A Triangle'
WHEN A=B OR A=C OR B=C THEN 'Isosceles'
ELSE 'Scalene'
END
FROM TRIANGLES
행렬 역변환, 데이터 피벗팅
--------------------------------------------------------------------------
SELECT
CASE
WHEN CategoryID = 1 THEN price
ELSE NULL
END AS Category1_price, *
FROM Products
SELECT
CASE
WHEN CategoryID = 1 THEN price
ELSE NULL
END AS Category1_price,
CASE
WHEN CategoryID = 2 THEN price
ELSE NULL
END AS Category2_price,
CASE
WHEN CategoryID = 3 THEN price
ELSE NULL
END AS Category3_price
FROM Products
>>> CASE문에 다른 함수 추가할 때, CASE부터 END까지 괄호 씌워야
SELECT
AVG(CASE
WHEN CategoryID = 1 THEN price
ELSE NULL
END) AS Category1_price,
AVG(CASE
WHEN CategoryID = 2 THEN price
ELSE NULL
END) AS Category2_price
FROM Products
Category1_price Category2_price
37.979166666666664 23.0625
차이 확인하기
SELECT CategoryID, AVG(Price)
FROM Products
GROUP BY CategoryID
CategoryID AVG(Price)
1 39.2
2 40.3
-------------------------------------------------------------------------
INNER 조인 - 교집합
--------------------------------------------------------------------------
SELECT *
FROM Users
INNER JOIN Orders ON Users.ID = Orders.userID
JOIN Orders >>> 붙일 테이블
ON Users.ID = Orders.userID
>>> Users의 ID컬럼과 Orders의 userID 컬럼이 같을때,
이것을 기준으로 해서 조인, 조인이 되는 기준 컬럼
SELECT * FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
SELECT * FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
INNER JOIN Shippers ON Orders.ShippersID = Shippers.ShippersID
이너 중첩
--------------------------------------------------------------------------
SELECT * FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
LEFT JOIN - Null 값이 포함될 수 있음.
--------------------------------------------------------------------------
SELECT *
FROM Users >>> 왼쪽에 두고 싶은 테이블
LEFT JOIN Orders ON Users.Id = Orders.UserId >>> User.Id 기준으로
>>> 오른쪽에 두고 싶은 테이블
LEFT JOIN - 한번도 주문하지 않은 고객들
--------------------------------------------------------------------------
SELECT * FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE OrderID is null