用于按日、周、月、年保存统计数据的数据库结构 [英] Database structure for holding statistics by day, week, month, year

问题描述

我必须按网站用户活动的天数、周数、月数和年数收集统计数据.我是数据库设计阶段，我想正确地完成这个阶段，因为它会让我的编码生活更轻松.

I have to collect statisctics by days, weeks, months and years of user activity for a site. I am the DB design stage and I wanted to do this stage properly since it will make my coding life easier.

我要做的只是在每次活动发生时将数据库中字段中的值加 1.这样我就可以每天、每周、每月和每年拉出日期.我的数据库应该如何构建?抱歉，如果这对大多数人来说是一个简单的问题.如果这种结构可以扩展以便可以分解为其他类别，那也是很棒的.

What I have to do is just simply increment the values in the fields by 1 in the DB each time an activity happens. So then I can pull up the date by each day, each week, each month and year. How should my DB be structured? Apologies if this is a simple question for most. It would also be great if this structure could be extendable so that it can be broken down by other categories.

我遇到的问题是每个月都由更多天组成，而这些日子在每个日历年都发生变化.

The bit am having trouble with is each month is made up of more days and these days change each calender year.

感谢大家的帮助或指导.

Thanks all for any help or direction.

其他信息:Linux 机器，使用 PHP 和 MySQL

Other info: Linux Machine, making use of PHP and MySQL

推荐答案

无需更新每天、每周等的计数，只需在每次活动发生时向表中插入一行，如下所示:

Instead of updating counts per day, week etc. just INSERT a row into a table each time an activity happens like this:

insert into activities (activity_date, activity_info) 
values (CURRENT_TIMESTAMP, 'whatever');

现在您的报告非常简单，例如:

Now your reports are very simple like:

select count(*) from activities
where activity_date between '2008-01-01' and '2008-01-07';

或

select YEARWEEK(`activity_date`) as theweek, count(*)
group by theweek

这篇关于用于按日、周、月、年保存统计数据的数据库结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！