1

I have a time sequence data set to analyze but the difficulty is that

  1. the data set is very big.
  2. the time sequence events are committed by different objects with affiliation.
  3. its in mysql, there is no id like index column to join

e.g.

----------------
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
----------------

I am trying to know the delay between each commit from each component of each object. i.e. obj_a.comp_1.delay1 = obj_a.compoent_1.t4 - obj_a.component_1.t1

etc.

I tried to join

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
)

which taking forever, so I am thinking if there is any way to sort on the results and do inter-row calculation with variables, it would be much faster. but it failed every way with sub-query and views where the sort is always done after calculation. From what I read on order by, it seems impossible. So what options do I have to to achieve this reasonably fast?

To be more specific: If I do the following:

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

the result is

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

Since the calculation happens on unsorted data, the results do not make sense. The table in my case is actually a view, the component is calculated field. It doesn't have an index. The join-subquery way could takes half an hour. But if I save the sorted results into a table and calculate with variable, it only takes minutes. The data set is very big, I only tested with a small subset. I am not sure the method of saving to extra table is a scalable solution. So I am looking for advise on alternatives.

  • This has a couple of examples of successfully using @variable with ORDER BY. – Rick James Sep 11 '16 at 16:30
  • Thanks for the link, good read. but in my case, I have to order the data first before attaching variables and do the calculation. the sorting works, but the calculation happens before the sorting. I haven't find a way to do this without saving the sorted results to a table first... – Ben Sep 12 '16 at 8:23
  • Often that can be done via a 'derived' table: SELECT ... FROM ( SELECT ... ) .... – Rick James Sep 12 '16 at 18:53
2

I have managed to successfully use variables and sorting to solve your problem. This is my test set-up:

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')
;

And this is the query:

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
;

As this SQL Fiddle demo will show, the query returns the following output:

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
  • Hi Andriy sorry for the late rely. I think I can label it as correct answer at least for my specific question, but a slight twist on the data e.g. if I change the "FROM atable" => "FROM (SELECT ta.dt, ta.obj, ta,comp FROM atable as ta JOIN atable as tb ON ta.dt=tb.dt)" thus it won't work on any view with JOIN operations . it wil just broke the sort / calculation sequence. Any insight ? – Ben Sep 21 '16 at 9:35
  • Hi @Ben. I'm afraid I don't have much to say. When testing on SQL Fiddle, it didn't make any difference. And I don't have MySQL to test this more rigorously. Sorry. – Andriy M Sep 22 '16 at 11:37
1

This is a typical WINDOW function use case with little twist. If there is any answer I found. That would be to switch to some DB with WINDOW function support. I think I made a mistake to choose Mysql for a project involving various time sequence related analysis. The lack of WINDOW function support make things really cumbersome.

  • 1
    MariaDB has implemented window functions in their 10.2 version. It's still in RC version (Release Candidate) but be checking their site for when they publish a stable (GA) version. I hear MySQL will also have window functions in their next version but I have no idea when it's going to come out. – ypercubeᵀᴹ Apr 1 '17 at 15:58
  • Good to know. Thanks for the info. Though looks like it still lacks support for common function such as LAG, Its still a lot better than these awkward workarounds. Maybe I'll try it out after RC. – Ben Apr 4 '17 at 11:58
  • You can experiment in dbfiddle.uk – ypercubeᵀᴹ Apr 4 '17 at 12:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.