sql - Postgresql 之间的最小值最大值在哪里

Question

使用 Postgresql 9.6 表结构如下:

who | when

每天都有多条记录，相同的who和不同的when。潜在地，每条记录都是who的in或out Action ，因此需要获取每个who的总时间。

即

who | when
  A | 2017-03-01 08:00 
  A | 2017-03-01 12:00
  A | 2017-03-01 13:00
  A | 2017-03-01 15:00

我怎样才能得到总的6小时数？

我认为 max(when) - min(when) 得到周期但需要减去计算中间最小值和最大值的中间数据。

所以需要将 12:00 设为“morningout”，将 13:00 设为“afternoonin”，但是当我将 betweeen min max 放在它提示的地方时

'no aggregate function possible in where'

select who, 
      to_char(date_trunc('day', when), 'YYYY-MM-DD')  "thisday", 
       count(who) as 'signIn'
       min(when) as 'morningout'          
       max(when) as 'afternoonin' 


from the_table 
where when between max(when) and min(when)

group by who, "thisday"
order by who;

Answer 1

您可以使用 window functions 执行此操作:

select   who,
         sum("when" - lag)
from     (select row_number() over w,
                 who,
                 "when",
                 lag("when") over w
          from   t
          window w as (partition by who order by "when")) d
where    row_number % 2 = 0
group by who

如果您需要每天，只需在group by 子句中使用date_trunc('day', "when")。您还可以将 date_trunc('day', "when") 放在窗口定义内的 partition by 子句中，以避免跨越几天的配对:

select   who,
         date_trunc('day', "when"),
         sum("when" - lag)
from     (select row_number() over w,
                 who,
                 "when",
                 lag("when") over w
          from   t
          window w as (partition by who, date_trunc('day', "when") order by "when")) d
where    row_number % 2 = 0
group by who, date_trunc('day', "when")

但是，这些解决方案要求行必须是in + out 对。要获得更可靠的解决方案，您需要一个 direction 列。

http://rextester.com/UJWWH59178