oracle--彙總函式和case when結合使用

標籤：varchar   date   har   sel   3.3   最佳化   arch   分享   acl   

需求：

1 根據客戶資訊表中資訊比較出相應餘額。

2 表中有客戶號，餘額，各個時間點的分區資訊，每個分區中客戶號唯一。

3 客戶資訊中分區有10、11、12、13、14、15。

4 得到每個比上日餘額資訊，如下面表樣。

--建立測試表 客戶資訊表CREATE TABLE CUST_INFO_TEST(CUST_NO VARCHAR2(5),AUM_EDU NUMBER,DT VARCHAR(10))--在表中插入測試資料SELECT * FROM CUST_INFO_TEST FOR UPDATE --測試資料如下CUST_NO       AUM_EDU DT---------- ---------- -----------1                  11 201806102                  22 201806103                  33 201806101               11.11 201806112               22.22 201806113               33.33 201806111               11.21 201806122               22.32 201806123               33.33 201806121               11.44 201806132               22.44 201806133               33.44 20180613--使用表的自關聯實現SELECT A1.CUST_NO      ,A3.AUM_EDU - A2.AUM_EDU AS BSR_11      ,A4.AUM_EDU - A3.AUM_EDU AS BSR_12      ,A1.AUM_EDU - A4.AUM_EDU AS BSR_13  FROM CUST_INFO_TEST A1LEFT JOIN CUST_INFO_TEST A2  ON A1.CUST_NO = A2.CUST_NO  AND A2.DT = ‘20180610‘  LEFT JOIN CUST_INFO_TEST A3  ON A1.CUST_NO = A3.CUST_NO  AND A3.DT = ‘20180611‘ LEFT JOIN CUST_INFO_TEST A4  ON A1.CUST_NO = A4.CUST_NO  AND A4.DT = ‘20180612‘ WHERE A1.DT= ‘20180613‘;--得到如下結果CUST_NO      BSR_11     BSR_12     BSR_13-------- ---------- ---------- ----------1              0.11        0.1       0.232              0.22        0.1       0.123              0.33          0       0.11--最佳化SELECT        CUST_NO      ,SUM(CASE WHEN DT=‘20180611‘ THEN AUM_EDU ELSE 0 END - CASE WHEN DT=‘20180610‘ THEN AUM_EDU ELSE 0 END) AS BSR_11      ,SUM(CASE WHEN DT=‘20180612‘ THEN AUM_EDU ELSE 0 END - CASE WHEN DT=‘20180611‘ THEN AUM_EDU ELSE 0 END) AS BSR_12      ,SUM(CASE WHEN DT=‘20180613‘ THEN AUM_EDU ELSE 0 END - CASE WHEN DT=‘20180612‘ THEN AUM_EDU ELSE 0 END) AS BSR_13  FROM CUST_INFO_TEST WHERE DT IN (‘20180610‘,‘20180611‘,‘20180612‘,‘20180613‘) GROUP BY CUST_NO  --得到結果和上面一樣CUST_NO      BSR_11     BSR_12     BSR_13-------- ---------- ---------- ----------1              0.11        0.1       0.232              0.22        0.1       0.123              0.33          0       0.11

 

oracle--彙總函式和case when結合使用