时间戳

首页 > 解决方案 > SQL - 如何识别给定数据中的 1 小时时间段孤岛？

sql - SQL - 如何识别给定数据中的 1 小时时间段孤岛？

问题描述

目标是接受收到的第一个投诉并拒绝在第一个投诉后 1 小时内收到的所有投诉。例如我有下面的数据。

投诉编号 约会时间
1 2019 年 12 月 24 日下午 1:07
2 2019 年 12 月 24 日下午 1:20
3 2019 年 12 月 24 日下午 1:40
4 2019 年 12 月 24 日下午 2:00
5 2019 年 12 月 24 日下午 2:10
6 2019 年 12 月 24 日下午 2:12
7 2019 年 12 月 24 日下午 2:50
8 2019 年 12 月 24 日下午 2:55
9 2019 年 12 月 24 日下午 3:00
10 2019 年 12 月 24 日下午 3:08
11 2019 年 12 月 24 日下午 4:00
12 2019 年 12 月 24 日下午 4:50
13 2019 年 12 月 24 日下午 7:00
14 2019 年 12 月 26 日下午 7:01

所需输出：

投诉编号 约会时间 地位
1 2019 年 12 月 24 日下午 1:07 接受
2 2019 年 12 月 24 日下午 1:20 拒绝
3 2019 年 12 月 24 日下午 1:40 拒绝
4 2019 年 12 月 24 日下午 2:00 拒绝
5 2019 年 12 月 24 日下午 2:10 接受
6 2019 年 12 月 24 日下午 2:12 拒绝
7 2019 年 12 月 24 日下午 2:50 拒绝
8 2019 年 12 月 24 日下午 2:55 拒绝
9 2019 年 12 月 24 日下午 3:00 拒绝
10 2019 年 12 月 24 日下午 3:08 拒绝
11 2019 年 12 月 24 日下午 4:00 接受
12 2019 年 12 月 24 日下午 4:50 拒绝
13 2019 年 12 月 24 日下午 7:00 接受
14 2019 年 12 月 26 日下午 7:01 接受

我知道使用编程语言会很容易，但是我需要一个 SQL 解决方案。

编辑：

根据@Gordon 的建议，我实现了以下递归查询并且它有效！但是，它在大数据上似乎效率低下。

with RECURSIVE t AS (
    select row_number as rn,ts, lag(ts,1) over (order by row_number) as baseline from main_table where row_number<3
  UNION ALL
    SELECT 
    rn+1 as rn 
    ,(select ts from main_table where row_number=rn+1) as ts
    ,case when datediff('hour',ts,baseline)>24 then ts else baseline end as baseline
     from (select * FROM t order by rn desc limit 1 )t where rn<=(select count(*)-1 from main_table)
)

,real_baseline as 
(
select rn,ts,lead(baseline,1) over (order by rn) as real_baseline from t
)

select * 
,case when row_number() over (partition by real_baseline order by ts) =1 then 'Accept'
else 'Reject' end as status
from real_baseline

标签： sqlgaps-and-islands

解决方案

这是简单的方法。将每个 datetime 截断为 Hour 然后在每个小时内将 First 或 Minimum datetime 作为 accept ，将 other 作为 denied 。

PS 我已经使用 table_name 作为投诉更改它。在 Postgresql 8 中测试。

SELECT ComplaintID,DateTime,CASE WHEN row_number() over(partition by hour order by 
DateTime)=1 THEN 'Accept' else 'Reject' end as Status from 
(select ComplaintID,DateTime ,date_trunc('hour',DateTime)as hour  from complaint)A ;

推荐阅读