CODEWITHSUNDEEP
sqloracle-databasefunctioncase
Thinker
Thinker

Reputation: 6892

SQL query to get the latest price from the data?

I have this data in my Oracle table:

enter image description here

In this table, I have multiple products with having product_id and price at a particular time.

I want to write a SQL query to get latest price of each product based on the TIME column (Oracle - Long Type).

But here is the problem, for our table price monitoring for the product starts at 23:00 previous date.

Output required: when I passed 2-Dec-2019 as parameter into the SQL query

enter image description here

How can I do this with a SQL query? Or do I need to write a stored procedure for it, based on if and else condition?

Thanks in advance.

Upvotes: 0

Views: 1421

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 95052

First of all you are storing date and time separately, which makes working with the date and time more complicated than needed.

One way to combine the two:

local_date + interval '1' second * (to_number(substr(time, 5, 2)) +
                                    to_number(substr(time, 3, 2)) * 60 +
                                    to_number(substr(time, 1, 2)) * 3600)

Another:

to_date(to_char(local_date, 'yyyymmdd') || time, 'yyyymmddhh24miss')

With this datetime you want something along the lines of

where the_time >= timestamp '2019-12-01 23:00:00' and the_time < date '2019-12-03'

For some date yet unknown that will be provided when running the query:

where the_time >= :date - interval '1' hour and the_time < :date + interval '1' day

For the current day, supposing there is no future data in the table:

where the_time >= trunc(sysdate) - interval '1' hour

As to preferring a more current datetime over an older one, use a window function, e.g. MAX OVER.

with rows_with_datetime as
(
  select
    mytable.*, 
    to_date(to_char(local_date, 'yyyymmdd') || time, 'yyyymmddhh24miss') as dt
  from mytable
)
, two_days_with_maxdatetime as
(
  select
    rows_with_datetime.*,
    max(dt) over (partition by product_id order by dt) as max_dt
  from rows_with_datetime
  where dt >= trunc(sysdate) - interval '1' hour
)
select *
from two_days_with_maxdatetime
where dt = max_dt
order by product_id;

As the datetime gets calculated, no index will be used here and the query may be quite slow accordingly. So, you should better change the data model to have a datetime you can easily index and work with instead of separate date and time. If you can't, you may want to apply an additional redundant condition on the date alone:

where local_date >= trunc(sysdate) - interval '1' day

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270523

Oh, I see. You can use two parameters in the ORDER BY:

SELECT local_date, system_date, currency, product_id, time, current_price
FROM (SELECT t.*,
             ROW_NUMBER() OVER (PARTITION BY product_id
                                ORDER BY local_date DESC, time DESC
                               ) as seqnum
      FROM t
      WHERE system_date = @parameter
     ) t
WHERE rn = 1;

I'm not sure if you really want the filtering on date like that. You might want:

      WHERE system_date <= @parameter

or:

      WHERE system_date < @parameter + interval '1' day

These would allow you to get data from earlier days, if there is no data on the date specified by the parameter. The second version also works if system_date has a time component (which is allowed for date data types in Oracle).

Upvotes: 1

Marmite Bomber
Marmite Bomber

Reputation: 21095

You may extend the idea from other answer (using ROW_NUMBER) to define a view providing only the records with the last price per day.

You must provide you dimensions (product_id and day) in the PARTITION BY clause, which will work for any product and any day - selecting only the row with the highest time per day and product.

create view last_price_per_day as 
with last_price as (
select 
  SYSTEM_DATE, PRODUCT_ID, TIME, CURRENT_PRICE,
  row_number() over (partition by PRODUCT_ID, SYSTEM_DATE order by TIME desc) as rn
from tab)
select 
  SYSTEM_DATE, PRODUCT_ID, TIME, CURRENT_PRICE
from last_price
where rn = 1;

Note that this approach is superior to altervatives using MAX(TIME) in correlated subquery, as it works fine even if ties occurres (more rows with the same highest time).

If this is a relevant topic, you may add further columns to the ORDER BY clause, e.g. by adding CURRENT_PRICE DESC you will get the highest price from the tied rows.

A good check for such last row views is to verify if the columns in PARTITIEN BY and ORDER BY are unique in the table.

In your case PRODUCT_ID, SYSTEM_DATE, TIME should be unique, otherwise the view is not deterministic and will return on repeated queries different results - which may be not what you expect.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522181

We can try using ROW_NUMBER for this requirement:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY time DESC) rn
    FROM yourTable t
    WHERE system_date = date '2019-12-19'
)

SELECT local_date, system_date, currency, product_id, time, current_price
FROM cte
WHERE rn = 1;

Upvotes: 2

Related Questions