While loading my table, I would like to establish an subselect on the same data source.
Entry
Timestamp
EventId
PartnerEventId
TimestampPartner
2569
10.06.2024 00:40:00.00
5
15
2570
10.06.2024 00:56:53.53
3
7
2571
10.06.2024 01:25:00.00
12
5
10.06.2024 00:40:00.00
2572
10.06.2024 01:39:33.33
3
7
2573
10.06.2024 02:15:57.57
5
15
When e.g. loading line 2571 I would like to lookup the TimestampPartner according to PartnerEventId = EventId and having the next older timestamp.
A "classic" SQL statement could look like: SELECT LIMIT 1 timestamp FROM table t WHERE t.timestamp < timestamp AND PartnerId = t.EventId ORDER BY t.Timestamp DESC
I expanded the example a bit to have multiple partner events that we should find the nearest, earlier timestamp for. Did I understand the problem correctly?
DataIn:
Load * INLINE [
Entry, Timestamp, EventId, PartnerEventId
2566, 2024-06-10 00:25:00.00,7,15
2567, 2024-06-10 00:30:00.00,7,15
2568, 2024-06-10 00:35:00.00,5,15
2569, 2024-06-10 00:40:00.00,5,15
2570, 2024-06-10 00:56:53.53,3,7
2571, 2024-06-10 01:25:00.00,12,5
2572, 2024-06-10 01:39:33.33,3,8
2573, 2024-06-10 02:15:57.57,5,15
];
DataJoined:
noconcatenate Load * resident DataIn;
left join
Load
EventId as PartnerEventId,
Timestamp as PartnerTimestamp
resident DataIn;
drop table DataIn;
DataFiltered:
NoConcatenate Load
*,
if(peek(EventId)<>EventId,1,peek(Rank)+1) as Rank
resident DataJoined where PartnerTimestamp < Timestamp order by Entry,PartnerTimestamp desc;
drop table DataJoined;
Data:
NoConcatenate Load * resident DataFiltered where Rank=1;
