select count(*)
from ad_union_statistics a
where not exists (select 1 from ad_union_statistics where id>a.id and create_time<a.create_time-interval 30 minute)
from ad_union_statistics a
where not exists (select 1 from ad_union_statistics where id>a.id and create_time<a.create_time-interval 30 minute)
from ad_union_statistics a
where not exists (select 1 from ad_union_statistics where id>a.id and adId=a.adId and create_time<a.create_time+interval 30 minute)
INSERT INTO `ad_union_statistics` VALUES ('15', '4', '14', '2', '2011-10-14 17:30:47', '0');
INSERT INTO `ad_union_statistics` VALUES ('16', '4', '13', '2', '2011-10-17 09:50:00', '0');
INSERT INTO `ad_union_statistics` VALUES ('17', '4', '15', '2', '2011-10-17 09:50:46', '0');
INSERT INTO `ad_union_statistics` VALUES ('18', '4', '18', '2', '2011-10-17 11:40:16', '0');
INSERT INTO `ad_union_statistics` VALUES ('19', '1', '20', '2', '2011-10-20 09:20:25', '0');
INSERT INTO `ad_union_statistics` VALUES ('20', '1', '20', '2', '2011-10-20 09:49:02', '0');
INSERT INTO `ad_union_statistics` VALUES ('21', '1', '22', '2', '2011-10-20 09:49:11', '0');
INSERT INTO `ad_union_statistics` VALUES ('22', '1', '21', '2', '2011-10-20 09:49:11', '0');
INSERT INTO `ad_union_statistics` VALUES ('23', '1', '22', '2', '2011-10-20 09:59:11', '0');
INSERT INTO `ad_union_statistics` VALUES ('24', '1', '22', '2', '2011-10-20 10:49:11', '0');
INSERT INTO `ad_union_statistics` VALUES ('25', '1', '22', '2', '2011-10-20 10:49:11', '0');
AND TIMEDIFF(a.`create_time`,`create_time`)>=30*60);
-> from ad_union_statistics a
-> where not exists (select 1 from ad_union_statistics where id>a.id and adI
d=a.adId and create_time<a.create_time+interval 30 minute)
-> group by date(create_time);
+-------------------+----------+
| date(create_time) | count(*) |
+-------------------+----------+
| 2011-10-14 | 2 |
| 2011-10-17 | 3 |
| 2011-10-20 | 4 |
+-------------------+----------+
3 rows in set (0.06 sec)mysql>
AND TIMEDIFF(a.`create_time`,`create_time`)>=30*60);SELECT DATE_FORMAT(a.`create_time`,'%Y-%m-%d'),COUNT(*) FROM `ad_union_statistics` a WHERE NOT EXISTS(SELECT 1 FROM `ad_union_statistics` WHERE a.`uid`=`uid` AND a.`adId`=`adId` AND a.`appId`=a.`appId`
AND TIMEDIFF(a.`create_time`,`create_time`)>=30*60) GROUP BY DATE_FORMAT(a.`create_time`,'%Y-%m-%d') ;