[DB]데이터베이스 명령어5 (MariaDB) - 그룹함수(집합함수)

 

1. 문자열 함수:
   • CONCAT(str1, str2, ...): 문자열을 연결합니다.
   • SUBSTRING(str, pos, len): 문자열의 일부분을 추출합니다.
   • LENGTH(str): 문자열의 길이를 반환합니다.
2. 숫자 함수:
   • ROUND(x): 소수점 이하를 반올림합니다.
   • ABS(x): 숫자의 절댓값을 반환합니다.
3. 날짜 및 시간 함수:
   • NOW(): 현재 날짜와 시간을 반환합니다.
   • DATE_FORMAT(date, format): 날짜를 지정된 형식으로 포맷합니다.
4. 논리 함수:
   • IF(expr, true_value, false_value): 조건에 따라 값을 반환합니다.
   • COALESCE(val1, val2, ...): NULL이 아닌 첫 번째 값을 반환합니다.
5. 집계 함수:
   • COUNT(expr): 특정 조건을 만족하는 행의 수를 계산합니다.
   • SUM(expr): 특정 열의 합을 계산합니다.
6. 시스템 함수:
   • DATABASE(): 현재 데이터베이스의 이름을 반환합니다.
   • USER(): 현재 사용자의 이름을 반환합니다.

---

 

그룹함수(집합함수)

• 국제 표준
• 집계 함수
  • count() : 행의 갯수 (컬럼명을 사용하는 경우 null은 제외)
  •  avg() : 평균 (null인 경우 처리 필요)
  •  max() : 최대값 (null인 경우 처리 필요)
  •  min() : 최소값 (null인 경우 처리 필요)
  • sum() : 합계 (null인 경우 처리 필요)

 

집계 함수

컬럼명을 사용하는 경우 null은 제외 (따라서 *을 많이 사용!)

- count(*) 또는 count(mem_mileage)
- NULL이 존재하는 컬럼의 경우에는 null 체크를 해야함 (nvl함수)
- not null 조건인 컬럼은 제외해도 무관

 

SELECT COUNT(*) AS all_cnt,
    AVG(mem_mileage) AS all_avg,
    SUM(mem_mileage) AS all_sum,
    MAX(mem_mileage) AS mileage_max,
    MIN(mem_mileage) AS mileage_min
FROM member;

↓

SELECT COUNT(nvl(prod_mileage, 0)) AS all_cnt1,
 COUNT(*) AS all_cnt2,
    AVG(nvl(prod_mileage, 0)) AS all_avg,
    SUM(nvl(prod_mileage, 0)) AS all_sum,
    MAX(nvl(prod_mileage, 0)) AS mileage_max,
    MIN(nvl(prod_mileage, 0)) AS mileage_min
FROM prod;

 

---

그룹함수

- 일반 조건 : Where절 사용
- 그룹함수에 대한 조건 : Group By ~ Having 절 사용
- Group By절 : 그룹으로 묶고자 하는 컬럼명 or 일반함수를 처리한 컬럼 지정 // 같은 값끼리 묶어라
- Having절 : 그룹함수를 이용한 조건(비교 연산자) 사용
- 그룹 조건이 없으면 Having절은 사용안해도 됨 (Group By절만 사용 가능)
- 그룹에 대한 문제로 인식하는 방법 : '~별로 조회' 라는 개념적 용어가 사용되면 Group By를 사용
- 그룹화할 컬럼 지정(그룹화한 컬럼명 조회해주는게 예의!)
- group by에 지정한 컬럼 or 일반함수를 이용한 경우, select절에 예의상 넣어주기

 

 ● 회원 취미별로 count하기

SELECT mem_like, COUNT(nvl(mem_like,0)) AS cnt_like
FROM member
GROUP BY mem_like;

* COUNT()

 - count(*) 또는 count(컬럼명)

 - 컬럼명을 사용하는 경우 null은 제외 (따라서 *을 많이 사용!)
 - NULL이 존재하는 컬럼의 경우에는 null 체크를 해야함 (nvl함수)
 - not null 조건인 컬럼은 제외해도 무관

 

● 일반적으로 집계함수와 일반컬럼 or 일반함수가 적용된 경우에는 group by절에도 존재해야 한다.

SELECT mem_like, COUNT(nvl(mem_like,0)) AS cnt_like,
               SUM(nvl(mem_mileage, 0)) AS sum_mile 
FROM member
GROUP BY mem_like;

 

문제1

내용

1. 회원의 성별 count값을 조회

2. 조회 컬럼 : 회원성별, count값

3. 성별값 : 남자 or 여자

< 교수님 코드 >

SELECT COUNT(nvl(mem_regno2, 0)) AS cnt
FROM member
GROUP BY SUBSTRING(mem_regno2, 1, 1);

 

< 내 코드 >

SELECT if(mod(SUBSTRING(mem_regno2, 1, 1), 2) = 0, '여자', '남자') AS gender,
               COUNT(nvl(mem_regno2, 0)) AS count
FROM member
GROUP BY gender

 

문제1

 

 

 

<처리순서>

1. Select             

2. From 테이블

3. Where ~ And

4. Group By       

5. Having           

6. Select 뒤에 컬럼들

7. Order By        

 

 

 

<SQL작성>

   SELECT 컬럼들

FROM 테이블

WHERE 일반조건

AND 일반조건

GROUP BY 그룹컬럼

HAVING 그룹조건

ORDER BY 정렬할 컬럼들

 

 

 

<Group by를 사용한 경우 규칙>

- Group By절에 제시된 컬럼들은 원형그대로, 조회할 컬럼에 제시
- Select절 뒤에 컬럼 또는 일반함수를 이용해서 조회하고자 하는 컬럼들은 Group By절에 원형 그       대로 제시가 되어있어야 오류가 안남(원칙상)
- 컬럼 뒤에 별칭(as)은 Group By절에는 사용하면 오류남(원칙상)

 

 

문제2

내용

1. cart 테이블을 이용해서 회원별로 count하기

< 교수님 코드 >

SELECT cart_member, COUNT(*) AS cnt, MAX(cart_qty) AS max_qty,
 SUM(cart_qty) AS sum_qty
FROM cart
GROUP BY cart_member;

문제2

문제3

내용

1. 임의 회원이 2005년 4월 1일에 상품하나를 구매 했습니다.

2. 주문번호를 발급해야한다

3. 주문번호 : 앞자리 8자리는 주문했던 년월일, 뒷자리 5자리는 주문일 기준 1씩 증가된 값

     - 주문이 일어나면 1씩 증가된 주문번호가 부여됨
3. 신규 주문번호만 조회하기( 예상 결과값 = 2005040100004)

 

< 교수님 코드 >

SELECT max(cart_no) + 1
FROM cart
WHERE cart_no LIKE '20050401%';

기본값

문제3

문제4

내용

1. 오늘 날짜(년월일) 기준으로 주문번호를 발급하기.

2. 기존에 값이 있을 수도 있고, 없을 수도 있다.
3. 위 상황을 고려해서 sql구문을 만들어 주세요.
4. 조회컬럼 : 신규 주문번호

 

< 내 코드 >

SELECT if(substring(max(cart_no), 1 ,8) = DATE_format(now(),'%Y%m%d'),
               MAX(cart_no)+1 , concat(DATE_format(now(),'%Y%m%d'), '00001' ))
FROM cart;

 

문제4