postgresql timestamp min max不正确/如何处理异常值

jw5wzhpr 于 2021-08-01 发布在 Java

关注(0)|答案(1)|浏览(385)

这是我的第一篇文章，请耐心等待。我对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%的用户/项目
我开始遇到的问题是，当出现异常情况或timestap进入第二天时
问题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 postgresql sql-timestamp

来源：https://stackoverflow.com/questions/62683300/postgresql-timestamp-min-max-incorrect-how-to-handle-outliers

1条答案

按热度按时间

mwyxok5s1#

这是一个“缺口和孤岛”问题，有一个转折点，即考虑何时“结束一天”可能会有延迟。我决定两天之间至少间隔两小时。您可以将其更改为更长或更短的间距，以满足您的需要。
以下查询生成您要询问的结果：

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

参见db fiddle的运行示例。

赞(0）回复(0）举报 2021-08-01

我来回答

postgresql timestamp min max不正确/如何处理异常值

1条答案

相关问题

热门标签

最新问答