Which window function is faster?-CodePudding

count(*) OVER (PARTITION BY a, b ORDER BY a, b, c) * 10

This produces the same result as:

dense_rank() OVER (PARTITION BY a, b ORDER BY a, b, c) * 10

Used in a query like this:

SELECT
    dense_rank() OVER (ORDER BY a, b) ,
    a || b,
    count(*) OVER (
        PARTITION BY a, b
        ORDER BY a, b, c
    ) * 10 ,
    a2,
    b1,
    c1,
    cc1,
    c2,
FROM
  join ....
ORDER BY 1, 6;

I'm happy with my query result.
But should I appreciate one approach over the other and why?

CodePudding user response：

After PARTITION BY a, b there is no point in adding aor b to ORDER BY, like David commented.

So we simplify to:

count(*)     OVER (PARTITION BY a, b ORDER BY c) * 10
dense_rank() OVER (PARTITION BY a, b ORDER BY c) * 10

These two only happen to be equivalent while c is UNIQUE. Else they are not.

You'd need to define exactly what the number is supposed to signify, and your table definition, and the exact query because joins can introduce duplicates and NULL values.

row_numer() or rank() are similar window functions ...

Performance is practically the same for all of them.