I have a SQL data table like this, and I wanted to calculate the rolling percentage change (by row and category). So that the result looks like this below
The SQL query I use is really slow and it takes forever to calculate it when there are thousands of categories, do you have an idea what's going on? Or any improvement?
First create a sample data_table:
CREATE TABLE IF NOT EXISTS data_table (
id INT AUTO_INCREMENT,
num INT,
category VARCHAR(10),
price FLOAT(20,2),
PRIMARY KEY (id)
);
INSERT INTO data_table(num,category,price)
VALUES(1,"A","10"),
(2,"A","20"),
(3,"A","30"),
(1,"B","20"),
(2,"B","30"),
(3,"B","40");
SQL for calculating percentage change:
SELECT
A.*,
CASE WHEN (A.price IS NULL OR B.price IS NULL OR B.price=0) THEN 0 ELSE
(A.price - B.price)/(B.price) *100 END AS perc
FROM (SELECT
num,
category,
price
FROM data_table
) A LEFT JOIN (SELECT
num,
category,
price
FROM data_table
) B
ON (A.num = B.num+1) AND A.category=B.category;
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `data_table` ADD INDEX `data_table_idx_category` (`category`);
SELECT
A.*,
CASE
WHEN (A.price IS NULL
OR B.price IS NULL
OR B.price = 0) THEN 0
ELSE (A.price - B.price) / (B.price) * 100 END AS perc
FROM
data_table A
LEFT JOIN
data_table B
ON (
A.num = B.num + 1
)
AND A.category = B.category