如何用SQL筛选2022-2024年电动车销量渗透率负增长的州？

这里提供两种高效的SQL实现方式，帮你计算2022-2024年电动车销量渗透率的变化并筛选负增长的州：

方法1：条件聚合（推荐，单表扫描效率更高） #

通过CASE WHEN在同一查询中分别计算2022和2024年的渗透率，直接计算差值后筛选：

SELECT 
    state,
    -- 计算2024年渗透率
    (SUM(CASE WHEN fiscal_year = 2024 THEN electric_vehicles_sold ELSE 0 END)::FLOAT / 
     SUM(CASE WHEN fiscal_year = 2024 THEN total_vehicles_sold ELSE 0 END)) * 100 AS penetration_2024,
    -- 计算2022年渗透率
    (SUM(CASE WHEN fiscal_year = 2022 THEN electric_vehicles_sold ELSE 0 END)::FLOAT / 
     SUM(CASE WHEN fiscal_year = 2022 THEN total_vehicles_sold ELSE 0 END)) * 100 AS penetration_2022,
    -- 计算渗透率差值
    ((SUM(CASE WHEN fiscal_year = 2024 THEN electric_vehicles_sold ELSE 0 END)::FLOAT / 
      SUM(CASE WHEN fiscal_year = 2024 THEN total_vehicles_sold ELSE 0 END)) - 
     (SUM(CASE WHEN fiscal_year = 2022 THEN electric_vehicles_sold ELSE 0 END)::FLOAT / 
      SUM(CASE WHEN fiscal_year = 2022 THEN total_vehicles_sold ELSE 0 END))) * 100 AS penetration_change
FROM sales_by_states
WHERE fiscal_year IN (2022, 2024) -- 只过滤需要的年份
GROUP BY state
HAVING penetration_change < 0 -- 筛选负增长的州

注意点： #

• 使用::FLOAT（或CAST(xxx AS FLOAT)）将整数转换为浮点数，避免整数除法导致的精度丢失（比如整数相除结果为0的情况）。
• HAVING子句用于筛选聚合后的结果，替代WHERE（因为WHERE不能过滤聚合计算出的字段）。

方法2：子查询+JOIN（逻辑更直观） #

先分别计算两年的渗透率，再通过state字段关联，最后计算差值筛选：

WITH penetration_2022 AS (
    SELECT 
        state,
        (SUM(electric_vehicles_sold)::FLOAT / SUM(total_vehicles_sold)) * 100 AS rate_2022
    FROM sales_by_states
    WHERE fiscal_year = 2022
    GROUP BY state
),
penetration_2024 AS (
    SELECT 
        state,
        (SUM(electric_vehicles_sold)::FLOAT / SUM(total_vehicles_sold)) * 100 AS rate_2024
    FROM sales_by_states
    WHERE fiscal_year = 2024
    GROUP BY state
)
SELECT 
    p22.state,
    p24.rate_2024,
    p22.rate_2022,
    p24.rate_2024 - p22.rate_2022 AS penetration_change
FROM penetration_2022 p22
JOIN penetration_2024 p24 ON p22.state = p24.state
WHERE p24.rate_2024 - p22.rate_2022 < 0

适用场景： #

如果需要单独复用两年的渗透率数据，或者逻辑拆分更清晰时，这种方法更合适。

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