PostgreSQL引用非FROM子句表创建百分比列及报错解决

两个查询的问题说明 #

• 第一个查询问题：PostgreSQL 中整数相除默认执行整数截断逻辑，COUNT(*) 和子查询返回的总骑行量均为整数类型，二者相除时只要分子小于分母就会直接返回 0，导致所有 Trips_percent 取值为 0。
• 第二个查询问题：你猜测的原因是正确的，WITH 子句定义的 total 临时表未在主查询的 FROM 子句中被引用，因此查询引擎无法识别 total.records 字段，触发表不存在的报错。

解决方法 #

方案1：直接修改原查询的除法逻辑，显式做类型转换 #

只需要将除法的其中一个操作数转换为浮点/精确数值类型即可避免整数截断，也可额外增加百分比格式化逻辑，示例代码如下：

SELECT  Start_day,
        member_casual AS Membership,  
        COUNT(*) AS Trips_Count, 
        -- 转成numeric类型后计算，保留2位小数输出百分比格式
        ROUND(COUNT(*)::numeric * 100 / (SELECT count(*) from rides), 2) AS Trips_percent, 
        AVG(duration) AS Average_Trip_Duration
FROM (SELECT member_casual,  
      ended_at - started_at AS duration, 
      EXTRACT(DOW from started_at) AS Start_day_num, 
      TO_CHAR(started_at,'DY') AS Start_day 
      FROM rides
     ) AS member_duration
GROUP BY Start_day, Start_day_num, member_casual 
ORDER BY Start_day_num, Trips_Count;

方案2：修复CTE写法的引用问题 #

由于total临时表只有一行总记录数，直接在主查询的FROM子句中添加交叉关联即可，同时保留类型转换逻辑，修改后代码如下：

WITH total AS 
    (SELECT COUNT(member_casual) AS records 
     FROM rides)
SELECT  Start_day,
        member_casual AS Membership,  
        COUNT(*) AS Trips_Count, 
        ROUND(COUNT(*)::numeric * 100 / total.records, 2) AS Trips_percent, 
        AVG(duration) AS Average_Trip_Duration
FROM (SELECT member_casual,  
      ended_at - started_at AS duration, 
      EXTRACT(DOW from started_at) AS Start_day_num, 
      TO_CHAR(started_at,'DY') AS Start_day 
      FROM rides
     ) AS member_duration
CROSS JOIN total -- 新增该行关联CTE临时表
GROUP BY Start_day, Start_day_num, member_casual, total.records -- 分组维度新增total.records
ORDER BY Start_day_num, Trips_Count;

内容的提问来源于stack exchange，提问作者Mohammad ElNesr