sql - 按 id 和第二列分组

Question

我有一个返回三列的存储过程

| ID | EVENTS_ID | EVENT_TIME_FROM | 
| 46 | 1 | "2015-03-09 15:00:00" |
| 46 | 2 | "2015-03-16 15:00:00" |
| 46 | 3 | "2015-03-18 15:00:00" |
| 47 | 4 | "2015-03-12 16:30:00" |
| 47 | 5 | "2015-03-23 16:30:00" |
| 47 | 6 | "2015-03-23 16:30:00" |
| 47 | 7 | "2015-03-23 16:30:00" |
| 47 | 8 | "2015-03-23 16:30:00" |

给定每个 ID 的最大 EVENT_TIME_FROM，我想查询所有 id + events_ids

即结果会是

| ID | EVENTS_ID | EVENT_TIME_FROM | 
| 46 | 3 | "2015-03-18 15:00:00" |
| 47 | 5 | "2015-03-23 16:30:00" |
| 47 | 6 | "2015-03-23 16:30:00" |
| 47 | 7 | "2015-03-23 16:30:00" |
| 47 | 8 | "2015-03-23 16:30:00" |

另外，为了让这更困难，该表实际上是存储过程调用和一些分组的结果。

即当前查询是

SELECT DISTINCT id,events_id, event_time_from
FROM my_stored_proc(...data)
GROPU BY id, events_id, event_time_from
ORDER BY id ,events_id ,event_time_from DESC

我在 Postgres 上，尝试了一些东西但不确定。

Answer 1

如何使用诸如 rank() 之类的排名函数:

select id,events_id, event_time_from,
from (
    select id,events_id, event_time_from,
        rank() over (partition by id order by event_time_from desc) rnk
    from my_stored_proc(...data)
) t 
where rnk = 1

---

或者，您可以使用 max() 聚合将结果连接回它们自己:

select m.id,m.events_id,m.event_time_from
from my_stored_proc(...data) m
    join (
        select id, max(event_time_from) event_time_from
        from my_stored_proc(...data)
        group by id
   ) t on m.id = t.id and m.event_time_from = t.event_time_from