如何在SQL中基于不同州值计算KY与TN仓库的数量比例？

需求与示例数据 #

需要计算肯塔基州（KY）仓库数量与田纳西州（TN）仓库数量的比例，示例数据表如下：

现有尝试的问题 #

使用WHERE子句只能筛选单个州的数据，无法同时获取两个州的数量进行计算；你尝试的子查询写法会因为外层FROM table返回多行数据，导致结果重复，并且SELECT子句中不能直接使用刚定义的别名进行计算。

推荐解决方案 #

方案1：条件聚合（高效，单次表扫描）

这种方法只需扫描一次表，通过CASE WHEN在聚合时分别统计两个州的仓库数量，再计算比例：

SELECT
    COUNT(DISTINCT CASE WHEN state = 'KY' THEN warehouse_id END) AS ky_warehouse_count,
    COUNT(DISTINCT CASE WHEN state = 'TN' THEN warehouse_id END) AS tn_warehouse_count,
    -- 处理TN数量为0的情况，避免除以0错误
    COUNT(DISTINCT CASE WHEN state = 'KY' THEN warehouse_id END) / 
    NULLIF(COUNT(DISTINCT CASE WHEN state = 'TN' THEN warehouse_id END), 0) AS ky_tn_ratio
FROM warehouse_table;

说明：COUNT函数会忽略NULL值，所以不符合条件的仓库ID会被自动排除，无需额外过滤。NULLIF用于防止TN仓库数量为0时触发除以0的错误，此时比例会返回NULL。

方案2：修正子查询写法（避免多行结果）

如果你更倾向于使用子查询，可以将两个统计子查询放到一个派生表中，外层再计算比例，避免返回多行：

SELECT
    ky_count,
    tn_count,
    ky_count / NULLIF(tn_count, 0) AS ky_tn_ratio
FROM (
    SELECT
        (SELECT COUNT(DISTINCT warehouse_id) FROM warehouse_table WHERE state = 'KY') AS ky_count,
        (SELECT COUNT(DISTINCT warehouse_id) FROM warehouse_table WHERE state = 'TN') AS tn_count
) AS state_counts;

说明：派生表state_counts只会返回一行数据，外层查询基于这一行计算比例，解决了原写法返回多行的问题。

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