postgres-按日期时间和状态的最小值进行分组 [英] postgres - having group by min of date time and status

问题描述

我想从下表中检索具有minb为
（svc_date）且状态为A的car_id。

预期为103和100，它们的状态为 A

因为它的状态为A，且svc_date最小

  car_id svc_date状态
 
 100 03/01/2013 A 
 100 04/02 / 2013 B 
 100 05/03/2013 C 
 101 06/01/2013 A 
 101 05/01/2013 B 
 102 06/06/2013 A 
 102 2013年5月5日B 
 103 2013/05/25 A 
  

我是使用postgres并尝试

 从car_tbl组中选择car_id，svc_date，状态，由
 car_id具有min（svc_date）和status ='A'
  

svc_date是带有时区的时间戳，
出现错误

AND的

 参数必须为布尔型，而不是时区为
的时间戳
  

是整个sql错误还是任何类型强制转换都可以？

解决方案

真的，您很难理解：

  cte as（
 select 
 car_id，
 status，
 rank（）OVER（
 PARTITION BY car_id 
 ORDER BY svc_date）
从car_tbl 
）
 select * 
 from cte 
其中rank = 1 
并且status ='A'
  

结果：

  | CAR_ID |状态|排名| 
 -------------------------- 
 | 100 | A | 1 | 
 | 103 | A | 1 | 
  

I would like to retrieve car_id from the below table which have min (svc_date) with status=A .

expected is 103 and 100 which have status 'A'

because it have the status A with minimum svc_date

car_id   svc_date            status

100     03/01/2013          A           
100     04/02/2013          B
100     05/03/2013          C 
101     06/01/2013          A
101     05/01/2013          B
102     06/06/2013          A
102     05/05/2013          B 
103     05/25/2013          A

i am using postgres and am trying

    select   car_id,svc_date,status  from  car_tbl  group by 
car_id having min(svc_date) and status='A'

svc_date is timestamp with timezone and getting error

  argument of AND must be type boolean, not type timestamp with time zone

is the entire sql is wrong or any type cast will help?

解决方案

Really, it is hard to understand to you:

with cte as (
select 
  car_id ,
  status,
  rank() OVER (
     PARTITION BY car_id 
     ORDER BY svc_date )       
from car_tbl
)
select * 
from cte 
where rank = 1 
and status = 'A'

Results:

| CAR_ID | STATUS | RANK |
--------------------------
|    100 |      A |    1 |
|    103 |      A |    1 |

这篇关于postgres-按日期时间和状态的最小值进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！