sql - 提高多表 sql 的性能

Question

我有两个表:Log(id,user,action,date) 和 ActionTypes(action,type)。给定一个 Action A0 和一个类型 T0，我想计算每个用户在 A0 之后使用其他 Action Ai 的次数，但跳过不属于 T0 类型的 Log Action 。例如:

日志:

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 类型:

action  type
--------------
start   0
open    1
play    1
jump    2
close   1
delete  1

因此，给定操作“开始”和类型“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

我们的 Log 表有大约 100 万个元组，查询可以工作，但速度太慢。我们正在使用 SQLServer。关于如何使其更快的任何提示？谢谢

Answer 1

您能尝试一下这个查询吗？它使用存在来测试先前的时间记录是否是所请求的类型。我相信这会比自加入更快。 I have put a demo @ Sql Fiddle .

select log.[user], log.action, count(*) ntimes
  from log
 inner join actiontype t
    on log.action = t.action
 where t.type = 1
   and exists (select *
                 from 
                   (select top 1 t1.type
                      from log l1
                     inner join actiontype t1
                        on l1.action = t1.action
                     where l1.[user] = log.[user]
                       and l1.date < log.date
                       and t1.type in (0, 1)
                     order by l1.date desc
                   ) prevEntry
                where prevEntry.type = 0
               )
 group by log.[user], log.action

我不明白为什么 mary\close 在结果列表中。上一条记录是 2 类型的跳转，不应跳过以开始。