SQL同一查询中计算列间除法运算报错的解决方法

我有如下SQL查询语句：

SELECT `NeighbourhoodName`,
count(NAME) as `Number of Parks`,
sum(CASE 
    WHEN `parks`.`Advisories` = 'Y' THEN 1
    ELSE 0 
END) as Advisories,
FROM parks
GROUP BY `NeighbourhoodName`;

我希望将Advisories列的所有值除以Number of Parks的值，于是修改查询如下：

SELECT `NeighbourhoodName`,
count(NAME) as `Number of Parks`,
sum(CASE 
    WHEN `parks`.`Advisories` = 'Y' THEN 1
    ELSE 0 
END)/`Number of Parks` as Advisories
FROM parks
GROUP BY `NeighbourhoodName`;

但收到错误：

Unknown column, `Number of Parks` in field list.

请问如何在同一个查询中完成该除法运算？

解决方案 #

方法1：重复聚合计算 #

SQL的SELECT子句中无法直接引用同层级定义的别名，因此可以直接在除法运算里重复count(NAME)的计算逻辑：

SELECT `NeighbourhoodName`,
count(NAME) as `Number of Parks`,
sum(CASE 
    WHEN `parks`.`Advisories` = 'Y' THEN 1
    ELSE 0 
END)/count(NAME) as `Advisory Rate`
FROM parks
GROUP BY `NeighbourhoodName`;

注意：如果count(NAME)可能为0，建议用NULLIF避免触发除以0的错误：

sum(CASE 
    WHEN `parks`.`Advisories` = 'Y' THEN 1
    ELSE 0 
END)/NULLIF(count(NAME), 0) as `Advisory Rate`

方法2：使用子查询或CTE #

先通过子查询或CTE计算出基础聚合结果，再在外部查询中进行除法运算：

子查询写法

SELECT 
    `NeighbourhoodName`,
    `Number of Parks`,
    `Advisories`/`Number of Parks` as `Advisory Rate`
FROM (
    SELECT `NeighbourhoodName`,
    count(NAME) as `Number of Parks`,
    sum(CASE 
        WHEN `parks`.`Advisories` = 'Y' THEN 1
        ELSE 0 
    END) as Advisories
    FROM parks
    GROUP BY `NeighbourhoodName`
) AS park_stats;

CTE写法（适配MySQL 8.0+、PostgreSQL、SQL Server等支持CTE的数据库）

WITH park_stats AS (
    SELECT `NeighbourhoodName`,
    count(NAME) as `Number of Parks`,
    sum(CASE 
        WHEN `parks`.`Advisories` = 'Y' THEN 1
        ELSE 0 
    END) as Advisories
    FROM parks
    GROUP BY `NeighbourhoodName`
)
SELECT 
    `NeighbourhoodName`,
    `Number of Parks`,
    `Advisories`/`Number of Parks` as `Advisory Rate`
FROM park_stats;

方法3：使用窗口函数（部分数据库支持） #

如果你的数据库支持窗口函数，可以通过PARTITION BY实现分组统计后再计算比值：

SELECT DISTINCT
    `NeighbourhoodName`,
    count(NAME) OVER (PARTITION BY `NeighbourhoodName`) as `Number of Parks`,
    sum(CASE WHEN `Advisories` = 'Y' THEN 1 ELSE 0 END) OVER (PARTITION BY `NeighbourhoodName`)
    / count(NAME) OVER (PARTITION BY `NeighbourhoodName`) as `Advisory Rate`
FROM parks;

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