[SQL] MySQL 날짜 함수/연산 정리

MySQL 날짜 함수/연산 정리

 

 

 

1. 날짜 추출

• NOW(), CURDATE(), CURTIME() : 현재 날짜/시간, 날짜, 시간 추출
• DATE(), YEAR(), MONTH(), DAY(), WEEK(), DAYNAME(): 날짜, 연도, 월, 일, 주, 요일 추출
• DATE_FORMAT(col, format): format대로 추출 ex) '%Y-%m-%d'

 

2. 날짜 연산

• DATEDIFF(d1, d2) : d1 - d2
• DATE_SUB(d, INTERVAL 30 DAY) : 30일 전 날짜 계산
• LAST_DAY(d) : 해당 월의 마지막 날 계산 
• TIMESTAMPDIFF(MONTH, start, end) : 연, 월, 일 지정해서 차이 계산

 

3. 특정 날짜 / 패턴 찾기

 

월별 주문 건수)

SELECT DATE_FORMAT(order_date, '%Y-%m') AS months, COUNT(*) AS order_cnt
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')

 

요일별 주문 건수)

SELECT DAYNAME(order_date) as weekday, COUNT(*) as cnt
FROM orders
GROUP BY DAYNAME(order_date)

 

주차별 매출 합계)

SELECT YEARWEEK(order_date) as year_week, SUM(sales) as total_sales
FROM orders
GROUP BY YEARWEEK(order_date)

 

고객별 첫 구매일)

SELECT customer_id, MIN(order_date) as first_order_date
FROM orders
GROUP BY customer_id

 

고객별 구매 간격)

SELECT customer_id, DATEDIFF(MAX(order_date), MIN(order_date))as active_days
FROM orders
GROUP BY customer_id

 

연속 3일 이상 구매한 고객)

SELECT DISTINCT customer_id
FROM (SELECT customer_id, order_date,
		LAG(order, 2) OVER (PARTITION BY customer_id ORDER BY order_date) AS lag2, 
		LAG(order, 1) OVER (PARTITION BY customer_id ORDER BY order_date) AS lag1
	  FROM orders) C
WHERE DATEDIFF(order_date, lag1) = 1 
	  AND DATEDIFF(lag1, lag2) = 1

 

4. 증감률 / 결측 등 응용 

월별 전월 대비 매출 증감률)

-- 월별 전월대비 매출 증감률
SELECT month, sales, LAG(total_sales,1) OVER (ORDER BY month) AS prev_sales,
	ROUND((total_sales - LAG(total_sales,1) OVER (ORDER BY month))/LAG(total_sales,1) OVER (ORDER BY month) *100) AS growth_rate 
FROM (SELECT DATE_FORMAT(order_date, '%Y-%m') as month, SUM(sales) AS total_sales
	FROM orders
	GROUP BY DATE_FORMAT(order_date, '%Y-%m'))T

 

고객별 가장 많이 구매한 요일)

SELECT customer_id, weekday
FROM (SELECT customer_id, DAYNAME(order_date) as weekday, COUNT(*) AS cnt,
			RANK() OVER (PARTITION BY customer_id ORDER BY COUNT(*)) as rank
	FROM orders
	GROUP BY customer_id, DAYNAME(order_date)) t
WHERE rank = 1;

 

특정 기간동안 구매가 없는 고객)

SELECT
FROM customer c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
	AND o.order_date BETWEEN '2025-01-01' AND '2025-03-31'
WHERE o.customer_id IS NULL 
-- o.customer_id가 null이라는 건 order이 하나도 없다는 뜻