Setting a column to a difference between rows values with an indeterminate number of rows between them

Question

Been trying to wrap my head around how to set Duration in this table, with a single query, that is the difference between the row's timestamp, and the previous row of the same SkillTargetID value. I found some similar questions already (this one was particularly helpful), but they all were able to predict how far away the second row is, based on a month value, for example. For each row of my data, its "sister" row could be adjacent to it, or not.

Here is a reduced version of my table for this example:

mysql> select * from testdur order by id;
+----+---------------+--------------+----------+
| id | SkillTargetID | UTC_DateTime | Duration |
+----+---------------+--------------+----------+
|  1 |          5000 |   1323719341 |     NULL |
|  2 |          5010 |   1323719341 |     NULL |
|  3 |          5000 |   1323719342 |     NULL |
|  4 |          5010 |   1323719342 |     NULL |
|  5 |          5000 |   1323719343 |     NULL |
|  6 |          5055 |   1323719345 |     NULL |
|  7 |          5010 |   1323719350 |     NULL |
|  8 |          5010 |   1323719441 |     NULL |
|  9 |          5010 |   1323719444 |     NULL |
| 10 |          5000 |   1323719445 |     NULL |
| 11 |          5055 |   1323719445 |     NULL |
| 12 |          5060 |   1323719445 |     NULL |
| 13 |          5000 |   1323719445 |     NULL |
| 14 |          5010 |   1323719445 |     NULL |
| 15 |          5060 |   1323719446 |     NULL |
| 16 |          5000 |   1323719460 |     NULL |
| 17 |          5000 |   1323719460 |     NULL |
| 18 |          5060 |   1323719500 |     NULL |
+----+---------------+--------------+----------+

The base data in the table adheres to this rule: when ordered by id, the values of UTC_DateTime will always be greater than or equal to the previous row, as this example data shows. The order of different SkillTargetID values with the same UTC_DateTime is not predictable, and many rows will have the same UTC_DateTime and SkillTargetID (such as 16 and 17).

The best attempt I have come up with so far includes a subquery to find the previous associated row, if it exists (I also selected the 2nd UTC_DateTime so you can see what is being subtracted):

SELECT
 t.id,
 t.SkillTargetID,
 t.UTC_DateTime,
 t2.UTC_DateTime AS UTC_DateTime2,
 (CASE WHEN t2.UTC_DateTime IS NULL THEN 0 ELSE t.UTC_DateTime - t2.UTC_DateTime END) AS Duration
FROM testdur t LEFT JOIN testdur t2
 ON t.SkillTargetID = t2.SkillTargetID
 AND t2.id = (
  SELECT id FROM testdur
  WHERE SkillTargetID = t.SkillTargetID AND id < t.id
  ORDER BY id DESC
  LIMIT 1 )
ORDER BY t.id;
+----+---------------+--------------+---------------+----------+
| id | SkillTargetID | UTC_DateTime | UTC_DateTime2 | Duration |
+----+---------------+--------------+---------------+----------+
|  1 |          5000 |   1323719341 |          NULL |        0 |
|  2 |          5010 |   1323719341 |          NULL |        0 |
|  3 |          5000 |   1323719342 |    1323719341 |        1 |
|  4 |          5010 |   1323719342 |    1323719341 |        1 |
|  5 |          5000 |   1323719343 |    1323719342 |        1 |
|  6 |          5055 |   1323719345 |          NULL |        0 |
|  7 |          5010 |   1323719350 |    1323719342 |        8 |
|  8 |          5010 |   1323719441 |    1323719350 |       91 |
|  9 |          5010 |   1323719444 |    1323719441 |        3 |
| 10 |          5000 |   1323719445 |    1323719343 |      102 |
| 11 |          5055 |   1323719445 |    1323719345 |      100 |
| 12 |          5060 |   1323719445 |          NULL |        0 |
| 13 |          5000 |   1323719445 |    1323719445 |        0 |
| 14 |          5010 |   1323719445 |    1323719444 |        1 |
| 15 |          5060 |   1323719446 |    1323719445 |        1 |
| 16 |          5000 |   1323719460 |    1323719445 |       15 |
| 17 |          5000 |   1323719460 |    1323719460 |        0 |
| 18 |          5060 |   1323719500 |    1323719446 |       54 |
+----+---------------+--------------+---------------+----------+

Obviously an UPDATE like this gets nasty real fast as this table grows. This is all I could come up with before going back around in circles:

UPDATE testdur t SET t.Duration = t.UTC_DateTime - (
 SELECT UTC_DateTime FROM testdur
 WHERE SkillTargetID = t.SkillTargetID AND id < t.id
 ORDER BY id DESC LIMIT 1 );
ERROR 1093 (HY000): You can't specify target table 't' for update in FROM clause

What other options do I have?

Here's the test data I was using:

CREATE TABLE `testdur` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `SkillTargetID` int(10) unsigned NOT NULL,
  `UTC_DateTime` int(10) unsigned NOT NULL,
  `Duration` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO testdur (SkillTargetID,UTC_DateTime) VALUES (5000,1323719341),(5010,1323719341),(5000,1323719342),(5010,1323719342),(5000,1323719343),(5055,1323719345),(5010,1323719350),(5010,1323719441),(5010,1323719444),(5000,1323719445),(5055,1323719445),(5060,1323719445),(5000,1323719445),(5010,1323719445),(5060,1323719446),(5000,1323719460),(5000,1323719460),(5060,1323719500);

BONUS - Is it possible to do this while inserting new multi-row data if it includes the ordered id already? Such as during:

INSERT INTO testdur (id,SkillTargetID,UTC_DateTime) VALUES
(19,5010,1323719505),
(20,5055,1323719510);

Thanks for the help ahead of time!

score 1 · Accepted Answer · answered Apr 11 '12 at 14:16

You could probably have it much more simplified by implementing @ mysql variables. Pre-query to get an ID, and the ID of the next associated with it, then do a join to the original table TWICE (different aliases) to have proper values, then do whatever calc you need... So, the "LastID" column is whatever the @WasLastID value was IF it was within the same skill target ID qualification. If not, it gets set back to zero. AFTER THAT TEST is applied, then the @WasLastID gets the ID of the current record as BASIS of the next entry's test.

select
      t.id,
      t.SkillTargetID,
      t.UTC_DateTime,
      t.UTC_DateTime2,
      if( @WasLastTarget = t.SkillTargetID, @WasLastID, 0 ) LastID,
      @WasLastID := t.ID as tmpLastID,
      @WasLastTarget := t.SkillTargetID as tmpLastTarget
   from
      testdur t,
      ( select @WasLastID = 0,
               @WasLastTarget := 0 ) sqlvars
   order by
      t.skillTargetID,
      t.id

The order by will be applied to the result set before any of the @ variables are processed, so the above query would in essence create the following result ensuring all skill targets are properly sequenced when applying the @ last target comparisons...

    +----+---------------+--------------+--------+------------+---------------+
    | id | SkillTargetID | UTC_DateTime | LastID |  tmpLastID | tmpLastTarget |
    +----+---------------+--------------+--------+------------+---------------+
    |  1 |          5000 |   1323719341 |     0  |      1     | 5000 
    |  3 |          5000 |   1323719342 |     1  |      3     | 5000
    |  5 |          5000 |   1323719343 |     3  |      5     | 5000
    | 10 |          5000 |   1323719445 |     5  |     10     | 5000
    | 13 |          5000 |   1323719445 |    10  |     13     | 5000
    | 16 |          5000 |   1323719460 |    13  |     16     | 5000
    | 17 |          5000 |   1323719460 |    16  |     17     | 5000
-- BREAK BETWEEN SKILL TARGET...
    |  2 |          5010 |   1323719341 |     0  |      2     | 5010
    |  4 |          5010 |   1323719342 |     2  |      4     | 5010
    |  7 |          5010 |   1323719350 |     4  |      7     | 5010
    |  8 |          5010 |   1323719441 |     7  |      8     | 5010
    |  9 |          5010 |   1323719444 |     8  |      9     | 5010
    | 14 |          5010 |   1323719445 |     9  |     14     | 5010
-- BREAK BETWEEN SKILL TARGET...
    |  6 |          5055 |   1323719345 |     0  |      6     | 5055
    | 11 |          5055 |   1323719445 |     6  |     11     | 5055
-- BREAK BETWEEN SKILL TARGET...
    | 12 |          5060 |   1323719445 |     0  |     12     | 5060
    | 15 |          5060 |   1323719446 |    12  |     15     | 5060
    | 18 |          5060 |   1323719500 |    15  |     18     | 5060
    +----+---------------+--------------+--------+------------+---------------+

Now, all that said and sample provided, you could then expand on it such as...

select
      PreQuery.*,
      CASE WHEN t1.UTC_DateTime IS NULL THEN 0 
           ELSE t2.UTC_DateTime - t1.UTC_DateTime END) AS Duration
   from
      ( above full select query ) as PreQuery
         LEFT JOIN testdur t1
            on PreQuery.ID = t1.ID
         LEFT JOIN testdur t2
            on PreQuery.LastID = t2.ID

I ended doing most of this in code instead of SQL, but your idea does work - I tested it. I will probably be able to use this idea for other things though. Thanks for taking the time. — aparker, Apr 25 '12 at 20:18

Setting a column to a difference between rows values with an indeterminate number of rows between them

1 Answers1