基于同表双查询的太阳能板年度效率计算SQL问题

你之前用UNION得到的是同一年份的两行记录（一行发电量、一行日照时长），这种行合并的方式无法直接做除法计算效率，正确的做法是把同一年的两个指标放在同一行，再进行运算。

假设你的表名为monthly_meter_data，核心字段包括：

• reading_date：月度记录的日期（用于提取年份）
• meter_reading：电表读数（累计值）
• sunlight_hours：当月日照时长

下面是两种可行的SQL写法：

方法1：使用CTE（公共表表达式）分步计算 #

WITH annual_generation AS (
    -- 计算每年的总发电量：年末读数 - 年初读数
    SELECT
        EXTRACT(YEAR FROM reading_date) AS year,
        MAX(meter_reading) - MIN(meter_reading) AS annual_kwh
    FROM monthly_meter_data
    GROUP BY EXTRACT(YEAR FROM reading_date)
),
annual_sunlight AS (
    -- 计算每年的总日照时长
    SELECT
        EXTRACT(YEAR FROM reading_date) AS year,
        SUM(sunlight_hours) AS total_sunlight_hours
    FROM monthly_meter_data
    GROUP BY EXTRACT(YEAR FROM reading_date)
)
-- 关联两个CTE，计算年度效率
SELECT
    ag.year,
    ag.annual_kwh,
    asl.total_sunlight_hours,
    -- 计算每日照小时发电量，保留两位小数
    ROUND(ag.annual_kwh / asl.total_sunlight_hours, 2) AS efficiency_kwh_per_hour
FROM annual_generation ag
JOIN annual_sunlight asl ON ag.year = asl.year
ORDER BY ag.year;

方法2：在主查询中用子查询直接关联 #

SELECT
    year,
    annual_kwh,
    total_sunlight_hours,
    ROUND(annual_kwh / total_sunlight_hours, 2) AS efficiency_kwh_per_hour
FROM (
    SELECT
        EXTRACT(YEAR FROM reading_date) AS year,
        MAX(meter_reading) - MIN(meter_reading) AS annual_kwh,
        SUM(sunlight_hours) AS total_sunlight_hours
    FROM monthly_meter_data
    GROUP BY EXTRACT(YEAR FROM reading_date)
) AS annual_stats
ORDER BY year;

关键说明 #

• 两种方法都是先按年份聚合，把发电量和日照时长放在同一行，再做除法运算，避免了UNION带来的行拆分问题。
• 如果你的电表读数是每月递增的，MAX(meter_reading) - MIN(meter_reading)就能准确得到年度总发电量；如果存在跨年读数异常，可能需要额外过滤年初（1月）和年末（12月）的数据来计算差值。
• 用ROUND函数可以控制效率值的小数位数，根据需求调整即可。

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