Programing Language/Database

mysql) 이번주 또는 저번주 방문자 통계 sql 문

Jude_Song 2023. 2. 18. 00:47
728x90
반응형

- 월요일에서부터 일요일까지 기준으로 통계 SELECT SQL 

CREATE TABLE `statistics` (
  `no` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `date` timestamp NULL DEFAULT NULL,
  `value` int(11) DEFAULT NULL,
  PRIMARY KEY (`no`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;

- 이번주(월 ~ 일)

SELECT date, DATE_FORMAT(`date`, '%a') AS `week`, value AS total from statistics_table where date_format(date,'%Y-%m-%d')
    BETWEEN
        (SELECT ADDDATE(CURDATE(), - WEEKDAY(CURDATE()) + 0 ))
    AND
        (SELECT ADDDATE(CURDATE(), - WEEKDAY(CURDATE()) + 6 ))
    order by date desc

- 저번주 (월 ~ 일)

SELECT date, DATE_FORMAT(`date`, '%a') AS `week`, value AS total from statistics_table where date_format(date,'%Y-%m-%d')
    BETWEEN
        (SELECT ADDDATE(CURDATE(), - WEEKDAY(CURDATE()) - 7 ))
    AND
        (SELECT ADDDATE(CURDATE(), - WEEKDAY(CURDATE()) - 1 ))
    order by date desc

 

참고되셨으면 하단의 광고 클릭센스!!

728x90
반응형