我有两个表: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
因此，给定 Action “开始”和类型“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。有关如何使其更快的任何提示？谢谢
最佳答案
你能试试这个查询吗？它使用存在来测试以前的时间顺序记录是否是请求的类型。我相信它会比自加入更快。 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 类型的 jump ，不应跳过以开始。关于sql - 使用多个表提高 sql 的性能，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/11535991/