Nehmen Sie an der Rangliste Zeit auf einem einmaligen Rang teil, um den Abstand zu erhalten:
with cte_ranked as (
select *, row_number() over (partition by UserId order by Time) as rn
from table)
select l.*, datediff(minute, r.Time, l.Time) as gap_length
from cte_ranked l join cte_ranked r on l.UserId = r.UserId and l.rn = r.rn-1
Sie können dann viele Methoden anwenden, um die maximale Lücke zu ermitteln, wann sie begonnen hat usw.
Update
Meine ursprüngliche Antwort wurde von einem Mac aus geschrieben, ohne dass ich eine Datenbank zum Testen hatte. Ich hatte etwas mehr Zeit, um mit diesem Problem zu spielen und tatsächlich zu testen und zu messen, wie es mit einer Tabelle mit 1 Mio. Datensätzen funktioniert. Meine Testtabelle ist wie folgt definiert:
create table access (id int identity(1,1)
, UserId int not null
, Time datetime not null);
create clustered index cdx_access on access(UserID, Time);
go
Für die Auswahl des Datensatzes für jegliche Informationen ist meine bevorzugte Antwort bisher diese:
with cte_gap as (
select Id, UserId, a.Time, (a.Time - prev.Time) as gap
from access a
cross apply (
select top(1) Time
from access b
where a.UserId = b.UserId
and a.Time > b.Time
order by Time desc) as prev)
, cte_max_gap as (
select UserId, max(gap) as max_gap
from cte_gap
group by UserId)
select g.*
from cte_gap g
join cte_max_gap m on m.UserId = g.UserId and m.max_gap = g.gap
where g.UserId = 42;
Von 1M Datensatz, ~47k verschiedene Benutzer, wird das Ergebnis für diese in 1ms auf meinem Test puny Instanz (warmen Cache), 48 Seite liest zurückgegeben.
Wenn der Filter UserId=42 entfernt wird, benötigen die maximale Lücke und die Zeit, zu der sie für jeden Benutzer auftrat (mit Duplikaten für mehrere maximale Lücken) 6379139 Lesungen, was ziemlich schwer ist und auf meinem Testrechner 14 Sekunden dauert.
Die Zeit kann halbiert werden, wenn nur die UserId und der maximale Abstand benötigt werden (keine Info wenn die maximale Lücke aufgetreten ist):
select UserId, max(a.Time-prev.Time) as gap
from access a
cross apply (
select top(1) Time
from access b
where a.UserId = b.UserId
and a.Time > b.Time
order by Time desc
) as prev
group by UserId
Hierfür sind nur 3193448 Lesevorgänge erforderlich, also nur die Hälfte im Vergleich zu früher, und der Vorgang ist bei 1 Mio. Datensätzen in 6 Sekunden abgeschlossen. Der Unterschied ist darauf zurückzuführen, dass in der vorherigen Version jede Lücke einmal ausgewertet werden musste, um die maximale Lücke zu finden, und dann erneut ausgewertet werden musste, um die Lücken zu finden, die mit der maximalen Lücke übereinstimmen. Beachten Sie, dass die Struktur der von mir vorgeschlagenen Tabelle mit einem Index auf (UserId, Time) für diese Leistungsergebnisse wie folgt aussieht kritisch .
Was die Verwendung von CTEs und "Partitionen" (besser bekannt als Ranking-Funktionen) betrifft, so ist dies alles ANSI SQL-99 und wird von den meisten Anbietern unterstützt. Das einzige SQL-Server-spezifische Konstrukt war die Verwendung der datediff Funktion, die nun entfernt ist. Ich habe das Gefühl, dass einige Leser "agnostisch" als "kleinster gemeinsamer Nenner von SQL, das auch von meinem Lieblingsanbieter verstanden wird" verstehen. Beachten Sie auch, dass die Verwendung von gemeinsamen Tabellenausdrücken und des Cross-Apply-Operators nur dazu dient, die Lesbarkeit der Abfrage zu verbessern. Beide können durch abgeleitete Tabellen ersetzt werden, indem man eine einfache, mechanische Ersetzung vornimmt. Hier ist die genau dasselbe Abfrage, bei der die CTEs durch abgeleitete Tabellen ersetzt wurden. Ich überlasse es Ihnen, die Lesbarkeit im Vergleich zur CTE-basierten Abfrage selbst zu beurteilen:
select g.*
from (
select Id, UserId, a.Time, (a.Time - (
select top(1) Time
from access b
where a.UserId = b.UserId
and a.Time > b.Time
order by Time desc
)) as gap
from access a) as g
join (
select UserId, max(gap) as max_gap
from (
select Id, UserId, a.Time, (a.Time - (
select top(1) Time
from access b
where a.UserId = b.UserId
and a.Time > b.Time
order by Time desc
)) as gap
from access a) as cte_gap
group by UserId) as m on m.UserId = g.UserId and m.max_gap = g.gap
where g.UserId = 42
Verdammt, ich hatte gehofft, dass es noch komplizierter wird, lol. Dies ist recht lesbar, weil es nur zwei CTEs zu starten hatte. Dennoch, auf Abfragen mit 5-6 abgeleitete Tabellen, die CTE-Form ist Weg, Weg mehr lesbar.
Der Vollständigkeit halber ist hier dieselbe Transformation auf meine vereinfachte Abfrage angewandt (nur maximale Lücken, keine Lückenendzeit und Zugangskennung):
select UserId, max(gap)
from (
select UserId, a.Time-(
select top(1) Time
from access b
where a.UserId = b.UserId
and a.Time > b.Time
order by Time desc) as gap
from access a) as gaps
group by UserId
0 Stimmen
Können Sie präzisieren: Suchen Sie nach der größten Lücke zwischen angrenzend Datensätze, geordnet nach ID und gefiltert nach Benutzer, oder die größte Lücke zwischen zwei beliebige Datensätze für denselben Benutzer? Für beide Fälle lautet die Antwort 2 für Ihren Testfall.
0 Stimmen
@richardtellent: Ich suche nach der längsten Lücke zwischen "benachbarten" Benutzereinträgen, wobei "benachbart" bedeutet, dass kein Datum-Zeit-Eintrag dazwischen liegt (und nicht auf IDs basiert). Ich hoffe, das war klar. Ich bin mir nicht sicher, ob ich Ihre zweite Erklärung verstanden habe, denn die größte Lücke zwischen zwei beliebigen Einträgen liegt zwischen dem ersten (1) und dem letzten (4).