php - 查找PostgreSQL中集合之间的所有行

我有一个名为tc_fuel的表，它接收来自GPS车辆的所有与燃油相关的数据，我想得到最后一个“油箱使用量”来计算整个油箱的MPG，但是当油箱充满时（100）的读数有时会在两行或三行或更多行之后重复，所以我留下的是彼此相邻的两个100值，我想得到最后一个“填充”的开始和结束ID。到目前为止我所拥有的：SELECT "tc_fuel".deviceid, "tc_fuel"."id", "tc_fuel".fuel, "tc_fuel"."fuelUsed", "tc_fuel"."fuelUsed"FROM "tc_fuel"WHERE fuel=100 AND deviceid=19ORDER BY IDDESC LIMIT 2然后我进入PHP检查id是否与100多条记录存在差异，以检查fuel值是否彼此相邻，但这比我想知道是否有更好的方法做得更多。例如，这辆车在满油箱的情况下启动，然后降到6%的油箱，并进行满油箱加注，我想能够获取最后一个油箱的所有数据。id | deviceId | fuel------+-----------+-------1 | 19 | 100 <-- This should be starting point2 | 19 | 973 | 19 | 1004 | 19 | 965 | 19 | 946 | 19 | .... (keeps dropping)7 | 19 | 338 | 19 | 319 | 19 | 3010 | 19 | ....11 | 19 | 612 | 19 | 513 | 19 | 6 <-- This should be end point (will flag this id as processed)14 | 19 | 100 <-- Starts all over again in next iteration of the php script15 | 19 | 9916 | 19 | 9817 | 19 | 10018 | 19 | 9919 | 19 | 9720 | 19 | 9621 | 19 | .... 最佳答案 “填充”的定义有点模糊。当燃油值上升超过50时，我会假设是加油。替换为您选择的数字。似乎一个新的坦克必须从fuel = 100开始（尽管这是一个奇怪的情况）。我添加了注释-取消注释以激活：SELECT *FROM ( SELECT *, count(*) FILTER (WHERE fillup) OVER (PARTITION BY device_id ORDER BY id) AS tank FROM ( SELECT * , fuel - lag(fuel, 1, 0) OVER (PARTITION BY device_id ORDER BY id) > 50 -- AND fuel = 100 -- additional condition(s)? AS fillup FROM tbl ) sub1 ) sub2WHERE device_id = 19AND tank = 1;分贝小提琴here在子查询sub1中，使用window function lag()计算每个设备前面的燃料条目与当前条目之间的差异。值得注意的是，我使用带有3个参数的变量，为每个分区的第一行提供0作为缺省值。增加超过50表示有新的补充。在subquerysub2中，使用另一个窗口函数计算一段时间内的填充数，从而为每行指定一个“tank”数字。在外部SELECT中，选择您的设备和“油箱”加注的编号。喂。如果将条件WHERE device_id = 19移到最里面的suqbquery，则可以删除PARTITION子句。更快，功能更少。关于FILTER子句：How can I simplify this game statistics query?Conditional lead/lag function PostgreSQL?只获取给定设备的最后一个油箱根据你的评论，定义为“上次油箱从20或以下充到100”。我假设后面的时间点对应于更高的id值。（请注意，在并发的写负载下，串行列可能会出现拐角情况的复杂情况。）最简单的方法是：颠倒顺序，从底部开始计数：SELECT *FROM ( SELECT *, count(*) FILTER (WHERE fillup) OVER (ORDER BY id DESC) AS tank FROM ( SELECT *, lag(fuel, 1, 0) OVER (ORDER BY id DESC) = 100 AND fuel <= 20 AS fillup FROM tbl WHERE device_id = 19 ) sub1 ) sub2WHERE tank = 0-- ORDER BY id -- optional to get result in ascending order分贝小提琴here为此，在程序上通过一排可能更快，因为这只需要一次传球，并且在发现第一个坦克后可以立即停止。用tbl(device_id, id DESC)上的索引支持它。示例代码：GROUP BY and aggregate sequential numeric valuesHow to number consecutive records per island?关于php - 查找PostgreSQL中集合之间的所有行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58312219/