光伏电站监控SQL查询优化：全零场景返回零值需求

需求背景 #

数据表结构如下：

table.column1 = timestamp  -- 数据采集时间戳
table.column2 = double precision  -- 光伏电站输出功率值

表中存储的功率值包含两种情况：

• 有效值：大于0（正常发电场景）
• 零值：等于0（无日照、组件积雪等场景）

原查询语句仅筛选当日功率>0的数据，导致夜间或积雪时全零数据无法被查询到。需要实现逻辑：若当日所有功率值均为零，则筛选零值；否则筛选所有大于零的有效值。

优化后的SQL语句 #

select
  date_trunc('m', t.column1) as minute_timestamp,
  avg(t.column2) as avg_power
from (
  select
    column1,
    column2,
    -- 计算当日功率的最大值，判断是否存在有效值
    max(column2) over (partition by date(column1)) as daily_max_power
  from table
  where date(column1) = date(current_timestamp)
) t
-- 根据当日是否有有效值筛选数据
where (t.daily_max_power = 0 and t.column2 = 0) 
   or (t.daily_max_power > 0 and t.column2 > 0)
group by date_trunc('m', t.column1)
order by minute_timestamp;

逻辑说明 #

1. 子查询部分：通过窗口函数max(column2) over (partition by date(column1))计算当日所有功率值的最大值，每一行数据都会带上这个当日最大值，用于判断当日是否存在有效发电数据。
2. 筛选条件：
   • 如果当日最大值为0，说明全是零值，此时保留column2=0的数据
   • 如果当日最大值大于0，说明存在有效值，此时保留column2>0的数据
3. 分组与排序：按分钟截断时间戳分组求平均功率，修正了原语句中order by date_trunc('m', column2)的错误（原语句误将功率值当作时间戳处理），改为按分钟时间戳排序。

场景验证 #

• 正常发电日：子查询计算出的daily_max_power>0，筛选出所有>0的功率值，按分钟统计平均功率，与原查询逻辑一致。
• 全零数据日：子查询计算出的daily_max_power=0，筛选出所有零值数据，按分钟统计平均功率（结果为0），避免无查询结果的问题。

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