[SQL] Running difference (verschil tussen rijen)

[insan1ty]

Ik heb een klein (maar misschien niet simpel) probleempje met SQL.
Ik heb een MS SQL server waar een applicatie bij draait die het mogelijk maakt om queries op een database uit te voeren. Deze queries kan ik zelf ingeven. Ik heb nu het volgende probleem:

code:

+-------+---------+-----------------------+
|key    |activity |timestamp              |
+-------+---------+-----------------------+
|0      |Complete |2007-05-07 09:51:30 AM |
|1      |Complete |2007-05-07 09:52:30 AM |
|2      |Complete |2007-05-07 09:54:30 AM |
|3      |Complete |2007-05-07 09:56:30 AM |
|4      |Complete |2007-05-07 09:57:30 AM |
+-------+---------+-----------------------+

Ik heb deze tabel en ik wil het verschil in seconden weten tussen de timestamps van de verschillende rijen. Is hier een standaard SQL commando voor?

Er moet dus dit uit komen, if you catch my drift:

code:

+-------+---------+-----------------------+-----+
|key    |activity |timestamp              |diff |
+-------+---------+-----------------------+-----+
|0      |Complete |2007-05-07 09:51:30 AM |NULL |
|1      |Complete |2007-05-07 09:52:30 AM |60   |
|2      |Complete |2007-05-07 09:54:30 AM |120  |
|3      |Complete |2007-05-07 09:56:30 AM |120  |
|4      |Complete |2007-05-07 09:57:30 AM |60   |
+-------+---------+-----------------------+-----+

[sig69]

DateDiff?

[insan1ty]

sig69 schreef op maandag 07 mei 2007 @ 10:01:
DateDiff?

Dat werkt alleen tussen kolommen in dezelfde rij..

[TheRookie]

Als de key inderdaad oplopend is zou je een subquery kunnen doen door select top 1 where key < huidige key
pseudo:

SQL:

select 
  datediff (t1.timestamp, (select top 1 timestamp from tabel t2 where t2.key < t1.key order by t2.key desc) as runtime from tabel t1

[pistole]

of zo:

SQL:

select 
    t.*, 
    datediff(second, timestamp, (select timestamp from tabel where id=t.id+1)),
from tabel t

(ik deed hem precies andersom; ik vergelijk met het volgende record...)

[ Voor 21% gewijzigd door pistole op 07-05-2007 10:13 ]

[TheRookie]

@pistole: dit werkt alleen als er geen gaten in de 'key' zitten ...

[P_de_B]

sig69 schreef op maandag 07 mei 2007 @ 10:01:
DateDiff?

Het probleem is denk ik niet de functie, maar het gegeven dat je een resultaat uit de vorige rij moet hebben.

Onderstaande moet wel werken denk ik

SQL:

SELECT key, activity, timestamp, 
    (SELECT TOP 1 DateDiff(ss,timestamp,t1.timestamp) FROM tabel t2 WHERE timestamp < t1.timestamp ORDER BY Timestamp DESC) Diff
FROM tabel t1

Het zou traag kunnen zijn door de subquery. Er is dan denk ik nog wel een oplossing met een join.

edit:
traag

[pistole]

TheRookie schreef op maandag 07 mei 2007 @ 10:13:
@pistole: dit werkt alleen als er geen gaten in de 'key' zitten ...

Helemaal mee eens, je krijgt anders een NULL terug.
De tip van P_de_B om het met een join te doen is wellicht slimmer (sneller), maar dan geldt dezelfde beperking.
Je kan veiligheidshalve misschien een view over de tabel gooien met daarin een nieuwe identity kolom om zeker te weten dat je 'key' kolom aansluitend is.

[P_de_B]

Mijn oplossing werkt met gaten in de key


edit:
en die van TheRookie ook zie ik

[ Voor 35% gewijzigd door P_de_B op 07-05-2007 10:22 ]

[insan1ty]

Bedankt voor de tips! Ik ga het zo even proberen..

[curry684]

P_de_B schreef op maandag 07 mei 2007 @ 10:14:
[...]

Het zou traag kunnen zijn door de subquery. Er is dan denk ik nog wel een oplossing met een join.

Ik heb vergelijkbare constructies wel eens met een subquery, inner join en cross join gebouwd, en uit de query optimizer rolt uiteindelijk toch wel hetzelfde execution path. We hebben het hier over een echt DBMS he, niet MySQL

[P_de_B]

curry684 schreef op maandag 07 mei 2007 @ 11:12:
[...]

Ik heb vergelijkbare constructies wel eens met een subquery, inner join en cross join gebouwd, en uit de query optimizer rolt uiteindelijk toch wel hetzelfde execution path. We hebben het hier over een echt DBMS he, niet MySQL

Dat denk ik ook wel, maar toch: http://www.sqlteam.com/item.asp?ItemID=3856

[Grijze Vos]

P_de_B schreef op maandag 07 mei 2007 @ 11:18:
[...]

Dat denk ik ook wel, maar toch: http://www.sqlteam.com/item.asp?ItemID=3856

Ik gebruik eigenlijk nooit temp tables, dus misschien een domme vraag. Maar geeft dat geen concurrency problemen als je veel requests krijgt voor precies die query?

[P_de_B]

Tijdelijke tabellen die je aanmaakt met een #-teken zijn alleen benaderbaar binnen de huidige scope, andere gebruikers kunnen de tabellen niet zien. (Het zijn eigenlijk 'fysieke tabellen in de tempdb met een numerieke suffix). Tijdelijke tabellen met twee ##-tekens zijn wel globaal.

CREATE TABLE #temp = lokaal
CREATE TABLE ##temp = globaal

[curry684]

P_de_B schreef op maandag 07 mei 2007 @ 11:18:
[...]

Dat denk ik ook wel, maar toch: http://www.sqlteam.com/item.asp?ItemID=3856

Hmmm verbazend dat de Guru Guide solution zo traag blijkt daar, die zou ik zelf ook voorgesteld hebben