Oracle 分組彙總統計函式的使用

問題描述

具有原始資料資訊如下：

想得到如下彙總資訊（統計每個產品所有資料日期 TRANS_DATE、所有客戶來源 CUSTOMER_SOURCE 下的金額 AMT、份額 SHARES 總值）：

問題解決

grouping（columnA）函式的意思：當前行如果是由rollup彙總產生的，那麼columnA這個欄位值為1否則為0

SELECT
	t.PRD_CODE,
	t.TRANS_DATE,
	-- 如果當前行是由 TRANS_DATE 和 CUSTOMER_SOURCE 彙總產生的，則將CUSTOMER_SOURCE列的值設定為"單產品彙總"
	decode(grouping(t.TRANS_DATE)+grouping(t.CUSTOMER_SOURCE),2, '單產品彙總',t.CUSTOMER_SOURCE) CUSTOMER_SOURCE,
	SUM(AMT),
	SUM(SHARES) 
FROM
	T_PRODUCT t
	-- 統計每個產品的所有TRANS_DATE、CUSTOMER_SOURCE 進行彙總
GROUP BY rollup(t.TRANS_DATE, t.CUSTOMER_SOURCE),
	-- 按PRD_CODE分組，每個產品出一條彙總資訊
	t.PRD_CODE
-- 去除 CUSTOMER_SOURCE 中的 null 值
having 
		-- 保留原始資料
		t.CUSTOMER_SOURCE is not null
		-- 保留 "單產品彙總" 彙總記錄
		or (t.trans_date is null and t.CUSTOMER_SOURCE is null)
ORDER BY
	PRD_CODE

執行效果如下：

可能有人對上述 SQL 還不完全理解，那我們再來具體分析一下：

decode

decode(條件,值1,返回值1,值2,返回值2,…值n,返回值n,預設值)
該函式的含義如下：

IF 條件=值1 THEN
    RETURN(翻譯值1)
ELSIF 條件=值2 THEN
    RETURN(翻譯值2)
    ......
ELSIF 條件=值n THEN
    RETURN(翻譯值n)
ELSE
    RETURN(預設值)
END IF

decode(grouping(t.TRANS_DATE)+grouping(t.CUSTOMER_SOURCE),2, '單產品彙總',t.CUSTOMER_SOURCE) CUSTOMER_SOURCE, 的含義：
如果grouping(t.TRANS_DATE)+grouping(t.CUSTOMER_SOURCE) = 2，使用 ‘單產品彙總’ 文字填充 CUSTOMER_SOURCE 欄位值。

grouping

grouping(t.TRANS_DATE)的意思：當前行如果是由TRANS_DATE彙總產生的，那麼grouping(t.TRANS_DATE)為1，否則為0

having 條件

首先看一下不新增 having 條件的 SQL：

SELECT
	t.PRD_CODE,
	t.TRANS_DATE,
	decode(grouping(t.TRANS_DATE)+grouping(t.CUSTOMER_SOURCE),2, '單產品彙總',t.CUSTOMER_SOURCE) CUSTOMER_SOURCE,
	SUM(AMT),
	SUM(SHARES) 
FROM
	T_PRODUCT t
GROUP BY rollup(t.TRANS_DATE, t.CUSTOMER_SOURCE),
	t.PRD_CODE
-- having 1=2
-- 		or (t.trans_date is null and t.CUSTOMER_SOURCE is null)
-- 		or (t.trans_date is not null and t.CUSTOMER_SOURCE is not null)
ORDER BY
	PRD_CODE

結果集如下：

我們發現其中有一些 CUSTOMER_SOURCE 為 null 的記錄（統計每個產品PRD_CODE每個資料日期TRANS_DATE的金額、餘額合計），但我們並不想要這些記錄，我只想要統計每個產品PRD_CODE所有資料日期TRANS_DATE、所有客戶來源CUSTOMER_SOURCE的合計，所以需要過濾掉 CUSTOMER_SOURCE 為 null 的記錄，保留
CUSTOMER_SOURCE 不為 null 的資料。即在 group by 後面新增 having t.CUSTOMER_SOURCE is not null

SELECT
	t.PRD_CODE,
	t.TRANS_DATE,
	decode(grouping(t.TRANS_DATE)+grouping(t.CUSTOMER_SOURCE),2, '單產品彙總',t.CUSTOMER_SOURCE) CUSTOMER_SOURCE,
	SUM(AMT),
	SUM(SHARES) 
FROM
	T_PRODUCT t
GROUP BY rollup(t.TRANS_DATE, t.CUSTOMER_SOURCE),
	t.PRD_CODE
having 
	t.CUSTOMER_SOURCE is not null	
ORDER BY
	PRD_CODE

這時又發現沒有了合計資料了，因為合計資料的 CUSTOMER_SOURCE 原本也為 null（雖然後來用了"單產品彙總"，填充，但查出來的原始 CUSTOMER_SOURCE 為 null），所以被過濾掉了。
於是這時就要修改過濾條件，在 hving 後面新增 or (t.trans_date is null and t.CUSTOMER_SOURCE is null)

SELECT
	t.PRD_CODE,
	t.TRANS_DATE,
	decode(grouping(t.TRANS_DATE)+grouping(t.CUSTOMER_SOURCE),2, '單產品彙總',t.CUSTOMER_SOURCE) CUSTOMER_SOURCE,
	SUM(AMT),
	SUM(SHARES) 
FROM
	T_PRODUCT t
GROUP BY rollup(t.TRANS_DATE, t.CUSTOMER_SOURCE),
	t.PRD_CODE
having 
	t.CUSTOMER_SOURCE is not null
	or (t.trans_date is null and t.CUSTOMER_SOURCE is null)
		
ORDER BY
	PRD_CODE

此時終於得到我們想要的資料了：每個產品出一條合計記錄（統計所有資料日期、客戶來源的金額、份額合計）

SQL 建表及插入資料語句

CREATE TABLE "test"."T_PRODUCT" (
  "TRANS_DATE" NUMBER(8) VISIBLE ,
  "PRD_CODE" VARCHAR2(255 BYTE) VISIBLE ,
  "CUSTOMER_SOURCE" VARCHAR2(255 BYTE) VISIBLE ,
  "AMT" NUMBER(18,2) VISIBLE ,
  "SHARES" NUMBER(18,2) VISIBLE 
)
TABLESPACE "test"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN "test"."T_PRODUCT"."TRANS_DATE" IS '資料日期';
COMMENT ON COLUMN "test"."T_PRODUCT"."PRD_CODE" IS '產品程式碼';
COMMENT ON COLUMN "test"."T_PRODUCT"."CUSTOMER_SOURCE" IS '客戶來源';
COMMENT ON COLUMN "test"."T_PRODUCT"."AMT" IS '金額';
COMMENT ON COLUMN "test"."T_PRODUCT"."SHARES" IS '份額';

-- ----------------------------
-- Records of "T_PRODUCT"
-- ----------------------------
INSERT INTO "test"."T_PRODUCT" VALUES ('20200101', 'P1_10001', '北京市', '20.22', '22.54');
INSERT INTO "test"."T_PRODUCT" VALUES ('20200202', 'P2_10001', '上海市', '33.33', '34.67');
INSERT INTO "test"."T_PRODUCT" VALUES ('20200202', 'P3_10001', '雲南市', '109.23', '78.35');
INSERT INTO "test"."T_PRODUCT" VALUES ('20200303', 'P1_10001', '濟南市', '44.89', '66.61');
INSERT INTO "test"."T_PRODUCT" VALUES ('20200505', 'P3_10001', '青島市', '50.43', '44.36');
INSERT INTO "test"."T_PRODUCT" VALUES ('20200505', 'P2_10001', '北京市', '62.98', '50.43');
INSERT INTO "test"."T_PRODUCT" VALUES ('20201010', 'P1_10001', '濟南市', '38.07', '39.02');
INSERT INTO "test"."T_PRODUCT" VALUES ('20201010', 'P3_10001', '上海市', '40.32', '48.65');
INSERT INTO "test"."T_PRODUCT" VALUES ('20201010', 'P1_10001', '北京市', '98.23', '55.71');