Re: [SQL] join tables by nearest timestamp
От | Brice André |
---|---|
Тема | Re: [SQL] join tables by nearest timestamp |
Дата | |
Msg-id | CAOBG12nncp-9GA3AN2XwQcH2X4+SJmXmRia3tZorU2nXiScQGQ@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: [SQL] join tables by nearest timestamp (Achilleas Mantzios <achill@matrix.gatewaynet.com>) |
Ответы |
Re: [SQL] join tables by nearest timestamp
(Brice André <brice@famille-andre.be>)
|
Список | pgsql-sql |
Many thanks Achilleas. I did not think to use an outer join in combination with Distnct and order by clauses, which seems to be the key to my problem.
I slighly adapted your proposal to match my DB schema, but also to select the real nearest point (and not the nearest one after). I defined a function 'abs' that computes the absolute value from a timestamp and the query looks like : SELECT DISTINCT ON (l1."ID") (l1."Time"-l2."Time") as time_diff, l1.*,l2.* from
"KnxBusAccess" l1
LEFT OUTER JOIN
"KnxBusAccess" l2
ON ('t')
where
l1."ToGroupAddress" = '2/0/1' AND
l1."Time" >= (now()-interval '1 day') AND
l2."ToGroupAddress" = '2/5/1' AND
l2."Time" >= (now()-interval '1 day')
order by l1."ID", abs(l2."Time"-l1."Time")
2017-11-01 9:11 GMT+01:00 Achilleas Mantzios <achill@matrix.gatewaynet.com>:
On 01/11/2017 10:06, Achilleas Mantzios wrote:On 01/11/2017 07:53, Brice André wrote:oopss, sorry I forgot, you'll have to add a DISTINCT ON and order by l2.logtime in order to have what you want :Dear all,smth like :
I am running a postgresql 9.1 server and I have a table containing events information with, for each entry, an event type, a timestamp, and additional information.
I would want to write a query that would return all events of type 'a', but each returned entry should be associated to the neraest event of type 'b' (ideally, the nearest, non taking into account if it happened before or after, but if not possible, it could be the first happening just after).
By searching on the web, I found a solution base on a "LEFT JOIN LATERAL", but this is not supported by postgresql 9.1 (and I cannot update my server) :
SELECT *
FROM
(SELECT * FROM events WHERE type = 'a' ) as t1
LEFT JOIN LATERAL
(SELECT * FROM events WHERE type = 'b' AND timestamp >= t1.timestamp ORDER BY timestamp LIMIT 1) as t2
ON TRUE;
Any idea on how to adapt this query so that it runs on 9.1 ? Or any other idea on how to perform my query ?
SELECT l1.*,l2.logtime,l2.category,l2.username from logging l1 LEFT OUTER JOIN logging l2 ON ('t') where l1.category='vsl.login' AND (l2.category IS NULL OR l2.category='vsl.SpareCases') AND (l2.logtime IS NULL OR l2.logtime>=l1.logtime) order by l1.logtime;
SELECT DISTINCT ON (l1.logtime,l1.category,l1.username,l1.action) l1.*,l2.logtime,l2.category,l2 .username from logging l1 LEFT OUTER JOIN logging l2 ON ('t') where l1.category='vsl.login' AND (l2.category IS NULL OR l2.category='vsl.SpareCases') AND (l2.logtime IS NULL OR l2.logtime>=l1.logtime) order by l1.logtime,l1.category,l1.user name,l1.action,l2.logtime;
Thanks in advance,
Brice
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
В списке pgsql-sql по дате отправления: