属于同一组但存储在两行中的值之差 [英] Difference of values that belong to same group but stored in two rows

问题描述

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

I have a problem where i need to fetch 2 specific records with 2 different value and find the difference between their amount. This needs to be done for each device. Lets take the following table as example

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，并且注销event（8）是50，所以差是34，如果大于10，则需要列出该设备。
（请注意，同一条记录还有5和8的情况，差异不大于5），但是第一组的差异大于10，因此我们需要显示该设备ID

So my reason of interest is 5 and 8. 5 is device logon and 8 is logout and other numbers refer to other things. I want to fetch records with device logon reason(5) and the next device logout(8) and find the difference in its amount value so in the table above for device 99, amount for reason 5 is 84 and the logout event(8) is 50, so the difference is 34 which if greater than 10 i need list that device. (please note there is another case of 5 and 8 for the same record, the difference is not greater than 5) but the first set has diff greater than 10 so we need to display that device id

因此上述内容的预期输出是

So the expected output for the above is

DevID
-------
99

我当时想加入
连接表A（其所有记录均为5（按deviceid，date排序））和表B（所有记录均具有8），然后减去其数量并显示值大于10的记录。
不确定加入方式是否可行？任何简单/快速的解决方案？

i was thinking of join Join table A which has all records with 5(sorted by deviceid,date) and table B which has all records of with 8 and then subtract their amounts and display the records with value greater than 10. Not sure if join is the way to go? any simpler/fast solution?

推荐答案

您可以使用 LEAD 函数将登录时间与注销时间匹配：

You can use LEAD function to match logon time with logout:

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

这篇关于属于同一组但存储在两行中的值之差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！