python - 如何从SELECT查询中获取最大值或最小值

我有一张放水表读数的桌子。现在，这个表每月都会保存数百万条记录，我正在寻找一种方法，从特定帐号的当前电表读数中减去以前的电表读数，并得到所述期间消耗的单位
我已经成功地提取了一个特定帐号的最后两个条目，并尝试在一个select查询中使用MAX或MIN，但它似乎只适用于列
这是我的密码

 SELECT (MAX(SELECT actual_reading FROM test_meter_readings
                        WHERE account_number = '23-456789T67'
                        ORDER BY timestamp_capture
                        DESC
                        LIMIT 2)) -
                    (MIN(SELECT actual_reading FROM test_meter_readings
                        WHERE account_number = '23-456789T67'
                        ORDER BY timestamp_capture
                        DESC
                        LIMIT 2))
                      ''',)

我希望从表中每个帐户的最后两个条目中得到差额

最佳答案

使用LAG()：

select (actual_reading - prev_actual_reading) as diff
from (select tmr.*,
             lag(actual_reading) over (partition by account_number order by timestamp_capture) as prev_actual_reading
      from test_meter_readings tmr
      where account_number = '23-456789T67'
     ) tmr
order by timestamp_capture desc
fetch first 1 row only;  -- or limit 1

编辑：
要处理多个帐户，可以使用row_number()或distinct on。第一种方法如下：

select (actual_reading - prev_actual_reading) as diff
from (select tmr.*,
             lag(actual_reading) over (partition by account_number order by timestamp_capture) as prev_actual_reading,
             row_number() over (partition by account_number order by timestamp_capture) as seqnum
      from test_meter_readings tmr
      -- where account_number = '23-456789T67'
     ) tmr
where seqnum = 1;