PostgreSQL按首次注册日期划分车龄分组统计单列平均里程实现方法

基础数据说明 #

当前使用的车辆信息表为简化版本，核心存储字段包括首次注册日期、行驶里程、价格、车辆型号等，表结构与示例数据如下：

需求与现有进度 #

需要基于firstregistration字段计算车辆车龄，按固定区间完成分组，提取各维度的统计分析指标。目前已经完成了各车龄分组的车辆数量统计，对应查询语句如下：

SELECT SUM(CASE WHEN date_part('year', age(firstregistration))<1 THEN 1 ELSE 0 END) AS "0-1",
    SUM(CASE WHEN date_part('year', age(firstregistration)) BETWEEN 1 AND 2 THEN 1 ELSE 0 END) AS "1-2",
    SUM(CASE WHEN date_part('year', age(firstregistration)) BETWEEN 2 AND 3 THEN 1 ELSE 0 END) AS "2-3",
    SUM(CASE WHEN date_part('year', age(firstregistration)) BETWEEN 3 AND 4 THEN 1 ELSE 0 END) AS "3-4",
    SUM(CASE WHEN date_part('year', age(firstregistration)) BETWEEN 4 AND 5 THEN 1 ELSE 0 END) AS "4-5",
    SUM(CASE WHEN date_part('year', age(firstregistration)) BETWEEN 5 AND 6 THEN 1 ELSE 0 END) AS "5-6",
    SUM(CASE WHEN date_part('year', age(firstregistration)) BETWEEN 6 AND 7 THEN 1 ELSE 0 END) AS "6-7",
    SUM(CASE WHEN date_part('year', age(firstregistration)) BETWEEN 7 AND 8 THEN 1 ELSE 0 END) AS "7-8",
    SUM(CASE WHEN date_part('year', age(firstregistration)) BETWEEN 8 AND 9 THEN 1 ELSE 0 END) AS "8-9",
    SUM(CASE WHEN date_part('year', age(firstregistration)) BETWEEN 8 AND 9 THEN 1 ELSE 0 END) AS "9-10",
    SUM(CASE WHEN date_part('year', age(firstregistration)) >= 10 THEN 1 ELSE 0 END) AS ">=10" FROM car_table

注意：原统计语句中9-10车龄分组的判断逻辑误复制了8-9分组的条件，实际使用前需要修正为匹配9-10岁车龄的判断规则。

独立统计平均里程的实现方案 #

按照要求，平均里程统计不需要和原数量统计查询合并，直接复用现有分组判断逻辑改写即可，核心是将原来的计数聚合逻辑替换为对km字段求平均的逻辑，具体查询语句如下：

SELECT 
  AVG(CASE WHEN date_part('year', age(firstregistration)) < 1 THEN km END) AS "0-1",
  AVG(CASE WHEN date_part('year', age(firstregistration)) BETWEEN 1 AND 2 THEN km END) AS "1-2",
  AVG(CASE WHEN date_part('year', age(firstregistration)) BETWEEN 2 AND 3 THEN km END) AS "2-3",
  AVG(CASE WHEN date_part('year', age(firstregistration)) BETWEEN 3 AND 4 THEN km END) AS "3-4",
  AVG(CASE WHEN date_part('year', age(firstregistration)) BETWEEN 4 AND 5 THEN km END) AS "4-5",
  AVG(CASE WHEN date_part('year', age(firstregistration)) BETWEEN 5 AND 6 THEN km END) AS "5-6",
  AVG(CASE WHEN date_part('year', age(firstregistration)) BETWEEN 6 AND 7 THEN km END) AS "6-7",
  AVG(CASE WHEN date_part('year', age(firstregistration)) BETWEEN 7 AND 8 THEN km END) AS "7-8",
  AVG(CASE WHEN date_part('year', age(firstregistration)) BETWEEN 8 AND 9 THEN km END) AS "8-9",
  AVG(CASE WHEN date_part('year', age(firstregistration)) BETWEEN 9 AND 10 THEN km END) AS "9-10",
  AVG(CASE WHEN date_part('year', age(firstregistration)) >= 10 THEN km END) AS ">=10"
FROM car_table

实现要点说明：

• 分组判断逻辑和原数量统计完全一致，仅当车辆匹配对应车龄区间时返回该车辆的km值，不匹配的场景不写ELSE分支，默认返回NULL
• AVG()聚合函数会自动忽略返回的NULL值，仅对当前分组内的有效里程值计算平均值，避免不匹配的车辆被纳入计算导致结果偏差；如果手动添加ELSE 0分支，会让所有非当前分组的车辆以0值参与平均计算，拉低结果数值
• 语句中已修正原数量统计里9-10区间判断逻辑写错的问题，和分组命名保持匹配
• 如果需要对平均里程值做整数取整，可以在外层套ROUND(AVG(...), 0)函数控制输出精度

内容的提问来源于stack exchange，提问作者Andrija Glavas