属于同一组但存储在两行中的值之差

admin

属于同一组但存储在两行中的值之差

sql

我有一个问题，我需要提取2个具有2个不同值的特定记录，并找出它们之间的差额。这需要为每个设备完成。让我们以下表为例

DevID   reason    amount         DateTime
--------------------------------------------------
99       5        84       18-12-2016 18:10
99       0        35       18-12-2016 18:11
99       0        80       18-12-2016 18:12
99       0        34       18-12-2016 18:15
23       5        36       18-12-2016 18:16
23       4        22       18-12-2016 18:17
23       1        22       18-12-2016 18:18
23       2        22       18-12-2016 18:19
99       2        11       18-12-2016 18:20
99       8        50       18-12-2016 18:21
99       0        23       18-12-2016 18:22
99       5        06       18-12-2016 18:25
99       8        12       18-12-2016 18:30

因此，我感兴趣的原因是5和8。5是设备登录，8是注销，其他数字表示其他含义。我想获取具有设备登录原因（5）和下一个设备注销（8）的记录，并找到其金额值的差异，因此在设备99的上表中，原因5的金额为84，注销事件（8）是50，所以差是34，如果大于10，我需要列出该设备。（请注意，同一条记录还有另一种情况5和8，相差不大于5），但是第一组的差异大于10，因此我们需要显示该设备ID

所以上述的预期输出是

DevID
-------
99

我在考虑加入联接表A，该表的所有记录均为5（按deviceid，date排序），表B的所有记录均为8，然后减去它们的数量并显示值大于10的记录。该走的路？任何更简单/快速的解决方案？

阅读 143

2021-07-01

共1个答案

admin

您可以使用LEAD功能将登录时间与注销时间进行匹配：

WITH cte AS (
    SELECT devid
         , reason
         , amount
         , LEAD(reason) OVER (PARTITION BY devid ORDER BY datetime) AS next_reason
         , LEAD(amount) OVER (PARTITION BY devid ORDER BY datetime) AS next_amount
    FROM t
    WHERE reason IN (5, 8)
)
SELECT *, amount - next_amount AS diff
FROM cte
WHERE reason = 5               -- logon
AND next_reason = 8            -- next event is a logout
AND amount - next_amount >= 10 -- difference of current and next

2021-07-01