我正在使用 Postgresql 和 Sequelize。我必须从阅读表中找到消耗量。目前，我有从前一行减去值的查询。但问题是如果该值小于前一个值意味着我必须忽略该行并需要等待更大的值进行计算。
当前查询
select "readingValue",
"readingValue" - coalesce(lag("readingValue") over (order by "id")) as consumption
from public."EnergyReadingTbl";
示例记录和当前输出
id readingValue consumption
65479 "35.8706703186035" "3.1444168090820"
65480 "39.0491638183594" "3.1784934997559"
65481 "42.1287002563477" "3.0795364379883"
65482 "2.38636064529419" "-39.74233961105351"
65483 "5.91744041442871" "3.53107976913452"
65484 "9.59204387664795" "3.67460346221924"
65485 "14.3925561904907" "4.80051231384275"
65486 "19.4217891693115" "5.0292329788208"
65487 "24.2393398284912" "4.8175506591797"
65488 "29.2515335083008" "5.0121936798096"
65489 "34.2519302368164" "5.0003967285156"
65490 "38.6513633728027" "4.3994331359863"
65491 "43.7513643778087" "5.1000010050060"
在这张图中，最后一个最大值是 42.1287002563477。我必须等到获得比 42.1287002563477 更大的值才能像下一个更大的值 - 42.1287002563477 一样进行计算。在此，43.7513643778087 - 42.1287002563477。
预期产出
id readingValue consumption
65479 "35.8706703186035" "3.1444168090820"
65480 "39.0491638183594" "3.1784934997559"
65481 "42.1287002563477" "3.0795364379883"
65482 "2.38636064529419" "0"
65483 "5.91744041442871" "0"
65484 "9.59204387664795" "0"
65485 "14.3925561904907" "0"
65486 "19.4217891693115" "0"
65487 "24.2393398284912" "0"
65488 "29.2515335083008" "0"
65489 "34.2519302368164" "0"
65490 "38.6513633728027" "0"
65491 "43.7513643778087" "1.1226641214710"
有没有机会在查询中解决这个问题？
最佳答案
您可以使用 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING 来限制窗口函数的框架，因此您可以使用最多但不包括当前行的行的 MAX 值减去 MAX 到当前行:
SELECT readingValue,
MAX(readingValue) OVER (ORDER BY id) - MAX(readingValue) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
FROM e;
┌──────────────────┬─────────────────┐
│ readingvalue │ ?column? │
├──────────────────┼─────────────────┤
│ 35.8706703186035 │ (null) │
│ 39.0491638183594 │ 3.1784934997559 │
│ 42.1287002563477 │ 3.0795364379883 │
│ 2.38636064529419 │ 0 │
│ 5.91744041442871 │ 0 │
│ 9.59204387664795 │ 0 │
│ 14.3925561904907 │ 0 │
│ 19.4217891693115 │ 0 │
│ 24.2393398284912 │ 0 │
│ 29.2515335083008 │ 0 │
│ 34.2519302368164 │ 0 │
│ 38.6513633728027 │ 0 │
│ 43.7513643778087 │ 1.622664121461 │
└──────────────────┴─────────────────┘
(13 rows)
Time: 0,430 ms
关于postgresql - 如果大于最大值，则从前一行值中减去该值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53831372/