[기본 쿼리] 집계함수, Group by/Having, Order by, Like, IsNull, Rank 등

SUM, AVG, MAX, MIN

• SUM(): 컬럼값의 합계
• AVG(): 컬럼값의 평균
• MAX(): 컬럼값의 최대값
• MIN(): 컬럼값의 최소값
  • as(alias) 활용하기 : 위 4가지 함수를 적용한 column명을 다르게 정의하고 싶을 때 사용
  → SELECT 함수(컬럼명) FROM 테이블명;
• SELECT SUM(Population) FROM city; SELECT MAX(Population) FROM city; SELECT MIN(Population) FROM city; SELECT AVG(Population) FROM city; -- as로 컬럼명 새로 지정하기 / as를 생략하고 바로 새로운 컬럼명만 써도 돌아간다. SELECT SUM(Population) as 합계, MAX(Population) as 최대, MIN(Population) as 최소, AVG(Population) as 평균 FROM city;

---

Group by

• 특정 Column을 기준으로 데이터 묶기
• 위의 SUM, AVG, MAX, MIN와 같이 잘 쓰이는 함수로 그룹화 하는데 사용

--- 각 국가별 도시 중 최대 Population 을 출력하시오.
SELECT MAX(Population) 
FROM city 
GROUP BY CountryCode;

--- 각 국가별 총 Population 과 해당 '국가코드'를 출력하시요
SELECT CountryCode, SUM(Population) 
FROM city 
GROUP BY CountryCode;

 

---

Having

• 집계함수 사용 시 WHERE절을 쓰지 않고 GROUP BY와 함께 사용 가능
• 집계함수 : count, max, min, avg, sum….

-- HAVING
/* 각 메인 카테고리별로(서브카테고리포함) 베스트 상품 갯수가 20개 이상인 판매자의
판매자별 평균할인가격, 평균할인율, 베스트 상품 갯수 출력해보기 */
SELECT main_category, provider, AVG(dis_price), AVG(discount_percent), COUNT(*)
FROM items I
JOIN ranking R ON I.item_code = R.item_code
GROUP BY main_category, provider -- 메인 카테고리별로(서브카테고리포함)/main_category와 provider를 써주어야 함
HAVING COUNT(*) >= 20; -- 베스트 상품 갯수가 20개 이상 / WHERE이 아닌, HAVING을 사용할 수 있냐를 확인하는 문제

-- WHERE
/* 'items' 테이블에서 'dis_price'가 50000 이상인 상품들 중,
각 'main_category'별 평균 'dis_price'와 'discount_percent' 출력해보기 */
SELECT main_category, AVG(dis_price), AVG(discount_percent)
FROM items I
JOIN ranking R ON I.item_code = R.item_code
WHERE dis_price >= 50000  -- HAVING으로 안되고 WHERE 사용해야 하는 문제
GROUP BY main_category;

 

 

---

Order by

• 특정 Column을 기준으로 데이터 정렬하기

/* 연습문제15
country 테이블에서 Region 별로,
IndepYear 가 1900 이상인 국가만 대상으로
평균 GNP 이 낮은 순으로
5개의 Region 만 Region 과 평균 GNP 를 출력하세요.
*/

SELECT Region, AVG(GNP) FROM country
WHERE IndepYear >= 1900
GROUP BY Region
ORDER BY AVG(GNP)
LIMIT 5;

* LIMIT : 상위 몇개의 행만 추출할 수 있음

  단, 중간에 n번째 행부터 추출하고 싶다면 LIMIT a OFFSET n으로 n번째 행부터 a개의 행을 조회할 수 있음

 

---

In

• 2개 이상 값의 목록에 해당하는 행을 추출하고 싶을 때 사용함
• WHERE절에서 or 대신 더 간단하게 쿼리를 만들 수 있음

-- WHERE절의 다중조건 입력하기
SELECT title FROM film WHERE rating = 'PG' or rating = 'G';
SELECT title FROM film WHERE (release_year = 2006 or 2007) and (rating = 'PG' or 'G');
SELECT title FROm film WHERE release_year in (2006, 2007) and rating in ('PG', 'G');

 

 

---

like

• %: 1개 이상의 문자열
• _: 1개의 문자열

SELECT * FROM file WHERE name like '%kim'
SELECT * FROM file WHERE name like '_kim'

 

 

---

IS NULL / IS NOT NULL

• 데이터값이 NULL이거나 아닌 행을 조회
• NOT(컬럼명 = ‘값’) 으로 WHERE에 넣는 함수도 존재

 

---

RANK / DENSE_RANK / ROW_NUMBER

새로운 컬럼을 반환하여 RANK를 보여줌

• RANK : 공동 2위가 있으면 그 다음은 4위가 됨
• DENSE_RANK : 공동 2위가 있어도 그 다음은 3위가 됨
• ROW_NUMBER : 공동순위를 매기지 않음. 행순위에 따라 같은 점수라도 2, 3등으로 등수를 다르게 매김
• (PARTITION BY A ORDER BY B)로 그룹별 랭킹을 부여할 수도 있음

 

 

---

IF / IFNULL / CASE

• IF(조건, 참인 결과, 거짓인 결과) : 1개의 조건만 설정할 때
• IFNULL(컬럼명, NULL일 때 결과값)
• CASE : 2개 이상의 조건을 설정할 때