Question regarding new windowing functions in 8.4devel

On Thu, Jan 15, 2009 at 03:06:47PM +0100, A. Kretschmer wrote:

Hi,

first, many thanks to all for the great work, i'm waiting for 8.4.

I have played with the new possibilities:

test=# select typ, ts, rank() over (partition by typ order by ts desc ) from foo;
typ | ts | rank
-----+-------------------------------+------
1 | 2009-01-15 13:03:57.667631+01 | 1
1 | 2009-01-15 13:03:56.554659+01 | 2
1 | 2009-01-15 13:03:55.694803+01 | 3
1 | 2009-01-15 13:03:54.816871+01 | 4
1 | 2009-01-15 13:03:53.521454+01 | 5
2 | 2009-01-15 13:04:02.223655+01 | 1
2 | 2009-01-15 13:04:01.30692+01 | 2
2 | 2009-01-15 13:04:00.05923+01 | 3
3 | 2009-01-15 13:04:14.27154+01 | 1
3 | 2009-01-15 13:04:05.395805+01 | 2
3 | 2009-01-15 13:04:04.365645+01 | 3
4 | 2009-01-15 13:04:11.54897+01 | 1
4 | 2009-01-15 13:04:10.778115+01 | 2
4 | 2009-01-15 13:04:10.013001+01 | 3
4 | 2009-01-15 13:04:09.324396+01 | 4
4 | 2009-01-15 13:04:08.523507+01 | 5
4 | 2009-01-15 13:04:07.375874+01 | 6
(17 rows)

Okay, fine.

Now i want only 3 records for every typ:

test=# select typ, ts, rank() over (partition by typ order by ts desc ) from foo where rank <= 3;
ERROR: column "rank" does not exist
LINE 1: ...rtition by typ order by ts desc ) from foo where rank <= 3;

I tried this:

SELECT
typ,
ts,
rank() over w AS foo_rank
FROM
foo
WINDOW w AS (partition by typ order by ts desc)
WHERE
foo_rank < 4;

ERROR: syntax error at or near "WHERE"
LINE 8: WHERE
^
Possibly the above is not a bug, but I'm pretty sure this is:

SELECT
typ,
ts,
rank() over w AS foo_rank
FROM
foo
WINDOW w AS (partition by typ order by ts desc)
WHERE
typ < 4;

ERROR: syntax error at or near "WHERE"
LINE 8: WHERE
^

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Fri, Jan 16, 2009 at 12:07 PM, David Fetter <david@fetter.org> wrote:

Now i want only 3 records for every typ:

test=# select typ, ts, rank() over (partition by typ order by ts desc ) from foo where rank <= 3;
ERROR: column "rank" does not exist
LINE 1: ...rtition by typ order by ts desc ) from foo where rank <= 3;

maybe the rank should go in a having clause? i'm not familiar about
window functions yet... just guessing...

I tried this:

SELECT
typ,
ts,
rank() over w AS foo_rank
FROM
foo
WINDOW w AS (partition by typ order by ts desc)
WHERE
foo_rank < 4;

ERROR: syntax error at or near "WHERE"
LINE 8: WHERE
^

the WINDOW specification goes after the WHERE clause not before

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

On Fri, Jan 16, 2009 at 12:23:16PM -0500, Jaime Casanova wrote:

On Fri, Jan 16, 2009 at 12:07 PM, David Fetter <david@fetter.org> wrote:

Now i want only 3 records for every typ:

test=# select typ, ts, rank() over (partition by typ order by ts desc ) from foo where rank <= 3;
ERROR: column "rank" does not exist
LINE 1: ...rtition by typ order by ts desc ) from foo where rank <= 3;

maybe the rank should go in a having clause? i'm not familiar about
window functions yet... just guessing...

I tried HAVING, too, and it's epic fail. :(

ERROR: syntax error at or near "WHERE"
LINE 8: WHERE
^

the WINDOW specification goes after the WHERE clause not before

Thanks :)

Still remaining is our inability to limit windowing functions other
than via subselect or equivalently CTEs. I believe this is a bug.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david@fetter.org> writes:

I tried this:

SELECT
typ,
ts,
rank() over w AS foo_rank
FROM
foo
WINDOW w AS (partition by typ order by ts desc)
WHERE
foo_rank < 4;

ERROR: syntax error at or near "WHERE"
LINE 8: WHERE
^

RTFM ... WINDOW goes after WHERE (and GROUP BY, HAVING, ...)

Also, we have never allowed SELECT-alias references in WHERE;
window functions have nothing to do with that.

regards, tom lane

On Fri, Jan 16, 2009 at 12:34:34PM -0500, Tom Lane wrote:

David Fetter <david@fetter.org> writes:

I tried this:

SELECT
typ,
ts,
rank() over w AS foo_rank
FROM
foo
WINDOW w AS (partition by typ order by ts desc)
WHERE
foo_rank < 4;

ERROR: syntax error at or near "WHERE"
LINE 8: WHERE
^

RTFM ... WINDOW goes after WHERE (and GROUP BY, HAVING, ...)

Thanks :)

Also, we have never allowed SELECT-alias references in WHERE; window
functions have nothing to do with that.

We don't appear to be able to use the actual thing in the target list
either. At a minimum, this is a pretty enormous POLA violation, and I
think it rises to the level of a bug.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david@fetter.org> writes:

We don't appear to be able to use the actual thing in the target list
either.

Would you translate that into English? Or at least an example without
trivial syntax errors?

regards, tom lane

On Fri, Jan 16, 2009 at 12:41:59PM -0500, Tom Lane wrote:

David Fetter <david@fetter.org> writes:

We don't appear to be able to use the actual thing in the target list
either.

Would you translate that into English? Or at least an example without
trivial syntax errors?

This works:

SELECT
typ,
ts,
rank() over w AS foo_rank
FROM
foo
WHERE
typ < 4
WINDOW w AS (partition by typ order by ts desc);

This doesn't:

SELECT
typ,
ts,
rank() over w AS foo_rank
FROM
foo
WHERE
rank() over w < 4
WINDOW w AS (partition by typ order by ts desc);

ERROR: window functions not allowed in WHERE clause
LINE 8: rank() over w < 4

This doesn't either, going with a "windows are like aggregates" theory:

SELECT
typ,
ts,
rank() over w AS foo_rank
FROM
foo
HAVING
rank() over w < 4
WINDOW w AS (partition by typ order by ts desc);
ERROR: column "foo.typ" must appear in the GROUP BY clause or be used
in an aggregate function
LINE 2: typ,
^

Basically, there is no way I've found so far to qualify any window
function in the target list, which makes a giant POLA violation. With
any item in the target list other than a window function, it's
possible to qualify it either in the WHERE clause for non-aggregates
or in the HAVING clause for aggregates.

While we probably don't want to open the "qualify by alias" can of
worms, we might want to make it at least possible to add qualifiers to
window functions short of CTEs/subselects.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david@fetter.org> writes:

Basically, there is no way I've found so far to qualify any window
function in the target list, which makes a giant POLA violation.

The FM points out in at least two places that window functions logically
execute on the output of the WHERE/GROUP BY/HAVING steps. It's
conceptually nonsensical to have window function calls in those clauses,
just like it's conceptually nonsensical to use aggregates in WHERE.

Therefore, if you need to filter on the results of the window functions,
you put them into a sub-select and write the filter condition in the
outer query. This is required by spec, and it really does not matter
whether you find it astonishing.

regards, tom lane