mysql查询涨价幅度最大的商品

Question

我有一个表“item_prices”:

resource_id, avg_price, time_stamp, samples

项目每天都会在表中插入一个新的平均价格。旧平均值不会被删除。

如何查询自昨天平均以来“价格涨幅”最高的 10 件商品？我还想检查样本是否 > 10 以确保准确性。

澄清“价格上涨百分比”:

percent_increase = (todays_avg_price - yesterdays_avg_price) / yesterdays_avg_price

示例

resource_id |  avg_price |  time_stamp |  samples

    1           450         1380526003     12
    2           650         1380526002     2
    3           980         1380526001     68

    1           400         1380440003     24
    2           700         1380440002     13
    3           400         1380440001     38

    1           900         1380300003     11
    2           250         1380300002     8
    3           300         1380300001     4

返回

resource id  |  percent_increase

     3              1.45    
     1              0.125

Answer 1

select  
today.resource_id, 
(today.avg_price - yesterday.avg_price) / yesterday.avg_price as percent_increase
from 
item_prices today, 
item_prices yesterday
where today.resource_id = yesterday_resource_id
and   DATE(FROM_UNIXTIME(today.timestamp)) = $today
and   DATE(FROM_UNIXTIME(yesterday.timestamp)) = $yesterday
order by percent_increase desc 
limit 10

这是一个自连接；对陈旧的连接语法表示歉意，这是一个我发现很难改掉的坏习惯。