CODEWITHSUNDEEP
sqloracle-database
Matt Kunze
Matt Kunze

Reputation: 3

Flattening previous history records in Oracle

I have a table that looks like [ id, group, reading, reading_date ] that contains reading values for a particular group that are filled out periodically throughout the day.

I want to generate results that look like [ id, group, reading, reading_date, prev_reading, prev_date ] where the previous reading/date are the latest values for the group that are at least one day older than reading_date

Basically it seems like I should be able to self-join and get the id of the reading at least one day old, but I haven't been able to find a combination that actually works.

select h1.id, h1.group, h1.reading, h1.reading_date, h2.id prev_id
from history h1, history h2
where h2.id = (
  select ...
)

I've also tried a handful of variants using analytic functions, but rank and lag don't seem to work because the offset between the records is variable, and I'm not seeing how to get first_value to do what I want.

thanks for any pointers in the right directly, my Oracle analytic skills are fairly lacking

Upvotes: 0

Views: 70

Answers (1)

David Faber
David Faber

Reputation: 12485

Maybe something like this?

SELECT h1.id, h1.group, h1.reading, h1.reading_date
     , MAX(h2.id) KEEP (DENSE_RANK FIRST ORDER BY h2.reading_date DESC) AS prev_id
     , MAX(h2.reading) KEEP (DENSE_RANK FIRST ORDER BY h2.reading_date DESC) AS prev_reading
     , MAX(h2.reading_date) KEEP (DENSE_RANK FIRST ORDER BY h2.reading_date DESC) AS prev_date
  FROM history h1 LEFT JOIN history h2
    ON h1.group = h2.group
   AND h1.reading_date - 1 >= h2.reading_date
 GROUP BY h1.id, h1.group, h1.reading, h1.reading_date;

This will get the most recent record in h2 that is at least one day older than the record in h1. Keep in mind that h1 will have older records as well as the latest ones.

Upvotes: 1

Related Questions