Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Window Functions in SQL (2013)

blog.jooq.org

Post reply on HN
Print view View original

1–10 of 36 posts

Re: Window Functions in SQL (2013)

#3

Post by mmsmatt »

> Once you know window functions, you risk putting them all over the place

Agreed.

A few years of writing expressive, powerful, performant SQL in big blocks will really change your mental model of data across _all_ languages.

I highly recommend a deep dive into the relational world, and I feel super grateful for the SQL-focused job I held previously. Brought those lessons back with me to both FP and OO.

Re: Window Functions in SQL (2013)

#4

Post by thom »

I think my only real complaint about window functions is that you can't access the 'current' row in aggregates and 'filter' conditions. It means very complex look aheads or look behinds aren't really possible.

It would also be great if windows could be function parameters in Postgres. I like to abstract complicated conditionals and calculations into functions, but it falls down if you're operating over a window.

Re: Window Functions in SQL (2013)

#8

Post by jnordwick »

LAG/LEAD are basically SQL having to give in a little to the timeseries and column db people. It obliterates the set theoretic nature of SQL, and they are dog slow.

This is why any decent columnar/timeseries db has its own often arrish query language to take advantages of ordering information inherent in the data. There is an order in that index and SQL table, you just don't have good tools to access and use it.

This touches on a conversation I was having a couple weeks ago where somebody was saying that column/array storage is purely a backend consideration and don't need to care about it in the front end of the database. That view gets you ugly primitives like LAG/LEAD. Being able to exploit your backend decision is paramount to performance.

Products like Informix or KDB+ just give you full access to the data on and off disk as an array so you ca build complex windowing primitives (and even store them and reuse them later).

(PS: I just find this really funny. KDB+ is probably is the only database I know that literally does streaming analytics on a Raspberry Pi. No joke, the full DB on Linux ARM/Pi. https://kx.com/download/ )

Re: Window Functions in SQL (2013)

#9

Post by saurik »

jnordwick wrote: post #8

LAG/LEAD are basically SQL having to give in a little to the timeseries and column db people. It obliterates the set theoretic nature of SQL, and they are dog slow. This is why any decent columnar/timeseries db has its own often arrish query language to take advantages of ordering information inherent in the data. There is an order in that index and SQL table, you just don't have good tools to access and use it. This…

Exciting! Can you point at something where one could learn about this specific feature of these kinds of databases? (Maybe like, "this is an example of that aspect of their syntax in particular, go read how it works", or "here is an article taking about how awesome this property is".)

Re: Window Functions in SQL (2013)

#10

Post by koolba »

tracker1 wrote: post #7
okket wrote: post #2

"Modern SQL" feature overview for MySQL + forks: https://dveeden.github.io/modern-sql-in-mysql/ (aka "Look, window functions are coming")

Similarly, there's plv8 for PostgreSQL

> Similarly, there's plv8 for PostgreSQL

How is that similar? plv8 is a procedural language (javascript) for writing user defined functions.

Post reply on HN