MY_SQL 많이 쓰는 구문 정리 - 2) 집계함수 (GROUP BY) /DISTINCT

** 해당 글의 실습은 https://www.w3schools.com/mysql/trymysql.asp?filename=trysql_select_all

MySQL Tryit Editor v1.0

해당 글은 https://www.youtube.com/channel/UC2nkWbaJt1KQDi2r2XclzTQ 유튜브를 시청하며 공부한 내용을

정리한 글입니다.

 

1) 집계함수 (GROUP BY)

1) 기본 사용법

-> 기본 사용법은 GROUP BY 뒤에 그륩할 COLUMNS을 작성해준다. 여러개도 가능함.

Pandas에서 자주쓰는 아래의 groupby의 구문과 동일하게 생각하면 GROUP BY 뒤에 오는 것은 groupby의 형태와 동일하며, SELECT 문에 사용하는 것은 transform 안에 들어가는 집계함수와 동일하다고 생가할 수 있음.

### GROUP BY 에서 사용하는 것과 groupby 에서 사용하는 값이 같으며,
### SELECT문에 사용 시에 집계함수 (MIN / MAX/ COUNT 등)과 괕이 아래의 transform안에 들어가는 
### 집계함수와 동일하게 사용함.
group = df.groupby(['Country', 'City'])['Country'].transform('min')

 

SELECT Country,City,CONCAT_WS(" - ",Country,City)
,COUNT(Country)
,COUNT(City)
, COUNT(CONCAT_WS(" - ",Country,City)) 
FROM Customers GROUP BY Country;

 

 

2) 사용예시

-> 각 PRODUCT ID 마다 Quantity의 SUM / MAX / MIN / MEDIAN / COUNT를 계산함

이 때 GROUP BY는 ProductID로 사용 내가 실제로 합쳐서 사용할 COLUMNS ( Transform) 할 columns에다가

집계함수를 적용한다.

-- 각 PRODUCT ID 마다 Quantity의 SUM / MAX / MIN / MEDIAN / COUNT를 계산함
SELECT ProductID,
SUM(Quantity),
MAX(Quantity),
MIN(Quantity),
TRUNCATE((MAX(Quantity) - MIN(Quantity))/2,1) AS MEDIAN,
COUNT(Quantity)
FROM OrderDetails
GROUP BY ProductID;

 

3) ROLL UP (<- 잘안사용하니깐 예시만)

SELECT
  Country, COUNT(*)
FROM Suppliers
GROUP BY Country
WITH ROLLUP;

 

 

 

4) HAVING 

->  GROUP BY로 한 값에 조건을 추가할 때 사용하며, WHERE의 경우는 GROUP BY 전의 조건을 HAVING은 GROUP BY후의 조건을 나타낸다. 

 

* 사용예시 

-> OrderData가 말일에서 10일 이내의 것들만 GROUP 하여 갯수를 구하고, DATEDIFF의 평균을 계산함.
   1) GROUP BY 하기 전에 TIMEDIFF를 구해서 10일 이내의 것들만 WHERE 조건문 추가함
   2) GROUP BY로 OrderDate를 그륩하여 AVG를 통해 평균 적으로 말일 까지 얼마나 남아 있는지를 계산함

   3) ORDER BY를 사용하여 정렬

-- OrderData가 말일에서 10일 이내의 것들만 GROUP 하여 갯수를 구하고, DATEDIFF의 평균을 계산함.
-- 1) GROUP BY 하기 전에 TIMEDIFF를 구해서 10일 이내의 것들만 WHERE 조건문 추가함
-- 2) GROUP BY로 OrderDate를 그륩하여 AVG를 통해 평균 적으로 말일 까지 얼마나 남아 있는지를 계산함

SELECT OrderDate,
LAST_DAY(OrderDate),
DATEDIFF(LAST_DAY(OrderDate),OrderDate) AS DATE_DIFF,
TRUNCATE(AVG(DATEDIFF(LAST_DAY(OrderDate),OrderDate)),1) AS AVG_DATE_DIFF,
COUNT(OrderDate)
FROM Orders
WHERE DATEDIFF(LAST_DAY(OrderDate),OrderDate) <=10
GROUP BY OrderDate
HAVING AVG_DATE_DIFF >=0
ORDER BY AVG_DATE_DIFF DESC;

 

 

-> CategoryID가 2 보다 큰 항목들을 그륩하여 MAX/ MIN / MEDIAN / AVG 가격을 구하고

    HAVING을 통하여 필터링을 함.

SELECT
  CategoryID,
  MAX(Price) AS MaxPrice, 
  MIN(Price) AS MinPrice,
  TRUNCATE((MAX(Price) + MIN(Price)) / 2, 2) AS MedianPrice,
  TRUNCATE(AVG(Price), 2) AS AveragePrice
FROM Products
WHERE CategoryID > 2
GROUP BY CategoryID
HAVING
  AveragePrice BETWEEN 20 AND 30
  AND MedianPrice < 40;

 

 

5) DISTINCT - 중복된 값을 제거함.

-> GROUP BY가 여러개의 중복을 제거하는 것과 같은 기능을 하므로, DISTINCT를 사용할 때의 결과와 동일함.

그러나 DISTINCT는

1) 집계 함수가 사용되지 않으며.

2) GROUP BY와 다르게 정렬을 하지 않으므로 더 빠르다고 함.

 

-> Pandas에서의 아래 unique()와 동일하다고 생각됨.

### pandas
df['CategoryID'].unique() : 중복을 제거한 값들의 LIST를 return
df['CategoryID'].nunique() : 중복을 제거한 값들의 갯수를 return

 

### UNIQUE()와 동일 ####
SELECT DISTINCT CategoryID
FROM Products;


### NUNIQUE()와 동일 ####
SELECT COUNT(DISTINCT CategoryID)
FROM Products;

SELECT
  Country,
  COUNT(DISTINCT CITY)
FROM Customers
GROUP BY Country;