主页 / dba / 问题 / 149296
Accepted
Ben
Asked: 2016-09-11 08:18:22 +0800 CST

如何在行间计算前对记录进行排序？

  • 772

我有一个时间序列数据集要分析，但困难在于

  1. 数据集非常大。
  2. 时序事件由具有从属关系的不同对象提交。
  3. 它在mysql中，没有索引列之类的id可以加入

例如

----------------
dt     obj        comp
----------------
t1,    object_a,   component_1
t2,    object_b,   component_1
t3,    object_b,   component_2
t4,    object_a,   component_1
t5,    object_b,   component_1
t6,    object_b,   component_2
----------------

我想知道每个对象的每个组件的每次提交之间的延迟。即 obj_a.comp_1.delay1 = obj_a.compoent_1.t4 - obj_a.component_1.t1

等等

我试着加入

select timediff( t1.dt, t.dt ) as delay
from  table as t 
join table as t1
on t1.comp = t.comp
and t1.obj = t.obj
and t1.dt = (
    select min(t2.dt)
    from table as t2
    where t2.obj = t.obj 
    and t2.comp = t.comp 
    and t2.dt > t.dt
)

这需要永远，所以我在想如果有任何方法可以对结果进行排序并使用变量进行行间计算，它会快得多。但是它在子查询和视图中的所有方式都失败了，其中排序总是在计算之后完成。从我读到的内容来看order by，这似乎是不可能的。那么我必须有哪些选择才能相当快地实现这一目标？

更具体地说：如果我执行以下操作：

SELECT  
    obj
    , comp
    , dt - @prev
    , @prev := dt
FROM 
   table
ORDER BY obj, comp, dt ASC

结果是

obja, comp1, t1-null
obja, comp1, t4-t3      # should be t4-t1
objb, comp1, t2-t1      
objb, comp1, t5-t4      # should be t5-t2
objb, comp2, t3-t2
objb, comp2, t6-t5      # should be t6-t3

由于计算发生在未排序的数据上，因此结果没有意义。我的例子中的表实际上是一个视图，组件是计算字段。它没有索引。连接子查询方式可能需要半个小时。但是，如果我将排序后的结果保存到一个表中并使用变量进行计算，则只需几分钟。数据集很大，我只测试了一小部分。我不确定保存到额外表的方法是否是可扩展的解决方案。所以我正在寻找替代方案的建议。

mysql view

2 个回答

  1. Best Answer

    我已经成功地使用变量和排序来解决你的问题。这是我的测试设置：

    CREATE TABLE atable
    (`dt` datetime, `obj` varchar(8), `comp` varchar(11))
;

INSERT INTO atable
    (`dt`, `obj`, `comp`)
VALUES
    ('2016-09-13 06:00:01', 'object_a', 'component_1'),
    ('2016-09-13 06:00:02', 'object_b', 'component_1'),
    ('2016-09-13 06:00:04', 'object_b', 'component_2'),
    ('2016-09-13 06:00:08', 'object_a', 'component_1'),
    ('2016-09-13 06:00:16', 'object_b', 'component_1'),
    ('2016-09-13 06:00:32', 'object_b', 'component_2')
;

    这是查询：

    SELECT
  dt,
  obj,
  comp,
  diff
FROM
  (SELECT @obj := '', @comp := '', @dt := CAST('1970-01-01' AS datetime)) AS var,
  (
    SELECT
      TIMESTAMPDIFF(SECOND, IF(obj = @obj AND comp = @comp, @dt, NULL), dt) AS diff,
      @dt := dt AS dt,
      @obj := obj AS obj,
      @comp := comp AS comp
    FROM
      atable
    ORDER BY
      obj,
      comp,
      dt
  ) AS sub
;

    如这个 SQL Fiddle 演示所示，查询返回以下输出：

    dt                   obj       comp         diff
-------------------  --------  -----------  ----
2016-09-13 06:00:01  object_a  component_1  NULL
2016-09-13 06:00:08  object_a  component_1  7
2016-09-13 06:00:02  object_b  component_1  NULL
2016-09-13 06:00:16  object_b  component_1  14
2016-09-13 06:00:04  object_b  component_2  NULL
2016-09-13 06:00:32  object_b  component_2  28
    • 2

  2. 这是一个典型的 WINDOW 函数用例，几乎没有变化。如果我找到任何答案。那将是切换到一些具有 WINDOW 功能支持的数据库。我想我为一个涉及各种时序相关分析的项目选择Mysql是错误的。缺少 WINDOW 功能支持使事情变得非常麻烦。

    • 1

相关问题