SQL选择连续列之间的差值小于X的行
基本上是Mysql:查找时间戳差小于x的行，但我想在时间戳差大于X的第一个值处停止。
我到目前为止：
SELECT *
FROM (
SELECT *,
(LEAD(datetime) OVER (ORDER BY datetime)) - datetime AS difference
FROM history
) AS sq
WHERE difference < '00:01:00'这似乎正确地返回了行与“后面”的差小于一分钟的所有行，但这意味着我仍然会在datetimes中得到很大的跳转，这是我不想要的--我想选择最近的行“运行”，其中"run“被定义为”datetime中的时间戳差不到1分钟“。
例如，我有一些行的假设时间戳如下：
24, 22, 21, 19, 18, 12, 11, 9, 7...我对差异的限制是3，即我希望运行“时间戳”之间的差小于3的行；因此，只需：
24, 22, 21, 19, 18这在SQL中是可能的吗？
回答 3
Stack Overflow用户
发布于 2016-12-22 13:35:40
您可以使用lag获取上一行的时间戳，并检查当前行是否在其3分钟内。如果条件失败，请重置组。在完成这个分组之后，您可以找到最新的这样的组，使用max来获得它。然后从最新的组中获取所有这些行。
如果必须对表中的每个id执行partition by子句，则在窗口函数lag、sum和max中包含一个sum子句。
with grps as (
select x.*,sum(col) over(order by dt) grp
from (select t.*
--checking if the current row's timestamp is within 3 minutes of the next row
,case WHEN dt BETWEEN LAG(dt) OVER (ORDER BY dt)
AND LAG(dt) OVER (ORDER BY dt) + interval '3 minute' THEN 0 ELSE 1 END col
from t) x
)
select dt
from (select g.*,max(grp) over() maxgrp --getting the latest group
from grps g
) g
where grp = maxgrp上面的内容将为您提供最新的组中的成员，即使它有一行。为了避免这样的结果，请获得有超过1行的最新组。
with grps as (
select x.*,sum(col) over(order by dt) grp
from (select t.*
,case WHEN dt BETWEEN LAG(dt) OVER (ORDER BY dt)
AND LAG(dt) OVER (ORDER BY dt) + 3 THEN 0 ELSE 1 END col
from t) x
)
,grpcnts as (select g.*,count(*) over(partition by grp) grpcnt from grps g)
select dt from (select g.*,max(grp) over() maxgrp
from grpcnts g
where grpcnt > 1
) g
where grp = maxgrpStack Overflow用户
发布于 2016-12-22 13:12:06
您可以通过使用基于lead()或lag()值的标志来做到这一点。我相信这能做你想做的：
SELECT h.*
FROM (SELECT h.*,
SUM( (next_datetime < datetime + interval '1 minute')::int) OVER (ORDER BY datetime DESC) as grp
FROM (SELECT h.*,
LEAD(h.datetime) OVER (ORDER BY h.datetime)) as next_datetime
FROM history h
) h
WHERE next_datetime < datetime + interval '1 hour'
) h
WHERE grp IS NULL OR grp = 0;Stack Overflow用户
发布于 2016-12-22 15:56:14
这可以很容易地用递归CTE解决(这将逐个选择行，并在范围interval '1 min'中没有行时停止)：
with recursive h as (
select * from (
select *
from history
order by history.datetime desc
limit 1
) s
union all
select * from (
select history.*
from h
join history on history.datetime >= h.datetime - interval '1 min'
and history.datetime < h.datetime
order by history.datetime desc
limit 1
) s
)
select * from h如果您在history.datetime上有一个索引，这应该是有效的。不过，如果您关心性能，则应该针对基于窗口函数的性能进行测试。(当看到足够多的子查询和窗口函数来解决这个问题时，我会感到头疼。具有讽刺意味的是，postgresql并不直接支持ORDER BY子句，因此我不得不使用两个无意义的子查询来“隐藏”它们)。
雷克斯试验器
https://stackoverflow.com/questions/41283940
复制相似问题
腾讯云开发者
