ClickHouse无法引用外部查询列：按Metric筛选最大Accept_date行

需求：按metric分组，筛选出每组中accept_date等于该组最大值的所有数据行。PostgreSQL中可通过关联子查询实现，但ClickHouse不支持子查询引用外部查询的列，执行相同SQL会触发UNKNOWN_IDENTIFIER错误。

解决方案 #

方法1：窗口函数（最推荐） #

利用窗口函数计算每个分组的最大accept_date，再筛选匹配行：

SELECT timestamp, value, metric, accept_date
FROM (
    SELECT 
        timestamp, 
        value, 
        metric, 
        accept_date,
        MAX(accept_date) OVER (PARTITION BY metric) AS max_accept_date
    FROM test_max_date
) t
WHERE accept_date = max_accept_date

方法2：预计算分组最大值后关联 #

先统计每个metric的最大accept_date，再与原表关联筛选：

SELECT tmd.timestamp, tmd.value, tmd.metric, tmd.accept_date
FROM test_max_date tmd
JOIN (
    SELECT metric, MAX(accept_date) AS max_accept_date
    FROM test_max_date
    GROUP BY metric
) max_dates ON tmd.metric = max_dates.metric AND tmd.accept_date = max_dates.max_accept_date

方法3：多列IN子查询 #

通过(metric, accept_date)的组合匹配分组最大日期：

SELECT timestamp, value, metric, accept_date
FROM test_max_date
WHERE (metric, accept_date) IN (
    SELECT metric, MAX(accept_date)
    FROM test_max_date
    GROUP BY metric
)

为什么原PostgreSQL写法失效？ #

ClickHouse为适配大数据场景，默认不支持相关子查询（即子查询引用外部查询的列）。这类子查询需要逐行执行，在大数据量下性能极差，因此ClickHouse设计上限制了这种写法，要求子查询独立执行，不能依赖外部查询的列。

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