mysql - SQL - 价格随时间增加

Question

我确定这个问题已经有人回答了，我只是不知道我在找什么。

我得到的是每月更新的价目表，我正在寻找一个列出所有价格上涨超过 20% 的项目 (tnr) 的查询

在这种情况下，我想要“tnr”

136234194430
832124069830
183078059150

我可以遍历所有我知道有更智能、更快速、更优雅的方法的项目

用于测试内容的虚拟表

CREATE TABLE `pricelist` (
  `tnr` bigint(64) NOT NULL,
  `price` double NOT NULL,
  `discount` int(8) NOT NULL,
  `date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `pricelist` (`tnr`, `price`, `discount`, `date`) VALUES
(183078059150, 33.89, 26, '2018-08-01'),
(514780535750, 78.73, 19, '2018-08-01'),
(121475122010, 521.54, 16, '2018-08-01'),
(726576581300, 168.36, 10, '2018-08-01'),
(832124069830, 22.69, 50, '2018-08-01'),
(342122275110, 131.5, 26, '2018-08-01'),
(345067567690, 6.34, 26, '2018-08-01'),
(121113618790, 195.5, 16, '2018-08-01'),
(511681969780, 291.74, 23, '2018-08-01'),
(411372385450, 129.75, 23, '2018-08-01'),
(15097806600, 46.68, 19, '2018-08-01'),
(613592995940, 259.47, 19, '2018-08-01'),
(135414163780, 17, 19, '2018-08-01'),
(726076671410, 68.91, 11, '2018-08-01'),
(136234194430, 36.86, 23, '2018-08-01'),
(541122685800, 10.25, 16, '2018-08-01'),
(514722202230, 83.19, 23, '2018-08-01'),
(125177976530, 257.12, 26, '2018-08-01'),
(114377922120, 19.18, 23, '2018-08-01'),
(642169317400, 2.54, 26, '2018-08-01'),
(14085256200, 16.44, 14, '2018-08-01'),
(114313045460, 22.46, 16, '2018-08-01'),
(331014284930, 1042.02, 19, '2018-08-01'),
(183078059150, 53.89, 26, '2018-09-01'),
(514780535750, 78.73, 19, '2018-09-01'),
(121475122010, 521.54, 16, '2018-09-01'),
(726576581300, 168.36, 10, '2018-09-01'),
(832124069830, 42.69, 50, '2018-09-01'),
(342122275110, 131.5, 26, '2018-09-01'),
(345067567690, 6.34, 26, '2018-09-01'),
(121113618790, 195.5, 16, '2018-09-01'),
(511681969780, 291.74, 23, '2018-09-01'),
(411372385450, 129.75, 23, '2018-09-01'),
(15097806600, 46.68, 19, '2018-09-01'),
(613592995940, 259.47, 19, '2018-09-01'),
(135414163780, 17, 19, '2018-09-01'),
(726076671410, 68.91, 11, '2018-09-01'),
(136234194430, 66.86, 23, '2018-09-01'),
(541122685800, 10.25, 16, '2018-09-01'),
(514722202230, 83.19, 23, '2018-09-01'),
(125177976530, 257.12, 26, '2018-09-01'),
(114377922120, 19.18, 23, '2018-09-01'),
(642169317400, 2.54, 26, '2018-09-01'),
(14085256200, 16.44, 14, '2018-09-01'),
(114313045460, 22.46, 16, '2018-09-01'),
(331014284930, 1042.02, 19, '2018-09-01');

非常感谢

Answer 1

• 利用 Correlated subquery 确定每个 tnr 和日期的“远期价格”(下一个日期的新价格) .

• 使用 Derived tables并对 tnr 进行分组，过滤(使用 Having 子句)“增长”大于 20% 的 tnr

使用以下查询 ( SQL Fiddle Demo ):

SELECT inner_nest.tnr, 100*(inner_nest.forward_price - inner_nest.price)/inner_nest.price as growth
FROM 
(
  SELECT t1.*, (SELECT t2.price 
                FROM pricelist AS t2 
                WHERE t2.tnr = t1.tnr 
                  AND t2.date > t1.date 
                ORDER BY t2.date ASC LIMIT 1) AS forward_price 
  FROM pricelist AS t1
) AS inner_nest 
GROUP BY inner_nest.tnr 
HAVING growth > 20