基于日期的价格历史

问题描述：

要求将所有价格变化列为单个输出。基于日期的价格历史

我的表中有数据如下

让我们把表PRICE_HIST

Itemid Price Price_Change_Date 
111A112 1,000.00 10/03/2014 
111A114 1,111.00 10/03/2014 
111A11 1,111.00 10/03/2014 
111A111 1,000.00 10/03/2014 
111A114 2,222.00 10/09/2014 
111A111 2,222.00 10/09/2014

与需要帮助的查询如下得到输出。

Itemid Last_Modified Current_Price Last_Change Last_Change1 Last_Change2 
111A112 10/03/2014  1,000.00  NA   NA   NA 
111A11 10/03/2014  1,111.00  NA   NA   NA 
111A114 10/09/2014  2,222.00  1,111.00 NA   NA 
111A111 10/09/2014  2,222.00  1,000.00 NA   NA

第一次用户:-(无法格式化代码

，你能告诉我们您的查询？ – zoran404 2014-10-09 22:24:49

那么到目前为止，你尝试了什么？你有一个示例查询，你得到的错误？ – Jason 2014-10-09 22:25:06

嗯尝试与顶端1，然后TOp2消除一..没有工作接下来尝试获得与Rank Function的子查询，但它也没有奏效。 – Machha 2014-10-09 22:29:30

答

你的问题被标记与MySQL

这是一个MySQL的解决方案：

select itemid, 
     max(price_date) as last_modified, 
     max(case when rn = 1 then price end) as current_price, 
     max(case when rn = 2 then price end) as last_change, 
     max(case when rn = 3 then price end) as last_change1, 
     max(case when rn = 4 then price end) as last_change2 
from 
(
select @rn := case 
     when @itemid = itemid then 
      @rn + 1 
     else 
      1 
     end as rn, 
     @itemid := itemid as itemid, 
     price, 
     price_date 
    from price_hist 
cross join (select @rn := 0, @itemid := '') as t 
order by itemid, price_date desc 
) x 
group by itemid 
order by last_modified, itemid

此用途变量来模仿其他数据库中可用的row_number（）功能，实质上是在itemid上进行分区，然后使用条件聚合来确定每个项目的4个最新价格。

如果你确实使用SQL Server，你可以使用ROW_NUMBER（）

这是一个SQL Server解决方案：

select itemid, 
     max(price_date) as last_modified, 
     max(case when rn = 1 then price end) as current_price, 
     max(case when rn = 2 then price end) as last_change, 
     max(case when rn = 3 then price end) as last_change1, 
     max(case when rn = 4 then price end) as last_change2 
from 
(
select row_number() over (partition by itemid order by itemid, price_date desc) as rn, 
     itemid, 
     price, 
     price_date 
    from price_hist 
) x 
group by itemid 
order by last_modified, itemid

基于日期的价格历史

相关推荐