访问间隔小于100秒的访问的首次进入和最后退出时间

Question

我有一个表，其中包含进入时间、退出时间、位置id、行数(自动生成)、间隔(与上次访问的间隔)、final_group (为分隔具有gap>100秒的记录而生成的值)。

我需要合并所有间隔小于100秒的访问。为此，我需要获取组中的第一次进入时间和最后一次退出时间。请参阅附件中的图像以查看数据。filter_table image

我想，每次更改最终间隙的值时，都会有一个新的增量值，这样我就可以轻松地对它进行分组，并找到最小进入时间和最大退出时间。我需要像在图像列“final_group”中那样获得输出。enter image description here

请帮助我解决这个问题。提前谢谢你。

Answer 1

乍一看，这似乎需要一个多步骤的答案，包括一个包含GROUPed结果的临时表，该临时表的总耗时介于MIN(entry_time)和MAX(exit_time) < 100之间，然后UNIONing到原始表，将JOINed保留为temp WHERE NULL，但随后我意识到Gap不是花费在上面的时间，而是花费的时间。所以..。

如果我正确理解了这个问题，这应该会起到作用：

select min(entry_time), max(exit_time), id, location_id 
    from filter_table 
    group by id, location_id, case when gap < 100 then 0 else entry_time end

我尽量保持SQL的通用性。诀窍在于GROUP BY中的CASE语句；它泛化了小于100的间隔，从而对它们进行分组，但保持较大的间隔是唯一的和未分组的。

第2轮-添加窗口函数

事实证明，我们需要在第一个解决方案上进行扩展(参见注释)。

select min(a.entry_time), max(a.exit_time), a.id, a.location_id 
    from 
        (select *, sum(Final_group) over (order by row_num rows unbounded preceding) as running_total
            from filter_table 
        ) a
    group by a.id, a.location_id, case when a.gap < 100 then 0 else entry_time end, a.running_total

通过在新的子查询中添加窗口函数，我们可以计算Final_group的运行总数，并且通过对其进行分组，我们现在可以区分gap > 100的任何一侧的组。

第3轮-使用自连接代替

我被可靠地告知，MonetDB不支持窗口函数。因此，让我们重写子查询，以使用老式的自连接来获得运行总数：

select min(a.entry_time), max(a.exit_time), a.id, a.location_id 
    from 
        (select t1.entry_time, t1.exit_time, t1.id, t1.location_id. t1.gap, 
                sum(t2.Final_group) as running_total
            from filter_table t1
            inner join filter_table t2 on t2.row_num <= t1.row_num
            group by t1.entry_time, t1.exit_time, t1.id, t1.location_id. t1.gap
        ) a
    group by a.id, a.location_id, case when a.gap < 100 then 0 else entry_time end, a.running_total

最后移动了Case语句

Monet也不喜欢Where子句中的Case语句，所以：

select min(a.entry_time), max(a.exit_time), a.id, a.location_id 
    from 
        (select t1.entry_time, t1.exit_time, t1.id, t1.location_id. t1.gap, 
                sum(t2.Final_group) as running_total,
                case when t1.gap < 100 then 0 else t1.entry_time end as case_gap
            from filter_table t1
            inner join filter_table t2 on t2.row_num <= t1.row_num
            group by t1.entry_time, t1.exit_time, t1.id, t1.location_id. t1.gap
        ) a
    group by a.id, a.location_id, a.case_gap, a.running_total

我希望这能帮到你