8 ms·
Window Functions in SQL (2013)
- okket 10y ago"Modern SQL" feature overview for MySQL + forks: https://dveeden.github.io/modern-sql-in-mysql/ https://dveeden.github.io/modern-sql-in-mysql/ (aka "Look, window functions are coming")
- chucky_z 10y agoDoing some research looks like they're not that close. =( https://jira.mariadb.org/browse/MDEV-6115 https://jira.mariadb.org/browse/MDEV-6115
- tracker1 10y agoSimilarly, there's plv8 for PostgreSQL
- koolba 10y ago> Similarly, there's plv8 for PostgreSQL How is that similar? plv8 is a procedural language (javascript) for writing user defined functions.
- mmsmatt 10y ago> 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.
- nickpeterson 10y agoThey are extremely useful, although they seem to vary in performance quite a bit (SQL Server experience here, so YMMV). I'm always a bit shocked how little SQL most software developers know, especially because it's often the bottleneck if used poorly.
- thom 10y agoI 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.
- lukaseder 10y agoWould you mind providing an example of what you'd like to do, which is too complex for window functions?
- thom 10y agoApologies for contrived examples. First, it'd be nice to be able to use both rows inside some aggregates, e.g.: min(ST_Distance(this.point, foo.point)) over (blah) That is, for each 'foo', get me the minimum distance to another foo in the current window. The second peeve is to do with filters, e.g.: count(*) filter (where this.time - foo.time < 5 and type_id = 42) over (blah) That is, how many events of type 42 are there up to 5 seconds before the current event. In both cases, it's sad not being able to address the current row as well as the row in the window. I'm not saying either form is completely impossible to get working somehow, I just think it'd be a nice feature.
- lukaseder 10y agoThanks for clarifying. Oh, I see yes indeed. We can only nest aggregate functions inside of window functions, not the other way round (unless resorting to derived tables, views, ctes, etc.). Indeed, it's logical to know why it's not possible (the way the language was designed), but the language could have been designed differently. Do note that Oracle has the KEEP clause that might probably solve your issue with aggregate functions only... But it's a rather esoteric, vendor-specific extension. Anyway, I do agree, what you're describing would be a very nice feature.
- taeric 10y agoSo, yeah. Window functions are new to me. I was actually asking the team about a month ago if there was a way to do this in SQL. Consider my mind blown, and thanks for posting!
- jnordwick 10y agoLAG/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/ https://kx.com/download/ )
- saurik 10y agoExciting! 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".)
- jnordwick 10y agoNot exciting. Most of the array processing / streaming facilities are steaming piles. You have Hadoop/MR and Monet in the OSS group. SysbaseIQ, KDB, Teradata, Vertica, VectorWise, etc. all provide either extended SQL or additional query support to use ordering information in some (often weak) way. You can even count Oracle In Memory Analytics with TSQL with its cursor model if you really want. For example for KDB to get the last price and sum of volumer for each second of AAPL you would do in KSQL: select last px, sum vol by time.second from trade where sym=`AAPL on it can be done in its lower level language K/Q as (approx, I'm a little rusty) g:group trade.time.second @ where trade.sym=`AAPL flip`px`vol!(last each trade.px g; sum each trade.vol g)
- cygned 10y agoThe most useful feature imho is temporal data. Too bad Postgres does not support it.
- teilo 10y agoWindows functions are a feature I cannot live without. They achieve results that otherwise could only be achieved with complex and ugly CASE and embedded SELECTs. Another reason Postgres is superior to MySQL.