Clickhouse无GROUP BY时聚合查询WHERE子句不生效问题

问题描述 #

尝试在不使用GROUP BY的情况下，按三个不同条件统计sum和count，用flag_purchase字段区分结果；之后在CTE外部筛选特定flag的记录，但WHERE子句未按预期过滤，结果与未添加外部过滤时一致。添加GROUP BY后，查询则正常生效，仅返回目标flag的记录。

表结构与测试数据 #

CREATE TABLE t15
(
    `winner_price_wo_vat` Nullable(Decimal(38, 2)),
    `prc_lot` Nullable(String),
    `business_code` String,
    `business_unit` Nullable(String),
    `is_finished` Nullable(String),
)
ENGINE = MergeTree
ORDER BY business_code
SETTINGS index_granularity = 8192;

INSERT INTO t15 (winner_price_wo_vat,prc_lot,business_code,business_unit,is_finished) VALUES
     (100.00,'1','1','1','1'),
     (100.00,'2','1','2','1'),
     (200.00,'3','2','1','1'),
     (250.00,'4','2','1','1'),
     (300.00,'5','3','1','1');

测试查询语句 #

with cte as (
select 
    'competitive' as flag_purchase
    , SUM(winner_price_wo_vat) as sum_wo
    , COUNT(prc_lot) as cnt
from t15
where 
    business_code = '1'
    and is_finished = '1'
union all 
select 
    'open' as flag_purchase
    , SUM(winner_price_wo_vat) as sum_wo
    , COUNT(prc_lot) as cnt
from t15
where 
    business_code = '2' and business_unit = '2'
    and is_finished = '1'
union all 
select 
    'closed' as flag_purchase
    , SUM(winner_price_wo_vat) as sum_wo
    , COUNT(prc_lot) as cn -- 存在别名笔误
from t15
where 
    business_code = '3' and business_unit = '1'
    and is_finished = '1'
)
select 
    flag_purchase
    , sum_wo
    , cnt
from cte 
where 
    flag_purchase = 'closed'

实际输出结果 #

flag_purchase|sum_wo|cnt|
-------------+------+---+
competitive  |      |  0|
open         |      |  0|
closed       |300.00|  1|

原因分析 #

1. 无GROUP BY的聚合查询特性：
   在ClickHouse中，包含聚合函数（如SUM/COUNT）但未指定GROUP BY的查询，无论WHERE条件是否匹配数据，都会强制返回一行聚合结果：

   • 无匹配数据时，COUNT返回0，SUM返回NULL；
   • 有匹配数据时，返回对应聚合值。
     因此你的三个UNION ALL子查询，每个都会生成一行记录，CTE最终包含三行数据，外部WHERE过滤本应只保留closed行，但输出异常是因为查询存在笔误（第三个子查询的COUNT别名为cn，外部查询取cnt导致列值异常），且核心问题是无GROUP BY时子查询始终返回行。

2. GROUP BY的作用：
   添加GROUP BY flag_purchase后，聚合查询仅在有匹配数据时才返回对应分组的行；如果WHERE条件无匹配数据，该子查询不会返回任何行。因此三个子查询UNION ALL后，只有closed对应的子查询有结果，外部过滤后自然仅返回一行。

解决方案 #

不一定必须使用GROUP BY，有两种替代方式：

方式一：过滤空聚合结果 #

在每个子查询中添加HAVING条件，仅保留有有效数据的聚合结果：

with cte as (
    select 
        'competitive' as flag_purchase
        , SUM(winner_price_wo_vat) as sum_wo
        , COUNT(prc_lot) as cnt
    from t15
    where 
        business_code = '1' and is_finished = '1'
    having cnt > 0
    union all 
    select 
        'open' as flag_purchase
        , SUM(winner_price_wo_vat) as sum_wo
        , COUNT(prc_lot) as cnt
    from t15
    where 
        business_code = '2' and business_unit = '2' and is_finished = '1'
    having cnt > 0
    union all 
    select 
        'closed' as flag_purchase
        , SUM(winner_price_wo_vat) as sum_wo
        , COUNT(prc_lot) as cnt -- 修正别名笔误
    from t15
    where 
        business_code = '3' and business_unit = '1' and is_finished = '1'
    having cnt > 0
)
select * from cte where flag_purchase = 'closed'

方式二：条件聚合替代UNION ALL #

用单个查询通过条件聚合生成不同flag的统计结果，避免多个子查询：

select
    multiIf(
        business_code = '1' and is_finished = '1', 'competitive',
        business_code = '2' and business_unit = '2' and is_finished = '1', 'open',
        business_code = '3' and business_unit = '1' and is_finished = '1', 'closed',
        NULL
    ) as flag_purchase,
    SUM(winner_price_wo_vat) as sum_wo,
    COUNT(prc_lot) as cnt
from t15
where flag_purchase is not NULL
group by flag_purchase
having flag_purchase = 'closed'

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