iT邦幫忙

2

【SQLServer】使用Outer Apply + Order top 1 分析最近一次價格成本漲跌情況

最近公司需要分析物料單價與前一次變動價格的漲跌差異,之前使用rownumber方式解決遇到效能的問題。

在S.O發文求助後,從Squirrel大的回答學到可以使用OUTER APPLY + Order top 1方式高效解決此問題。連結 : Stack Overflow 覺得有幫助記得幫Squirrel大按個Upvote。


舉例 :

目前有採購明細資料如下 :

當使用者查詢 order_date between 2020-02-02 and 2020-02-10會希望得到以下結果
得出與最近一次採買,單價、成本的漲跌情況

邏輯像圖片顯示這樣


Squirrel大主要利用apply的特性,可以將外查詢的值帶入子查詢特性,以日期倒序 + top 1 + 單價不同條件 抓取同物料最後一筆不同單價的資料,至於使用OUTER原因是以外查詢為準

查詢Script如下 :

SELECT *,
       unit_price_diff = T.[unit_price] - L.[last_unit_price_before]
FROM   T
OUTER APPLY(
    SELECT TOP 1 
            last_unit_price_before = x.[unit_price],
            last_unit_price_change_date = x.[order_date]
    FROM   T x
    WHERE  x.[prdt_no] = T.[prdt_no]
        AND    x.[order_date] < T.[order_date] 
        AND    x.[unit_price] <> T.[unit_price]
    ORDER BY x.[order_date] DESC
) L
WHERE  T.[order_date] >= '2020-02-01' AND  T.[order_date] <= '2020-02-10'

線上測試demo連結 : SQL Server 2012 | db<>fiddle


幫助我

假如平常我的回答或是文章有幫助到你,可以幫我的Linkedin加個聯絡人並點擊認同技能
Wei | LinkedIn
或是Github點個追蹤或是Star,謝謝你 ^_^
Wei | Github


1 則留言

1
一級屠豬士
iT邦高手 1 級 ‧ 2020-02-22 00:27:47

SQL Server 的 JOIN APPLY, 與 ANSI SQL 的 JOIN LATERAL 是不同名字.
簡單改一下.

create table it200222(
  prdt_no text not null
, order_date date not null
, quantity int not null
, unit_price int not null
);

insert into it200222 values
('A001', '2020-01-01', 100, 10),
('A001', '2020-01-10', 200, 10),
('A001', '2020-02-01', 100, 20),
('A001', '2020-02-05', 100, 20),
('A001', '2020-02-07', 100, 20),
('A001', '2020-02-10', 100, 15),
('A002', '2020-01-01', 100, 10),
('A002', '2020-01-10', 200, 10),
('A002', '2020-02-01', 100, 20),
('A002', '2020-02-05', 100, 20),
('A002', '2020-02-07', 100, 20),
('A002', '2020-02-10', 100, 15);


select *
     , t.unit_price - y.last_unit_price_before as unit_price_diff
  from it200222 t
 inner join lateral 
       (select unit_price as last_unit_price_before
             , order_date as last_unit_price_change_date
          from it200222 x
         where x.prdt_no = t.prdt_no
           and x.order_date < t.order_date
           and x.unit_price <> t.unit_price
         order by order_date desc
         limit 1
        ) y
     on true
  where order_date >= '2020-02-01'::date
    and order_date <= '2020-02-10'::date
  ;

https://ithelp.ithome.com.tw/upload/images/20200222/20050647LxUYfziyeJ.png

JOIN LATERAL 我在it幫有示範過好幾次,在聊聊資料庫系列,介紹Array時,也有用到,
現在你再去看那部分,就比較能體會了.
另外 之前有幫友在
https://ithelp.ithome.com.tw/articles/10195219
說到:這兩個語法效果等同於 INNER JOIN 與 LEFT OUTER JOIN。
這個就是有點誤解了.

看更多先前的回應...收起先前的回應...
暐翰 iT邦大師 1 級‧ 2020-02-22 09:44:31 檢舉

謝謝大大分享 /images/emoticon/emoticon41.gif
的確,文章裡面提到的效果不應該跟JOIN一樣來看待
看過網上幾篇也常出現這概念

應該說是 JOIN 的一種,但不是那個 "效果等同於 INNER JOIN 與 LEFT OUTER JOIN".
最近蠻多年輕人,看似洋洋灑灑寫了一堆東西,但是不夠深入與嚴謹,
很快就下了結論.這些對發文者與閱讀的人都不好.
我不是針對他,只是剛好看到,藉此機會提出.

暐翰 iT邦大師 1 級‧ 2020-02-22 15:13:33 檢舉

是,大大
個人是這樣理解,不知道是否準確
outer join = 外查詢為準加入子查詢
left join = 左查詢為準加入右查詢
所以兩者最大差別在前者可以帶入外查詢的值

你有點被那篇搞亂了,別理會他說的,因為他是把apply 跟正常的
搞混了.
你應該看ANSI 定義的 join lateral, 這個Oracle / Postgresql 都有.

暐翰 iT邦大師 1 級‧ 2020-02-22 16:37:36 檢舉

再次感謝大大

我要留言

立即登入留言