CODEWITHSUNDEEP
mysqlmysql-5.7
manlike
manlike

Reputation: 45

MYSQL 5.7 percentage change for each product

I have a table

Month_year          product   flow
---------------------------------
01-09-2021        shoes     21707294.4600
01-10-2021        shoes     97019343.1700
01-11-2021        shoes     5280468.9900
01-09-2021        dress     4078356.6700
01-10-2021        dress     2665287.0800
01-11-2021        dress     7728339.6500
01-09-2021        hats      7728339.6500
01-10-2021        hats      23326.2500

Expected results

Month_year          product   flow             change(%)
---------------------------------------------------------
01-09-2021        shoes     21707294.4600       0
01-10-2021        shoes     97019343.1700      346.94
01-11-2021        shoes     5280468.9900       -94.56
01-09-2021        dress     4078356.6700        0
01-10-2021        dress     2665287.0800       -34.65
01-11-2021        dress     7728339.6500       189.96
01-09-2021        hats      47600.0000          0  
01-10-2021        hats      23326.2500         -51.00

I have tried this query and it works but for mysql 8

SELECT product,
       DATE_FORMAT(`date`,'%M, %y') Month_year,
       SUM(flow) AS amount,
       SUM(flow) / LAG(SUM(flow)) OVER (PARTITION BY product
                                            ORDER BY Month_year) - 1 AS percent_change
FROM product
GROUP BY product,
         Month_year
ORDER BY product,
         Month_year

But mysql 5.7 does not support this.

How can I replicate this in MySQL5.7 to get the expected results?

Upvotes: 1

Views: 73

Answers (1)

P.Salmon
P.Salmon

Reputation: 17665

A simple self join

DROP TABLE IF EXISTS T;

CREATE TABLE T
(Month_year   VARCHAR(10),       product VARCHAR(20),   flow DECIMAL(15,4));
INSERT INTO T VALUES
('01-09-2021'    ,    'shoes'  ,   21707294.4600),
('01-10-2021'    ,    'shoes'  ,   97019343.1700),
('01-11-2021'    ,    'shoes'  ,   5280468.9900),
('01-09-2021'    ,    'dress'  ,   4078356.6700),
('01-10-2021'    ,    'dress'  ,   2665287.0800),
('01-11-2021'    ,    'dress'  ,   7728339.6500),
('01-09-2021'    ,    'hats'   ,   7728339.6500),
('01-10-2021'    ,    'hats'   ,   23326.2500);

SELECT SUBSTRING_INDEX(T.MONTH_YEAR,'-', -1) * 100 Y,SUBSTRING(T.MONTH_YEAR,4,2) M,
         SUBSTRING_INDEX(T.MONTH_YEAR,'-', -1) * 100 + SUBSTRING(T.MONTH_YEAR,4,2) YM, T.PRODUCT, T.FLOW,
         T1.FLOW,
         T.FLOW / T1.FLOW * 100 - 100 AS PERCENTCHANGE
FROM T
LEFT JOIN T T1 ON T1.PRODUCT = T.PRODUCT AND  
         SUBSTRING_INDEX(T1.MONTH_YEAR,'-', -1) * 100 + SUBSTRING(T1.MONTH_YEAR,4,2) = 
         SUBSTRING_INDEX(T.MONTH_YEAR,'-', -1) * 100 + SUBSTRING(T.MONTH_YEAR,4,2) - 1
ORDER BY T.PRODUCT DESC,Y,M;

+--------+------+--------+---------+---------------+---------------+---------------+
| Y      | M    | YM     | PRODUCT | FLOW          | FLOW          | PERCENTCHANGE |
+--------+------+--------+---------+---------------+---------------+---------------+
| 202100 | 09   | 202109 | shoes   | 21707294.4600 |          NULL |          NULL |
| 202100 | 10   | 202110 | shoes   | 97019343.1700 | 21707294.4600 |  346.94350716 |
| 202100 | 11   | 202111 | shoes   |  5280468.9900 | 97019343.1700 |  -94.55730289 |
| 202100 | 09   | 202109 | hats    |  7728339.6500 |          NULL |          NULL |
| 202100 | 10   | 202110 | hats    |    23326.2500 |  7728339.6500 |  -99.69817256 |
| 202100 | 09   | 202109 | dress   |  4078356.6700 |          NULL |          NULL |
| 202100 | 10   | 202110 | dress   |  2665287.0800 |  4078356.6700 |  -34.64801400 |
| 202100 | 11   | 202111 | dress   |  7728339.6500 |  2665287.0800 |  189.96274765 |
+--------+------+--------+---------+---------------+---------------+---------------+
8 rows in set (0.002 sec)

Upvotes: 1

Related Questions