시간별 MySQL 통계 방법 소개(코드 예시)

이 글은 시간별 MySQL 통계 방법(코드 예시)을 소개합니다. 필요한 참고가 될 수 있는 내용이길 바랍니다.

데이터베이스 통계를 하다 보면 연, 월, 일 단위로 데이터를 집계한 뒤 차트를 활용해 시각화 효과를 내야 하는 경우가 많습니다. echarts来制作可视化效果。

• 数据库：MySQL（推荐教程：MySQL教程）

思路

• 按照时间维度进行统计的前提是需要数据库中有保留时间信息，建议是使用MySQL自带的datetime类型来记录时间。

`timestamp` datetime DEFAULT NULL,

• 在MySQL中对于时间日期的处理的函数主要是DATE_FORMAT(date,format)。可用的参数如下

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天 （0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位

注：当涉及到按日统计是，需要使用%j，而如果使用%d, %e, %w的话，那么不同月份/周里的相同值会统计在一起。

• 涉及到获取当前时间，则可以通过now()或者sysdate()来获取。

SELECT SYSDATE() FROM DUAL;
SELECT NOW() FROM DUAL;

• 按照实际需求使用group by查询即可。

结论

需统计的表结构如下：

CREATE TABLE `apilog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(64) DEFAULT NULL,
  `action` varchar(64) DEFAULT NULL,
  `params` text,
  `result` text,
  `timestamp` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
)

• 统计时间范围内不同分类action的数量

  # 当日
  SELECT action, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`,'%j') = DATE_FORMAT(now(),'%j') ORDER BY count desc;
  # 当周
  SELECT action, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`,'%u') = DATE_FORMAT(now(),'%u') ORDER BY count desc;
  # 当月
  SELECT action, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`,'%m') = DATE_FORMAT(now(),'%m') ORDER BY count desc;
  # 当年
  SELECT action, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`,'%Y') = DATE_FORMAT(now(),'%Y') ORDER BY count desc;

• 统计某分类action的时间维度数量

  # 按日
  SELECT action, DATE_FORMAT(`timestamp`,'%j'), COUNT(id) count FROM apilog WHERE action = 'xxx' GROUP BY DATE_FORMAT(`timestamp`,'%j')
  # 按周
  SELECT action, DATE_FORMAT(`timestamp`,'%u'), COUNT(id) count FROM apilog WHERE action = 'xxx' GROUP BY DATE_FORMAT(`timestamp`,'%u')
  # 按月
  SELECT action, DATE_FORMAT(`timestamp`,'%m'), COUNT(id) count FROM apilog WHERE action = 'xxx' GROUP BY DATE_FORMAT(`timestamp`,'%m')
  # 按年
  SELECT action, DATE_FORMAT(`timestamp`,'%Y'), COUNT(id) count FROM apilog WHERE action = 'xxx' GROUP BY DATE_FORMAT(`timestamp`,'%Y')

  同时按action

  • 데이터베이스: MySQL(권장 튜토리얼: MySQL 튜토리얼)

  아이디어

  • 시간 차원에 따른 통계를 만들기 위한 전제 조건은 데이터베이스가 시간 정보를 유지해야 한다는 것입니다. 시간을 기록하려면 MySQL에 포함된 datetime 유형을 사용하는 것이 좋습니다. .
    

    # 按日
    SELECT action, DATE_FORMAT(`timestamp`,'%j'), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`,'%j')
    # 按周
    SELECT action, DATE_FORMAT(`timestamp`,'%u'), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`,'%u')
    # 按月
    SELECT action, DATE_FORMAT(`timestamp`,'%m'), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`,'%m')
    # 按年
    SELECT action, DATE_FORMAT(`timestamp`,'%Y'), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`,'%Y')

    • MySQL에서 시간과 날짜를 처리하는 주요 함수는 DATE_FORMAT(date,format)입니다. 사용 가능한 매개변수는 다음과 같습니다🎜🎜🎜 tr> 분, 값(00-59) td >

      형식	설명
      %a	약식 주 이름
      %b	약식 월 이름
      %c	월, 값
      %D	월의 일자와 영어 접두어
      %d	일, 값(00-31)
      %e	일, 값(0-31)
      %f	마이크로초
      %H	시(00-23)
      %h	시(01-12)
      %I	시간(01-12)
      %i
      %j	일(001-366)
      %k	시간(0-23)
      %l	시간(1-12)
      %M	월 이름
      %m	월, 값 ( 00-12)
      %p	오전 또는 오후
      %r	시간, 12시간(hh:mm:ss AM 또는 PM)
      %S	초(00-59)
      %s	초(00-59)
      %T	time , 24시간(hh:mm:ss)
      %U	주(00-53) 일요일은 주의 첫날입니다
      %u	주(00-53) 월요일이 주의 첫날
      %V	주(01-53) 일요일은 주의 첫 번째 날이며, %X와 함께 사용됩니다.
      %v	주(01-53) 월요일은 주의 첫 번째 날이며, %x
      %W	주 이름
      %w	요일(0=일요일, 6=토요일)
      %X	연도, 일요일은 주의 첫 번째 날, 4자리, %V와 함께 사용됨
      %x	연도, 월요일 주입니다. 첫날, 4자리, %v와 함께 사용됨
      %Y	연도, 4자리
      %y	연도, 2자리

      🎜참고: 일일 통계의 경우, %j를 사용해야 하며, %d, %e, %w를 사용하면 서로 다른 월/주에 동일한 값이 함께 계산됩니다. 🎜
      • 🎜 now() 또는 sysdate()를 통해 얻을 수 있는 현재 시간을 얻는 것과 관련됩니다. 🎜🎜🎜rrreee
        • 🎜실제 필요에 따라 쿼리하려면 group by를 사용하세요. 🎜🎜🎜

          결론

          🎜계산할 테이블 구조는 다음과 같습니다. 🎜rrreee
          • 🎜다른 카테고리 action내 통계적 시간 범위 >숫자🎜rrreee🎜
          • 🎜특정 카테고리 액션🎜rrreee🎜의 시간 차원 수를 통계합니다. 액션별 통계 및 시간 차원 same time🎜rrreee🎜🎜🎜위의 비교는 일반적으로 사용되는 시간 통계를 요약한 것입니다. 더 많은 시간 차원에 대해서는 위의 매개변수 표를 참조하여 유사한 처리를 수행할 수 있습니다. 🎜🎜🎜🎜🎜 🎜

위 내용은 시간별 MySQL 통계 방법 소개(코드 예시)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!