greatest-n-per-group - ClickHouse 中按组排列的前 N ​​行

Question

在ClickHouse中按组查询前N行的正确方法是什么？
让我们以具有 id2、id4、v3 列且 N=2 的 tbl 为例。我尝试了以下

SELECT                                                                          
    id2,                                                                        
    id4,                 
    v3 AS v3        
FROM tbl
GROUP BY                 
    id2,                 
    id4                  
ORDER BY v3 DESC                                                                
LIMIT 2 BY                       
    id2,                 
    id4      

但出现错误

Received exception from server (version 19.3.4):
Code: 215. DB::Exception: Received from localhost:9000, 127.0.0.1. DB::Exception
: Column v3 is not under aggregate function and not in GROUP BY..

我可以将 v3 放入 GROUP BY 中，它似乎确实有效，但按指标分组效率不高。

有 any 聚合函数，但我们实际上想要 all 值(由 LIMIT BY 子句限制为 2)而不是 any 值，所以这听起来不像是合适的解决方案。

SELECT                                                                          
    id2,                                                                        
    id4,                 
    any(v3) AS v3        
FROM tbl
GROUP BY                 
    id2,                 
    id4                  
ORDER BY v3 DESC                                                                
LIMIT 2 BY                       
    id2,                 
    id4      

Answer 1

可以使用aggregate functions像这样:

SELECT
    id2,
    id4,
    arrayJoin(arraySlice(arrayReverseSort(groupArray(v3)), 1, 2)) v3
FROM tbl
GROUP BY
    id2,
    id4