暐翰這篇 公司需要分析物料單價與前一次變動價格的漲跌差異
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