时间戳

首页 > 解决方案 > SQL - 随着时间的推移价格上涨

mysql - SQL - 随着时间的推移价格上涨

问题描述

我确信这个问题已经得到解答，我只是不知道我在寻找什么。

我得到的是每个月更新的价目表，我正在寻找一个列出所有价格上涨超过 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');

非常感谢

标签： mysql

解决方案

  • 使用Correlated subquery确定每个 tnr 和日期的“远期价格”（下一个日期的新价格）。

  • 使用派生表和分组tnr，过滤（使用 Have 子句）tnr“增长”大于 20%

使用以下查询（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

推荐阅读