暐翰這篇 公司需要分析物料單價與前一次變動價格的漲跌差異
https://ithelp.ithome.com.tw/articles/10230119

這裡使用了 JOIN Lateral,但是只找前一次的話,人都是有了就想要更好.
我就使用這個例子,繼續延伸,使用了聚合,再放進 hstore, 這樣後續要取出值會比單純string聚合的,
更為方便,畢竟hstore有配套的函數可以使用.因為使用了string_agg(),所以裡面的值都已經是
text型態,取出後可以轉成integer計算,在 join lateral 裡面也可以加上 limit n,
這樣很有彈性的.

select prdt_no, order_date, quantity as qty, unit_price as price
     , hstore(string_agg(bf_date || '=>' || price_diff, ',' order by bf_date)) as date_price_diff_hs
  from it200222 t
 inner join lateral 
       (select x.order_date as bf_date
             , t.unit_price - x.unit_price as price_diff
          from it200222 x
         where x.prdt_no = t.prdt_no
           and x.order_date < t.order_date
           and x.unit_price <> t.unit_price
       ) y
    on true
 where order_date >= '2020-02-01'::date
   and order_date <= '2020-02-10'::date
 group by prdt_no, order_date, quantity, unit_price
 order by prdt_no, order_date, quantity, unit_price
 ;
 
+---------+------------+-----+-------+--------------------------------------------------------------------------------------------------+
| prdt_no | order_date | qty | price |                                        date_price_diff_hs                                        |
+---------+------------+-----+-------+--------------------------------------------------------------------------------------------------+
| A001    | 2020-02-01 | 100 |    20 | "2020-01-01"=>"10", "2020-01-10"=>"10"                                                           |
| A001    | 2020-02-05 | 100 |    20 | "2020-01-01"=>"10", "2020-01-10"=>"10"                                                           |
| A001    | 2020-02-07 | 100 |    20 | "2020-01-01"=>"10", "2020-01-10"=>"10"                                                           |
| A001    | 2020-02-10 | 100 |    15 | "2020-01-01"=>"5", "2020-01-10"=>"5", "2020-02-01"=>"-5", "2020-02-05"=>"-5", "2020-02-07"=>"-5" |
| A002    | 2020-02-01 | 100 |    20 | "2020-01-01"=>"10", "2020-01-10"=>"10"                                                           |
| A002    | 2020-02-05 | 100 |    20 | "2020-01-01"=>"10", "2020-01-10"=>"10"                                                           |
| A002    | 2020-02-07 | 100 |    20 | "2020-01-01"=>"10", "2020-01-10"=>"10"                                                           |
| A002    | 2020-02-10 | 100 |    15 | "2020-01-01"=>"5", "2020-01-10"=>"5", "2020-02-01"=>"-5", "2020-02-05"=>"-5", "2020-02-07"=>"-5" |
+---------+------------+-----+-------+--------------------------------------------------------------------------------------------------+
(8 rows)

Time: 1.249 ms