sql - PostgreSQL 在函数中获得最新记录

Question

我有一个名为 cdr_get_call_log 的函数。我正在从这个函数中获取我的数据。正如您在示例数据记录中看到的那样，3 号和 4 号数据记录具有相同的 source_caller_id 和同一天，但时间不同。所以我想如果我的数据中有相同的 source_caller_id 我只想记录 start_time 是最新的

select source_caller_id,destination_dn,recording_url,start_time 
from (
  select * 
  from cdr_get_call_log
  where destination_type=0 
    and talking_duration is not null 
    and source_caller_id not like 'Ext%' 
    and source_caller_id like '0%' 
    and destination_dn in('628','627','629','630','631','626','600','632') 
    and source_caller_id<> '09903114243' 
    and subrow_desc_number='1' 
    and trim(recording_url)<> ''
) as a

表结构

| source_caller_id | destination_dn |   recording_url    |      start_time       |
|------------------|----------------|--------------------|-----------------------|
|    1.05356785544 |            627 | [627]/20180230.wav | 01/12/2018 11.12:39   |
|    2.05551165244 |            632 | [632]/20180230.wav | 04/12/2018 09.12:39   |
|    3.05556665544 |            621 | [627]/20180230.wav | 06/12/2018 05.12:39   |
|    4.05556665544 |            626 | [627]/20180230.wav | 06/12/2018 05.15:39   |

标签： sqlpostgresql

Answer 1

您可以使用distinct on：

with q as (<your query here>)
select distinct on (caller_id, date_trunc('day', start_time)) q.*
from q
order by caller_id, date_trunc('day', start_time), start_time desc;