sql - 选择期间内加上期间之前的最后一个

Question

感谢所有花时间发表评论和回答的人。

-

我有一个这样的价格历史表(伪代码):

table price_history (
    product_id,
    price,
    changed_date
)

其中存储了一些产品的历史价格:

1,  1.0, '2017-12-18'
1,  1.2, '2017-12-20'
1,  0.9, '2018-04-20'
1,  1.1, '2018-07-20'
1,  1.3, '2018-07-22'
2, 10.0, '2017-12-15'
2, 11.0, '2017-12-16'
2,  9.9, '2018-01-02'
2, 10.3, '2018-04-04

现在我想要一些产品在一定时期内的价格。例如。从 2018-01-01 到现在。

简单的方法:

    SELECT * FROM price_history
      WHERE product_id in (1,2) AND changed_date >= 2018-01-01

不行，因为从 2018-01-01 到第一次价格变动的每个产品的单独价格不包括在内:

1,  0.9, '2018-04-20'
1,  1.1, '2018-07-20'
1,  1.3, '2018-07-22'
2,  9.9, '2018-01-02'
2, 10.3, '2018-04-04

但了解期初的价格至关重要。

所以，除了期间内的价格变化外，还必须包括之前的最后一次变化。结果应该是这样的:

1,  1.2, '2017-12-20'
1,  0.9, '2018-04-20'
1,  1.1, '2018-07-20'
1,  1.3, '2018-07-22'
2, 11.0, '2017-12-16'
2,  9.9, '2018-01-02'
2, 10.3, '2018-04-04

问:如何指定这样的select语句？

编辑:

Ajay Gupta 的测试场景和解决方案

CREATE TABLE price_history (
        product_id integer,
        price float,
        changed_date timestamp
    );

INSERT INTO price_history (product_id,price,changed_date) VALUES
    (1, 1.0, '2017-12-18'),
    (1, 1.2, '2017-12-20'),
    (1, 0.9, '2018-04-20'),
    (1, 1.1, '2018-07-20'),
    (1, 1.3, '2018-07-22'),
    (2, 10.0, '2017-12-15'),
    (2, 11.0, '2017-12-16'),
    (2, 9.9, '2018-01-02'),
    (2, 10.3, '2018-04-04');

获奖选择:

with cte1 as
  (Select *, lag(changed_date,1,'01-01-1900')
  over(partition by product_id order by changed_date)
    as FromDate from price_history),
  cte2 as (Select product_id, max(FromDate)
           as changed_date from cte1
           where '2018-01-01'
             between FromDate and changed_date group by product_id)
  Select p.* from price_history p
    join cte2 c on p.product_id = c.product_id
  where p.changed_date >= c.changed_date
  order by product_id,changed_date;

结果:

 product_id | price |    changed_date     
------------+-------+---------------------
          1 |   1.2 | 2017-12-20 00:00:00
          1 |   0.9 | 2018-04-20 00:00:00
          1 |   1.1 | 2018-07-20 00:00:00
          1 |   1.3 | 2018-07-22 00:00:00
          2 |    11 | 2017-12-16 00:00:00
          2 |   9.9 | 2018-01-02 00:00:00
          2 |  10.3 | 2018-04-04 00:00:00

我必须承认，这远远超出了我有限的 (PG-)SQL 技能。

Answer 1

使用 Lag 和 cte

with cte1 as (
  Select *, 
         lag(changed_date,1,'01-01-1900') over(partition by product_id order by changed_date) as FromDate 
  from price_history
), cte2 as  (
   Select product_id, max(FromDate) as changed_date 
   from cte1  
   where '2018-01-01' between FromDate and changed_date 
   group by product_id
)
Select p.* 
from price_history p 
  join cte2 c on p.product_id = c.product_id
where p.changed_date >= c.changed_date;