Postgresql时间戳最小最大值不正确/如何处理异常值
Postgresql时间戳最小最大值不正确/如何处理异常值
提问于 2020-07-02 02:33:32
这是我的第一篇文章，请耐心等待。我是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我不确定如何处理这个问题，所以我希望得到任何指导
Stack Overflow用户
发布于 2020-07-02 04: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 请参阅DB Fiddle上的运行示例。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/62683300
复制相关文章
相似问题
腾讯云开发者
