php - MySQL 查询 : Calculate Biggest Daily/Weekly Price Increases/Decreases

Question

我有以下 3 个表:

CREATE TABLE IF NOT EXISTS `item` (
 `id` int(11) NOT NULL auto_increment,
 `subcategory` mediumint(9) NOT NULL,
 `name` varchar(64) NOT NULL,
 `pagetitle` varchar(128) NOT NULL,
 `metadescription` varchar(256) NOT NULL,
 `properties` varchar(256) NOT NULL,
 `specification` text NOT NULL,
 `summary` text NOT NULL,
 `homepage` varchar(256) NOT NULL default 'none',
 PRIMARY KEY  (`id`)
);

CREATE TABLE IF NOT EXISTS `link` (
  `id` mediumint(9) NOT NULL auto_increment,
  `item` mediumint(9) NOT NULL,
  `merchant` mediumint(9) NOT NULL,
  `rawlink` varchar(256) NOT NULL,
  PRIMARY KEY  (`id`)
);

CREATE TABLE IF NOT EXISTS `prices` (
  `id` mediumint(9) NOT NULL auto_increment,
  `link` mediumint(9) NOT NULL,
  `timestamp` date NOT NULL,
  `price` float NOT NULL,
  PRIMARY KEY  (`id`)
);

这些表保存价格比较网站的数据。

每个商品都有多个链接(指向不同的商店)link.item = item.id。

每个链接都有多个价格(每天一个跟踪价格)prices.link = link.id。

问题

我想创建一个 MySQL 查询，它返回:

1. 所有商品每日价格涨幅前 3 名
2. 所有商品每日 Markdown 前 3 名

到目前为止我已经想出了这个:

SELECT item.name AS iname, item.id AS iid,
((SELECT MIN(price) FROM prices, link WHERE prices.timestamp = CURDATE() AND link.id = prices.link AND link.item = iid) - 
(SELECT MIN(price) FROM prices, link WHERE prices.timestamp = CURDATE() - INTERVAL 1 DAY AND link.id = prices.link AND link.item = iid))
AS changeprice
FROM prices, link, item
WHERE link.id = prices.link AND link.item = item.id
GROUP BY link.item
ORDER BY changeprice DESC
LIMIT 0,3

它在技术上可行，但是一个低效的解决方案(服务器超时)。

我知道它是有效的，因为我通过添加这个额外的 WHERE 子句来限制项目以提高速度:

item.id BETWEEN '20' AND '30'

我目前有 55 个项目，事实证明这太多了，所以我需要一个更快/更简单的解决方案。

我找到了this alternative solution ，但它似乎不起作用(我收到错误)。

提前致谢!

样本数据

INSERT INTO `item` (`id`, `subcategory`, `name`, `pagetitle`, `metadescription`, `properties`, `specification`, `summary`, `homepage`) VALUES
(1, 5, 'Item 1', 'TEST', 'TEST', '0', 'TEST', 'TEST', 'TEST'),
(2, 5, 'Item 2', 'TEST', 'TEST', '0', 'TEST', 'TEST', 'TEST'),
(3, 5, 'Item 3', 'TEST', 'TEST', '0', 'TEST', 'TEST', 'TEST');

INSERT INTO `link` (`id`, `item`, `merchant`, `rawlink`) VALUES
(1, 1, 1, 'TEST'),
(2, 1, 2, 'TEST'),
(3, 1, 3, 'TEST'),
(4, 2, 1, 'TEST'),
(5, 2, 2, 'TEST'),
(6, 2, 3, 'TEST'),
(7, 3, 1, 'TEST'),
(8, 3, 2, 'TEST'),
(9, 3, 3, 'TEST');

INSERT INTO `prices` (`id`, `link`, `timestamp`, `price`) VALUES
(1, 1, '2013-02-07', 110.00),
(2, 1, '2013-02-06', 75.00),
(3, 2, '2013-02-07', 105.00),
(4, 2, '2013-02-06', 100.00),
(5, 3, '2013-02-07', 105.00),
(6, 3, '2013-02-06', 100.00),
(7, 4, '2013-02-07', 50.00),
(8, 4, '2013-02-06', 50.00),
(9, 5, '2013-02-07', 50.00),
(10, 5, '2013-02-06', 50.00),
(11, 6, '2013-02-07', 50.00),
(12, 6, '2013-02-06', 50.00),
(13, 7, '2013-02-07', 10.00),
(14, 7, '2013-02-06', 4.00),
(15, 8, '2013-02-07', 8.00),
(16, 8, '2013-02-06', 6.00),
(17, 9, '2013-02-07', 10.00),
(18, 9, '2013-02-06', 10.00);

预期输出

Item 1 - 30
Item 3 - 4
Item 2 - 0

澄清一下:第 1 项 = 30 因为:

(今天商品 1 最低价)-(昨天商品 1 最低价)

=(链接 2 和 3 今天为 105.00)-(链接 1 昨天为 75.00)

= (105.00) - (75.00) = 30

(链接 1、链接 2 和链接 3 都属于项目 1。我需要今天的最低价格(这 3 个链接中的)- 昨天的最低价格(这 3 个链接中的))。

网站示例

我比较了 iPad 的价格。 (项目1 = iPad)。

我链接到 3 家销售 iPad 的商店。 (链接 1 = 亚马逊、链接 2 = eBay、链接 3 = Argos)。

每个链接都有价格历史记录。 (亚马逊今天是 110.00，但昨天是 75.00。eBay 今天是 105.00，但昨天是 100.00。Argos 今天是 105.00，但昨天是 100.00。)

因此，最低价格如下:

今天 = eBay 和 Arogs (105.00)昨天 = 亚马逊 (75.00)

iPad 的价格上涨 30.00。

Answer 1

试试这个

  select item.name AS iname, item.id AS iid ,changeprice from(
  select t1.link, min(pricem) - min(pricemm) as changeprice from (
  select id , link, price pricemm from prices
  where prices.timestamp = CURDATE() - INTERVAL 1 DAY
  group by price)t1
  inner join(
  select id , link, price pricem from prices
  where prices.timestamp = CURDATE() 
  group by price )t2
  on t1.link = t2.link
  )t3
  INNER JOIN link
  ON t3.link = link.id
  inner join item 
  ON link.item = item.id

  group by link.item
  ORDER BY changeprice DESC
  LIMIT 3 ;

编辑:

您的创建表单是错误的，不要在列和表名称周围使用单引号，而是使用反引号 -> ' ` '

或者使用那些:

CREATE TABLE IF NOT EXISTS `item` (
 id int(11) NOT NULL auto_increment,
 subcategory mediumint(9) NOT NULL,
 name varchar(64) NOT NULL,
 pagetitle varchar(128) NOT NULL,
 metadescription varchar(256) NOT NULL,
 properties varchar(256) NOT NULL,
 specification text NOT NULL,
 summary text NOT NULL,
 homepage varchar(256) NOT NULL default 'none',
 PRIMARY KEY  (id)
 );
CREATE TABLE IF NOT EXISTS link (
id mediumint(9) NOT NULL auto_increment,
item mediumint(9) NOT NULL,
merchant mediumint(9) NOT NULL,
rawlink varchar(256) NOT NULL,
PRIMARY KEY  (id)
);

CREATE TABLE IF NOT EXISTS prices (
 id mediumint(9) NOT NULL auto_increment,
 link mediumint(9) NOT NULL,
 timestamp date NOT NULL,
 price float NOT NULL,
 PRIMARY KEY  (id)
 );

编辑:

有 3 个条目，第一个不是 0，而是 -36,04

尝试将最后一组替换为

   group by link.item

由

   group by t3.link

DEMO here请注意，有 3 种不同的价格。链接。

编辑2这是一个很长的查询，但它给出了你想要的。

 select iname , iid , min(changeprice) changeprice from (
 select t1.iname , t1.iid , pricem - pricemm as changeprice from 
 (select item.name AS iname, item.id AS iid ,min(price) pricem from prices
  INNER JOIN link
  ON prices.link = link.id
  inner join item 
  ON link.item = item.id
  where prices.timestamp = CURDATE()
  group by item.name
  ORDER BY price DESC
   )t1,
  ( select item.name AS iname, item.id AS iid ,min(price) pricemm from prices
  INNER JOIN link
  ON prices.link = link.id
  inner join item 
  ON link.item = item.id
  where prices.timestamp = CURDATE() - INTERVAL 1 DAY
  group by item.name
  ORDER BY price DESC
  )t2
  having changeprice >=0
  ORDER BY changeprice DESC
  )h
  group by iname
  order by changeprice desc  LIMIT 3

http://sqlfiddle.com/#!2/2dfe7/12