[ MySql ] 주단위 데이터 조회

참고 링크 

https://recoderr.tistory.com/52

MYSQL 쿼리문 통계(일간/주간/월간/기간별), DATE_FORMAT() 알아보기

 

 

 

 

내가 만든 쿼리 

# 주간통계
SELECT min(start_time) AS 'start_time', max(end_time) AS 'end_time' , date_time , sum(count) AS total_count  FROM (
# SELECT *, sum(count), min(start_time), max(end_time) FROM (
	SELECT DATE_FORMAT(DATE_SUB(`agree_time`, INTERVAL (DAYOFWEEK(`agree_time`)-1) DAY), '%Y-%m-%d') AS start_time,
    	   DATE_FORMAT(DATE_SUB(`agree_time`, INTERVAL (DAYOFWEEK(`agree_time`)-7) DAY), '%Y-%m-%d') AS end_time,
	       DATE_FORMAT(`agree_time`, '%Y%U') AS `date_time`,
    	   count(*) AS count, 
	       product_option_seq_conetns
	FROM (
		SELECT * FROM inf_product_contents GROUP BY product_option_seq_conetns
	) `prod`
	WHERE agree_time BETWEEN '2022-01-01 00:00:00' AND '2023-01-31 23:59:59'
	GROUP BY  agree_time 
) `prod_2` 
GROUP BY  date_time ;