postgreSQL获取每个成员的最新可用性

Question

我有一个情况，我在一个表中存储每个成员的可用性。这是一个简单的4列表。

CREATE TABLE availablities (
availablity_id serial PRIMARY KEY,
member_id serial,
availablity_status_id serial,
start_date timestamp
);

每个成员可以在表中有多条记录，并获取当前状态对于每个成员，我得到最近的start_date小于now()的记录。

我首先尝试使用朴素的Max()和Group by查询

select 
status_code, max(start_date) start_date,availablities.member_id 
from 
availablities
join 
availablity_status on availablity_status.availablity_status_id = availablities.availablity_status_id
where 
start_date <= now() 
group by 
status_code,availablities.member_id;

但是这将为每个用户返回多个记录，因为我将根据用户和状态获取最近的记录。

我终于想出了一个查询，给了我预期的结果。

select status_code,start_date,a2.member_id  from availablities a2 
join availablity_status on availablity_status.availablity_status_id = a2.availablity_status_id
where a2.availablity_id in(
select 
max(availablity_id)
from availablities a 
where 
a.member_id = a2.member_id and 
start_date in(
select 
max(start_date) start_date
from availablities
where 
start_date <= now() 
and a.member_id = availablities.member_id    
)
);

但是这个查询需要60倍的时间来执行，感觉不太对。我很确定一定有更好的解决办法，但是我找不到。

获得预期结果的正确方法是什么?

我已经创建了一个DB-fiddle，使它更容易看到。当我们有更多的数据时，查询1是不正确的，查询2要慢得多。

https://www.db-fiddle.com/f/iWgvuj8kcms9F5CKuoKsny/2

Answer 1

看起来您需要在这里使用一个简单的row_number窗口函数:

with a as (
select *, Row_Number() over(partition by member_id order by start_date desc, availablity_id desc) rn
from availablities 
where start_date<now()
)
select s.status_code, a.start_date, a.member_id
from a join availablity_status s on s.availablity_status_id=a.availablity_status_id
where rn=1

请注意您的数据没有足够的选择性，所以对于member_id 3，它是否可用?如果有两个相同的日期，最近的日期是哪一天?

我添加了一个决定论，也按availability_id排序，以获得您期望的结果

实际上是availablity_id-你这里似乎有一个常见的打字错误!

查看更新后的Fiddle