I have a table that looks like this:
Unique ID Product Price Date
112 XYZ $10.00 10/10/21
112 XYZ $10.00 10/11/21
112 XYZ $10.00 10/12/21
113 ABC $15.99 10/10/21
113 ABC $17.99 10/11/21
113 ABC $17.99 10/12/21
114 LMO $5.00 10/10/21
114 LMO $5.00 10/11/21
115 DEF $9.00 10/10/21
115 DEF $8.00 10/11/21
I am trying to use the 10/10/21 date as my benchmark to map out price changes by product. I created an index column so now my table looks like this:
Unique ID Product Price Date Index
112 XYZ $10.00 10/10/21 Index
112 XYZ $10.00 10/11/21 No
112 XYZ $10.00 10/12/21 No
113 ABC $15.99 10/10/21 Index
113 ABC $17.99 10/11/21 No
113 ABC $17.99 10/12/21 No
114 LMO $5.00 10/10/21 Index
114 LMO $5.00 10/11/21 No
115 DEF $9.00 10/10/21 Index
115 DEF $8.00 10/11/21 No
Next, I'm trying to add a column to show % change for each product based on the index column, so output should look something like this:
Unique_id Product Price Date Index Price_change
112 XYZ $10.00 10/10/21 Index 0%
112 XYZ $10.00 10/11/21 No 0%
112 XYZ $10.00 10/12/21 No 0%
113 ABC $15.99 10/10/21 Index 0%
113 ABC $17.99 10/11/21 No 12.5%
113 ABC $17.99 10/12/21 No 12.5%
114 LMO $5.00 10/10/21 Index 0%
114 LMO $5.00 10/11/21 No 0%
115 DEF $9.00 10/10/21 Index 0%
115 DEF $8.00 10/11/21 No -11.1%
My SQL statement to do this looks like this:
select *
, case when index = 'index'
THEN (select price/price -1)
ELSE ( price / (select price from my_table where product = 'XYZ' and index = 'index') - 1)
END AS price_change
from my_table
where product = 'XYZ'
This works for the unique product XYZ, but I'm not able to figure out how to do this for the whole data frame across all the products. Is there where I need to use a subquery? Any help would be appreciated.
CodePudding user response：
This should work. Assuming there is a value for 10-10-2021 for every product.
Select
a.product,
a.price,
(a.price-b.price)*100/b.price as percent_change
from
my_table a
inner join
(select Product,price from my_table where date = '2021-10-10' group by Product,price )b
on a.product=b.product
CodePudding user response：
I presume that the index value is the earliest date:
select
"Unique Id", product, price, "Date", "Index",
price / first_value(price)
over (partition by "Unique Id" order by "Date") * 100.00
- 100.00 as Percent_Change
from tbl
If you want to use the "Index" column then just pick it out with a case expression:
select
"Unique Id", product, price, "Date", "Index",
price / min(case when "Index" = 'Index' then price end)
over (partition by "Unique Id") * 100.00
- 100.00 as Percent_Change
from tbl
CodePudding user response：
https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=448996f2f534d1142e5c23b219a9879e
Try this fiddle. Also, provide a basic fiddle, along with some other information when you post database questions. It will help people answer. Since I don't know what database you are using, I answered in mySQL 5.5. I also assumed that "index" was the first date. If so, it's not necessary to mark it specifically. You can just use the query to get the first date.
There are absolutely better ways to do this, but in earlier versions of mySQL (before the introduction of CTE) I don't believe there is a way to do this without several nested joins: one to get the max and min dates, another to get the prices associated with those dates, and then a third query to get the values and pct change (I actually used a fourth nest to calculate the pct change because I thought it would read better. It was not absolutely necessary).
Or you can do it in two nested queries by using group_concat and ORDER BY to isolate the first and last prices, but it's a little hacky. Again, I gave the two answers that I knew would work in a random version of mySQL. There is also a way to do it with session variables, but I believe those are deprecated.
If you need a running percent change, see the last query.
Let me know what your actual version is, and maybe I can do better.