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

问题描述

我必须按网站的用户活动的日，周，月和年来收集统计数据。我是DB设计阶段，我想正确地做这个阶段，因为它将使我的编码生活更轻松。

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。所以我可以每天，每周，每个月和每年提高日期。我的DB应该如何结构化？道歉，如果这是一个简单的问题，大多数。如果这个结构可以扩展，以便可以按其他类别分解，这也是很好的。

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.

有麻烦的位是每个月由

感谢所有人的帮助或指导。

Thanks all for any help or direction.

其他信息：Linux Machine，使用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屋！