PostgreSQL多CTE运算结果异常：整数除法致结果为0

PostgreSQL中，两个整数做除法时会自动舍弃小数部分取整。你的主队获胜数、客队获胜数和总比赛数都是整数类型，当获胜数小于总比赛数时，整数/整数的结果就会被截断为0，这就是你得到异常结果的原因。

只需要把除法运算中的任意一个数值转换为浮点类型，强制PostgreSQL执行浮点除法即可。修改后的代码如下：

WITH home_team_wins (match) AS (
        SELECT match
        FROM penalties
        WHERE neutral_stadium = 0
            AND attacker_home = 1
            AND match_winner = 1
        GROUP BY match
    ),
    away_team_wins (match) AS (
        SELECT match
        FROM penalties
        WHERE neutral_stadium = 0
            AND attacker_home = 0
            AND match_winner = 1
    ),
    num_home_wins (match) AS (
        SELECT COUNT(DISTINCT match)
        FROM home_team_wins
    ),
    num_away_wins (match) AS (
        SELECT COUNT(DISTINCT match)
        FROM away_team_wins
    ),
    total_matches (match_count) AS (
        SELECT COUNT(DISTINCT match)
        FROM penalties
        WHERE neutral_stadium = 0
    )
SELECT 
    num_home_wins.match::FLOAT / total_matches.match_count AS home_win_percentage,
    num_away_wins.match::FLOAT / total_matches.match_count AS away_win_percentage
FROM num_home_wins, num_away_wins, total_matches;

你也可以用CAST(num_home_wins.match AS FLOAT)或者num_home_wins.match * 1.0来实现同样的类型转换效果。

你当前的多CTE写法会多次扫描表，效率偏低。可以通过一次聚合查询完成所有统计，逻辑更简洁且性能更好：

SELECT
    COUNT(DISTINCT CASE WHEN attacker_home = 1 AND match_winner = 1 THEN match END)::FLOAT / COUNT(DISTINCT match) AS home_win_percentage,
    COUNT(DISTINCT CASE WHEN attacker_home = 0 AND match_winner = 1 THEN match END)::FLOAT / COUNT(DISTINCT match) AS away_win_percentage
FROM penalties
WHERE neutral_stadium = 0;

或者用分组统计的方式，可读性更强：

WITH match_results AS (
    SELECT 
        DISTINCT match,
        CASE 
            WHEN attacker_home = 1 AND match_winner = 1 THEN 'home_win'
            WHEN attacker_home = 0 AND match_winner = 1 THEN 'away_win'
        END AS result
    FROM penalties
    WHERE neutral_stadium = 0
)
SELECT
    SUM(CASE WHEN result = 'home_win' THEN 1 ELSE 0 END)::FLOAT / COUNT(*) AS home_win_percentage,
    SUM(CASE WHEN result = 'away_win' THEN 1 ELSE 0 END)::FLOAT / COUNT(*) AS away_win_percentage
FROM match_results;

内容的提问来源于stack exchange，提问作者23manders