两行大表之间的 MySQL 时间戳差异

我有一个包含超过 2,500,000 行和三列(相关的(的发生业务表：id、company_id 和 created_at。 id 标识交易，company_id标识哪个公司收到它，created_at是执行交易时间的时间戳。

我想要的是获得给定公司每对连续交易之间的差异列表。换句话说，如果我的表格是：

 id | company_id | created_at
------------------------------
 01 |     ab     | 2016/01/02
 02 |     ab     | 2016/01/03
 03 |     cd     | 2016/01/03
 04 |     ab     | 2016/01/03
 05 |     cd     | 2016/01/04
 06 |     ab     | 2016/01/05

(请注意，在给定公司的两笔连续交易之间可能存在任意数量的其他公司交易。

然后我希望输出是：

 diff | company_id
-------------------
  01  |     ab
  00  |     ab
  01  |     cd
  02  |     ab

(我以天为单位编写了created_at和差异值，但这只是为了便于可视化。

我尝试使用它，但它太慢了。

--编辑：

"这"是：

SELECT (B.created_at - A.created_at) AS diff, A.company_id
FROM Transactions A CROSS JOIN Transactions B
WHERE B.id IN (SELECT MIN (C.id) FROM Transactions C WHERE C.id > A.id AND C.company_id = A.company_id)
ORDER BY A.id ASC

为了获得看起来像您期望的结果，我有时会使用 MySQL 用户定义的变量，并让 MySQL "按顺序"执行行的处理，这样我就可以将当前行与上一行的值进行比较。

为了有效地运行，我们需要一个适当的索引，以避免昂贵的"使用文件排序"操作。(我们将需要按company_id顺序排列行，然后按id顺序排列，因此这些行将是索引中的前两列。当我们这样做时，我们不妨包括created_at列并使其成为覆盖索引。

... ON Transactions (company_id, id, created_at)

然后我们可以尝试这样的查询：

SELECT t.diff
     , t.company_id
  FROM (
         SELECT IF(r.company_id = @pv_company_id, r.created_at - @pv_created_at, NULL) AS diff
              , IF(r.company_id = @pv_company_id, 1, 0) AS include_
              , @pv_company_id := r.company_id          AS company_id
              , @pv_created_at := r.created_at          AS created_at
           FROM (SELECT @pv_company_id := NULL, @pv_created_at := NULL) i
          CROSS
           JOIN Transactions r
          ORDER
             BY r.company_id
              , r.id
       ) t
 WHERE t.include_

MySQL 参考手册明确警告不要在语句中使用这样的用户定义变量。但是我们在MySQL 5.1和5.5中观察到的行为是一致的。(最大的问题是MySQL的某些未来版本可能会使用不同的执行计划。

别名为 i 的内联视图只是为了初始化几个用户定义的变量。在运行查询之前，我们可以轻松地将其作为单独的步骤来执行此操作。但是我喜欢在语句本身中包含初始化权限，所以我不需要单独的 SELECT/SET 语句。

MySQL访问事务表，并首先处理ORDER BY，按(company_id，id(顺序对Transactions中的行进行排序。 (我们更喜欢通过索引来完成此操作，而不是通过昂贵的"使用文件排序"操作，这就是为什么我们希望定义该索引，将company_id和id作为前导列。

"诀窍"是将当前行中的值保存到用户定义的变量中。处理下一行时，前一行的值在用户定义的变量中可用，用于执行比较(当前行是否与上一行的company_id相同？(和执行计算(两行created_at值之间的差异(。

根据减法运算的用法，我假设created_at列是整数/数字。也就是说，我假设created_at不是DATE、DATETIME或TIMESTAMP数据类型，因为我们不使用减法运算来查找差异。

SELECT a
     , b
     , a - b                  AS `subtraction`
     , DATEDIFF(a,b)          AS `datediff`
     , TIMESTAMPDIFF(DAY,b,a) AS `tsdiff`
  FROM ( SELECT DATE('2015-02-17') AS a
              , DATE('2015-01-16') AS b
       ) t

a           b           subtraction  datediff  tsdiff  
----------  ----------  -----------  --------  ------
2015-02-17  2015-01-16          101        32      32

(减法运算不会引发错误。但它返回的内容可能是出乎意料的。在此示例中，它返回两个整数值 20150217 和 20150116 之间的差值，这不是两个DATE表达式之间的天数。

编辑

我注意到原始查询包含一个ORDER BY.如果需要按特定顺序返回的行，则可以在内联视图查询中包含该列，并对外部查询使用ORDER BY。

SELECT t.diff
     , t.company_id
  FROM (
         SELECT IF(r.company_id = @pv_company_id, r.created_at - @pv_created_at, NULL) AS diff
              , IF(r.company_id = @pv_company_id, 1, 0) AS include_
              , @pv_company_id := r.company_id          AS company_id
              , @pv_created_at := r.created_at          AS created_at
              , r.id                                    AS id
           FROM (SELECT @pv_company_id := NULL, @pv_created_at := NULL) i
          CROSS
           JOIN Transactions r
          ORDER
             BY r.company_id
              , r.id
       ) t
 WHERE t.include_
 ORDER BY t.id

抱歉，无法绕过外部查询ORDER BY的"使用文件排序"。

您可以使用游标功能。如果你打开一个光标，你滑动每一行，每两行获取，你就会有所作为。我认为这种方法更有效，因为滑动表的所有行而不是连接超过 250 万。

也试试这个。

SELECT company_id, 
  (SELECT DATEDIFF(created_at,TR.created_at) 
    FROM transactions 
    WHERE id > TR.id AND company_id = TR.company_id LIMIT 0,1) AS diff
FROM transactions AS TR
HAVING diff is not null

试试这个

SELECT
    t1.company_id,
    t2.created_at - t1.created_at as diff
FROM Transactions t1
LEFT JOIN Transactions t2
    on t2.created_at > t1.created_at
    and t2.company_id = t1.company_id

相关内容

最新更新

热门标签：