2018年3月6日 星期二

利用彙總運算特性及HAVING子句取代IF-ELSE判斷式

本範例將利用HAVING子句及彙總運算特性,以取代原本使用IF-ELSE判斷式之成立/不成立時均需寫入之需求,以銷售記錄寫入為例,當有銷售記錄時則全部寫入,如無銷售記錄時,則仍需寫入一筆註記資料。測試資料及SQL如下:
--00. 建立資測試資料#Sales
DROP TABLE #Sales

SELECT *
      INTO #Sales
FROM (VALUES ('DDR', 50,  '光華店')
          , ('DDR2', 10,  '公館店')
          , ('DDR4', 80,  '站前店')           
     ) Store (Prod, Qty, Loc)
WHERE 1=1

--01. 建立資測試資料#Log
DROP TABLE #Log 

SELECT *, GETDATE() Date_Log  INTO #Log
FROM #Sales
WHERE 1=0

以下列出常用方法以IF-ELSE依銷售筆數進行判斷及執行,以及使用HAVING子句及彙總運算特性之範例,SQL及說明如下:

方法-1: IF-ELSE
方法-2: HAVING
SQL
--若有銷售資料(Rows>0)則寫入#Log資料表
--否則,仍需寫一筆記錄
IF (SELECT COUNT(*) FROM #Sales)>0
   --有銷售記錄: 銷售記錄(3)全部寫入
   INSERT INTO #Log
         (Prod, Qty, Loc, Date_Log)
   SELECT Prod, Qty, Loc, GETDATE()
   FROM #Sales
ELSE
   --無銷售記錄: 仍寫入一筆記錄
   INSERT INTO #Log
          (Prod, Qty, Loc, Date_Log)
   SELECT 'None', 0, '', GETDATE()

INSERT INTO #Log
       (Prod, Qty, Loc, Date_Log)
SELECT Prod, Qty, Loc, GETDATE()   --有銷售記錄
FROM #Sales 
UNION ALL
SELECT 'None', 0, '', GETDATE()--無銷售記錄
FROM #Sales 
HAVING(COUNT(*) =0)--#Sales為0筆時,寫入此筆資料

說明
- COUNT(*)計算筆數
- IF-ELSE依筆數判斷

- COUNT(*)計算筆數
- 利用無GROUP BY子句之彚總運算必會傳回一筆之特性,再使用HAVING進行判斷篩選

SELECT命令中(如下圖),後續針對彙總運算特性及HAVING子句資料篩選分別說明。
SELECT [ALL | DISTINCT] <欄位名稱>
FROM <資料來源名稱>
[WHERE <搜尋條件>]
[GROPY BY <分群欄位名稱>
[HAVING <彙總運算條件式>]]
[ORDER BY <排序欄位名稱> [ASC | DESC]]
首先,通常進行彙總運算時會搭配GROUP BY子句使用,以彙總函數計算總合,當省略GROUP BY子句時,即可針對整個資料集進行彙總運算,不過不論是否有任何資料符合篩選條件,必將傳回一筆運算結果,以下將以範例進行比較說明。在此請特別注意,當針對整個資料集進行彙總運算時,當無任何資料資料符合時,仍將傳回一筆結果為NULL的資料。
[具GROUP BY子句]-針對產品彙總運算

成立
不成立
SQL

SELECT Prod
      , SUM(Qty)
FROM (VALUES ('DDR2', 50,  '光華店')
         , ('DDR2', 10,  '公館店')
         , ('DDR4', 80,  '站前店')           
     ) Store (Prod, Qty, Loc)
WHERE 1=1 --成立
GROUP BY Prod
SELECT Prod
      , SUM(Qty)
FROM (VALUES ('DDR2', 50,  '光華店')
         , ('DDR2', 10,  '公館店')
         , ('DDR4', 80,  '站前店')           
     ) Store (Prod, Qty, Loc)
WHERE 1=0 --不成立:無符合資料
GROUP BY Prod
傳回
2
0

[不具GROUP BY子句]-整個資料集彙總運算

成立
不成立
SQL
SELECT SUM(Qty)
FROM (VALUES ('DDR2', 50,  '光華店')
         , ('DDR2', 10,  '公館店')
         , ('DDR4', 80,  '站前店')           
     ) Store (Prod, Qty, Loc)
WHERE 1=1 --成立

SELECT SUM(Qty)
FROM (VALUES ('DDR2', 50,  '光華店')
         , ('DDR2', 10,  '公館店')
         , ('DDR4', 80,  '站前店')           
     ) Store (Prod, Qty, Loc)
WHERE 1=0 --不成立:無符合資料
傳回
1
1筆,傳回NULL

再者,SQL語法中WHEREHAVING子句均可用於資料篩選,將依搜尋條件傳回符合搜尋條件之資料集,兩者區分在於,WHERE子句是在資料彙總或分組前進行核對資料列(Row)資格;而HAVING則是在資料已完成彙總或分組後,再行篩選,亦可應用於WHERE子句篩選後所採用之其他篩選,可應用於SELECT使用彙總函式之選取清單中。將前述概念整理如下表所示。

篩選時點
資料
WHERE
彙總或分組前
一般資料值
HAVING
已完成彙總或分組後
彙總結果

HAVING子句通常會與GROUP BY子句搭配使用,以篩選彙總運算之結果。不過,即使沒有GROUP BY,仍可使用HAVING子句,但不論篩選條件成立與否必定回傳一筆結果,本範例重點在利用此特性達成判斷運算,當具有銷售資料時,則HAVING篩選判斷為不符合,反之,則成立。  

沒有留言:

張貼留言