CODEWITHSUNDEEP
sqlfunctionpostgresqljoin
javshak
javshak

Reputation: 329

Combine results from two independent queries

I'm fairly new to Postgres and need to fetch two separate data : 1) values of avg, min, max of various records/resuts of table T1 2) column values of the 'last' record of the table T1 based on the most recent timestamp

The problem is I cannot run these queries separately as it will cause performance issues. (the data in this table can be in tens of thousands or records and combining them into a result object, even more complex).

Is it possible to combine the results of these two data side by side into one monster of a query that will return the desired output?

Appreciate your help.

Updated with queries:

1st query :

select
    rtp.id, rtp.received_timestamp,
    rtp.agent_time, rtp.sourceip, rtp.destip, rtp.sourcedscp,
    sum(rtp.numduplicate) as dups, avg(rtp.numduplicate) as avgdups,
    min(rtp.numduplicate) as mindups, max(rtp.numduplicate) as maxdups
from rtp_test_result rtp
where
    rtp.received_timestamp between 1274723208 and 1475642299
group by rtp.sourceip, rtp.destip, rtp.sourcedscp
order by rtp.sourceip, rtp.destip, rtp.sourcedscp

2nd query:

select id, received_timestamp, numooo
from rtp_test_result 
where received_timestamp = (select max(received_timestamp) mrt from rtp_test_result)
group by id,received_timestamp, numooo 
order by id desc limit 1

Upvotes: 1

Views: 1379

Answers (1)

roman
roman

Reputation: 117485

something like

with cte as (
    select
        val,
        last_value(val) over(order by ts asc rows between unbounded preceding and unbounded following) as lst_value
    from T1
)
select
    avg(val) as avg_value,
    min(val) as min_value,
    max(val) as max_value,
    max(lst_value) as lst_value
from cte

or

select
    avg(val) as avg_value,
    min(val) as min_value,
    max(val) as max_value,
    (select val from T1 order by ts desc limit 1) as lst_value
from T1

sql fiddle demo

Upvotes: 2

Related Questions