mysql - 以更好的方式编写此 SQL 查询的好方法是什么？

Question

SELECT
    MAX(timestamp) as end,
    MIN(timestamp) as start, 
    (MAX(odometerKM) - MIN(odometerKM)) as distanceTravelled,
    ( SELECT COUNT(*) FROM EventData WHERE speedKPH = 0 AND timestamp >= ? AND timestamp <= ? AND  deviceID = ?)  as stopsDuration,
    ( SELECT COUNT(*) FROM EventData WHERE speedKPH != 0 AND timestamp >= ? AND timestamp <= ? AND  deviceID = ? )  as tripDuration,
    (MAX(odometerKM) - MIN(odometerKM)) / ( SELECT fuelEconomy FROM Device WHERE deviceID = ?) as fuelConsumption
FROM EventData
WHERE deviceID = ? AND timestamp >= ? AND timestamp <= ?

非常困惑，但我创建了这个查询来查找一组行的摘要。最小和最大时间戳很简单，但要找到速度为零和不为零的行数，我做了一个丑陋的黑客攻击。我确定有更好的方法来做到这一点？

更新:“？”请参阅由 CodeIgniter 的不成熟查询生成器替换。遗憾的是，它还不支持命名参数。

Answer 1

您应该包括 DeviceID，这样才有意义。试试这个:

SELECT
DeviceID,
MAX(timestamp) as end,
MIN(timestamp) as start, 
(MAX(odometerKM) - MIN(odometerKM)) as distanceTravelled,
SUM (
  case speedKPH 
    when  0  then 1
    else 0
  end)  as stopsDuration,

SUM (
  case speedKPH 
    when  0  then 0
    else 1
  end)  as tripDuration,

(MAX(odometerKM) - MIN(odometerKM)) / Device.fuelEconomy  as fuelConsumption

FROM EventData
Join Device ON Device.deviceID = EventData.deviceID 

WHERE deviceID = ? AND timestamp >= ? AND timestamp <= ?

GROUP BY  DeviceID