Sveicināti!
Ir process, kurš MS SQL tabulā EVENTS raksta notikumus (līdzīgi ka "event log") YYYYMMDDHHMMSS formātā.
Piemēram:
IF OBJECT_ID('events', 'U') IS NOT NULL DROP TABLE events;
CREATE TABLE events
(
event_sequence_number INT NOT NULL IDENTITY(1,1) PRIMARY KEY, event_timestamp float
);
GO
insert into events (event_timestamp) values (20180108121314), (20180108131415) , (20180108131516), (20180108151617)
select * from events
event_sequence_number event_timestamp
1 20180108121314
2 20180108131415
3 20180108131516
4 20180108151617
Kādā veidā noteikt tos divus ierakstus starp kuriem ir vislielākais intervāls?
Šinī piemērā tie ir ieraksti #3 un #4.
Starpība - 20101 sekundes.
Piemēram SQL 2014 to atrod ar pieprasījumu:
SELECT event_sequence_number, event_timestamp,
Lead(event_timestamp, 1) OVER(ORDER BY event_timestamp) AS next_event_timestamp,
Lead(event_timestamp, 1) OVER(ORDER BY event_timestamp) - event_timestamp AS diff
FROM events
ORDER by diff DESC;
event_sequence_number event_timestamp next_event_timestamp diff
3 20180108131516 20180108151617 20101
1 20180108121314 20180108131415 10101
2 20180108131415 20180108131516 101
4 20180108151617 NULL NULL
Kā to atrast bez "Lead" funkcijas?
Paldies.