Clickhouse查询每日各product_id对应最大total值的实现问询

核心需求回顾 #

现有Clickhouse表包含plan_id、timestamp、client_id、account_id、product_id、total字段，字段关系：

• 单个client_id对应多个account_id，单个account_id对应多个product_id
• plan_id与product_id为1:1关系，同一account_id下不会重复出现同一product_id
• 需每日结束后，获取每个product_id在当日内（含跨天的最近有效记录）对应最大total值的完整行数据，当前每分钟执行查询但性能不佳。

优化后的查询方案 #

方案1：窗口函数+分区裁剪（单次查询优化） #

通过窗口函数筛选目标记录，结合时间范围裁剪减少数据扫描量：

WITH target_date AS (SELECT toDate(now()) - INTERVAL 1 DAY)
SELECT plan_id, timestamp, client_id, account_id, product_id, total
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY total DESC, timestamp DESC) AS rn
    FROM your_table_name
    WHERE 
        -- 覆盖当日及跨天的最近有效记录：取当日0点前1天内的最近1条 + 当日所有记录
        (timestamp >= toDateTime(target_date) - INTERVAL 1 DAY AND timestamp < toDateTime(target_date))
        OR timestamp >= toDateTime(target_date)
)
WHERE rn = 1

说明：target_date设为前一天，确保覆盖跨天场景；ROW_NUMBER()按total降序、timestamp降序排序，保证最大total优先，同total取最新时间的记录。

方案2：物化视图预计算（高频查询首选） #

针对每分钟执行的高频查询，用物化视图提前计算每日结果，查询时直接读取视图：

1. 创建物化视图：

CREATE MATERIALIZED VIEW mv_product_daily_max_total
ENGINE = MergeTree()
PARTITION BY toDate(timestamp)
ORDER BY (product_id, total DESC, timestamp DESC)
POPULATE
AS
SELECT 
    toDate(timestamp) AS record_date,
    plan_id,
    timestamp,
    client_id,
    account_id,
    product_id,
    total,
    ROW_NUMBER() OVER (PARTITION BY product_id, toDate(timestamp) ORDER BY total DESC, timestamp DESC) AS rn
FROM your_table_name

2. 查询前一日结果：

SELECT plan_id, timestamp, client_id, account_id, product_id, total
FROM mv_product_daily_max_total
WHERE record_date = toDate(now()) - INTERVAL 1 DAY AND rn = 1

说明：物化视图自动同步原表数据，按日期分区、product_id排序，查询时仅需过滤目标分区和rn=1，性能提升显著。

额外性能优化建议 #

• 表结构调整：
  • 主键设为(product_id, timestamp)，让Clickhouse快速定位每个产品的时间范围数据
  • 按toDate(timestamp)分区，大幅减少扫描的数据量
• 索引优化：为product_id添加布隆过滤器二级索引（INDEX product_idx product_id TYPE bloom_filter GRANULARITY 1），加速分区内的产品筛选
• 数据清理：定期清理过期历史数据，避免冗余数据拖慢查询速度

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