问Postgresql时间戳最小最大值不正确/如何处理异常值
EN

Stack Overflow用户

提问于 2020-07-01 18:33:32

回答 1查看 58关注 0票数 0

这是我的第一篇文章，请耐心等待。我是sql的新手，但我想我已经掌握了一些基础知识。

我在计算每个人在现场的时间。有几个时间戳，所以我一直使用以下代码

MIN (to_char(c.capture_time, 'YYYY-MM-DD HH24:MI:SS')) AS "start day",              
MAX(to_char(c.capture_time, 'YYYY-MM-DD HH24:MI:SS')) AS "end of day",

我得到了下面的输出

User        Project     Start Day       End Day     
person A    project 1   6/10/20 15:10   6/10/20 20:43       
person A    project 2   6/11/20 12:10   6/11/20 16:10       
person B    project 8   6/11/20 11:10   6/11/20 18:23

然后我将数据下载到csv文件，并通过执行(end-start)*24来计算持续时间

此代码适用于大约85%的用户/项目

我开始遇到的问题是当有异常情况或时间段进入第二天时

问题1-异常值(原始数据)

User        Project     Capture End time        
person A    Project 1   6/10/20 15:10       
person A    Project 1   6/10/20 15:20       
person A    Project 1   6/10/20 15:40       
person A    Project 1   6/10/20 16:05       
person A    Project 1   6/10/20 20:43

在这个例子中，最后一个时间戳是关闭的，你可以通过较大的时间间隔来判断。我需要以某种方式忽略极端异常值

问题2-时间戳进入第二天(原始数据)

User        Project     Capture End Time
person B    Project 2   6/15/20 23:12
person B    Project 2   6/15/20 23:45
person B    Project 2   6/16/20 0:15
person B    Project 2   6/16/20 0:30
person B    Project 2   6/16/20 20:05
person B    Project 2   6/16/20 20:30

我得到以下结果(输出)：

User        Project     Start Day       End Day
person B    Project 2   6/15/20 23:12   6/15/20 23:45
person B    Project 2   6/16/20 0:15    6/16/20 20:30

而不是(期望的结果)：

User        Project     Start Day       End Day
person B    Project 2   6/15/20 23:12   6/16/20 0:30
person B    Project 2   6/16/20 20:05   6/16/20 20:30

我不确定如何处理这个问题，所以我希望得到任何指导

sql-timestamp

sql

postgresql

回答 1

Stack Overflow用户

发布于 2020-07-01 20:38:55

这是一个“差距和岛屿”的问题，有一个扭曲，可以有一个延迟来考虑何时“关闭一天”。我决定在两天之间使用2小时的最小间隔。您可以将其更改为更长或更短的间隔，以满足您的需要。

下面的查询会产生您想要的结果：

select usr, project,
  min(captured) as start_day,
  max(captured) as end_day
from (
  select *,
    sum(new_group) over(partition by usr, project order by captured) as day
  from (
    select *,
      case when captured > 
        max(captured) over(partition by usr, project order by captured
        rows between unbounded preceding and 1 preceding) + interval '2 hour'
        then 1 else 0
      end as new_group
    from t
  ) x
) y
group by usr, project, day

结果：

usr        project     start_day                  end_day                   
--------- ----------- -------------------------- -------------------------- 
Person B   project 2   2020-06-15T23:12:00.000Z   2020-06-16T00:30:00.000Z  
Person B   project 2   2020-06-16T20:05:00.000Z   2020-06-16T20:30:00.000Z