2015-01-07 23:48 GMT+01:00 Rami Ojares <[email protected]>: > 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 > > Interesting approach. Obviously, this would have to be critically reviewed from a performance perspective as most databases implement window functions rather efficiently. They can, because the function is applied on the previously materialised result set that is about to be projected by the SELECT clause. Adding JOINs can have quite different implications for the execution plan. I'd probably emulate this using a nested SELECT, to prevent accidental interference with WHERE, GROUP BY, and HAVING clauses that might be added as well.
How would you emulate ORDER BY and ROWS BETWEEN ... clauses? > 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; > It's a bit more complex if you have an even number of values in your set, then, the mean of the two values in the middle is expected. E.g. the median of 1, 2, 3, 10 is 2.5 -- 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.
