Condition pushdown: Window function is found in HAVING in query.

[UnamedRus]

Describe what's wrong

ClickHouse pushdown condition for Window Function

Does it reproduce on recent release?

Yes, 23.2

LAPTOP-. :) SELECT * FROM (SELECT dense_rank() OVER (ORDER BY number DESC) AS row FROM numbers(10)) WHERE row < 3;

SELECT *
FROM
(
    SELECT dense_rank() OVER (ORDER BY number DESC) AS row
    FROM numbers(10)
)
WHERE row < 3

┌─row─┐
│   1 │
│   2 │
└─────┘

2 rows in set. Elapsed: 0.001 sec.

LAPTOP-. :) SELECT * FROM (WITH dense_rank() OVER (ORDER BY number DESC) AS row SELECT row FROM numbers(10)) WHERE row < 3;

SELECT *
FROM
(
    WITH dense_rank() OVER (ORDER BY number DESC) AS row
    SELECT row
    FROM numbers(10)
)
WHERE row < 3


0 rows in set. Elapsed: 0.001 sec.

Received exception:
Code: 184. DB::Exception: Window function row is found in HAVING in query: While processing dense_rank() OVER (ORDER BY number DESC) AS row. (ILLEGAL_AGGREGATION)

Expected behavior

Query works

[den-crane]

allow_experimental_analyzer

SELECT *
FROM
(
    WITH dense_rank() OVER (ORDER BY number DESC) AS row
    SELECT row
    FROM numbers(10)
)
WHERE row < 3
SETTINGS allow_experimental_analyzer = 1

Query id: 496bd4eb-5697-4710-b416-37c183ede575

┌─row─┐
│   1 │
│   2 │
└─────┘

[binque]

I have a SQL query with window functions, and I'm wondering how to optimize it. The query is as follows:

SELECT *
FROM
(
    SELECT
        dense_rank() OVER (ORDER BY avg1 DESC) AS row,
        *
    FROM
    (
        SELECT
            number,
            avg(number) OVER (ORDER BY number ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS avg1
        FROM numbers(10)
    )
)
WHERE row < 3;

How can I optimize or push down the window functions in this query for better performance? @den-crane

[UnamedRus]

SELECT *
FROM
(
    WITH dense_rank() OVER (ORDER BY number DESC) AS row
    SELECT row
    FROM numbers(10)
)
WHERE row < 3

┌─row─┐
│   1 │
│   2 │
└─────┘


24.3