时间戳

首页 > 解决方案 > 属于同一组但存储在两行中的值的差异

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）但第一组的 diff 大于 10，因此我们需要显示该设备 id

所以上面的预期输出是

DevID
-------
99

我正在考虑加入所有记录为 5 的表 A（按 deviceid、日期排序）和表 B 的所有记录为 8，然后减去它们的数量并显示值大于 10 的记录。不确定是否加入是要走的路吗？任何更简单/快速的解决方案？

标签： sqlsql-serverdata-analysis

解决方案

您可以使用窗口函数获得下一个“8”值。然后join过滤：

select t.*,
       (t.value - t8.value) as diff
from (select t.*,
             min(case when reason = 8 then datetime end) over (partition by devid order by datetime desc) as next_8_datetime
      from t
     ) t left join
     t t8
     on t8.devid = t.devid and
        t8.datetime = t.next_8_datetime and
        t8.reason = 8
where t.reason = 5
order by diff desc
limit 1;

推荐阅读