달리기반 SQL Lv2

Lv2. 날짜별 획득 포인트 조회하기

 

point_users 테이블을 활용하여

날짜별로 획득한 포인트가 점점 늘어나는지 줄어드는지 확인하고 싶다.

그럼 날짜를 오름차순으로 정렬해야겠다.

결과물에 나와야 하는 것은 테이블에 있는 컬럼인 created_at 컬럼(아이디 생성날짜)과,

새롭게 만들어야 하는 컬럼인 average_points 컬럼이다. 이 컬럼은 유저가 획득한 날짜별 평균 포인트가 나와야 한다. 정수로 나타내라 했으니 소수점 첫째 자리에서 반올림해야겠다. decimal 함수(소수) 이용해야겠네?

그리고.. 날짜가 중복된다! 그 말은 avg() 함수를 이용해서 날짜별 평균 포인트를 구해줘야 한다는 것이다.(걷기반에서 많이 하던 거다.)

 

 

💡decimal () 함수는 숫자를 소수점 이하의 특정 자리수까지 표현할 때 사용된다.

decimal(숫자 혹은 숫자를 데이터로 가지고 있는 컬럼명, 정수로 표현할 자리수, 소수점 이하로 표현할 자리수(반올림됨))

 

decimal 함수를 적용할 특정 숫자가 정해져 있는 상황에서

총 5자리, 소수점 이하 2자리까지 표현

select decimal(123.4567, 5, 2)

 

결과는 123.46이다.

 

decimal 함수를 적용할 특정 숫자가 정해져 있지 않은 상황에서

select decimal(price, 10, 2)

 

price 컬럼의 값들을 정수는 10자리까지, 소수는 2자리까지 (3자리에서 반올림된) 표현하겠다. 라는 뜻이다.

 

근데 우리는 평균 포인트가 정수 몇 자리인지는 미리 알 수 없지 않은가..? 

그러니 그냥 정수의 자리를 최대한 크게 잡고, 소수점 이하는 필요없으니 0으로 설정하자. (소수점 이하 첫 자리에서 반올림됨)

 

💣여기까지 왔는데 문제가 발생했다!

avg() 함수와 decimal() 함수는 중첩할 수 없댄다..

 

avg 함수와 결합할 수 있는 함수를 찾아보자..

바로 round() 함수와 format() 함수이다.

 

💡round() 함수는 숫자를 소수점 이하 지정한 자릿수로 반올림할 때 사용한다.

select round(숫자, 나타낼 소수점 자릿수)

 

💡format() 함수도 숫자를 소수점 이하 지정한 자릿수로 반올림할 때 사용한다.

select format(숫자, 나타낼 소수점 자릿수)

 

이를 쿼리문에 적용해보자.

select created_at, round(avg(point),0) as 'average_points'
from point_users
group by 1

 

응? created_at이 현재 시간 분초까지 다 가지고 있어서 저걸로 그룹화했더니 같은 날짜 안에서도 싹 다 다른 그룹으로 적용된다.. 저거 시간 분초 어떻게 날려버리지?

 

이쯤돼서 힌트 1번을 쓱 열어본다.

 

아 date() 함수를 사용하면 타임스탬프(날짜와 시간정보를 표현한 데이터 형식)에서 날짜부분만 추출할 수 있구나!

date() 함수 굉장히 자주 나오니까 이참에 알아보고 가자.

 

💡date () 함수는 주어진 표현식(보통 날짜나 시간형식)을 날짜형식으로 변환한다.

 

1. 현재날짜를 date 형식으로 변환한다.

select date(now())

 

이렇게 하면 현시점의 yyyy-mm-dd 가 나온다.

 

2. 타임스탬프에서 날짜부분만 추출한다.

select date('2024-11-01 12:34:56')

 

실행하면 2024-11-01 나온다.

 

3. 테이블의 타임스탬프(값이 저장되어있는) 컬럼에서 날짜부분만 추출한다.

select date(order_date)
from orders

 

실행하면 각 주문의 날짜 부분만 반환된다.

 

그니까 우리는 3번의 상황이 적용되기 때문에 타임스탬프 형식으로 데이터가 저장되어있는 created_at 컬럼에 date() 함수를 씌워서 날짜부분만 꺼내서 그룹화해야 한다.

 

이렇게 해서 최종완성된 커리는 이러하다.

select date(created_at), round(avg(point),0) as 'average_points'
from point_users
group by 1
order by 1

 

만들어진 날짜(created_at) 별로 point의 평균을 구해줘. 평균값을 정수로 만들어주고 싶으니까 거기에 round 함수 씌워서 소수점 자리는 없애줘. 그리고 날짜별로 오름차순(오래된 날짜부터 시작)으로 정렬할게(날짜별로 포인트가 늘어나는지 줄어드는지 확인)

 

 

🤖간단할 줄 알았는데 새로운 함수도 나오고 타임스탬프가 뭔지도 몰랐어서 시간이 꽤 걸렸다.