我有两张表:Log(id,user,action,date)和ActionTypes(action,type)。给定一个动作A0和一个类型T0,我想要计算每个用户在使用A0后立即使用其他动作Ai的次数,但跳过不属于类型T0的Log动作。例如:
Log:
因此,针对操作“start”和类型“1”,答案将是:
Log:
id user action date
----------------------------------------
1 mary start 2012-07-16 08:00:00
2 mary open 2012-07-16 09:00:00
3 john start 2012-07-16 09:00:00
4 mary play 2012-07-16 10:00:00
5 john open 2012-07-16 10:30:00
6 mary start 2012-07-16 11:00:00
7 mary jump 2012-07-16 12:00:00
8 mary close 2012-07-16 13:00:00
9 mary delete 2012-07-16 14:00:00
10 mary start 2012-07-16 15:00:00
11 mary open 2012-07-16 16:00:00
动作类型:
action type
--------------
start 0
open 1
play 1
jump 2
close 1
delete 1
因此,针对操作“start”和类型“1”,答案将是:
user action ntimes
------------------------
mary open 2
mary close 1
john open 1
我的任务是
SELECT b.user,b.action, count(*)
FROM log a, log b
WHERE a.action='start' AND b.date>a.date AND a.user=b.user AND
1=(select type from ActionTypes where action=b.action) AND
not exists (SELECT c.action FROM log c where c.user=a.user AND
c.date>a.date and c.date<b.date and
1=(select type from ActionTypes where action=c.action))
GROUP BY b.user,b.action
我们的日志表有大约1百万个元组,查询功能可以使用,但是速度太慢了。我们正在使用SQLServer。有什么提示可以使它更快吗?谢谢。