1. 오늘 학습 키워드
Date, str_to_date, date_format, trim, ifnull, coalesce
2. 학습한 내용
✅ TRIM()
- 기능: 문자열의 양쪽 끝 공백을 제거
- 사용 예시: TRIM(' hello ') → 'hello'
- 활용:
- 공백이 입력된 데이터를 깔끔하게 정리할 때 사용
- 예: TRIM(날짜) = '' → 값이 공백만 있는 경우를 확인 가능
- 주의: NULL은 공백이 아니기 때문에 TRIM(NULL)은 여전히 NULL
✅ IFNULL(값1, 값2)
- 기능: 값1이 NULL일 경우 값2를 반환
- 사용 예시
- IFNULL(NULL, '대체값') → '대체값'
- IFNULL('데이터', '대체값') → '데이터'
✅ COALESCE(값1, 값2, 값3, ...)
- 기능: 여러 개의 값을 순서대로 살펴서,첫 번째로 NULL이 아닌 값을 반환하는 함수
- 한 행(row) 안에서 여러 컬럼 중에서 값을 찾을 때 사용
- 예시
테이블에 이런 데이터가 있을 때,
| id | nickname | user_name |
|---|---|---|
| 1 | NULL | alice |
| 2 | bob | robert |
SELECT id, COALESCE(nickname, user_name) AS display_name
FROM users;
| id | display_name |
|---|---|
| 1 | alice |
| 2 | bob |
여기서 COALESCE(nickname, user_name)는
nickname이 NULL이 아니면 nickname 값을
nickname이 NULL이면 user_name 값을
하나만 반환해서 display_name으로 보여줌
- 사용 예시: COALESCE(NULL, NULL, '값3') → '값3'
- COALESCE('A', 'B', 'C') → 'A'
- 장점: IFNULL()보다 더 많은 인자를 받을 수 있음
✅ 날짜 관련 함수들의 차이
| 함수 | 목적 | 결과 타입 | 주 용도 |
|---|---|---|---|
DATE() | 날짜에서 날짜만 추출 | DATE | DATETIME에서 '연-월-일' 추출 |
STR_TO_DATE() | 문자열을 날짜로 변환 | DATE | '2024-05-21' → 날짜 |
DATE_FORMAT() | 날짜를 문자열로 포맷팅 | VARCHAR | '2024년 5월 21일' 같은 출력 만들기 |
FORMAT() | 숫자/날짜를 포맷된 문자열로 | VARCHAR | 소수점 자리 지정, 천단위 콤마 등 |
CAST() | 데이터 타입 변환 | 변환된 타입 | 숫자 ↔ 문자열, 문자열 ↔ 날짜 등 |
3. 학습하며 겪었던 문제점
문제1 - JOIN 활용
조건1) 알맞은 join 방식을 사용하여 users 테이블을 기준으로, payment 테이블을 조인해주세요.
조건2) case when 구문을 사용하여 결제를 한 유저와 결제를 하지 않은 게임계정을 구분해주시고, 컬럼이름을 gb로 지정해주세요.
조건3) gb를 기준으로 게임계정수를 추출해주세요. 컬럼 이름은 usercnt로 지정해주시고, 결과값은 아래와 같아야 합니다.
select case when p.pay_type is not null then '결제함'
else '결제안함' end gb,
count(distinct u.game_account_id) usercnt
from marketer_sql_users u left join marketer_sql_payment p
on u.game_account_id=p.game_account_id
group by gb;
문제2 - JOIN 응용1
조건1) users 테이블에서 서버번호가 2 이상인 데이터와 payment 테이블에서 결제방식이 CARD 모두를 만족하는 경우를 알맞은 방식으로 join 해 주세요. payment 테이블의 매출 금액이 중복되는 것을 방지하기 위해 모든 값을 고유하게 추출해야 합니다.
조건2) 조인한 결과를 바탕으로 users 테이블의 game_account_id 를 기준으로 game_actor_id수를 중복값없이 세고 컬럼 이름을 actor_cnt로 지정해주세요. 또한 pay_amount 값을 더해주시고, 컬럼 이름을 sumamount로 지정해주세요.
조건3) having 을 사용하지 않고, 인라인 뷰 subquery 사용으로 actor_cnt수가 2 이상인 경우만 추출해주세요. 그리고 sumamount를 기준으로 내림차순 정렬해주세요.
결과값은 아래와 같아야 합니다. 전체결과 중 일부입니다.
select game_account_id,
actor_cnt,
sumamount
from
(select users.game_account_id,
count(distinct users.game_actor_id) actor_cnt,
sum(payment.pay_amount) sumamount
from
(select
from marketer_sql_users
where serverno>=2) users left join
(SELECT
from marketer_sql_payment
where pay_type='card') payment on users.game_account_id=payment.game_account_id
group by users.game_account_id
) a
where actor_cnt>=2
order by sumamount desc;
문제3 - JOIN 응용2
조건1) user 테이블에서 game_account_id, first_login_date, serverno 를 추출한 결과와
조건2) payment 테이블에서 game_account_id 별 가장 마지막 결제일자를 찾고 그 컬럼이름을 date2로 지정해주세요. 그 다음 inner join 을 진행해주세요. 다만, 첫 접속일자보다 마지막 결제일자가 큰 경우만 추출해주세요.
조건3) 조인 결과를 바탕으로 마지막 결제일자-첫 접속일자 를 구해주세요. 그리고 컬럼이름을 diffdate로 설정해주세요. 두 날짜의 형식은 같아야 합니다.
조건4) 인라인 뷰 subquery 를 이용하여 서버별 평균 diffdate를 구해주시고, 컬럼이름을avgdiffdate로 설정해주세요. 해당컬럼은 정수 형태로 출력되어야 합니다.
조건5) 조건절에 diffdate 값이 10일 이상인 경우를 필터링해주세요. 그리고 서버번호를 기준으로 내림차순 정렬해주세요. 결과값은 아래와 같아야 합니다. 전체결과 중 일부입니다.
힌트) 소수점을 반올림해주는 round 함수를 활용해주세요!
SELECT serverno,
ROUND(AVG(diffdate), 0) AS avgdiffdate
FROM (
SELECT users.game_account_id,
users.first_login_date,
users.serverno,
date(max(date(payment.approved_at))) AS date2,
datediff(date(max(date(payment.approved_at))), users.first_login_date) as diffdate
FROM marketer_sql_users users
INNER JOIN marketer_sql_payment payment
ON users.game_account_id = payment.game_account_id
group by 1,2,3
having DATE(max(date(payment.approved_at)))> date(users.first_login_date)
)a
where diffdate >=10
group by serverno
ORDER BY serverno DESC;
4. 오늘의 인사이트
나를 가장 괴롭게 했던 3번 문제...
처음에는 날짜가 문자열임을 고려하지 않고 그냥 냅다 산수 - 를 적용해서 결과값이 다르게 나왔고, 두번째로는 datediff(1,2)을 적용해서 풀었지만 1에서 2값이 빠짐을 고려하지 않고 순서를 바꿔서 작성해서 결과값이 다르게 나왔다.
그리고 where 절에 집계함수를 집어넣는 실수를 자꾸 했다.
WHERE은 집계 전에 개별 행을 대상으로 필터링하기 때문에, 집계 결과 조건은 적용할 수 없고 그러므로 집계 결과로 필터링할 땐 HAVING을 써야 한다는 것을 알면서도 자꾸 잘못 쓴걸 보면 습득이 제대로 되지 않았기 때문이겠지... ㅠㅠ
where, group by, having 순서의 작동원리를 제대로 알고, 꾸준히 문제를 푸는 것이 더욱!!! 필요할 것 같다.
5. 내일 학습 할 일 정리
파이썬 진도좀 나가보자 ㅠㅠㅠ
