These window functions seem to me like just a shorthand notation.
Here is an example from postgresql documentation.
http://www.postgresql.org/docs/9.1/static/tutorial-window.html
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM
empsalary;
This is just another way to say
SELECT
e1.*, e2.avg
FROM
empsalary e1
JOIN (
SELECT depname, AVG(salary) as avg
FROM empsalary
GROUP BY depname
) e2 ON e1.depname = e2.depname
SELECT MEDIAN(salary) FROM empsalary;
is just a shorthand for
SELECT salary
FROM empsalary
ORDER BY salary
LIMIT 1 OFFSET SELECT (COUNT(salary)+1)/2 FROM empsalary;
- Rami
On 7.1.2015 20:31, Lukas Eder wrote:
Hello,
Much more interesting than the MEDIAN() aggregate function itself are
inverse distribution functions, or ordered-set aggregate functions in
general. MEDIAN(xyz) can be emulated via PERCENTILE_CONT(0.5) WITHIN
GROUP (ORDER BY xyz), as documented in this blog post:
http://blog.jooq.org/2015/01/06/how-to-emulate-the-median-aggregate-function-using-inverse-distribution-functions
Have ordered-set aggregate functions been considered in H2, before? I
know, window functions are also on the roadmap. It would be awesome if
these features were a part of H2 ;-)
Cheers
Lukas
--
You received this message because you are subscribed to the Google
Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
