Introduction to Window Functions in SQL
khashtamov.com
Introduction to Window Functions in SQL
1–10 of 47 posts
Re: Introduction to Window Functions in SQL
#2Introduction to Window Functions with the same examples (salaries per department)
https://www.postgresql.org/docs/13/tutorial-window.html
While you are at it, check out common table expressions as well
Re: Introduction to Window Functions in SQL
#3Finally, comprehensive article about Windows Functions.
Re: Introduction to Window Functions in SQL
#4Introduction to Window Functions with the same examples (salaries per department) https://www.postgresql.org/docs/13/tutorial-window.html While you are at it, check out common table expressions as well https://www.postgresql.org/docs/13/queries-with.html
okay, thanks :)
Re: Introduction to Window Functions in SQL
#5Previous discussion on SQL Window functions: https://news.ycombinator.com/item?id=20872114
Helpful resource: http://www.windowfunctions.com/
Re: Introduction to Window Functions in SQL
#6I've always wondered and perhaps someone here might know....do postgres' CTEs translate into big select/sub select queries under the hood? Or are they something special entirely? Ie (forgive formatting as I'm on phone) does:
With mything as ( Select * from table where... ),
Myotherthing as ( Select * from mything where... )
Get translated to
Select * from (select * from ( select * from...)...)...)
So I'm just wondering are CTEs just easier to read, or do they offer any other known optimizations? We use them loads but mainly just for keeping larger queries easier to manage
Re: Introduction to Window Functions in SQL
#7Finally, comprehensive article about Windows Functions.
I thought the same. Assumed it was some Microsoft BS. Thankfully it's not :D
Re: Introduction to Window Functions in SQL
#8Previous discussion on SQL Window functions: https://news.ycombinator.com/item?id=20872114 Helpful resource: http://www.windowfunctions.com/
i would also recommend the wonderful https://modern-sql.com/
Re: Introduction to Window Functions in SQL
#9I've always wondered and perhaps someone here might know....do postgres' CTEs translate into big select/sub select queries under the hood? Or are they something special entirely? Ie (forgive formatting as I'm on phone) does: With mything as ( Select * from table where... ), Myotherthing as ( Select * from mything where... ) Get translated to Select * from (select * from ( select * from...)...)...) So I'm just wonderi…
In theory, both forms should get optimized similarly by the DB, but the practice will likely differ from database to database, and maybe even from DB version to DB version.
There are though things that CTEs can do and sub-selects can't (e.g. WITH RECURSIVE)
Re: Introduction to Window Functions in SQL
#10I've always wondered and perhaps someone here might know....do postgres' CTEs translate into big select/sub select queries under the hood? Or are they something special entirely? Ie (forgive formatting as I'm on phone) does: With mything as ( Select * from table where... ), Myotherthing as ( Select * from mything where... ) Get translated to Select * from (select * from ( select * from...)...)...) So I'm just wonderi…
Not 100% about the newest versions of Postgres. But certainly in older versions CTEs created query planner boundaries.
So the planner would optimise each CTE separately, but wouldn’t optimise the entire query with all CTEs together, which of course can result in some slightly nonsensical query plans.
To my knowledge this is considered a limitation rather than a feature as it can cause performance issues with some queries.
EDIT: Some quick Googling suggests the CTEs are no longer optimisation fences, and their handling can be tweaked on a CTE by CTE basic in Postgres 12 onwards [1]
[1] https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...