基于多条件抽样大型数据库：Clickhouse按日时段及类别抽足量样本

针对你需要从表中抽取每个类别在每日每小时维度下至少100条样本的需求，以下是两种适配ClickHouse的高效SQL方案，可根据实际场景选择：

方案1：保留所有满足条件组的全部数据 #

如果原数据中某个(日期+小时+类别)组的记录数≥100，就保留该组的所有数据：

WITH group_validity AS (
    SELECT
        toDate(recieved_time) AS stat_date,
        toHour(recieved_time) AS stat_hour,
        catgory,
        COUNT(*) AS record_count
    FROM your_table_name
    GROUP BY stat_date, stat_hour, catgory
    HAVING record_count >= 100
)
SELECT t.*
FROM your_table_name t
INNER JOIN group_validity g ON 
    toDate(t.recieved_time) = g.stat_date
    AND toHour(t.recieved_time) = g.stat_hour
    AND t.catgory = g.catgory

逻辑说明 #

1. 先用CTEgroup_validity统计每个时间维度+类别组的记录数，筛选出记录数≥100的有效组；
2. 通过关联原表，取出所有有效组的完整数据，确保每个目标维度下的样本数至少为100。

方案2：每个维度组固定抽取100条随机样本 #

如果需要控制数据量，每个有效组仅抽取100条随机样本（同时过滤掉记录数不足100的组）：

WITH group_validity AS (
    SELECT
        toDate(recieved_time) AS stat_date,
        toHour(recieved_time) AS stat_hour,
        catgory,
        COUNT(*) AS record_count
    FROM your_table_name
    GROUP BY stat_date, stat_hour, catgory
    HAVING record_count >= 100
)
SELECT id, recieved_time, catgory
FROM (
    SELECT
        t.*,
        row_number() OVER (
            PARTITION BY g.stat_date, g.stat_hour, g.catgory
            ORDER BY rand() -- 随机排序保证抽样的随机性
        ) AS sample_rank
    FROM your_table_name t
    INNER JOIN group_validity g ON 
        toDate(t.recieved_time) = g.stat_date
        AND toHour(t.recieved_time) = g.stat_hour
        AND t.catgory = g.catgory
) ranked_samples
WHERE sample_rank <= 100

逻辑说明 #

1. 同样先通过CTE筛选出记录数≥100的有效组；
2. 用窗口函数row_number()对每个有效组内的记录随机排序，取前100条作为样本，既满足数量要求，又避免返回过多数据。

性能优化建议 #

• 索引优化：确保recieved_time字段有合适的索引（比如DateTime类型的主键、二级索引或分区键），能大幅提升分组和关联的效率；
• 范围过滤：如果不需要全表数据，在CTE和原表查询中添加prewhere recieved_time BETWEEN 'start_time' AND 'end_time'，减少扫描的数据量；
• 抽样效率：ClickHouse的rand()函数生成随机数性能优异，适合快速实现随机抽样；如果需要更均匀的抽样，也可以结合sample子句，但窗口函数的方式更灵活可控。

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