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

在这里插入图片描述

相关推荐
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页