mysql - 与上个月相比，如何获得列中的最大差异？

Question

我有两个表 - 产品和价格

每个月的表格价格都会填充每种产品的新价格。如何获得价格与上月价格增量差异最大的 5 个产品？

表格产品

id | name
 1 | apples
 2 | pears
 3 | bananas

表价格

id | price | product_id | created_at
 1 |  10   |      1     | 2017-02-07 07:00:00
 2 |  10   |      2     | 2017-02-07 07:00:00
 3 |  15   |      3     | 2017-02-07 07:00:00
 5 |  15   |      1     | 2017-03-07 07:00:00
 6 |  20   |      2     | 2017-03-07 07:00:00
 7 |  25   |      3     | 2017-03-07 07:00:00

结果会发现

1.    Bananas has prices by 15 higher (lastMonth: 15, now: 25)
2.    Pears 2 has prices by 10 higher (lastMonth: 10, now: 20)
3.    Apples has prices by 5 higher (lastMonth: 10, now: 15)

我在想这样的事情(我知道这很糟糕)

SELECT products.id, products.name, prices.beforePrice, prices.afterPrice, prices.difference 
FROM products
INNER JOIN prices ON products.id = prices.product_id
WHERE 
     ( 
      SELECT *biggest-difference*
      FROM prices
      WHERE *difference_between_last_2_months*
      GROUP BY product_id
      LIMIT 5
     )

Answer 1

创建表/插入数据

CREATE TABLE Products
    (`id` INT, `name` VARCHAR(7))
;

INSERT INTO Products
    (`id`, `name`)
VALUES
    (1, 'apples'),
    (2, 'pears'),
    (3, 'bananas')
;


CREATE TABLE Prices
    (`id` INT, `price` INT, `product_id` INT, `created_at` DATETIME)
;

INSERT INTO Prices
    (`id`, `price`, `product_id`, `created_at`)
VALUES
    (1, 10, 1, '2017-02-07 07:00:00'),
    (2, 10, 2, '2017-02-07 07:00:00'),
    (3, 15, 3, '2017-02-07 07:00:00'),
    (5, 15, 1, '2017-03-07 07:00:00'),
    (6, 20, 2, '2017-03-07 07:00:00'),
    (7, 25, 3, '2017-03-07 07:00:00')
;

查询

SELECT 
   Products.id
 , Products.name  
 , (current_month.price - last_month.price) AS difference
 , (
    CASE 
       WHEN last_month.price > current_month.price
       THEN 'lower'

       WHEN last_month.price < current_month.price
       THEN 'higher'

     END
   ) AS incremental
 , last_month.price 'lastMonth'
 , current_month.price 'now'
FROM (
 SELECT 
  *
 FROM 
  Prices 
 WHERE
   MONTH(created_at) = MONTH((CURDATE() - INTERVAL 1 MONTH))
)
 AS
  last_month 
INNER JOIN (
  SELECT 
   *
  FROM 
   Prices 
  WHERE
    MONTH(created_at) = MONTH((CURDATE()))
)  
 AS 
  current_month
ON
  last_month.product_id = current_month.product_id
INNER JOIN
 Products
ON
  last_month.product_id = Products.id  
WHERE
  last_month.price < current_month.price #incremental should be higher
ORDER BY 
 difference DESC
LIMIT 5  

结果

    id  name     difference  incremental  lastMonth     now  
------  -------  ----------  -----------  ---------  --------
     2  pears            10  higher              10        20
     3  bananas          10  higher              15        25
     1  apples            5  higher              10        15