---是指计算下载量,对吧? ---加个日期字段(download_date) 就可以了啊, ---然后根据日期字段出查询数据啊select id, 下载地址, 名称, count(case when (TO_DAYS(NOW())-TO_DAYS(download_date))=0 then 1 else 0) as 今日下载, count(case when (TO_DAYS(NOW())-TO_DAYS(download_date))=1 then 1 else 0) as 昨日下载, count(case when (TO_DAYS(NOW())-TO_DAYS(download_date))=7 then 1 else 0) as 本周下载 from tb1 group by id, 下载地址, 名称;
id 下载地址 名称
1 aaa.rar x1
2 bbb.rar x2 比如今日有多少人下载了tab1中的aaa。rar,昨天有多少,上周多少,本月多少~~~
---加个日期字段(download_date) 就可以了啊,
---然后根据日期字段出查询数据啊select id, 下载地址, 名称,
count(case when (TO_DAYS(NOW())-TO_DAYS(download_date))=0 then 1 else 0) as 今日下载,
count(case when (TO_DAYS(NOW())-TO_DAYS(download_date))=1 then 1 else 0) as 昨日下载,
count(case when (TO_DAYS(NOW())-TO_DAYS(download_date))=7 then 1 else 0) as 本周下载
from tb1
group by id, 下载地址, 名称;
你的意思是否是这样的?
tab1
id 下载地址 名称 download_date
1 aaa.rar x1 20091109
2 bbb.rar x2 20091108我原本的想法是今日,昨日,本周,本月……下载量的积累。不是何时下载的,对不起是我说的不清楚
你这样没法实现统计的呀,一天无论下载多少只能统计出一次,我想下载一次统计数+1如果设置成id download_date
1 20091109
那么每下载一次就要往数据库中插入一次,一天100多w的下载次数的话mysql负荷太大了
mysql> create table test(
-> id int not null primary key auto_increment,
-> ddate date unique,
-> dnum int);
Query OK, 0 rows affected (0.09 sec)mysql> insert into test values(null,'2009-11-01',39),(null,'2009-11-05',80),
-> (null,now(),14);
Query OK, 3 rows affected, 1 warning (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 1mysql> insert into test values(null,now(),1)
-> on duplicate key update dnum=dnum+1;
Query OK, 2 rows affected, 1 warning (0.42 sec)mysql> select * from test;
+----+------------+------+
| id | ddate | dnum |
+----+------------+------+
| 1 | 2009-11-01 | 39 |
| 2 | 2009-11-05 | 80 |
| 3 | 2009-11-09 | 15 |
+----+------------+------+
3 rows in set (0.00 sec)
建议使用这种方法,你可以一天清理一次
100W次 insert 和 100W次UPDATE对MYSQL来说差不多。每天夜里把当天的做个汇总。然后删除昨天的记录。
tabDownCnt( id, ddate,cnt)
primary key (id,ddate)然后每次下载的时候
insert into tabDownCnt
values (1,curdate(),1)
on duplicate update cnt=cnt+1;