PL-SQL Query : Difference in values based on date from same table-CodePudding

I need some help with sql.

ID	Date	Price1
1	01-09-2020	32
1	02-09-2020	343
2	03-09-2020	54543
2	03-09-2020	43232
2	05-09-2020	3232
2	05-09-2020	34323
3	06-09-2020	3234213
3	07-09-2020	3232213

I have a table of this kind. I want to have difference in price based on dates. Like below

A.id	date 1	price 1	B.ID	date 2	price 2	diff
2	03/09/2020	54543	2	05/09/2020	3232	-51311
2	03/09/2020	43232	2	05/09/2020	34323	-8909

But I am getting is

A.id	date 1	price 1	B.ID	date 2	price 2	diff
2	03/09/2020	54543	2	05/09/2020	3232	-51311
2	03/09/2020	54543	2	05/09/2020	34323	-20220
2	03/09/2020	43232	2	05/09/2020	3232	-40000
2	03/09/2020	43232	2	05/09/2020	34323	-8909

i.e the price 1 column in repeating and taking difference for other values

Select a.id,a.date,a.price,b.id,b.date,b.price,(b.price-a.price)
from xyz a,xyz b      -- same table
where a.id = 2
and a.id = b.id
and a.date = todate(03092020,'ddmmyyyy') and b.date = todate(05092020,'ddmmyyyy')
orderby a.id,a.date

This is the code I'm using. All I need is diff in corresponding values

CodePudding user response：

To calculate the price difference you must get the previous price in the first step.

To get it you use preferably the LAG analytic function that can lookup the value from the preceeding row.

Before the use you must decide the partitioning, i.e. the column(s) that distinct the observed value - in you case the ID and

the column(s) that define ordering - in you case the date colum.

Query returns the LAG value

select ID, DT, PRICE,
lag(PRICE) over (partition by ID order by DT) PRICE_LAG
from tab
order by ID, DT;


        ID DT                       PRICE  PRICE_LAG
---------- ------------------- ---------- ----------
         1 01.09.2020 00:00:00         32           
         1 02.09.2020 00:00:00        343         32
         2 03.09.2020 00:00:00      54543           
         2 04.09.2020 00:00:00      43232      54543
         2 05.09.2020 00:00:00       3232      43232
         2 06.09.2020 00:00:00      34232       3232
         3 06.09.2020 00:00:00    3234213           
         3 07.09.2020 00:00:00    3232213    3234213

The next step is a simple calculation of the difference using

PRICE - lag(PRICE) over (partition by ID order by DT) PRICE_DIFF

Important note - a good practice while making such processing is to validate that the partitioning and ordering column togather build a unique key in the table. (In other words in your case each product at some tiem has a unique price).

Your data violates this so you may decide to re-thing the approach or clean the data.

CodePudding user response：

You can number the rows for each id/date partition and then pivot the rows to columns based on the dates:

SELECT id AS id1,
       "1_DATE" AS date1,
       "1_PRICE" AS price1,
       id AS id2,
       "2_DATE" AS date2,
       "2_PRICE" AS price2,
       "2_PRICE" - "1_PRICE" AS diff
FROM   (
  Select x.*,
         ROW_NUMBER() OVER (PARTITION BY id, "DATE" ORDER BY ROWNUM) AS rn
  from   xyz x
  where  id = 2
  and    "DATE" IN (DATE '2020-09-03', DATE '2020-09-05')
)
PIVOT (
  MAX("DATE") AS "DATE",
  MAX(price1) AS price
  FOR "DATE" IN (
    DATE '2020-09-03' AS "1",
    DATE '2020-09-05' AS "2"
  )
);

Note: The order of rows is non-deterministic so relying on ROWNUM to specify the order is a bad idea as it can change; however, the sample data in the question does not provide any "better" way of determining which rows for 2020-09-3 or 2020-09-05 should be "first" or "second". You should fix your table structure to ensure that there is a way to deterministically order the rows, such as having a position column and use that in place of ROWNUM.

Which, for the sample data:

CREATE TABLE xyz (ID, "DATE", Price1) AS
SELECT 1, DATE '2020-09-01',      32 FROM DUAL UNION ALL
SELECT 1, DATE '2020-09-02',     343 FROM DUAL UNION ALL
SELECT 2, DATE '2020-09-03',   54543 FROM DUAL UNION ALL
SELECT 2, DATE '2020-09-03',   43232 FROM DUAL UNION ALL
SELECT 2, DATE '2020-09-05',    3232 FROM DUAL UNION ALL
SELECT 2, DATE '2020-09-05',   34323 FROM DUAL UNION ALL
SELECT 3, DATE '2020-09-06', 3234213 FROM DUAL UNION ALL
SELECT 3, DATE '2020-09-07', 3232213 FROM DUAL;

Outputs:

ID1	DATE1	PRICE1	ID2	DATE2	PRICE2	DIFF
2	2020-09-03 00:00:00	54543	2	2020-09-05 00:00:00	3232	-51311
2	2020-09-03 00:00:00	43232	2	2020-09-05 00:00:00	34323	-8909

fiddle