ClickHouse计算每条记录前24小时同用户记录数的问题求助

需求 #

计算每条记录生成时，该用户在之前24小时内产生的记录数。传统SQL的关联子查询写法在ClickHouse中执行报错。

示例源表 #

ID | time          | name  
---+---------------+---------
1   05/05 14:20       bob         
2   05/05 14:30       josh       
3   05/05 18:30       bob         
4   06/05 15:30       bob         
5   08/05 18:30       josh           

期望查询结果 #

ID | time          | name  | nb_last_24hours
---+---------------+-------+----------------
1   05/05 14:20       bob         0
2   05/05 14:30       josh        0
3   05/05 18:30       bob         1
4   06/05 15:30       bob         1
5   08/05 18:30       josh        0

尝试的查询语句（报错写法） #

SELECT  
    b.ID, b.`time`, b.name,
    (SELECT COUNT(*) 
     FROM T1 AS a 
     WHERE a.name = b.name 
       AND a.`time` < b.`time` 
       AND a.`time` >= (b.`time` - (60 * 60 * 24))
    ) AS nb_last_24hours
FROM  
    T1 AS b;

报错信息 #

SQL Error [47] [07000]: Code: 47. DB::Exception: Missing columns: 'b.time' 'b.a_msisdn' while processing query: 'SELECT count() FROM VFG.voice_traffic AS a WHERE (a_msisdn = b.a_msisdn) AND (time < b.time) AND (time >= (b.time - ((60 * 60) * 24)))', required columns: 'a_msisdn' 'b.a_msisdn' 'time' 'b.time', maybe you meant: ['a_msisdn','a_msisdn','time','time']: While processing (SELECT count(*) FROM VFG.voice_traffic AS a WHERE (a.a_msisdn = b.a_msisdn) AND (a.time < b.time) AND (a.time >= (b.time - ((60 * 60) * 24)))) AS _subquery4916. (UNKNOWN_IDENTIFIER) (version 22.3.3.44 (official build))
, server ClickHouseNode [uri=http://192.168.13.79:8123/default, options={use_server_time_zone=false,use_time_zone=false}]@-1433098529

问题原因 #

ClickHouse不支持传统SQL中这种关联子查询（子查询引用外部查询的表别名b），子查询无法识别外部上下文的字段，因此抛出未知标识符错误。

正确实现方案 #

ClickHouse中推荐使用窗口函数结合范围窗口来实现该需求，性能远优于关联子查询，适配列式存储特性：

SELECT
    ID,
    `time`,
    name,
    COUNT(*) OVER (
        PARTITION BY name
        ORDER BY toUnixTimestamp(`time`)
        RANGE BETWEEN 86400 PRECEDING AND 1 PRECEDING
    ) AS nb_last_24hours
FROM T1
ORDER BY ID;

关键说明 #

• PARTITION BY name：按用户分组，仅统计当前用户的历史记录
• ORDER BY toUnixTimestamp(time)：将时间字段转换为时间戳，用于计算时间范围
• RANGE BETWEEN 86400 PRECEDING AND 1 PRECEDING：定义窗口范围为当前记录时间戳往前推24小时（86400秒）到当前记录的前一秒，排除当前记录本身，确保统计的是之前24小时内的历史记录数

执行该语句即可得到符合预期的结果。

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