I have a table wich holds the records for all the products purchases made. What I'm trying to do is to get a list of all the purchases made that contain one specific product to get a price variation history. I also want to display an icon showing if the price increased, decreased or is the same value as the previous record.
I almost made it work, however, the way I found to do it is based on the unique ID of each rows. The problem with this approach is that they are not sequential, so I can't rely on this method. What I need to compare is the date, but I'm finding it difficult to do.
First of all, here is my table structures:
**tb_purchase**
id | value_purchase | value_delivery | date
1 | 80.90 | 0.00 | 2019-07-01
2 | 24.90 | 5.00 | 2019-07-03
3 | 18.90 | 5.00 | 2019-07-03
[...continue]
**tb_purchase_product**
id | id_purchase | id_product | id_provider | price | quantity | total
1 | 27 | 33 | 2 | 16.90 | 1 | 16.90
2 | 27 | 87 | 2 | 34.90 | 1 | 34.90
3 | 28 | 64 | 2 | 19.90 | 1 | 19.90
4 | 29 | 33 | 3 | 15.80 | 3 | 47.40
5 | 30 | 48 | 3 | 18.90 | 1 | 18.90
6 | 31 | 33 | 1 | 15.80 | 1 | 15.80
7 | 32 | 33 | 3 | 17.90 | 2 | 35.80
[...continue]
**tb_provider**
id | name
1 | 'Mega Food'
2 | 'Best Market'
3 | 'Amazing Sales Store'
[...continue]
This is the query I did:
SELECT
a.price,
DATE_FORMAT(b.date, '%d/%m/%Y') as 'date',
c.name as 'provider',
CASE
WHEN a.price > d.price THEN 1
WHEN a.price < d.price THEN -1
ELSE 0 END AS 'variation'
FROM
tb_purchase_product a,
tb_purchase b,
tb_provider c,
tb_purchase_product d
WHERE a.id_product = :id_product -- {ex.: 33}
AND a.id_purchase = b.id
AND b.id_provider = c.id
AND b.date BETWEEN :date_initial AND :date_final
AND d.id = a.id + 1
ORDER BY b.date DESC
The expected result for id_product = 33
is as follow, but I'm getting inconsistent results since it's comparing the value from the previous id
and not previous record of the id_product
. I'm also having trouble to fix this since I don't know how to mach the date when it's the first smaller date.
price | date | provider | variation
15.80 | 05/07/2019 | 'Amazing Sales Store' | -1
17.90 | 04/07/2019 | 'Mega Food' | 1
15.80 | 03/07/2019 | 'Amazing Sales Store' | -1
16.90 | 02/07/2019 | 'Best Market' | 0
Note:
:id_product
,:date_initial
and:date_final
are passed viaphp
array argument when running the application online.
Best Answer
As already suggested by Rick James in the comments, if you are working with MySQL 8+, you can make this much easier by using the
LAG()
analytic function to return the previous row's price. You can also get the variation using theSIGN()
function instead of theCASE
expression, as demonstrated in the following query:In this query I have also rewritten the joins using the
JOIN
syntax. Using the explicitJOIN
syntax separates joining conditions from filter conditions more clearly, which, in my estimation, makes the code more understandable and easier to maintain (but your mileage may vary).And with the use of the
LAG()
function there is no need to join a second instance oftb_purchase_product
, which is another obvious difference of this query from the original.One important note regarding this query is that, as written, it works for a single
id_product
, which should be all right based on your filter conditions. But if you ever need to run it for multiple products simultaneously, you need to introduce partitioning to theLAG
function, like this:The
PARTITION BY
subclause ensures that the previouspprod.price
value is for the same product as the current row.