视图中的数据如下所示：
id time revs m_id tag_id
320518 2017-04-05 14:01:25 216210396 1 1
320620 2017-04-05 14:11:33 216217766 1 1
27346 2017-12-09 15:15:52 699 2 1
27347 2017-12-09 15:19:52 1618 2 1
目标：我需要通过执行以下操作来计算每分钟的转速：
(row2 rev- row1 rev)/ (row2 time - row1 time)=revs per minute每两排。也适用于每个m_id（1和2）。换句话说，当m_id改变时，它将执行与该m_id相关联的行。它还需要按标记id分组。
我需要结果数据如下：
time m_id tag_id time_diff rev_diff rpm
2017-04-05 14:11:33 1 1 10.13 7370 727.54
2017-12-09 15:15:52 2 1 4 919 229.75
有成千上万排。我已经把视图设置为按m_id排序，然后按时间排序。
将其全部保存在同一个表/视图中的原因是，我可以拥有任意数量的m_id。我需要它是动态的，所以不管创建了多少m_id，我仍然可以确定rpm。
我最初的解决方案是为每个m_id创建过滤的视图，但这对“drop-in”程序不起作用。
更新日期：4/20/17
我使用@Tim Biegeleisen answer作为模板（使用我的实际字段名）调整了脚本。
WITH cte AS (
SELECT
machine_id_id,
rfid_tag_id,
"event_at",
LAG("event_at") OVER (ORDER BY "event_at" PARTITION BY machine_id_id) AS prev_time,
rev_count, LAG(rev_count) OVER (ORDER BY "event_at" PARTITION BY machine_id_id) AS prev_revs
FROM machines_status_updates
)
SELECT t.*,
(rev_count - tprev_revs) /
(EXTRACT(EPOCH FROM (t.event_at - t.prev_time)) / 60) AS rpm
FROM cte t
WHERE t.prev_time IS NOT NULL
它仍然在第一个分区上抛出语法错误-但是我没有看到语法错误。
错误：
sql> WITH cte AS (
SELECT
machine_id_id,
rfid_tag_id,
"event_at",
LAG("event_at") OVER (ORDER BY "event_at" PARTITION BY machine_id_id) AS prev_time,
rev_count,
LAG(rev_count) OVER (ORDER BY "event_at" PARTITION BY machine_id_id) AS prev_revs
FROM machines_status_updates
)
[2017-04-20 10:36:16] [42601] ERROR: syntax error at or near "PARTITION"
[2017-04-20 10:36:16] Position: 154
看过文档（因为这个CTE对我来说有点新）-一切看起来都很好-我找不到我缺少的东西。
为了便于查看：
编辑（2017年4月52日更新）-取得的进展
这是脚本的最新版本（消除分区错误，然后除以零错误）
WITH cte AS (
SELECT
machine_id_id,
rfid_tag_id,
event_at,
LAG(event_at) OVER (ORDER BY machine_id_id, event_at) AS prev_time,
rev_count,
LAG(rev_count) OVER (ORDER BY machine_id_id, event_at) AS prev_revs
FROM machines_status_updates
)
SELECT t.*,
(t.rev_count - t.prev_revs) / NULLIF(
(EXTRACT(EPOCH FROM (t.event_at - t.event_at)) / 60), 0) AS rpm
FROM cte t
WHERE t.prev_time IS NOT NULL;
数据集如下：
RPM在整个数据集中都以空值返回—所有行似乎都工作得很好。有什么想法吗？
最终答案：
喔喔喔！终于明白了！：D根据@Tim Biegeleisen的答案工作。
最终脚本：
WITH cte AS (
SELECT
machine_id_id,
rfid_tag_id,
event_at,
LAG(event_at) OVER (ORDER BY machine_id_id, event_at) AS prev_time,
rev_count,
LAG(rev_count) OVER (ORDER BY machine_id_id, event_at) AS prev_revs
FROM machines_status_updates
)
SELECT t.*, (t.rev_count - t.prev_revs)AS rev_diff, (EXTRACT(EPOCH FROM (t.event_at - t.prev_time)
) / 60) AS time_diff,
(t.rev_count - t.prev_revs) / NULLIF(
(EXTRACT(EPOCH FROM (t.event_at - t.prev_time)) / 60), 0) AS rpm
FROM cte t
WHERE t.prev_time IS NOT NULL;
它是空的，因为我是从它自身减去t.event，而不是从上一个时间。我添加了一些列以便验证rpms。
最终结果：
最佳答案
WITH cte AS (
SELECT
m_id,
tag_id,
"time",
LAG("time") OVER (ORDER BY "time" PARTITION BY m_id) AS prev_time,
revs,
LAG(revs) OVER (ORDER BY "time" PARTITION BY m_id) AS prev_revs
FROM yourTable
)
SELECT t.*,
(t.revs - t.prev_revs) /
(EXTRACT(EPOCH FROM (t.time - t.prev_time)) / 60) AS rpm
FROM cte t
WHERE t.prev_time IS NOT NULL
关于postgresql - 计算数据库中每两行之间的字段-按实体分组-相同表-Postgresql，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/43498095/