PostgreSQL 9.6月度电表最大值及环比差值查询优化需求

场景与数据说明 #

使用PostgreSQL 9.6，现有电表数据表public.electricitymetering，每小时生成一条记录，示例数据如下：

需求说明 #

需要获取每个月的电表最大值（即月末的数值），以及当月数值与上月的差值，期望结果格式如下：

当前实现的SQL #

我目前写了四层嵌套的SELECT语句，通过row_number标记每月最大值，再用lag获取上月数值：

select date, max, max - lag "diff" from (
select date, max, lag(max) over (order by max) from (
select * from (
select  date(date_trunc('month', time)), max(value), row_number() over (partition by date(date_trunc('month', time)) order by value desc) rn
    
    from public.electricitymetering where "meterid" = '1'

group by date_trunc('month', time), value 
order by date(date_trunc('month', time)) desc ) as result
where rn = 1
    ) as temp ) as final

优化方案 #

可以利用PostgreSQL的DISTINCT ON特性简化查询，减少嵌套层数，同时提升效率：

SELECT
  month_date AS date,
  monthly_max AS max,
  monthly_max - LAG(monthly_max) OVER (ORDER BY month_date) AS diff
FROM (
  SELECT
    date_trunc('month', time)::date AS month_date,
    value AS monthly_max
  FROM (
    SELECT DISTINCT ON (date_trunc('month', time))
      time,
      value
    FROM public.electricitymetering
    WHERE meterid = '1'
    ORDER BY date_trunc('month', time), time DESC
  ) AS monthly_last_record
) AS monthly_values
ORDER BY month_date;

优化说明 #

1. DISTINCT ON 取月末记录：通过DISTINCT ON (date_trunc('month', time))按月份分组，结合ORDER BY date_trunc('month', time), time DESC，直接获取每个月时间最晚的记录（即月末的电表数值，同时也是当月最大值，因为电表数值为累计递增）。
2. 简化窗口函数计算：外层查询直接用LAG(monthly_max) OVER (ORDER BY month_date)获取上月数值，计算差值diff，无需多层嵌套。
3. 性能提升：若数据表存在(meterid, time)的联合索引，该查询会利用索引快速定位每个月的最后一条记录，效率远高于多层分组排序的写法。

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