241128 TIL_데이터 분석 과정(SQL - 집계함수, GROUP BY, HAVING, LAND, ORDER BY)

1.집계함수: 여러 행에서 단일 결과값을 반환하는 함수

-  MIN, MAX는 숫자뿐만 아니라 날짜 데이터에도 사용 

- 대부분의 집계함수는 NULL값을 제외하고 작동. / 예외) COUNT(*)는 NULL을 포함한다. 

 

함수	컬럼
COUNT	데이터의 행 수를 반환
COUNT(DISTINCT 컬럼)	고유한 값을 갖는 데이터의 행 수 반환
SUM	열의 값을 모두 더함
AVG	열 값의 평균 계산
MIN	열 값 중 최소값 반환
MAX	열 값 중 최대값 반환

 

-- 데이터의 행 수 세기
SELECT COUNT(*) AS row_count
FROM employees;

-- NULL 포함여부에 따른  COUNT 차이
SELECT COUNT(*) AS total_rows, -- NULL 포함
       COUNT(salary) AS non_null_salaries -- NULL 제외
FROM employees;

-- 부서별 고유한 직원 수 계산
SELECT department, COUNT(DISTINCT employee_id) AS unique_employees
FROM employees
GROUP BY department;

-- 특정 열의 값 합산
SELECT SUM(salary) AS total_salary
FROM employees;

-- 열 값의 평균 구하기
SELECT AVG(salary) AS average_salary
FROM employees;

-- 열 값 중 최대값과 최소값 찾기
SELECT MAX(age) AS max_age,
       MIN(age) AS min_age
FROM employees;

-- 날짜 데이터를 활용한 MIN/MAX
SELECT MIN(hire_date) AS earliest_hire,
       MAX(hire_date) AS latest_hire
FROM employees;

-- 여러 집계함수 동시 사용
SELECT COUNT(*) AS row_count,
       AVG(salary) AS average_salary,
       MAX(age) AS max_age,
       MIN(age) AS min_age,
       SUM(salary) AS total_salary
FROM employees;

 

 

2. GROUP BY, HAVING

(1) GROUP BY: 데이터를 특정 기준으로 그룹화하여 요약 정보를 생성함 

(2) HAVING: 그룹화된 데이터에서 조건을 추가로 필터링

HAVING 절은 GROUP BY로 그룹화된 데이터에 조건을 추가로 적용할 때 사용됩니다.

SELECT 
	기준열1,
	기준열2,
	기준열3, 
	집계함수(열)
FROM 테이블
GROUP BY 기준열1, 기준열2, 기준열3;

Figure1. 집계함수 예

 

위의 사진과 같이 그룹핑 된 상태에서 함수가 적용된다. 

 

SELECT 절에는 GROUP BY에 포함된 기준 열과 집계 함수만 사용할 수 있다. 

집계 함수를 제외한 다른 컬럼들은 모두 GROUP BY에 포함되어야 한다. ⚠️

 

SQL 실행순서
FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY →LIMIT

SQL 코딩순서
SELECT → FROM →JOIN → ON → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

-- 그룹화된 결과
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

 

 

❗ 조건절 분류 

• WHERE
  • 그룹화 이전에 데이터를 필터링
  • 개별 행을 기준으로 조건을 적용
• HAVING
  • 그룹화 이후에 데이터를 필터링, 즉 그룹화된 결과를 기준으로 조건을 적용
• 차이 요약
• 데이터 → WHERE (개별 행 필터링) → GROUP BY (그룹화) → HAVING (그룹화된 결과 필터링)

 

4. ORDER BY 

ORDER BY는 데이터를 원하는 순서대로 정렬. 기본은 오름차순

• 오름차순(ASC) 또는 내림차순(DESC) 옵션을 사용해 정렬 기준을 설정
• 여러 열을 기준으로 정렬할 수도 있으며, 각 열에 대해 오름차순 또는 내림차순을 개별적으로 설정
• 컬럼의 이름뿐만 아니라 컬럼 번호를 사용해 데이터를 정렬할 수 있다. 이 방법은 단순한 정렬 조건일 때 코드의 가독성을 높이는 데 유용하다. 단, 컬럼 번호는 코드 간결성을 높이는 데 유용하지만, 컬럼 순서가 변경되면 쿼리 수정이 필요할 수 있으므로 사용 시 주의해야 한다. 

5. LAND()

RAND 함수는 0 이상 1 미만의 난수(예: 0.456)를 생성한다. ORDER BY와 함께 사용하면 각 행마다 랜덤한 값을 생성한 후 이를 기준으로 정렬한다. 이후 LIMIT으로 원하는 갯수만큼 샘플링하는 효과를 얻을 수 있다. 

 

한계

1. 성능 저하: 데이터셋이 클수록 처리 시간이 급격히 증가한다.
2. 메모리 사용 증가: 전체 데이터셋을 메모리에 로드하여 정렬하기 때문에 대규모 데이터에서 정렬시 메모리 사용량이 증가한다.

→ 💡 권장: 10만 행 이하의 데이터셋에서 사용

 

 

 

6. 조건에 따라 포맷을 다르게 변경해야한다면 (IF, CASE)

(1) IF

IF 문은 원하는 조건에 충족할 때 적용할 방법과 아닌 방법을 지정해 줄 수 있음

if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)

 

(2) CASE

조건을 지정하다보면, 두 개 이상 지정을 해야 할 경우가 생긴다. 이 때는 case 문을 이용하여 여러번의 if 문을 적용한 효과를 줄 수 있다. 

 

- 조건문을 닫을 때는 end 사용 

- 그 외는 else 

case when 조건1 then 값(수식)1
     when 조건2 then 값(수식)2
     else 값(수식)3
end

 

코드스니펫:

• 음식 단가, 음식 종류 별로 음식점 그룹 나누기(가격 = 5000, 15000, 그 이상)
• (Korean = 한식 Japanese, Chinese, Thai, Vietnamese, Indian = 아시아식 그외 = 기타)

select restaurant_name,
       price/quantity "단가",
       cuisine_type,
       order_id,
       case when (price/quantity <5000) and cuisine_type='Korean' then '한식1'
            when (price/quantity between 5000 and 15000) and cuisine_type='Korean' then '한식2'
            when (price/quantity > 15000) and cuisine_type='Korean' then '한식3'
            when (price/quantity <5000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식1'
            when (price/quantity between 5000 and 15000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식2'
            when (price/quantity > 15000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식3'
            when (price/quantity <5000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타1'
            when (price/quantity between 5000 and 15000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타2'
            when (price/quantity > 15000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타3' end "식당 그룹"
from food_orders

 

(3) 조건문을 사용할 수 있는 경우 

• 새로운 카테고리 만들기
  • 음식 타입과 같이 새로운 카테고리를 만들 수 있다.
    • 한국 음식, 아시아 음식, 미국 음식, 유럽 음식 이런 식의 새로운 cuisine_category 를 생성 가능
  • 고객들의 분류도 만들 수 있다.
    • 10대 여성, 10대 남성, 20대 여성, 20대 남성 등, 이런 식의 성별과 나이별로 새로운 고객 군 카테고리를 생성할 수 있음. 
• 연산식을 적용할 조건 지정하기
  • 수수료를 계산할 때 카드와 현금 사용을 나눈다. 
  • 현금일 때의 수수료율과 카드일 때의 수수료율이 다르다면, 연산식을 만들 때 if 문 혹은 case 문으로 각각 다른 수수료율 혹은 수수료 계산 방식을 적용할 수 있다
• 다른 문법 안에서 적용하기
  • if, case 문 안에 다른 문법이나 연산을 넣을 수도 있지만, 다른 문법 안에 조건문을 넣을 수도 있다
  • 예를 들어 concat 문으로 여러 컬럼을 합칠 때, rating 이 있을 때는 rating 을 넣어주고 없을 때는 아무것도 넣지 않도록, concat 안에 if 문을 넣어줄 수 있다. 

 

7. cast

에러메시지에 data type이라고 뜨면 문자와 숫자가 구분되어  있지 않다는 의미이다. 따라서 문자, 숫자를 혼합하여 함수에 사용 할 때에는 데이터 타입을 변경해주어야 한다

--숫자로 변경
cast(if(rating='Not given', '1', rating) as decimal) 

--문자로 변경
concat(restaurant_name, '-', cast(order_id as char))

 

---

 

고등학생 때 이후로 이렇게 길게 공부를 해본 건 처음이다.. 눈도 아프고, 허리도 아프다. 그래도 집중력이 좋지 않은 내가 SQL 강의들을 주차별로 완강하고 있는 걸보니 뿌듯하다. 

12시간을 매일 공부하는 것은 쉬운 일이 아니구나를 느낀다...이 자세라면 뭐라도 할 것 같다. 

 

내일은 대망의 첫 팀 과제물 발표가 있는 날인데, 긴장하지 말고 잘 해내야겠다.