代码之家 › 专栏 › 技术社区 › Jon Hopkins

如何在没有游标的情况下分析SQL数据中记录之间的时间段?

sql

Jon Hopkins · 技术社区 · 15 年前

根本问题是:我有一个已经运行了几个月的应用程序。用户一直报告说,随着时间的推移,它的速度正在放缓(因此,5月份的速度比现在快)。我需要一些证据来支持或反驳这个说法。我对精确的数字不感兴趣(所以我不需要知道登录需要10秒),我对趋势感兴趣——过去需要x秒的东西现在需要y秒。

我拥有的数据是一个审计表,每当用户执行任何活动时,它都会存储一行数据,包括主键、用户id、日期时间戳和活动代码:

create table AuditData (
    AuditRecordID int identity(1,1) not null, 
    DateTimeStamp datetime not null,
    DateOnly datetime null,
    UserID nvarchar(10) not null,
    ActivityCode int not null)

(注意:DateOnly(datetime)是DateTimeStamp,去掉了时间以便于日常分析,它可以有效地复制数据以加快查询速度)。

此外,为了方便起见,您可以假设ID是按日期-时间顺序分配的,即1总是在2之前,2总是在3之前-如果这不是真的,我可以这样做)。

ActivityCode是一个整数,标识发生的活动,例如1可能是用户登录的,2可能是返回的用户数据,3可能是返回的搜索结果,等等。

为喜欢这类事情的人提供的样本数据…:

1, 01/01/2009 12:39, 01/01/2009, P123, 1
2, 01/01/2009 12:40, 01/01/2009, P123, 2
3, 01/01/2009 12:47, 01/01/2009, P123, 3
4, 01/01/2009 13:01, 01/01/2009, P123, 3

用户数据在登录后立即返回(活动代码2)(活动代码1),因此这可以作为登录所需时间的粗略基准(正如我所说,我对趋势很感兴趣,只要我衡量的是5月和7月的相同情况,如果这不是整个登录过程,那也没什么大不了的——它需要足够的时间来给出一个粗略的想法)。

所以我要做的是选择登录(比如ActivityID1)和登录之间的平均时间 之后的第一个实例是该用户当天的 返回的用户数据的数量(例如ActivityID 2)。

我可以用光标浏览表格,获取每个登录实例,然后进行选择,即获取当天该用户的最小用户数据返回,但这显然不是最优的,而且速度非常慢。

我的问题是(最后)-是否有一种“适当的”SQL方法可以使用自联接或类似的方式来完成这项工作,而不使用游标或类似的过程方法?我可以创建视图和任何符合我心意的内容,它不必是单一选择。

我可以把一些东西组合起来,但我想做分析,我正在做一个标准的产品功能,所以我希望它是正确的。

4 回复 | 直到 15 年前

idstam 15 年前

SELECT TheDay, AVG(TimeTaken) AvgTimeTaken
FROM (  
SELECT 
    CONVERT(DATE, logins.DateTimeStamp) TheDay
    , DATEDIFF(SS, logins.DateTimeStamp, 
                (SELECT TOP 1 DateTimeStamp 
                 FROM AuditData userinfo 
                 WHERE UserID=logins.UserID 
                    and userinfo.ActivityCode=2 
                    and userinfo.DateTimeStamp > logins.DateTimeStamp )
                )TimeTaken
FROM AuditData logins
WHERE 
    logins.ActivityCode = 1
) LogInTimes
GROUP BY TheDay

但在现实世界中,这可能非常缓慢。

APC 15 年前

在甲骨文中,由于分析函数的存在,这将是轻而易举的事。在这种情况下,LAG()可以轻松找到活动代码1和2的匹配对,并计算趋势。正如你所看到的,事情在1月2日变得更糟,在3日有了相当大的改善(我的工作时间是几秒钟而不是几分钟)。

SQL> select DateOnly
  2         , elapsed_time
  3         , elapsed_time - lag (elapsed_time) over (order by DateOnly) as trend
  4  from
  5      (
  6      select DateOnly
  7             , avg(databack_time - prior_login_time) as elapsed_time
  8      from
  9          ( select DateOnly
 10                  , databack_time
 11                  , ActivityCode
 12                  , lag(login_time) over (order by DateOnly,UserID, AuditRecordID, ActivityCode) as prior_login_time
 13            from
 14              (
 15                  select a1.AuditRecordID
 16                         , a1.DateOnly
 17                         , a1.UserID
 18                         , a1.ActivityCode
 19                         , to_number(to_char(a1.DateTimeStamp, 'SSSSS')) as login_time
 20                         , 0 as databack_time
 21                  from   AuditData a1
 22                  where a1.ActivityCode = 1
 23                  union all
 24                  select a2.AuditRecordID
 25                         , a2.DateOnly
 26                         , a2.UserID
 27                         , a2.ActivityCode
 28                         , 0 as login_time
 29                         , to_number(to_char(a2.DateTimeStamp, 'SSSSS')) as databack_time
 30                  from   AuditData a2
 31                  where a2.ActivityCode = 2
 32                  )
 33              )
 34      where ActivityCode = 2
 35      group by  DateOnly
 36  )
 37  /

DATEONLY  ELAPSED_TIME      TREND
--------- ------------ ----------
01-JAN-09          120
02-JAN-09          600        480
03-JAN-09          150       -450

SQL>

正如我在评论中所说的,我想你正在MSSQL中工作。我不知道那个产品是否有LAG()的等价物。

Anthony 15 年前

用户将不按强制顺序执行各种任务,并且
任何两项活动之间的差异反映了这两项活动中第一项活动执行所需的时间,

因此,这将有点奇怪和有趣,对于每个活动(除了登录和注销),时间戳将记录在两个不同的行中——一个记录在最后一个活动中(作为“完成”的时间),另一个记录在新行中(作为时间开始)。你最终会得到一个雅各布阶梯,但是找到你想要的数据要简单得多。

否则,您将陷入这样一个查询中

SELECT TIME_IN_SEC(row2-timestamp) - TIME_IN_SEC(row1-timestamp)

这将是相当缓慢的,正如你已经建议的那样。通过接受冗余,最终只需查询两列之间的差异。您可能也不太需要知道用户信息,因为您知道任何一行都显示两个活动代码,因此您可以查询任意一天所有用户的平均值,并将其与第二天进行比较(除非您试图找出哪些用户也有问题)。

Vukan Djurovic Vukan Djurovic 15 年前

这是一个更快的查询,在一行中,您将有current和row before datetime值,之后您可以使用DATEDIFF(datepart、startdate、enddate)。我使用@DammyVariable和DamyField,因为我记得如果不是update语句中的第一个@variable=Field,则会出现一些问题。

SELECT *, Cast(NULL AS DateTime) LastRowDateTime, Cast(NULL As INT) DamyField INTO #T FROM AuditData 
GO
CREATE CLUSTERED INDEX IX_T ON #T (AuditRecordID)
GO
DECLARE @LastRowDateTime DateTime
DECLARE @DammyVariable INT

SET @LastRowDateTime = NULL 
SET @DammyVariable = 1

UPDATE #T SET 
  @DammyVariable = DammyField = @DammyVariable
, LastRowDateTime = @LastRowDateTime 
    , @LastRowDateTime = DateTimeStamp 
option (maxdop 1)