MySQL 表上的已用时间计算 - elapsed time calculations on a mysql table

大家好。

我有这样的环境：在Mysql数据库上，每次用户登录站点时，都会创建一个新行，其中包含他的名字以及登录时间。由于系统是互斥的，因此在给定时间只有一个用户，如果新用户到达，则注销登录的用户。

现在他们要求我计算系统上所有用户的总时间，所以基本上，我必须将登录和下一次登录的所有时间差相加。

user  |       timestamp     |
------------------------------
alpha | 2013-01-19 03:14:07
beta  | 2013-01-20 11:24:04
alpha | 2013-01-21 02:11:37
alpha | 2013-01-21 03:10:31    <---- a user could login twice, it is normal
gamma | 2013-01-21 11:24:04
beta  | 2013-01-21 11:25:00

我想问问你的意见，因为有很多登录，计算用户总记录时间的最佳方法是什么？在此示例中，"Gamma"的登录时间为 56 秒，并且可以忽略 beta 的最后一次登录，因为它在执行此检查时将处于联机状态。所以"测试版"将只有一个条目。

有没有办法通过查询来计算它？或者最好添加一列"在线时间"，让系统计算用户每次注销时在线花费了多少时间？

如果要在MySQL中执行此操作，则需要自连接。进行自连接是一种痛苦，因为MySQL没有内置的rownum函数。但这仍然是可行的。

首先，我们需要创建一个子查询来创建一个模拟SELECT rownum, user, timestamp FROM login的虚拟表，我们可以这样做。 http://sqlfiddle.com/#!2/bf6ef/2/0

SELECT @a:=@a+1 AS rownum, user, timestamp
    FROM (
        SELECT user, timestamp
          FROM login
         ORDER BY timestamp
    ) C,
    (SELECT @a:=0) s

接下来，我们需要将此虚拟表自连接到其自身的副本。我们在此结果集中想要的是表中所有连续行对的列表。该查询是一个毛球 - 它将结构化的查询语言放在结构化的查询语言中。但它有效。这是：http://sqlfiddle.com/#!2/bf6ef/4/0

SELECT first.user AS fuser, 
       first.timestamp AS ftimestamp,
       second.user AS suser,
       second.timestamp as stimestamp,
       TIMESTAMPDIFF(SECOND, first.timestamp, second.timestamp) AS timeloggedin
  FROM (
       SELECT @a:=@a+1 AS rownum, user, timestamp
         FROM (
             SELECT user, timestamp
               FROM login
           ORDER BY timestamp
              ) C,
          (SELECT @a:=0) s
        ) AS first
  JOIN (
       SELECT @b:=@b+1 AS rownum, user, timestamp
         FROM (
             SELECT user, timestamp
               FROM login
           ORDER BY timestamp
              ) C,
          (SELECT @b:=0) s
        ) AS second ON first.rownum+1 = second.rownum

比较连续行的整个技巧是

SELECT (virtual_table) AS first
  JOIN (virtual_table) AS second ON first.rownum+1 = second.rownum

查询模式。rownum+1 = rownum 事物将具有连续行号的行收集在一起。

接下来，我们需要汇总该查询的结果，以获取每个用户登录的总时间。这将像这样工作：

  SELECT user, SUM(timeloggedin) AS timeloggedin
    FROM (
          /* the self-joined query */
         ) AS selfjoin
   GROUP BY user
   ORDER BY user

看起来像这样：http://sqlfiddle.com/#!2/bf6ef/5/0

这是整个查询放在一起。

SELECT user, SUM(timeloggedin) AS timeloggedin
  FROM (
      SELECT first.user AS user, 
             TIMESTAMPDIFF(SECOND, first.timestamp, second.timestamp) AS timeloggedin
        FROM (
             SELECT @a:=@a+1 AS rownum, user, timestamp
         FROM (
                   SELECT user, timestamp
                     FROM login
                 ORDER BY timestamp
                    ) C,
                (SELECT @a:=0) s
              ) AS first
        JOIN (
             SELECT @b:=@b+1 AS rownum, user, timestamp
               FROM (
                   SELECT user, timestamp
                     FROM login
                 ORDER BY timestamp
                    ) C,
                (SELECT @b:=0) s
              ) AS second ON first.rownum+1 = second.rownum
         ) AS selfjoin
   GROUP BY user
   ORDER BY user

对于习惯于程序化、算法思维的人来说，这不是真正的直觉。但这就是您在 SQL 中进行这种连续行比较的方式。

试试这个...少或多是你问题的解决方案...

    CREATE TABLE `matteo` (
      `user` varchar(20) DEFAULT NULL,
      `timestamp` int(11) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    INSERT INTO matteo(user, `timestamp`) VALUES ('alpha', 7);
    INSERT INTO matteo(user, `timestamp`) VALUES ('beta', 9);
    INSERT INTO matteo(user, `timestamp`) VALUES ('alpha', 17);
    INSERT INTO matteo(user, `timestamp`) VALUES ('alpha', 27);
    INSERT INTO matteo(user, `timestamp`) VALUES ('gamma', 77);
    INSERT INTO matteo(user, `timestamp`) VALUES ('beta', 97);
    select a.*,b.*,b.`timestamp`-a.`timestamp` as delta
    from
    (SELECT @rownum := @rownum + 1 AS id,t.*
          FROM matteo t,(SELECT @rownum := 0) r) a
    join
    (SELECT @rownum2 := @rownum2 + 1 AS id,t.*
          FROM matteo t,(SELECT @rownum2 := 0) r) b 
    where a.id=b.id-1

：-）周一见!!