如何获得表A中列的最小值/最大值以及B中的值，其中B的行由A中的另一列确定

Question

我正在努力了解如何编写查询，以便为某些列返回其他列具有最小值或最大值的值，同时返回这些列的最小值/最大值。

我有两个Postgresql表，其中包含驱动器(带车旅行(和街道地址，我想从中选择数据，将驱动器分组为旅行。例如，从我家到商店是一次驾车旅行，再次返回是另一次驾车，将两者组合在一起形成一次单独的旅行。对于每次这样的分组旅行，我想得到第一个驱动器的开始时间和地址，最终驱动器的结束时间和位置，以及所有驱动器时间和距离的总和。

这是一个关于获得三个驱动器的示例查询，我想将它们组合成一个行程：

SELECT
start_time,
end_time,
duration,
distance,
start_address.name AS start_address,
end_address.name AS end_address
FROM drives
LEFT JOIN addresses start_address ON start_address_id = start_address.id
LEFT JOIN addresses end_address ON end_address_id = end_address.id
WHERE drives.id=ANY('{10, 11, 12}');

运行此查询可能会产生以下结果：

row# | start_time | end_time   | duration | distance | start_address | end_address
----------------------------------------------------------------------------------
1      14:40:00     14:43:00     3          0.75       Home            The store
2      14:48:00     14:58:00     10         2.25       The store       Post office
3      15:10:00     15:08:00     8          2.00       Post office     Work

现在，我想构造一个单独的查询，该查询应该产生一行，表示这三个驱动器的组合行程。我希望该行包含最小开始时间、最大结束时间、持续时间之和、距离之和、从具有最小开始时间的行开始的地址和具有最大结束时间的行的结束地址。

如果我省略了地址部分，这很简单：

SELECT
min(start_time) AS start_time,
max(end_time) AS end_time,
sum(duration) AS duration,
sum(distance) AS distance
FROM drives
WHERE drives.id=ANY('{10, 11, 12}');

row# | start_time | end_time   | duration | distance
----------------------------------------------------
1      14:40:00     15:08:00     21          5.00

但是，我如何编写查询以便同时获得地址呢？我想要这个结果：

row# | start_time | end_time   | duration | distance | start_address | end_address
----------------------------------------------------------------------------------
1      14:40:00     15:08:00     21          5.00      Home            Work

显然，我需要将地址部分返回到查询中，但无论我尝试什么，我都无法弄清楚如何正确构建它。坦率地说，我甚至不确定我是否答对了这个问题的标题：/

Answer 1

我们可以在主查询中使用窗口函数first_value和last_value，然后与一级子查询聚合为，

SELECT
min(start_time) AS start_time,
max(end_time) AS end_time,
sum(duration) AS duration,
sum(distance) AS distance,
max(start_address) start_address,
max(end_address) end_address  
FROM
(
SELECT
start_time,
end_time,
duration,
distance,
first_value(start_address.name) over (order by start_time,end_time) start_address,
last_value(end_address.name) over (order by start_time,end_time rows between unbounded preceding and unbounded following) end_address
FROM drives
LEFT JOIN addresses start_address ON start_address_id = start_address.id
LEFT JOIN addresses end_address ON end_address_id = end_address.id
WHERE drives.id=ANY('{10, 11, 12}')
) c;