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');