基于MySQL和PHP的统计数据

I'm struggling with a problem I hope some of you might be able to help me with.

我正在努力解决一个问题,希望你们中的一些人能够帮助我。

I got the following table structure for a table which contains lots of actions. I then would like to make a php script which generates some statistics.

我得到了包含许多操作的表的以下表结构。然后我想创建一个生成一些统计信息的PHP脚本。

+---------------+-------------+------+-----+-------------------+----------------+
| Field         | Type        | Null | Key | Default           | Extra          |
+---------------+-------------+------+-----+-------------------+----------------+
| id            | int(11)     | NO   | PRI | NULL              | auto_increment |
| MemberID      | int(11)     | NO   |     | NULL              |                |
| MemberNumber  | int(11)     | NO   |     | NULL              |                |
| Status        | varchar(20) | NO   |     |                   |                |
| ClubID        | int(11)     | NO   |     | NULL              |                |
| Clubtype      | varchar(5)  | NO   |     | NULL              |                |
| Time          | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
| Gender        | varchar(10) | NO   |     |                   |                |
+---------------+-------------+------+-----+-------------------+----------------+

I'm wondering if it's best to let php or mysql manipulate the data. I would like to get some stats based on Time e.g year,month,week and some stats based on Clubtype e.g FK,UK and finally some stats based on Gender e.g Male,Woman. So is it best to work with count() in the mysql queries or is it better to get all the data and the let php generate the stats.

我想知道是否最好让php或mysql操纵数据。我想得到一些基于时间的统计数据,例如年,月,周和基于Clubtype的一些统计数据,例如FK,UK,最后一些基于性别的统计数据,例如男性,女性。所以最好在mysql查询中使用count(),或者更好地获取所有数据并让php生成统计数据。

I hope this makes sense. Thx for the help :)

我希望这是有道理的。谢谢你的帮助:)

EDIT: To explain my plan further. I would like to generate for every year grouped by month and for each month I want to get the

编辑:进一步解释我的计划。我想按月分组生成,每个月我想得到的

count(Status) total

count(Status) where Clubtype = $value

计数(状态),其中Clubtype = $ value

count(Status) where Gender = $value

count(Status)其中Gender = $ value

furthermore for each month i want to get the stats grouped by week and finally I want to get the stats for each week grouped by day. These stats makes use of same count as above.

此外,对于每个月我想获得按周分组的统计数据,最后我想获得按天分组的每周的统计数据。这些统计数据使用与上述相同的计数。

I hope this gives an better idea of what i want to do.

我希望这能更好地了解我想做什么。

1 个解决方案

#1

Use MySQL. It will be able to calculate and organize things much faster due to indexing. You will have to make heavy use of GROUP BY and the math functions.

使用MySQL。由于索引,它将能够更快地计算和组织事物。您将不得不大量使用GROUP BY和数学函数。