代码之家 › 专栏 › 技术社区 › Michael Todd

利用SQL确定“一组”记录时差的集理论方法

sql-server sql

Michael Todd · 技术社区 · 15 年前

我在sqlserver中有一个日志文件,它存储应用程序启动的时间、应用程序准备就绪的时间(即完成加载)和退出的时间。每一项都作为单独的条目出现。格式(和示例数据)如下:

Date/Time                  User    Type    Application  Message
2009-11-03 12:26:12.403 uname1  Info    app1         Started    
2009-11-03 12:26:22.403 uname1  Info    app1         Loaded 
2009-11-03 12:27:15.403 uname2  Info    app1         Started    
2009-11-03 12:27:16.401 uname1  Info    app1         Exited 
2009-11-03 12:27:18.403 uname2  Info    app1         Loaded 
2009-11-03 12:29:12.403 uname2  Info    app1         Exited

我想知道,每个应用程序和每个用户,应用程序达到就绪状态所需的时间量以及应用程序运行的时间量。如果每个日期/时间都在同一个记录中,这将是小菜一碟,而且将每个记录作为光标加载并筛选数据也很容易(尽管很乏味),但我认为必须有某种方法以一种集理论的方式“正确”地完成这一任务。

因此,需要重申的是,预期会有以下输出(来自上面的示例数据)(数字以秒为单位,向上取整):

User    Application    Ready    Uptime
uname1  app1           10       64
uname2  app1           3        117

有什么建议吗?

编辑:好消息是应用程序只能启动一次。但是,日志是这样的不考虑到应用程序是否崩溃(尽管我想我可以寻找“退出”和“崩溃”作为最终条件)。

2 回复 | 直到 15 年前

Charles Bretana 15 年前

怎么样

  Select S.user, S.Application,
        S.DateTime Started, L.DateTime Loaded, X.DateTime Exited,
        L.DateTime - S.DateTime LoadTime,
        X.DateTime - L.DateTime RunTime
  From LogFile S
      Full Join LogFile L
          On S.Message = 'Started'
             And L.Message = 'Loaded'
             And L.User = S.user
             And L.Application = S.Application
             And L.DateTime = (Select Min(DateTime)
                               From LogFile 
                               Where Message = 'Loaded'
                                 And application = S.Application
                                 And user = S.user
                                 And DateTime > S.DateTime)
      Full Join LogFile X
          On L.Message = 'Loaded'
             And X.Message = 'Exited'
             And X.User = L.user
             And X.Application = L.Application
             And  X.DateTime = (Select Min(DateTime)
                                From LogFile 
                                Where Message = 'Exited'
                                  And application = L.Application
                                  And user = L.user
                                  And DateTime > L.DateTime)

然后将聚合函数应用于此,以满足您的需要:

 Select user, Application, 
    Sum(LoadTime) TotLoadTime,
    Sum(RunTime) TotalRunTime
 From
      (Select S.user, S.Application,
        S.DateTime Started, L.DateTime Loaded, X.DateTime Exited,
        L.DateTime - S.DateTime LoadTime,
        X.DateTime - L.DateTime RunTime
       From LogFile S
          Full Join LogFile L
             On S.Message = 'Started'
                And X.Message = 'Loaded'
                 And L.User = S.user
                 And L.Application = S.Application
                 And L.DateTime =
                        (Select Min(DateTime)
                         From LogFile 
                         Where Message = 'Loaded'
                            And application = S.Application
                            And user = S.user
                            And DateTime > S.DateTime)
         Full Join LogFile X
             On L.Message = 'Loaded'
               And X.Message = 'Exited'
               And X.User = L.user
               And X.Application = L.Application
               And  X.DateTime =
                        (Select Min(DateTime)
                         From LogFile 
                         Where Message = 'Exited'
                             And application = L.Application
                             And user = L.user
                             And DateTime > L.DateTime)) Z
 Group By user, Application

Welbog 15 年前

我宁愿不在同一张表上多次联接,特别是当表变大时。这是一种双程的方法。第一次通过将时间排序到正确的位置,第二次通过将时间按用户和应用程序折叠起来:

SELECT 
  User,
  Application,
  MAX(StartTime) StartTime,
  MAX(ReadyTime) ReadyTime,
  MAX(ExitTime) ExitTime,
FROM (
  SELECT
    User,
    Application,
    CASE (
      WHEN Message = 'Started' THEN Date/Time
      ELSE NULL
    ) StartTime,
    CASE (
      WHEN Message = 'Loaded' THEN Date/Time
      ELSE NULL
    ) ReadyTime,
    CASE (
      WHEN Message = 'Exited' THEN Date/Time
      ELSE NULL
    ) ExitTime
  FROM Log
) Log
GROUP BY 
  User,
  Application

从那里开始,计算出你在不同时间想要的一切都是微不足道的。

这不是很“集合论”——比如,但是分组和聚合从来就不是。和Eric的解决方案一样,它无法处理同一用户多次使用该应用程序的情况。您需要第三个分组列(如“会话”或其他)来处理该场景。