时间戳

首页 > 解决方案 > 计数值检查是否连续

sql - 计数值检查是否连续

问题描述

这是我的桌子：

Event       Order               Timestamp
delFailed   281475031393706     2018-07-24T15:48:08.000Z
reopen      281475031393706     2018-07-24T15:54:36.000Z
reopen      281475031393706     2018-07-24T15:54:51.000Z

我需要计算事件 'delFailed' 和 'reopen' 的数量来计算#delFailed - #reopen。困难在于不能有两个相同的连续事件，因此在这种情况下，结果将是“0”而不是“-1”。

这是我到目前为止所取得的成就（这是错误的，因为它给了我 -1 而不是 0，因为有两个连续的“重新打开”事件）

with 
    events as (
        select 
            event as events,
            orders,
            "timestamp"
        from main_source_execevent
        where orders = '281475031393706'
        and event in ('reopen', 'delFailed')
        order by "timestamp"
    ),
    count_events as (
        select 
            count(events) as CEvents,
            events,
            orders
        from events
        group by orders, events
    )
select (
    (select cevents from count_events where events = 'delFailed') - (select cevents from count_events where events = 'reopen')
) as nAttempts,
orders
from count_events
group by orders

如果有两个相同的连续事件，我如何计算一次？

标签： sqlpostgresql-10

解决方案

我只是lag()用来获取任何相似值序列中的第一个事件。然后进行计算：

select sum( (event = 'reopen')::int ) as num_reopens,
       sum( (event = 'delFailed')::int ) as num_delFailed
from (select mse.*,
             lag(event) over (partition by orders order by "timestamp") as prev_event
      from main_source_execevent mse
      where orders = '281475031393706' and
            event in ('reopen', 'delFailed')
     ) e
where prev_event <> event or prev_event is null;

推荐阅读