PostgreSQL关联多表时获取非空mode的每个session_id最后记录

首先给出两张表的结构及插入数据：

CREATE TABLE labels(user_id INT, session_id INT, start_time TIMESTAMP, mode TEXT);
INSERT INTO labels (user_id, session_id, start_time, mode)
VALUES (48, 652, '2016-04-01 00:47:00', 'foot'),
       (46, 663, '2016-04-01 00:13:00', 'car'),
        (9, 658, '2016-04-01 00:45:19', '');

CREATE TABLE session(session_id INT, timestamp TIMESTAMP, speed DOUBLE PRECISION);
INSERT INTO session(session_id, timestamp, speed)
VALUES (652, '2016-04-01 00:47:00', 0.77), (652, '2016-04-01 00:53:00', 3.57), (652, '2016-04-01 01:04:00', 1.25),
       (663, '2016-04-01 00:13:00', 2.5), (663, '2016-04-01 00:20:00', 12.5),
        (658, '2016-04-01 00:45:19', 2.5), (658, '2016-04-01 00:50:19', 5.3), (658, '2016-04-01 01:15:19', 7.6);

原查询可返回session_id关联且mode不为空的两张表连接结果：

SELECT l.user_id, l.session_id, l.start_time, l.mode,  s.timestamp
FROM labels l
JOIN session s
 ON l.session_id = s.session_id
WHERE l.mode = '' IS NOT TRUE

要实现仅返回mode不为空的每个session_id对应的最后一条timestamp（命名为end_time），可以先对session表按session_id分组，取每组最大的timestamp作为end_time，再和labels表关联并过滤mode不为空的记录。修改后的查询如下：

SELECT l.user_id, l.session_id, l.start_time, l.mode, s.end_time
FROM labels l
JOIN (
    SELECT session_id, MAX(timestamp) AS end_time
    FROM session
    GROUP BY session_id
) s ON l.session_id = s.session_id
WHERE l.mode <> '';

预期输出 #

user_id session_id   start_time         mode         end_time
 48        652      2016-04-01 00:47:00 foot    2016-04-01 01:04:00
 46        663      2016-04-01 00:13:00  car    2016-04-01 00:20:00

内容的提问来源于stack exchange，提问作者Amina Umar