I have a group by query returning avg and max from a set of records. I need to return a new column with the latest value of a column("records") based on another column ("dates").

This query

with x as (select 'A' process, 10 records, sysdate-5 dates from dual union all
       select 'A' process, 20 records, sysdate-4 dates from dual union all
       select 'A' process, 30 records, sysdate-3 dates from dual union all
       select 'B' process, 25 records, sysdate-2 dates from dual union all
       select 'B' process, 15 records, sysdate-1 dates from dual)
select process, 
   avg(records) avgu, 
   max(records) maxu
  from x
 group by process
 order by 1

returns:

I need a new column (LATEST) with latest value of records based on dates, keeping the old columns too:

I'm playing with some window functions like RANK OVER PARTITION but I can't get the desired outcome in a single query.

Thank you in advance for any idea.

CodePudding user response：

Here's one option:

Sample data:

SQL> with x as (
  2         select 'A' process,10 records,sysdate-5 dates from dual union all
  3         select 'A',20,sysdate-4 from dual union all
  4         select 'A',30,sysdate-3 from dual union all
  5         select 'B',25,sysdate-2 from dual union all
  6         select 'B',15,sysdate-1 from dual),

Query begins here: first find the latest value per each process, then - in the final query - aggregate required values.

  7  temp as
  8    (select process,
  9       records,
 10       dates,
 11       first_value(records) over (partition by process order by dates desc) latest
 12     from x
 13    )
 14  select process,
 15    avg(records) avgu,
 16    max(records) maxu,
 17    max(latest) latest
 18  from temp
 19  group by process
 20  order by 1;

P       AVGU       MAXU     LATEST
- ---------- ---------- ----------
A         20         30         30
B         20         25         15

SQL>