在SQLite中工作的SQL Group By在Postgres中不工作

Question

此语句在SQLite中有效，但在Postgres中无效：

SELECT A.*, B.* 
FROM Readings A 
  LEFT JOIN Offsets B ON A.MeterNum = B.MeterNo AND A.DateTime > B.TimeDate 
WHERE A.MeterNum = 1 
GROUP BY A.DateTime 
ORDER BY A.DateTime DESC

读数表包含每个带有日期戳的电力分表读数。偏移表保存用户在将故障仪表替换为重新从零开始的新仪表后输入的调整。如果不使用Group By语句，查询将为每个仪表读数返回一行，每次之前的调整都在读数日期之前进行，而我只想要最后的调整。

我在Group By for Postgres上看到的所有文档都指出，我应该包含一个不需要也不能使用的聚合函数( Reading列包含从仪表返回的Modbus字符串)。

Answer 1

只需在派生表中选取最新读数即可。在Postgres中，这可以使用distinct on ()非常有效地完成。

SELECT A.*, B.* 
FROM readings A 
  left join (
     select distinct on (meterno) o.*
     from offsets o
     order by o.meterno, o.timedate desc
  ) B ON A.MeterNum = B.MeterNo AND A.DateTime > B.TimeDate 
WHERE A.meternum = 1 
ORDER BY A.DateTime DESC

由于order by ... , timedate desc的原因，distinct on ()每个meterno只返回一行，这是“最新的”行

通过使用lateral join将datetime > timedate上的条件推送到派生表中，查询速度可能会更快

SELECT A.*, B.* 
FROM readings A 
  left join lateral (
     select distinct on (meterno) o.*
     from offsets o
     where a.datetime > o.timedeate 
     order by o.meterno, o.timedate desc
  ) B ON A.MeterNum = B.MeterNo 
WHERE A.meternum = 1 
ORDER BY A.DateTime DESC