使用WITH CUBE子句生成重复行问题求助

测试环境与数据 #

创建表及插入数据的SQL：

CREATE TABLE venta_mes_hist (
    tienda_id INT,
    empresa_id INT,
    dia_id DATE,
    total_linea_brut DECIMAL(18, 2)
);

CREATE TABLE lk_tienda (
    tienda_id INT,
    empresa_id INT,
    tienda_desc VARCHAR(50)
);


-- Insert data into lk_tienda
INSERT INTO lk_tienda (tienda_id, empresa_id, tienda_desc)
VALUES 
(1, 1, 'SHIBORI'),
(2, 1, 'NARA'),
(3, 1, 'OSAKA');

-- Insert data into venta_mes_hist
INSERT INTO venta_mes_hist (tienda_id, empresa_id, dia_id, total_linea_brut)
VALUES
(1, 1, '2023-03-01', 100.00),
(1, 1, '2023-03-28', 2544.84),
(1, 1, '2023-04-15', 200.00),
(2, 1, '2023-03-01', 150.00),
(2, 1, '2023-03-28', 3000.00),
(2, 1, '2023-04-15', 250.00),
(3, 1, '2023-03-01', 200.00),
(3, 1, '2023-03-28', 3200.00),
(3, 1, '2023-04-15', 300.00),
(1, 1, '2023-05-10', 500.00);

执行的查询语句 #

DROP TABLE IF EXISTS #avenut;
SET DATEFORMAT mdy;

select cast(lk_tienda.tienda_desc as varchar(35)) Botiga,venta_mes_hist.dia_id Dia,cast(month(venta_mes_hist.dia_id) as int) Mes,
 sum(venta_mes_hist.total_linea_brut) Venut_valor_brut
 into #avenut
 from  lk_tienda, venta_mes_hist
 where 
 lk_tienda.tienda_id = venta_mes_hist.tienda_id and lk_tienda.empresa_id = venta_mes_hist.empresa_id 
 and venta_mes_hist.empresa_id in ('1') 
 and venta_mes_hist.dia_id >= '03/01/2023'
 and venta_mes_hist.dia_id <= '06/06/2024'
 group by cast(lk_tienda.tienda_desc as varchar(35)), venta_mes_hist.dia_id, cast(month(venta_mes_hist.dia_id) as int)
 with cube 
 order by cast(lk_tienda.tienda_desc as varchar(35)), venta_mes_hist.dia_id, cast(month(venta_mes_hist.dia_id) as int)

异常结果 #

执行查询：

SELECT * FROM #avenut
WHERE Botiga = 'SHIBORI' AND Dia = '2023-03-28';

返回两行重复数据：

Botiga  Dia         Mes Venut_valor_brut
SHIBORI 2023-03-28  3   2544.84
SHIBORI 2023-03-28  3   2544.84

疑问：WITH CUBE应该是插入含NULL的汇总行，预期只返回一行；移除月份列后返回一行，若保留月份列并新增年份列则返回4行重复，这是为什么？

原因解析 #

核心问题出在GROUP BY子句中包含了功能上依赖的列：dia_id（日期）和Mes（从dia_id提取的月份），这两个列不是独立的——给定一个dia_id，Mes的值是唯一确定的。

当使用WITH CUBE时，它会为GROUP BY中的每一列生成所有可能的组合（包括NULL的汇总项），但因为Mes依赖于dia_id，会出现逻辑上重复的分组：

• 第一行：是不做任何汇总的原始分组——按Botiga、Dia、Mes分组，对应具体的日期和月份，计算总和。
• 第二行：是仅对Mes列做汇总，但因为Mes完全依赖Dia，这个汇总分组和原始分组的结果完全一致——当CUBE尝试对Mes进行NULL汇总时，由于Dia已经确定，Mes的值无法被NULL替换（Dia固定时Mes是唯一的），所以这个分组的结果和原始分组完全相同，就出现了重复行。

当你移除Mes列时，GROUP BY只有Botiga和Dia，CUBE生成的汇总项要么是NULL的Dia（月度/全量汇总），要么是具体的Dia，不会出现逻辑重复，所以只有一行。

如果新增年份列（同样依赖于Dia），GROUP BY就有Botiga、Dia、Mes、Year四个列，其中Mes和Year都依赖Dia。此时CUBE会生成4种逻辑重复的分组：

• 原始分组（无汇总）
• 仅汇总Year的分组（但Dia固定时Year唯一，结果和原始一致）
• 仅汇总Mes的分组（同理，结果一致）
• 同时汇总Mes和Year的分组（同理，结果一致）
  所以会出现4行重复数据。

解决办法 #

不要在GROUP BY中包含依赖于其他列的派生列，比如直接从Dia提取的月份、年份，应该把这些派生列放到SELECT中，而不是GROUP BY里。修改后的GROUP BY应该只保留独立的维度列：

GROUP BY cast(lk_tienda.tienda_desc as varchar(35)), venta_mes_hist.dia_id
WITH CUBE

如果需要按月份汇总，应该单独做一个按Botiga和Mes分组的查询，或者使用ROLLUP/CUBE的正确维度组合，避免依赖列重复分组。

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