[CH] topk of row_number

[lgbo-ustc]

Description

Finding the top k rows of row_number is a common case. We compare following two ways

use aggregation

-- CH
SELECT
    x,
    y[1]
FROM
(
    SELECT
        x,
        groupArraySorted(1)(y) AS y
    FROM
    (
        SELECT
            rand() % 100000 AS x,
            rand() AS y
        FROM numbers(10000000)
    )
    GROUP BY x
)
FORMAT `Null`

Query id: c6630996-d937-4d62-b3f7-386d15cd3b3f

Ok.

0 rows in set. Elapsed: 0.370 sec. Processed 10.00 million rows, 80.00 MB (27.04 million rows/s., 216.28 MB/s.)
Peak memory usage: 13.02 MiB.

use window function

SELECT *
FROM
(
    SELECT
        x,
        y,
        row_number() OVER (PARTITION BY x ORDER BY y ASC) AS n
    FROM
    (
        SELECT
            rand() % 100000 AS x,
            rand() AS y
        FROM numbers(10000000)
    )
)
WHERE n <= 1
FORMAT `Null`

Query id: fd2ff438-2ab9-4644-ada4-3de48dec0eb4

Ok.

0 rows in set. Elapsed: 3.872 sec. Processed 10.00 million rows, 80.00 MB (2.58 million rows/s., 20.66 MB/s.)
Peak memory usage: 79.58 MiB.

The aggregation implement is more efficient .

[lgbo-ustc]

There is a transformer WindowGroupLimitExecTransformer, we could take advantage of this.