PostgreSQL技术问询：计算用户行程总时长并按指定规则排序

表结构 #

users: {
  id: BIGSERIAL PRIMARY KEY
  name: VARCHAR(255)
  phone: VARCHAR(255)
}

trips: {
  id: BIGSERIAL PRIMARY KEY
  user_id: BIGINT   
  started_at: TIMESTAMP
  finished_at: TIMESTAMP
}

查询需求 #

返回用户的id、name、phone，以及该用户所有行程的起止时间差之和的小时数的整数部分；结果按总时长（小时数）降序排序，若时长相同则按用户id升序排序。

原错误查询语句 #

SELECT id, name, phone, FLOOR(trips_length)
  FROM users JOIN (
      SELECT user_id, SUM(
          DATE_PART('hour', finished_at - started_at)  + 
          DATE_PART('minute', finished_at - started_at) / 60  +
          DATE_PART('second', finished_at - started_at) / 3600) AS trips_length
      FROM trips GROUP BY user_id ) AS twl ON id = user_id
  ORDER BY trips_length DESC, users.id ASC;

问题分析与修正方案 #

原查询的时间差计算方式逻辑可行，但在PostgreSQL中直接基于时间间隔的总秒数转换会更简洁准确，同时可避免拆分时分秒可能带来的精度问题。以下提供两种场景的修正方案：

方案1：仅统计有行程记录的用户 #

SELECT 
  u.id, 
  u.name, 
  u.phone, 
  FLOOR(SUM(EXTRACT(EPOCH FROM (t.finished_at - t.started_at)) / 3600)) AS total_hours
FROM users u
JOIN trips t ON u.id = t.user_id
GROUP BY u.id, u.name, u.phone
ORDER BY total_hours DESC, u.id ASC;

方案2：统计所有用户（含无行程的用户，总时长显示0） #

SELECT 
  u.id, 
  u.name, 
  u.phone, 
  FLOOR(COALESCE(SUM(EXTRACT(EPOCH FROM (t.finished_at - t.started_at)) / 3600), 0)) AS total_hours
FROM users u
LEFT JOIN trips t ON u.id = t.user_id
GROUP BY u.id, u.name, u.phone
ORDER BY total_hours DESC, u.id ASC;

代码说明 #

• EXTRACT(EPOCH FROM interval)：将时间间隔转换为总秒数，是PostgreSQL中计算时间差总时长的高效方式
• / 3600：将总秒数转换为小时数
• FLOOR()：取小时数的整数部分（即题目要求的"正确部分"）
• COALESCE(..., 0)：当用户无行程时，将SUM返回的NULL替换为0
• GROUP BY u.id, u.name, u.phone：因users.id是主键，按其分组即可直接关联同表其他字段（PostgreSQL支持主键分组后选择同表非分组字段）

内容的提问来源于stack exchange，提问作者Mr. A