首页 文章

postgresql选择具有最小值的行

提问于
浏览 次
0

这是我在PostgreSQL中的查询：

SELECT 
    "axapta_calls".id, 
    "axapta_calls".call_time, 
    calls.calltime, 
    "calls"."id" as "call_id",  
    abs(extract(epoch from (axapta_calls.call_time::timestamp - calls.calltime::timestamp))) as ab 
FROM 
    "axapta_calls" 
inner join 
    "calls" (ON 
        axapta_calls.converted_outer_phone=calls.caller_phone
        and abs(extract(epoch from (axapta_calls.call_time::timestamp - calls.calltime::timestamp)))<= 600 )
WHERE  ("axapta_calls"."id" > 0) 
GROUP BY "axapta_calls"."id", "calls"."id"

结果是：

如何只获得一个最小“ab”值的行？

我将此查询更改为：

SELECT 
    distinct on (axapta_calls.id)
    "axapta_calls".id, 
    "axapta_calls".call_time, 
    calls.calltime, 
    "calls"."id" as "call_id",  
    abs(extract(epoch from (axapta_calls.call_time::timestamp - calls.calltime::timestamp))) as ab 
FROM 
    "axapta_calls" 
inner join 
    "calls" ON 
        axapta_calls.converted_outer_phone=calls.caller_phone
        and abs(extract(epoch from (axapta_calls.call_time::timestamp - calls.calltime::timestamp)))<= 600 
WHERE  ("axapta_calls"."id" > 0) 
GROUP BY "axapta_calls"."id", "calls"."id"

但得到第二行ab = 347.783 . 我做错了什么？

postgresql postgresql-9.1 postgresql-9.3

2 回答

  • 1

    您也可以使用分析功能：

    select *
from 
(SELECT 
    "axapta_calls".id, 
    "axapta_calls".call_time, 
    calls.calltime, 
    "calls"."id" as "call_id",  
    abs(extract(epoch from (axapta_calls.call_time::timestamp - calls.calltime::timestamp))) as ab,
    rank() OVER (ORDER BY ab )rnk
FROM 
    "axapta_calls" 
inner join 
    "calls" (ON 
        axapta_calls.converted_outer_phone=calls.caller_phone
        and abs(extract(epoch from (axapta_calls.call_time::timestamp - calls.calltime::timestamp)))<= 600 )
WHERE  ("axapta_calls"."id" > 0) )
where rnk=1
    回复于
  • 0

    你错过了 ORDER BY 条款 . 您应首先通过 id ，然后通过 ab 列进行排序 . 然后，Postgres将为每个 id 返回一个对应于最低 ab 值的记录 .

    SELECT 
    distinct on (axapta_calls.id),
    "axapta_calls".id, 
    "axapta_calls".call_time, 
    calls.calltime, 
    "calls"."id" as "call_id",  
    abs(extract(epoch from (axapta_calls.call_time::timestamp - calls.calltime::timestamp))) as ab 
FROM 
    "axapta_calls" 
inner join "calls"
    ON axapta_calls.converted_outer_phone=calls.caller_phone and
       abs(extract(epoch from (axapta_calls.call_time::timestamp - calls.calltime::timestamp)))<= 600 )
WHERE  ("axapta_calls"."id" > 0)
ORDER BY
    axapta_calls.id, 5;
    回复于

相关问题