StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSetting a column to a difference between rows values with an indeterminate number of rows between them
text
Body
copied!<p>Been trying to wrap my head around how to set <code>Duration</code> in this table, with a single query, that is the difference between the row's timestamp, and the previous row of the same <code>SkillTargetID</code> value. I found some similar questions already (<a href="https://stackoverflow.com/questions/5078987/calculate-deltadifference-of-current-and-previous-row-in-sql">this one</a> 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.</p> <p>Here is a reduced version of my table for this example:</p> <pre><code>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 | +----+---------------+--------------+----------+ </code></pre> <p>The base data in the table adheres to this rule: when ordered by <code>id</code>, the values of <code>UTC_DateTime</code> will always be greater than or equal to the previous row, as this example data shows. The order of different <code>SkillTargetID</code> values with the same <code>UTC_DateTime</code> is not predictable, and many rows will have the same <code>UTC_DateTime</code> and <code>SkillTargetID</code> (such as 16 and 17).</p> <p>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 <code>UTC_DateTime</code> so you can see what is being subtracted):</p> <pre><code>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 | +----+---------------+--------------+---------------+----------+ </code></pre> <p>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:</p> <pre><code>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 </code></pre> <p>What other options do I have?</p> <p>Here's the test data I was using:</p> <pre><code>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); </code></pre> <p>BONUS - Is it possible to do this while inserting new multi-row data if it includes the ordered <code>id</code> already? Such as during:</p> <pre><code>INSERT INTO testdur (id,SkillTargetID,UTC_DateTime) VALUES (19,5010,1323719505), (20,5055,1323719510); </code></pre> <p>Thanks for the help ahead of time!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload