Solution to update the price range and price rate of the whole table with only one SQL sentence

Time:2021-7-27

Problem scenario

Changes in three scores (logistics, services and commodities) of major platform stores;
Records of daily changes in commodity prices;
Real time fluctuation of stocks;

Reproduction scene

Table: primary key ID, commodity number, time when recording, price when recording, and creation time.
Question: get the change of each commodity (rise and fall range, rise and fall rate).

Solution ideas

1. If you want to update the rise and fall efficiently, you must not update the data one by one. You should establish a corresponding relationship through the self connected table and associate each data with the last price data.

2. Because the database is very large, there may be a lot of garbage data. For example, if the relevant field value is null or invalid value, these data should be excluded first.


SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL;

3. Then, in order to obtain the last piece of data of each data, it is also necessary to eliminate the garbage data first.


SELECT tmp_a.*, MAX(tmp_b.goods_date) AS last_date FROM 
( SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_a
LEFT JOIN
( SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_b
ON tmp_a.goods_code = tmp_b.goods_code AND tmp_a.goods_date > tmp_b.goods_date GROUP BY tmp_a.id;

4. After obtaining the previous data, obtain the commodity price corresponding to the previous data.


SELECT tmp_ab.*,tmp_c.goods_price AS last_price FROM 
(
	SELECT tmp_a.*, MAX(tmp_b.goods_date) AS last_date FROM 
	( SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_a
	LEFT JOIN
	( SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_b
	ON tmp_a.goods_code = tmp_b.goods_code AND tmp_a.goods_date > tmp_b.goods_date GROUP BY tmp_a.id
) AS tmp_ab 
LEFT JOIN (SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_c
ON tmp_ab.goods_code = tmp_c.goods_code AND tmp_c.goods_date = tmp_ab.last_date ORDER BY tmp_ab.id;

5. After obtaining the previous data and the corresponding price, start the calculation to obtain the final result.

SELECT 
	*, 
	(CONVERT(goods_ price, DECIMAL(10,2)) - CONVERT(last_ Price, decimal (10,2))) as' rise and fall ',
	ROUND((CONVERT(goods_ price, DECIMAL(10,2)) - CONVERT(last_ price, DECIMAL(10,2)))/CONVERT(last_ Price, decimal (10,2)), 2) as' rise and fall rate ' 
FROM (
	SELECT tmp_ab.*,tmp_c.goods_price AS last_price FROM 
	(
		SELECT tmp_a.*, MAX(tmp_b.goods_date) AS last_date FROM 
		( SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_a
		LEFT JOIN
		( SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_b
		ON tmp_a.goods_code = tmp_b.goods_code AND tmp_a.goods_date > tmp_b.goods_date GROUP BY tmp_a.id
	) AS tmp_ab 
	LEFT JOIN (SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_c
	ON tmp_ab.goods_code = tmp_c.goods_code AND tmp_c.goods_date = tmp_ab.last_date ORDER BY tmp_ab.id
) AS tmp

Solution

--Create SQL table
CREATE TABLE `test_goods_price_change` (
  `id` int(11) NOT NULL AUTO_ Increment comment 'primary key ID',
  `goods_ Code ` varchar (50) not null comment 'commodity code',
  `goods_ Date ` int (11) not null comment 'time when recording',
  `goods_ Price ` decimal (10,2) not null comment 'price at the time of recording',
  `created_ At ` int (11) not null comment 'creation time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8mb4;

--Get up and down SQL
SELECT 
	*, 
	(CONVERT(goods_ price, DECIMAL(10,2)) - CONVERT(last_ Price, decimal (10,2))) as' rise and fall ',
	ROUND((CONVERT(goods_ price, DECIMAL(10,2)) - CONVERT(last_ price, DECIMAL(10,2)))/CONVERT(last_ Price, decimal (10,2)), 2) as' rise and fall rate ' 
FROM (
	SELECT tmp_ab.*,tmp_c.goods_price AS last_price FROM 
	(
		SELECT tmp_a.*, MAX(tmp_b.goods_date) AS last_date FROM 
		( SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_a
		LEFT JOIN
		( SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_b
		ON tmp_a.goods_code = tmp_b.goods_code AND tmp_a.goods_date > tmp_b.goods_date GROUP BY tmp_a.id
	) AS tmp_ab 
	LEFT JOIN (SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_c
	ON tmp_ab.goods_code = tmp_c.goods_code AND tmp_c.goods_date = tmp_ab.last_date ORDER BY tmp_ab.id
) AS tmp

This is the end of this article about updating the price range and price rate of the whole table with only one sentence of SQL. For more information about updating the whole table with SQL, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!