SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
create table T1 (event_id int, device_id char, event_time date, var1 int, var2 int); insert into T1 values (1, 'A', '2021-01-01', 5, 6), (2, 'C', '2021-01-02', 8, 7), (3, 'B', '2021-01-05', 1, 6), (4, 'C', '2021-01-07', 7, 7), (5, 'D', '2021-01-12', 8, 9), (6, 'C', '2021-01-18', 3, 4), (7, 'B', '2021-01-21', 7, 1); create table T2 (device_id char, incident_time date); insert into T2 values ('B', '2021-01-06'), ('C', '2021-01-17'), ('D', '2021-01-17'); with T AS ( select T2.*, T1.event_id, T1.event_time, var1, var2, ROW_NUMBER() over(partition by T2.device_id order by ABS(event_time - incident_time)) rn from T2 join T1 USING(device_id) ) SELECT device_id, incident_time, event_id, event_time, var1, var2 FROM T WHERE rn = 1 ; select distinct on (T2.device_id) T2.device_id, T2.incident_time, T1.event_id, T1.event_time, T1.var1, T1.var2 from T2 left join T1 ON T1.device_id = T2.device_id order by T2.device_id, ABS(T2.incident_time - T1.event_time); Select T1.event_id, T1.device_id, T1.event_time, T2.incident_time, T1.var1, T1.var2 From T1 Inner Join T2 On (T1.device_id=T2.device_id) Order by Abs(T1.event_time-T2.incident_time) FETCH FIRST 1 ROWS WITH TIES
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear