TIL#108 MySQL

브랜디 협업 프로젝트 중 그래프 데이터를 보내줘야 하는 부분이 있어서 그 부분을 오늘 구현하였다.
최근 30일간의 총 주문건수, 총 금액을 보내줘야 하는 거였는데 일단위로 보내줘야 했다. 처음에 간단할 거라고 생각했는데 막상 해보니 전혀 아니었다...😹

---

1. orders 테이블과 order_details 테이블의 join
2. 날짜 검색 및 정렬을 위한 created_at 이 orders 테이블에만 있음
3. total_price column은 order_details 테이블에 있음
4. 날짜별로 묶어서 결과가 나와야 함
5. sql 안에서 날짜형식을 바꿔서 보내고 싶음 (ex. 20201030)
6. 날짜별로 묶은 후 금액의 총 합을 구해야 함

---

일단 지금으로부터 30일 전의 날짜를 구해서 그 값을 where절에 넣어서 조건을 걸어야 했다.

from datetime import datetime, timedelta

# 지금의 날짜와 시간 
datetime.now()
>>> datetime.datetime(2020, 11, 5, 9, 40, 47, 855375)

# timedelta -> 시간 연산할 때 이용 
# 쓸수 있는 인자 : weeks, days, hours, minutes, seconds, milliseconds, microseconds
datetime.now()-timedelta(days=30)
>>> datetime.datetime(2020, 10, 6, 9, 50, 6, 769169)

# 형태 변환 -> strftime
a = datetime.now()-timedelta(days=30)
a.strftime('%Y%m%d')
>>> '20201006'
a.strftime('%Y-%m-%d')
>>> '2020-10-06'
a.strftime('%Y-%m-%d %H:%M:%S')
>>> '2020-10-06 12:29:26'

날짜 별로 group by 를 한다.

$ SELECT DATE_FORMAT(created_at, '%Y%m%d') AS date, count(*) AS cnt FROM orders GROUP BY DATE_FORMAT(created_at, '%Y%m%d') ORDER BY date;

DATE_FORMAT 날짜의 형식을 바꿔준다.

$ SELECT DATE_FORMAT (created_at, '%Y%m%d') AS date, count(*) AS cnt FROM orders WHERE created_at > 20201006 GROUP BY DATE_FORMAT(created_at, '%Y%m%d') ORDER BY date; 

-> 2020년 10월 6일 이후로 만들어진 orders 날짜, 총 개수의 값들이 나온다.

$ SELECT DATE_FORMAT(b.created_at, '%Y%m%d') AS date, CAST(sum(a.total_price) as signed) AS price FROM order_details a JOIN orders b ON b.id=a.order_id WHERE b.created_at > 20201006 GROUP BY date ORDER BY date;

-> orders 테이블과 order_details 테이블을 join하여 날짜로 묶어서 날짜, 결제금액의 합을 구한다. sum 함수의 결과가 decimal 형태로 나온다는 것을 이번에 알았고, type을 바꾸기 위해 convert 와 cast as int 를 써봤지만 타입이 계속 변하지 않았다. mysql은 cast as signed 가 int 형 변환이라는 걸 알게 되었고 실행하니 타입이 바뀌어 결과가 잘 나왔다🙂

아직은 sql이 어려워서 group by, join 에 대해서 많이 공부해야 할 것 같다📝