我有一张桌子，可以记录 run 情况.它有以下列:
| ID | distance | run_time | run_date |
|---|---|---|---|
| 1 | 10 | 01:35:00 | 2023-12-04 |
| 2 | 10 | 01:41:00 | 2023-12-05 |
我想计算特定日期范围内每英里的平均速度(格式为mm:ss).例如，日期范围为12/04-12/05，则每英里的速度为09:48.第一步是计算出总里程和分钟数.然后用分钟数除以总里程数.最后一步是将分钟数转换为mm:ss格式.
以下是我到目前为止所能想到的:
with average as (
select
runTime.totalTime / distance.totalDistance as averagePace
from
((
select
sum(distance) as totalDistance
from
runs
where
run_date >= '2023-12-04'
and run_date <= '2023-12-05') ) as distance,
(
select
extract / 60 as totalTime
from
(
select
extract(EPOCH
from
sum )
from
(
select
sum(run_time)
from
runs
where
run_date >= '2023-12-04'
and run_date <= '2023-12-05'))) as runTime)
select
TO_CHAR((averagePace || ' minute')::interval,
'MI:SS')
from
average
查询计算出了正确的速度，但我觉得它不需要这么长.有没有更干净、更有效的方法来计算每英里的平均速度？