ClickHouse中使用max()聚合时如何获取同一行的对应字段值

问题原因 #

你之前使用anyIf(OID, DaysOfHits = max(DaysOfHits))报错的核心原因是：同一层级的聚合函数为并行计算，聚合函数内部不能嵌套其他聚合函数，max(DaysOfHits)的计算结果在anyIf执行时还未生成，因此无法作为判断条件。

最优解决方案：使用原生argMax函数 #

ClickHouse 提供了专门的argMax(value, maximizer)聚合函数，功能就是返回maximizer字段取最大值时对应的value字段值，完全匹配你的需求，无需额外子查询，性能最优。

完整查询语句如下：

SELECT
    UserID,
    uniq(OID) AS UniqObjects,
    sum(TotalHits) AS TotalHits,
    round(avg(DaysOfHits), 2) AS AvgObjectHitDays,
    max(DaysOfHits) AS MaxHitPeriod,
    argMax(OID, DaysOfHits) AS MaxHitPeriodOID, -- 新增字段：最大命中天数对应的OID
    round((avg(DaysOfHits) / max(DaysOfHits)) * 100, 2) AS PerOfMaxHit
FROM 
    -- 此处替换为你实际的多表关联查询逻辑
    YOUR_MULTI_TABLE_JOIN_SUBQUERY
GROUP BY UserID

补充说明： #

• 如果同一个UserID下存在多条记录的DaysOfHits都等于最大值，argMax会返回第一条匹配到的OID
• 如果你需要获取所有匹配最大值的OID，可以替换为argMaxArray(OID, DaysOfHits)，返回值为OID数组

通用兼容方案：CTE分步骤计算 #

如果需要更复杂的匹配逻辑，可以拆分为多步CTE计算，兼容性更强：

WITH source_data AS (
    -- 第一步：先提取多表关联后的源数据
    SELECT * FROM YOUR_MULTI_TABLE_JOIN_SUBQUERY
),
user_base_agg AS (
    -- 第二步：计算基础聚合指标
    SELECT
        UserID,
        uniq(OID) AS UniqObjects,
        sum(TotalHits) AS TotalHits,
        round(avg(DaysOfHits), 2) AS AvgObjectHitDays,
        max(DaysOfHits) AS MaxHitPeriod,
        round((avg(DaysOfHits) / max(DaysOfHits)) * 100, 2) AS PerOfMaxHit
    FROM source_data
    GROUP BY UserID
),
user_max_oid AS (
    -- 第三步：匹配最大DaysOfHits对应的OID
    SELECT
        UserID,
        any(OID) AS MaxHitPeriodOID
    FROM source_data
    GROUP BY UserID, DaysOfHits
    QUALIFY DaysOfHits = max(DaysOfHits) OVER (PARTITION BY UserID)
)
-- 最终关联得到完整结果
SELECT a.*, o.MaxHitPeriodOID
FROM user_base_agg a
LEFT JOIN user_max_oid o ON a.UserID = o.UserID

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