sql - 选择同时显示记录之间的差异

Question

我正在运行 PostgreSQL 9.6。

我有一个通话记录表，显示了有多少人通过 VOIP 线路通话。

它的结构是:

table: voice_records

session_id | user_id | total_seconds_talked

我每 1-30 分钟左右获取一次记录，因此如果用户通话一个小时，我将收到该单次通话的多条记录。单个 session 的所有记录将包含相同的 session_id、user_id 但递增 total_seconds_talked。

例如

234gdd-542-vffd, 1001, 5
234gdd-542-vffd, 1001, 11
234gdd-542-vffd, 1001, 532
234gdd-542-vffd, 1001, 234
234gdd-542-vffd, 1001, 1159

重要的是要知道，我可能会以错误的顺序收到记录。

我想创建一个 VIEW，我可以在其中将记录视为独立记录，所以对于上面的例子，我会得到记录之间的差异 - 因为它是一个 VIEW 它应该在旅途中计算，所以当 SELECTing 时收到错误的订单会得到纠正VIEW。

例如

SELECT * FROM voice_records WHERE session_id = '234gdd-542-vffd'

OUTPUT:

234gdd-542-vffd, 1001, 5
234gdd-542-vffd, 1001, 6
234gdd-542-vffd, 1001, 223
234gdd-542-vffd, 1001, 298
234gdd-542-vffd, 1001, 627

我猜它涉及某种带有ORDER BY 和LIMIT 1 的SELECT，但我真的有点迷糊了正确，最有效地做到这一点。还有哪些 INDEXes 应该就位。

更新

简单的例子SELECT:

 user_id |   session_id    | seconds_this_time
---------+-----------------+--------
 1001    | 234gdd-542-vffd | 313557
 1001    | 234gdd-542-vffd | 314844
 1001    | 234gdd-542-vffd | 338980
 1001    | 234gdd-542-vffd | 507246
 1001    | 234gdd-542-vffd | 509233
 1001    | 234gdd-542-vffd | 509441
 1001    | 234gdd-542-vffd | 553260
 1001    | 234gdd-542-vffd | 556985
 1001    | 234gdd-542-vffd | 581958
 1001    | 234gdd-542-vffd | 586079
 1001    | 234gdd-542-vffd | 597381
 1001    | 234gdd-542-vffd | 597745
 1001    | 234gdd-542-vffd | 611672
 1001    | 234gdd-542-vffd | 709918
 1001    | 234gdd-542-vffd | 725510
 1001    | 234gdd-542-vffd | 743432
 1001    | 234gdd-542-vffd | 743835
 1001    | 234gdd-542-vffd | 743835
 1001    | 234gdd-542-vffd |

具有滞后功能:

 user_id |   session_id    | seconds_this_time
---------+-----------------+--------
 1001    | 234gdd-542-vffd |
 1001    | 234gdd-542-vffd |   1287
 1001    | 234gdd-542-vffd |  24136
 1001    | 234gdd-542-vffd | 168266
 1001    | 234gdd-542-vffd |   1987
 1001    | 234gdd-542-vffd |    208
 1001    | 234gdd-542-vffd |  43819
 1001    | 234gdd-542-vffd |   3725
 1001    | 234gdd-542-vffd |  24973
 1001    | 234gdd-542-vffd |   4121
 1001    | 234gdd-542-vffd |  11302
 1001    | 234gdd-542-vffd |    364
 1001    | 234gdd-542-vffd |  13927
 1001    | 234gdd-542-vffd |  98246
 1001    | 234gdd-542-vffd |  15592
 1001    | 234gdd-542-vffd |  17922
 1001    | 234gdd-542-vffd |    403
 1001    | 234gdd-542-vffd |      0
 1001    | 234gdd-542-vffd |

Answer 1

你显然想要lag():

select user_id, session_id,
       (total_seconds_talked -
        lag(total_seconds_talked, 1, 0::bigint) over (partition by user_id, session_id order by total_seconds_talked)
       ) as seconds_this_time
from voice_records;

根据您的描述，正在进行的调用的值可能会发生变化，因为未按顺序接收记录。

编辑:

如果由于某种原因您不能使用 lag() 的三参数形式，只需使用 coalesce():

select user_id, session_id,
       (total_seconds_talked -
        coalesce(lag(total_seconds_talked) over (partition by user_id, session_id order by total_seconds_talked), 0)
       ) as seconds_this_time
from voice_records;