调整GROUP BY分组后GROUPING SETS输出格式异常问题排查

我有一个运行正常的测试用例，但尝试按customer_id、TO_CHAR(p.purchase_date, 'IYYY"W"IW')分组时，输出格式和正常查询不一致。目前临时方案是用WHERE子句限定单个customer_id，但不想这么做，求问题原因和解决办法。

测试环境配置 #

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY  HH24:MI:SS.FF';

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

表结构创建语句 #

CREATE TABLE customers 
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Faith', 'Mazzarone' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM DUAL UNION ALL
SELECT 3, 'Micheal', 'Palmice' FROM DUAL UNION ALL
SELECT 4, 'Jerry', 'Torchiano' FROM DUAL;


CREATE TABLE items 
(PRODUCT_ID, PRODUCT_NAME, PRICE) AS
SELECT 100, 'Black Shoes', 79.99 FROM DUAL UNION ALL
SELECT 101, 'Brown Pants', 111.99 FROM DUAL UNION ALL
SELECT 102, 'White Shirt', 10.99 FROM DUAL;


CREATE TABLE purchases(
    PURCHASE_ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
    CUSTOMER_ID NUMBER, 
    PRODUCT_ID NUMBER, 
    QUANTITY NUMBER, 
   PURCHASE_DATE TIMESTAMP
);

数据插入语句 #

INSERT  INTO purchases
(CUSTOMER_ID, PRODUCT_ID, QUANTITY, PURCHASE_DATE) 
SELECT 1, 101, 3, TIMESTAMP'2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 1, 100, 1, TIMESTAMP '2022-10-12 19:04:18' FROM DUAL UNION ALL
SELECT 2, 101,1, TIMESTAMP '2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 2, 101, 3, TIMESTAMP '2022-10-17 19:34:58' FROM DUAL UNION ALL
SELECT 2, 102, 3,TIMESTAMP '2022-12-06 11:41:25' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM  dual CONNECT BY  LEVEL <= 6 UNION ALL
SELECT 2, 102, 3,TIMESTAMP '2022-12-26 11:41:25' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM  dual CONNECT BY  LEVEL <= 6 UNION ALL
SELECT 3, 101,1, TIMESTAMP '2022-12-21 09:54:48' FROM DUAL UNION ALL
SELECT 3, 102,1, TIMESTAMP '2022-12-27 19:04:18' FROM DUAL UNION ALL
SELECT 3, 102, 4,TIMESTAMP '2022-12-22 21:44:35' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM    dual
CONNECT BY  LEVEL <= 15 UNION ALL 
SELECT 3, 101,1, TIMESTAMP '2022-12-11 09:54:48' FROM DUAL UNION ALL
SELECT 3, 102,1, TIMESTAMP '2022-12-17 19:04:18' FROM DUAL UNION ALL
SELECT 3, 102, 4,TIMESTAMP '2022-12-12 21:44:35' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM    dual
CONNECT BY  LEVEL <= 5;

约束添加语句 #

ALTER TABLE customers 
ADD CONSTRAINT customers_pk PRIMARY KEY (customer_id);

ALTER TABLE items 
ADD CONSTRAINT items_pk PRIMARY KEY (product_id);

ALTER TABLE purchases 
ADD CONSTRAINT purchases_pk PRIMARY KEY (purchase_id);

ALTER TABLE purchases ADD CONSTRAINT customers_fk FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

ALTER TABLE purchases ADD CONSTRAINT items_fk FOREIGN KEY (PRODUCT_ID) REFERENCES items(product_id);

运行正常的查询语句 #

/* 运行正常的查询 */

SELECT    TO_CHAR (p.purchase_date, 'IYYY"W"IW')    AS year_week
,     p.customer_id
,     c.first_name
,     c.last_name
,     SUM (p.quantity * i.price)        AS total_amt
FROM      purchases  p
JOIN      customers  c  ON  p.customer_id = c.customer_id
JOIN      items      i  ON  p.product_id  = i.product_id
GROUP BY  GROUPING SETS (   (TO_CHAR (p.purchase_date, 'IYYY"W"IW'), p.customer_id, c.first_name, c.last_name)
      , (TO_CHAR (p.purchase_date, 'IYYY"W"IW'))
, ()
)
ORDER BY  TO_CHAR (p.purchase_date, 'IYYY"W"IW'), p.customer_id;

正常查询输出结果 #

输出异常的查询语句 #

/* 输出异常的查询 */

SELECT        p.customer_id,
      c.first_name,
      c.last_name,
TO_CHAR (p.purchase_date, 'IYYY"W"IW') AS year_week,
      SUM (p.quantity * i.price)        AS total_amt
FROM      purchases  p
JOIN      customers  c  ON  p.customer_id = c.customer_id
JOIN      items      i  ON  p.product_id  = i.product_id
GROUP BY  GROUPING SETS ( 
    (p.customer_id, c.first_name, c.last_name),
    (TO_CHAR (p.purchase_date, 'IYYY"W"IW')), 
(p.customer_id, c.first_name, c.last_name)
, ()
)
ORDER BY  p.customer_id,
TO_CHAR (p.purchase_date, 'IYYY"W"IW');

问题原因 #

1. GROUPING SETS定义错误：异常查询的分组集合里重复了(p.customer_id, c.first_name, c.last_name)分组，会生成重复的客户总计行。
2. 核心分组缺失：正常查询包含(year_week, customer_id, first_name, last_name)的组合分组，这是按客户+周维度统计的核心；但异常查询没有这个组合，仅包含单独客户、单独周、全局总计分组，因此无法输出客户+周的明细统计行，格式自然和正常查询不一致。
3. 排序逻辑差异：异常查询按customer_id优先排序，正常查询按year_week优先，也会加剧输出顺序的差异，但核心问题是分组集合的缺失。

解决办法 #

修正GROUPING SETS，补上客户+周的核心分组组合，同时去掉重复的分组项，调整后的查询如下：

SELECT        p.customer_id,
      c.first_name,
      c.last_name,
TO_CHAR (p.purchase_date, 'IYYY"W"IW') AS year_week,
      SUM (p.quantity * i.price)        AS total_amt
FROM      purchases  p
JOIN      customers  c  ON  p.customer_id = c.customer_id
JOIN      items      i  ON  p.product_id  = i.product_id
GROUP BY  GROUPING SETS ( 
    -- 客户+周的核心明细分组
    (p.customer_id, c.first_name, c.last_name, TO_CHAR (p.purchase_date, 'IYYY"W"IW')),
    -- 单独客户分组（客户总计）
    (p.customer_id, c.first_name, c.last_name),
    -- 单独周分组（周总计）
    (TO_CHAR (p.purchase_date, 'IYYY"W"IW')), 
    -- 全局总计
    ()
)
ORDER BY  p.customer_id, TO_CHAR (p.purchase_date, 'IYYY"W"IW');

说明 #

• 新增的(p.customer_id, c.first_name, c.last_name, TO_CHAR(p.purchase_date, 'IYYY"W"IW'))分组，会生成每个客户每周的明细统计行，和正常查询的对应部分一致。
• 保留单独的客户分组，会生成每个客户的总消费额行（year_week为null）。
• 保留单独的周分组，会生成每周的总消费额行（客户信息为null）。
• 保留全局总计行，生成所有数据的总消费额。
• 排序逻辑按客户+周，会把同一客户的所有周数据和总计行放在一起，符合预期的展示格式。

内容的提问来源于stack exchange，提问作者Beefstu